floating point exception - unexplainable - even after SP4 still ge

I am in the process of translating Access 97 Databases into SQL, and working
through many very complex interrelated queries which work fine in Access. On
translating many queries all work fine apart from when I get to the top leve
l
query which effectively nests many level of queries. On trying to display
this top level view I get the horrible
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction
is canceled.
Now I have tried to narrow the problem down, even removed any floating point
datatypes from the view but I still get the problem.
I now have a view which works if I join 3 tables but on trying to join 4
tables gives the exception. It does not matter which table I miss out...
Am I hitting some limit of SQL which does not exist in Access 97? Or is
these a nasty bug floating around which cannot handle nested views of a
certain level...
Could some clever person please could come back to this posting urgently
with when there will be another fix for this problem, or email me at
getalifestyle@.easyget.bizHi
The fix as described in
http://support.microsoft.com/defaul...kb;en-us;892840 is not
included in SP4, so you need to get a seperate hotfix for it from Microsoft.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Giergiel" wrote:

> I am in the process of translating Access 97 Databases into SQL, and worki
ng
> through many very complex interrelated queries which work fine in Access.
On
> translating many queries all work fine apart from when I get to the top le
vel
> query which effectively nests many level of queries. On trying to display
> this top level view I get the horrible
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current transacti
on
> is canceled.
> Now I have tried to narrow the problem down, even removed any floating poi
nt
> datatypes from the view but I still get the problem.
> I now have a view which works if I join 3 tables but on trying to join 4
> tables gives the exception. It does not matter which table I miss out...
> Am I hitting some limit of SQL which does not exist in Access 97? Or is
> these a nasty bug floating around which cannot handle nested views of a
> certain level...
> Could some clever person please could come back to this posting urgently
> with when there will be another fix for this problem, or email me at
> getalifestyle@.easyget.biz
>|||Hi
Can you please post the SQL Script here, so that we can try to give u a
solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

float in VB-Skript

Hi there
I have two Databases
in both databases are fields with float - no null
If I am transfering data from one database to the other everything works well unless there is a comma in the field ( 0,99 or 123,456 )

"SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti kelbezeichnung,Artikeltext1,EDVEingang ,EDVAusgang,InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & EDVEingang & "," & EDVAusgang & "," & ArtMenge & ")"
"
where EDVEingang and EDVAusgang are defined as float, no null

Then the programm stops with the following message:
Within the INSERT-Procedure there are less columns then there are Contents in the Value-Clause.

I have to finish the programm until tomorrow morning and don't know what the problem is.

If anybody has an idea, please let me know.

regards
Reineruse cast(columnname as float) to avoid this kind of errors !!!|||Thanks for your quick answer,
but if I'm trying this :
SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti kelbezeichnung,Artikeltext1,cast(EDVEingang as float),EDVAusgang,InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & EDVEingang & "," & EDVAusgang & "," & ArtMenge & ")"

I'm getting the error: Wrong Syntas near "("

regards|||SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti
kelbezeichnung,Artikeltext1,cast(EDVEingang as float),EDVAusgang,InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "EDVEingang & "," & EDVAusgang & "," & ArtMenge & ")"

You need to use this
SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti
kelbezeichnung,Artikeltext1,EDVEingang,EDVAusgang, InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "cast (" & EDVEingang &" as float) ," & EDVAusgang & "," & ArtMenge & ")"|||Originally posted by Enigma
You need to use this
SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti
kelbezeichnung,Artikeltext1,EDVEingang,EDVAusgang, InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "cast (" & EDVEingang &" as float) ," & EDVAusgang & "," & ArtMenge & ")"

Thanks again.
I did as you advised and got the error : wrong syntas near "as"

SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti kelbezeichnung,Artikeltext1,EDVEingang,EDVAusgang, InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "cast (" & EDVEingang & " as float)," & EDVAusgang & "," & ArtMenge & ")"|||Try this

SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti
kelbezeichnung,Artikeltext1,EDVEingang,EDVAusgang,
InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "convert(float," & EDVEingang & ")," & EDVAusgang & "," & ArtMenge & ")"|||Originally posted by Enigma
Try this

SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti
kelbezeichnung,Artikeltext1,EDVEingang,EDVAusgang,
InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "convert(float," & EDVEingang & ")," & EDVAusgang & "," & ArtMenge & ")"

I have tried this. No error is coming up any more, but all decimal values are rounded: 263,2569 => 263 142,7402 => 142|||Originally posted by ReinerS
I have tried this. No error is coming up any more, but all decimal values are rounded: 263,2569 => 263 142,7402 => 142

Is there anybody who has an idea about the problem with the figures after the decimal point?|||You sure?

What's the DDL of the Table?|||Originally posted by Brett Kaiser
You sure?

What's the DDL of the Table?

What do you mean with DDL of the Table|||Data Definition Language (DDL)...

Go to Enterprise Manager (EM) right click on the table go to all tasks, script database...preview Copy and paste the CREATE TABLE myTable99 (Col1 int, ect...

statement here...|||Originally posted by ReinerS
Thanks again.
I did as you advised and got the error : wrong syntas near "as"
SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti kelbezeichnung,Artikeltext1,EDVEingang,EDVAusgang, InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & "cast (" & EDVEingang & " as float)," & EDVAusgang & "," & ArtMenge & ")"

I suspect that "cast .. as .." was intended to be a visual basic function-call not part of the SQL statement but I'm not quite sure.

Is the "locale" setting for this workstation in-sync with what the SQL server expects?

The bottom line is: Different countries obviously use different meanings for "," vs. "." in a number and both the DBMS and the workstation should, in their own way, be prepared to handle it once you (in the appropriate way, whatever it is) tell them to do so. There may be options that you can set for one of the connection objects. Look for "national language" and so-on in the various pieces of documentation/help. Or "internationalization."

This is a classic internationalization-issue, and it has been "solved."|||Originally posted by Brett Kaiser
Data Definition Language (DDL)...

Go to Enterprise Manager (EM) right click on the table go to all tasks, script database...preview Copy and paste the CREATE TABLE myTable99 (Col1 int, ect...

statement here...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InventurDaten]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[InventurDaten]
GO

CREATE TABLE [dbo].[InventurDaten] (
[Artikelnummer] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Hauptartikelnummer] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Auspraegung] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Artikelbezeichnung] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Artikeltext1] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[EDVEingang] [float] NULL ,
[EDVAusgang] [float] NULL ,
[InventurmengeEDV] [float] NULL ,
[Inventurdatum] [datetime] NULL ,
[Inventurzaehlmenge] [float] NULL ,
[Inventurdifferenz] [float] NULL ,
[EKPreis] [float] NULL ,
[Inventurbetrag] [float] NULL ,
[Inventurdifferenzbetrag] [float] NULL ,
[EDVWertEingang] [float] NULL ,
[EDVWertAusgang] [float] NULL ,
[EDVUmsatzJahr] [float] NULL ,
[EDVRohertragJahr] [float] NULL ,
[EDVEinstandspreis] [float] NULL ,
[EDVRabatte] [float] NULL ,
[EDVWertProduktion] [float] NULL ,
[EDVMengeProduktion] [float] NULL ,
[EDVBezugskosten] [float] NULL
) ON [PRIMARY]
GO|||Please post the ddl for both tables.|||Originally posted by rnealejr
Please post the ddl for both tables.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T0302003]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T0302003]
GO

CREATE TABLE [dbo].[T0302003] (
[C008] [float] NOT NULL ,
[C009] [float] NOT NULL ,
[C012] [float] NOT NULL ,
[C013] [float] NOT NULL ,
[C015] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
[C017] [float] NOT NULL ,
[C018] [float] NOT NULL ,
[C019] [float] NOT NULL ,
[C021] [datetime] NULL ,
[C022] [datetime] NULL ,
[C024] [float] NOT NULL ,
[C026] [float] NOT NULL ,
[C031] [float] NOT NULL ,
[C032] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[C033] [float] NOT NULL ,
[C034] [float] NOT NULL ,
[C042] [float] NOT NULL ,
[C044] [float] NOT NULL ,
[C054] [float] NOT NULL ,
[C059] [float] NOT NULL ,
[C060] [datetime] NULL ,
[C079] [int] NOT NULL ,
[C100] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[C101] [datetime] NULL ,
[C102] [float] NOT NULL ,
[C103] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[C104] [datetime] NULL ,
[C105] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[C118] [float] NOT NULL ,
[C119] [float] NOT NULL ,
[C120] [float] NOT NULL ,
[ts] [timestamp] NULL
) ON [PRIMARY]
GO

[C012] => EDVWertEingang
[C013] => EDVWertAusgang|||Where are the values "& EDVEingang & "," & EDVAusgang " coming from that you are using in your insert statement and what data types are these in your vbscript ?|||Originally posted by rnealejr
Where are the values "& EDVEingang & "," & EDVAusgang " coming from that you are using in your insert statement and what data types are these in your vbscript ?

SQLString = "Select C008, C009, C012, C013, C015, C024, C026, C031, C033, C042, C044, C102 from T0302003"

EDVWertEingang = DoArtikel("C012")
EDVWertAusgang = DoArtikel("C013")

types are not declared|||When you perform this select in query analyzer, what do the float values look like that are returned ? Also, in this same table, if you insert a value like 3.456, will it accept it and what is returned by query analyzer if it is inserted successfully ?|||Originally posted by rnealejr
When you perform this select in query analyzer, what do the float values look like that are returned ? Also, in this same table, if you insert a value like 3.456, will it accept it and what is returned by query analyzer if it is inserted successfully ?

Result from query

Select C008, C009, C012, C013, C015, C024, C026, C031, C033, C042, C044, C102 from T0302003

225.0 147.0 263.25689999999997 171.9949 100121010H 316.05000000000001 144.05510000000001 1.170026 0.0 0.0 0.0 0.0|||Originally posted by ReinerS
Result from query

Select C008, C009, C012, C013, C015, C024, C026, C031, C033, C042, C044, C102 from T0302003

225.0 147.0 263.25689999999997 171.9949 100121010H 316.05000000000001 144.05510000000001 1.170026 0.0
0.0 0.0 0.0

Insert into Inventurdaten (Artikelnummer, EDVWertEingang) values ( 111111, 14.123123)

111111 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 14.123123 NULL NULL NULL NULL NULL NULL NULL NULL|||In your vbscript you can use the replace function - to replace the comma with a period.|||Originally posted by ReinerS

EDVWertEingang = DoArtikel("C012")
EDVWertAusgang = DoArtikel("C013")

types are not declared


Does that mean they are variant by default?

Why not declare them as float?|||Originally posted by Brett Kaiser
Does that mean they are variant by default?

Why not declare them as float?

I have already tried to declare them as float, but vbscript does not know float

How do I replace the comma by a point?|||I think Brett means single or double.|||Originally posted by ReinerS
I have already tried to declare them as float, but vbscript does not know float

How do I replace the comma by a point?

I found the replace command and...

you're great

it works

Thanks a lot|||replace(xxxx,",",".")|||Originally posted by rnealejr
I think Brett means single or double.

You would think he means that

What a maroon

what a gull-a-bull|||Dang ReinerS - you kept responding when I was responding.

Happy to help.

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> ****************************************
***************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> ****************************************
***************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> strange
rebooted
after
two
> flip
> the
databases?.
>

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> *******************************************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> *******************************************************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have three user databases on my production server and I noticed a
> strange
> > behavior whenever the server gets rebooted. on two of the databases the
> > recovery model flips back from Full to simple when the server is
rebooted
> > any time. On the third database the recover model stays as Full even
after
> > the reboot. Even after I switch the recovery model back to full on the
two
> > databases what I see is, as soon as the server gets rebooted, they both
> flip
> > back to simple.
> >
> > I am confused as to why it is happening only with two databases and not
> the
> > third. Is there something that is set up wrongly on these two
databases?.
> > Any insight is highly helpful.
> >
> > Thanks
> >
> > Meher
> >
> >
>

Flattening SQL Query result?

Hey all,

I have two different databases that store the same info. Sometimes the data doesn't match, and I have been asked to come up with a query to find the decrepencies.

That data is an employees payrates for StraightTime OverTime and Double time. The problem is they store the data differently.

System 1 stores it

EmployeeID - Name - StraightTime - OverTime - Doubletime

System 2 Stores it

EmployeeTable:

EmployeeID - Name

RateTable

EmployeeID - Rate - RateType

Id like to return the data in a single row:

EmployeeID - Name - System1StraightTime - System2StraightTime- System1OverTime - System2OverTime ...

I tried Left Joining the System2.RateTable 3x, but it will only return the first join's records, and then the second and third join's records appear ONLY if the the record if also wrong with the first joins rows.

Is there a way to do this in SQL, of do I need to bit the bullet, and run the query 3x, and then assemble them in code with a loop?

Thanks

Eric Wild

You might want to try a FULL JOIN on the System 1 table and the RateTable and then LEFT JOIN that combination to the Employee table ON COALESCE(system1Table.EmployeeID, RateTable.EmployeeID) = EmployeeTable.EmployeeID.

FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what you´ve done. Ok, it´s kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegroups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.

FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.
IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what youve done. Ok, its kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegr oups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>
|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.

FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what youve done. Ok, its kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegroups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.sql

FK Index Advice

I have a couple custom database that I am building. They will be 3NF with several many to many linking tables.

In general, both databases will have about 30 tables. MOST tables will have an autonumber PK, and two FK fields related to PK's on another table. My question is about indexing...

I will not be designing the application(s) using the DB's (as far as ASP code, VB script) etc, so I am not familiar with the SQL to be used, nor am I confident that my developers will run the code by me before the system is done.

Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this? Or, should I have one index that covers both FK columns rather than two indexes? Any thoughts? Please provide some reasoning with any answers.

Thanks in advance!

Ryan HuntRun the CPU intensive statements through the Index Tuning Wizard (ITW) in order to get any index recommendations that may be necessary.

Review information from this http://www.sql-server-performance.com/mr_indexing.asp link on choosing indexes.

FK from other database

how to solve that problem?
i have 2 databases, in few tables from one database i need foreign keys from
other databases.Cant do it. You could try using triggers and stored procedures to check for
consistency but you can't use FK across databases.
MC
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:OjfFmHtAFHA.1300@.TK2MSFTNGP14.phx.gbl...
> how to solve that problem?
> i have 2 databases, in few tables from one database i need foreign keys
> from other databases.
>

fixing orphan users

Hi All,
I'm trying to fix all the orphans users for all the databases in my sql
server 2000 using the code above, but I'm getting an error that the second
cursor already exist.
Can I do a cursor inside another one?
How can I fix this problem?
Any ideas?
Tks in advance
JFB
DECLARE @.DBName sysname
,@.DBStatus int
,@.dbid int
,@.TempDBName nvarchar(70)
SELECT @.DBName = '*'
SELECT @.TempDBName = ' '
DECLARE DBs CURSOR FOR
SELECT name, dbid, status, name
FROM master..sysdatabases
WHERE [name] <> 'tempdb'
and [name] <> 'master'
and [name] <> 'model'
FOR READ ONLY
OPEN DBs
FETCH NEXT FROM DBs INTO @.DBName, @.dbid, @.DBStatus, @.TempDBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Print @.TempDBName
DECLARE @.tempString nvarchar(255)
SELECT @.tempString = 'USE ' + @.TempDBName +'
DECLARE @.UserName nvarchar(50)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @.UserName
WHILE (@.@.fetch_status = 0)
BEGIN
PRINT @.UserName '' user name being resynced''
EXEC sp_change_users_login ''Update_one'', @.UserName, @.UserName
FETCH NEXT FROM orphanuser_cur INTO @.UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur'
EXEC (@.tempString)
FETCH NEXT FROM DBs INTO @.DBName, @.dbid, @.DBStatus, @.TempDBName
END
CLOSE DBs
DEALLOCATE DBsOn Wed, 16 Feb 2005 18:06:32 -0500, JFB wrote:

>I'm trying to fix all the orphans users for all the databases in my sql
>server 2000 using the code above, but I'm getting an error that the second
>cursor already exist.
>Can I do a cursor inside another one?
>How can I fix this problem?
>Any ideas?
Hi JFB,
The first thing to do when troubleshooting dynamic SQL is to change
EXEC (@.tempString)
to
PRINT @.tempString
and inspect the results.
If you do that, you'll instantly note that the length of your dynamic SQL
exceeds the 255 character you used in the declaration of @.tempString.
Another problem you'll find after fixing this one is here:
> PRINT @.UserName '' user name being resynced''
This should be changed to
PRINT @.UserName + '' user name being resynced''
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes Hugo, Tks for you reply and help.
I got those problems...
Rgds
JFB
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:f8v9115l8464ppt54s56bo45knisli5l3u@.
4ax.com...
> On Wed, 16 Feb 2005 18:06:32 -0500, JFB wrote:
>
> Hi JFB,
> The first thing to do when troubleshooting dynamic SQL is to change
> EXEC (@.tempString)
> to
> PRINT @.tempString
> and inspect the results.
> If you do that, you'll instantly note that the length of your dynamic SQL
> exceeds the 255 character you used in the declaration of @.tempString.
> Another problem you'll find after fixing this one is here:
> This should be changed to
> PRINT @.UserName + '' user name being resynced''
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes Hugo, Tks for you reply and help.
I got those problems...
Rgds
JFB
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:f8v9115l8464ppt54s56bo45knisli5l3u@.
4ax.com...
> On Wed, 16 Feb 2005 18:06:32 -0500, JFB wrote:
>
> Hi JFB,
> The first thing to do when troubleshooting dynamic SQL is to change
> EXEC (@.tempString)
> to
> PRINT @.tempString
> and inspect the results.
> If you do that, you'll instantly note that the length of your dynamic SQL
> exceeds the 255 character you used in the declaration of @.tempString.
> Another problem you'll find after fixing this one is here:
> This should be changed to
> PRINT @.UserName + '' user name being resynced''
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

First() to SQL?

Hi,
I'm working with a project translating Access databases to SQL Server.
Can anyone explain the mystic function First() to me?
How can it's function be replaced by SQL?
(I've posted this in the Accessforum also)As near as I remember the First() function is used as a sort of "get out of jail free card" for group by situations. Instead of grouping by the value in the column, or summing up the column, or getting a max or min of the column, Access grabs the first value it sees. Because of this, you can end up with different results in different situations, which is generally bad for business. Here is a link to some of the help I found..

http://office.microsoft.com/en-us/assistance/HA010345631033.aspx

In SQL Server, I would avoid using the concept of "first" as it does not really have any meaning, unless you impose a meaning like "chronologically first entered", in which case you would (hopefully) have an entered date to work with. Hope this helps.|||First() and Last automatically go to either the first or last record in your dataset (presumably sorted) and returns the field you specify.

In SQL you will need to do this in two stages. First, find the Primary Key value of the First or Last record, and then look up the value of the field in the record associated with that key.

select [YourValue] as FirstValue
from [YourTable]
inner join
(select min([SortKey]) as FirstKey from [YourTable]) Subquery
where [YourTable].[SortKey] = Subquery.FirstKey

If you sortkey is not unique, you will get multiple records in your result.