Friday, March 30, 2012

Not a valid Win32 application

New to SQL Server installations. I downloaded the SQL Server Express with Advanced; when I double click the Setup.exe, I get a pop-up "document path/file is not a valid Win32 application". I upgraded from XP Home to XP Pro SP2.0. Any suggestions?Seems like the downloaded exe is corrupt. Download it again.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

not a valid undo file for database

Hi,
I've restored a database from backup, with STANDBY option.
Now, when I'm trying to apply further Transaction logs to
this database, the restore log command is failing with an
error 'File 'd:\standby.undo' is not a valid undo file for
database 'xyz', database ID 24.
Please let me know how to I avoid this error and restore
logs to this database.
Does the file mentioned in the error message actually exist?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
> Hi,
> I've restored a database from backup, with STANDBY option.
> Now, when I'm trying to apply further Transaction logs to
> this database, the restore log command is failing with an
> error 'File 'd:\standby.undo' is not a valid undo file for
> database 'xyz', database ID 24.
> Please let me know how to I avoid this error and restore
> logs to this database.
|||Yes, it does. And I've tried renaming this file and then
it gives a different error that the file does not exist.
SO, I'm sure that it is able to access this file, but
something is not right in this file.
What's the purpose of this file anyway ?

>--Original Message--
>Does the file mentioned in the error message actually
exist?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
option.[vbcol=seagreen]
to[vbcol=seagreen]
an[vbcol=seagreen]
for
>
>.
>
|||The UNDO file is created when you perform RESTORE using the STANDBY option.
This is because SQL Server will actually perform recovery based on the transaction log when you are
using STANDBY, but as you say you want to be able to perform additional restores, SQL Server will
save the recovery work it performs in this undo file so it can undo the recovery work when you do
the next restore.
SQL Server will remember the name of the undo file so it will automatically find it when next
restore is performed. In this case, SQL Server doesn't recognize the undo file as a valid file.
Perhaps someone deleted the file and just created one through notepad, or picked some other UNDO
file and renamed it? Bottom-line is that SLQ Server *need* a valid UNDO file for the next restore.
You can always re-start all restores from the latest database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:2abc01c4ab08$347ecdb0$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes, it does. And I've tried renaming this file and then
> it gives a different error that the file does not exist.
> SO, I'm sure that it is able to access this file, but
> something is not right in this file.
> What's the purpose of this file anyway ?
> exist?
> message
> option.
> to
> an
> for
sql

not a valid undo file for database

Hi,
I've restored a database from backup, with STANDBY option.
Now, when I'm trying to apply further Transaction logs to
this database, the restore log command is failing with an
error 'File 'd:\standby.undo' is not a valid undo file for
database 'xyz', database ID 24.
Please let me know how to I avoid this error and restore
logs to this database.Does the file mentioned in the error message actually exist?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
> Hi,
> I've restored a database from backup, with STANDBY option.
> Now, when I'm trying to apply further Transaction logs to
> this database, the restore log command is failing with an
> error 'File 'd:\standby.undo' is not a valid undo file for
> database 'xyz', database ID 24.
> Please let me know how to I avoid this error and restore
> logs to this database.|||Yes, it does. And I've tried renaming this file and then
it gives a different error that the file does not exist.
SO, I'm sure that it is able to access this file, but
something is not right in this file.
What's the purpose of this file anyway ?
>--Original Message--
>Does the file mentioned in the error message actually
exist?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
message
>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
>> Hi,
>> I've restored a database from backup, with STANDBY
option.
>> Now, when I'm trying to apply further Transaction logs
to
>> this database, the restore log command is failing with
an
>> error 'File 'd:\standby.undo' is not a valid undo file
for
>> database 'xyz', database ID 24.
>> Please let me know how to I avoid this error and restore
>> logs to this database.
>
>.
>|||The UNDO file is created when you perform RESTORE using the STANDBY option.
This is because SQL Server will actually perform recovery based on the transaction log when you are
using STANDBY, but as you say you want to be able to perform additional restores, SQL Server will
save the recovery work it performs in this undo file so it can undo the recovery work when you do
the next restore.
SQL Server will remember the name of the undo file so it will automatically find it when next
restore is performed. In this case, SQL Server doesn't recognize the undo file as a valid file.
Perhaps someone deleted the file and just created one through notepad, or picked some other UNDO
file and renamed it? Bottom-line is that SLQ Server *need* a valid UNDO file for the next restore.
You can always re-start all restores from the latest database backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:2abc01c4ab08$347ecdb0$a501280a@.phx.gbl...
> Yes, it does. And I've tried renaming this file and then
> it gives a different error that the file does not exist.
> SO, I'm sure that it is able to access this file, but
> something is not right in this file.
> What's the purpose of this file anyway ?
>>--Original Message--
>>Does the file mentioned in the error message actually
> exist?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
>> Hi,
>> I've restored a database from backup, with STANDBY
> option.
>> Now, when I'm trying to apply further Transaction logs
> to
>> this database, the restore log command is failing with
> an
>> error 'File 'd:\standby.undo' is not a valid undo file
> for
>> database 'xyz', database ID 24.
>> Please let me know how to I avoid this error and restore
>> logs to this database.
>>
>>.

Not a valid Report Server Database?

I had to reinstall RS. While reinstalling Reporting Services I received an
error. I am at the point of specifying the report server database and I
tried to use the default, "ReportServer", which is the same as I used before.
The error = "A database with the specified name(ReportServer) already exists
and is not a valid Report Server Database. You must either remove the
existing database or specify a new database name.
According to my documentation I should be able to use the existing name and
the reinstall will not lose my existing reports.
Any idea why I would get this message?
--
DonI'm getting the same message. Anybody got any ideas?|||Are you connected to a database that was created by report server setup?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
<butter-ball@.usa.net> wrote in message
news:1106160275.812278.14380@.z14g2000cwz.googlegroups.com...
> I'm getting the same message. Anybody got any ideas?
>

Not a valid MSX account.

I just realized my subject line is too LONG! Re-posting
with shorter line. Sorry all...I won't double-post again!
Hello.
I am studying the MS Press book for SQL Server 2000 MCP
DBA 2nd edition, chapter 13, setting up a master and
target server. I have a default and a "mynamedinstance"
of SQL Server 2000.
I have installed SP3. I have Win 2003 Enterprise in a
VMWare box. All labs but one have completed successfully
(albeit after much effort in some cases.W2k3?)
The servers and their agents use a Domain User account of
SQLService. I have registered and re-registered them,
making them use SQL/Windows authentication, and Windows
Only authentication.
When I walk through the MSX wizard, after my last screen,
I get the following error:
The account 'SELFPACEDSQL\SQLService' is not a valid MSX
account. Contact the MSX administrator to get MSX account
information.
I added SQLService to domain admin and local admin
groups. I created an MSX account, member of Domain
users/admins, local admins.
Do I need to nuke it all, and start over with no SP? Is
that what's doing it? :-(
I searched newsgroups, KBs, you name it, and nothing!
Thank you.
MerylHello
Have you seen http://support.microsoft.com/default.aspx?
scid=kb;en-us;811326
J
>--Original Message--
>I just realized my subject line is too LONG! Re-posting
>with shorter line. Sorry all...I won't double-post again!
>Hello.
>I am studying the MS Press book for SQL Server 2000 MCP
>DBA 2nd edition, chapter 13, setting up a master and
>target server. I have a default and a "mynamedinstance"
>of SQL Server 2000.
>I have installed SP3. I have Win 2003 Enterprise in a
>VMWare box. All labs but one have completed successfully
>(albeit after much effort in some cases.W2k3?)
>The servers and their agents use a Domain User account of
>SQLService. I have registered and re-registered them,
>making them use SQL/Windows authentication, and Windows
>Only authentication.
>When I walk through the MSX wizard, after my last screen,
>I get the following error:
>The account 'SELFPACEDSQL\SQLService' is not a valid MSX
>account. Contact the MSX administrator to get MSX account
>information.
>I added SQLService to domain admin and local admin
>groups. I created an MSX account, member of Domain
>users/admins, local admins.
>Do I need to nuke it all, and start over with no SP? Is
>that what's doing it? :-(
>I searched newsgroups, KBs, you name it, and nothing!
>Thank you.
>Meryl
>
>.
>|||Hi.
Yes, I did. But it refers to updating MSX and TSX servers
to SP3. Not the other way around.
In other words, I installed SQL server, upgraded to SP3,
THEN tried to create the MSX.
'
Thank you!
ML
>--Original Message--
>Hello
>Have you seen http://support.microsoft.com/default.aspx?
>scid=kb;en-us;811326
>J
>>--Original Message--
>>I just realized my subject line is too LONG! Re-posting
>>with shorter line. Sorry all...I won't double-post
again!
>>Hello.
>>I am studying the MS Press book for SQL Server 2000 MCP
>>DBA 2nd edition, chapter 13, setting up a master and
>>target server. I have a default and a "mynamedinstance"
>>of SQL Server 2000.
>>I have installed SP3. I have Win 2003 Enterprise in a
>>VMWare box. All labs but one have completed
successfully
>>(albeit after much effort in some cases.W2k3?)
>>The servers and their agents use a Domain User account
of
>>SQLService. I have registered and re-registered them,
>>making them use SQL/Windows authentication, and Windows
>>Only authentication.
>>When I walk through the MSX wizard, after my last
screen,
>>I get the following error:
>>The account 'SELFPACEDSQL\SQLService' is not a valid
MSX
>>account. Contact the MSX administrator to get MSX
account
>>information.
>>I added SQLService to domain admin and local admin
>>groups. I created an MSX account, member of Domain
>>users/admins, local admins.
>>Do I need to nuke it all, and start over with no SP? Is
>>that what's doing it? :-(
>>I searched newsgroups, KBs, you name it, and nothing!
>>Thank you.
>>Meryl
>>
>>.
>.
>

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
Jeff
You can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff
sql

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

Not a valid identifier while executing an Stored procedure

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

|||

First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined.

But to solve the problem, you need to change

exec @.sqlstr

to

exec(@.sqlstr)

I'd rewrite the entire piece of code...

|||

This is a duplicate post.

Please see answer in your other posting.

|||

Use the following satement to execute the SP,

Code Snippet

Exec [GSU_Site_ReterieveActiveSitesOnSearch]' where GSUStatus.GSUStatusID=1and site.Sitename like''lakshmisite'' '

|||

Kusuma,

Instead passing the this value " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' ", use:

' where GSUStatus.GSUStatusID=1 and site.Sitename like ''lakshmisite'''

Notice that I am using two apostrophes per each one inside the string.

As you can see, you are setting QUOTED_IDENTIFIER to on, when creating the sp, so anything enclosed by double quote will be interprete as an identifier (name of a column, table, etc.), so when you pass that value to the sp, it will look like

...

SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID +

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and there is not such identifier in your db.

you can set QUOTED_IDENTIFIER to OFF, but I prefer to leave it as ON and use the other method to escape apostrophes.

AMB

|||

If you call it from any UI, the single quote will be automatically taken care by the providers/ADO classes. (since it is a parameter)

But when you test the sp, you have to use either escape sequence or as AMB sujest use the QUOTED_IDENTIFER OFF config.

|||

Thanks Mani :-)

Now it is working.

There were two problems. One

1)setQUOTED_IDENTIFIERON should be OFF

2)exec@.sqlstr should be exec(@.sqlstr)

Kusuma

|||

Hai Dalej,

Sorry for posting two times.

I need dynamic query for a searching -sitenames,Businessunit etc......... ( searching based on columns in a table)

Now the problem is solved by giving exec(@.sqlstr) instead of exec @.sqlstr.

Thanks for your help :-)

Kusuma

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQ
You need brackets when you execute a string:
EXEC('@.sql')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQYou need brackets when you execute a string:
EXEC('@.sql')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>sql

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQYou need brackets when you execute a string:
EXEC('@.sql')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>

Not A Trusted Connection

I set up my SQLExpress server using Windows Authentication only. Now when I
try to create a user account using Username and Password, the connection
returns the error: Not a trusted connection. Does this have something to do
with my previous server setup or is there a general setting somewhere I need
to change?
Regards,
Fred Chateau
http://hotelmotelnow.com
Never mind... I found the setting.
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u323BNenHHA.588@.TK2MSFTNGP06.phx.gbl...
>I set up my SQLExpress server using Windows Authentication only. Now when I
>try to create a user account using Username and Password, the connection
>returns the error: Not a trusted connection. Does this have something to do
>with my previous server setup or is there a general setting somewhere I
>need to change?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>

Not A Trusted Connection

I set up my SQLExpress server using Windows Authentication only. Now when I
try to create a user account using Username and Password, the connection
returns the error: Not a trusted connection. Does this have something to do
with my previous server setup or is there a general setting somewhere I need
to change?
Regards,
Fred Chateau
http://hotelmotelnow.comNever mind... I found the setting.
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u323BNenHHA.588@.TK2MSFTNGP06.phx.gbl...
>I set up my SQLExpress server using Windows Authentication only. Now when I
>try to create a user account using Username and Password, the connection
>returns the error: Not a trusted connection. Does this have something to do
>with my previous server setup or is there a general setting somewhere I
>need to change?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>

not a trusted connection

I never had this problem between two WIN2k servers.
I have a new Win2003(XP) server with SQL 2000 and an old Win2k server
with SQL 7. I'm trying to run a query for data from a table on 7 joined
to a table on 2000 inserting into a table on 2000.
Regardless of which server hosts the query I get an error about user
'null' not being a trusted SQL server connection. Same error whether I
run the query in QAnalyser or DTS.
I have admin rights on both servers and both DBMs.
I DON'T have a domained network; windows networking. Since I've done
things between the 7 server and my dev server (W2k/SQL2000) I'm guessing
it's something to do with a stricter W2003 and seperate, but same
username, identities on each local machine.
Can anybody tell me what the next step is?
thx
md
*** Sent via Developersdex http://www.codecomments.com ***Not a lot of information on the error but I'm guess you are
using a linked server that is set up to use the current
users security context. And you are logging into SQL Server
using windows authentication.
It's not clear from which box you are initiating this from.
Even if it was initiated from SQL 2000, sounds like you
would still need to use a SQL login for the linked server.
-Sue
On Tue, 13 Sep 2005 14:58:37 -0700, M D <mardukes@.aol.com>
wrote:

>I never had this problem between two WIN2k servers.
>I have a new Win2003(XP) server with SQL 2000 and an old Win2k server
>with SQL 7. I'm trying to run a query for data from a table on 7 joined
>to a table on 2000 inserting into a table on 2000.
>Regardless of which server hosts the query I get an error about user
>'null' not being a trusted SQL server connection. Same error whether I
>run the query in QAnalyser or DTS.
>I have admin rights on both servers and both DBMs.
>I DON'T have a domained network; windows networking. Since I've done
>things between the 7 server and my dev server (W2k/SQL2000) I'm guessing
>it's something to do with a stricter W2003 and seperate, but same
>username, identities on each local machine.
>Can anybody tell me what the next step is?
>thx
>md
>*** Sent via Developersdex http://www.codecomments.com ***

Not a Number Values in the Database

Our application has a table, which is populated via ADO.Net from C# with data originating from a C++ COM call. Today I encountered an entry that is C++ code for an undefined value: -1.#IND stored in the database. However, I could only discover what was stored in the table by Casting the value to a varchar -- simply selecting returned an error.

Is this expected behavior or a bug? It does not seem correct that SQL Server should store a value that cannot be displayed. In essence, either the value should not be allowed in the table because it violated the domain or SQL Server ought to have a way to display it with a Select *.

As fas as our application is concerned, we will be masking these values -- initially by ignoring them in the queries and eventually the loading program will convert to null.

What is the defined datatype for the field?|||

The column in question is a float column.

I would argue that the datatype should not be that important. The server should not allow a value to be stored which cannot be displayed.

|||

IND could be (COULD BE) referencing the unicode index control character. It looks like you had some sort of odd data transformation from the COM-C++-C#-SQL conversion process, especially considering it's a floating point variable which can lose precision through casting, it didn't surprise me to see that that was the data type in question.

Have you considered testing that single row by running it through your conversion process again by itself? Is the original float data for the column and row something... unusual?

|||

Hmm, very interesting. Try casting it to a numeric of some sort (particularly really large precision after the decimal)... That might give the clue. What datatype are you using in your C# application? And what prints out when you select from the column in Management Studio?

I can't imagine there is any value that is illegal for a float, but stranger things have happened.

|||

The specific data in the database was purged, due to some maintenance, so I'll have to see if it returns.

However, I made a simple example:

static void Main(string[] args)

{

double d = Double.NegativeInfinity;

using( SqlConnection conn = new SqlConnection( "server=(local);initial catalog=Northwind;integrated security=true" ) )

{

SqlCommand cmd = new SqlCommand( "Insert Foo(zero, one) Values( Datepart(mi, getdate() ),@.val )", conn );

cmd.Parameters.Add( "@.val", d );

conn.Open();

int rows = cmd.ExecuteNonQuery();

Console.WriteLine( rows );

}

Console.WriteLine( d );

}

with a target table:

Create Table Foo(

zero int not null,

one float null

)

Using any of Double.NegativeInfinity, Double.PositiveInfinity, or Double.NaN gives an odd result. The code succeeds and reports that a record has been added. However, the command Select * from Foo will not return records with these values. The values cannot be displayed by Casting to a numeric type, but output is produced by Casting to a varchar.

Using Select Count(*) from Foo will report the correct number of records.

|||

If you worked with MDX queries (analysis server), it is very common issue.

When you store the INDEFINITE/INFINTE value into your variable, compiler uses the special values to identify the INDEFINITE/INFINTE number/value,

1. 1.#IND (positive indefinite) or -1.#IND (negative indefinite)

2. 1.#INF (positive infinite)or -1.#INF (negative infinite)

Both are used to identify the compiler the variables hold the INDEFINITE/INFINTE, usally when you try to divide by zero it will be throw an error. But here you are forcefully stroring the infinite value. It is the compiler specific data. It can’t trusted across the compilers. So when you execute the code on your .NET framework will work fine. When try to pass this value into SQL Server it doesn’t understand, there is no specification available to identify the indefinite value in database.

Logically you can have nothing in your column (NULL/ZER0), but you are not allowed to store indefinite value in your column. Indefinite values are not comparable, these are imaginary values. But database store the masseurable values.

Solution:

Before passing the value to your database, validate the data, if it is INDEFINITE/INFINTE reset the value to NULL or other identifiable value for your future logics...

|||

I know you are a moderator, but as a general rule the thread creator should close threads. Being a moderator does not automatically make your information infallible.

As a matter of fact, on this issue you did not really add any additional information to the thread other than that you have experienced the issue as well.

I would now contend that this is, in fact, a bug in the SQL Server implementation. Either these values -- NaN, PositiveInfinity, and NegativeInfinity -- should be excluded from the domain of the Float datatype and rejected on Insert; or they are included in the domain and should be handled in a logical and consistent manner -- certainly they should have a display representation and they should have defined behavior in functions.

The current implementation is just a landmine waiting to go off at inopportune times.

|||

I marked the thread as 'answered'. As a 'general rule', we all take responsibitly to close a thread when there doesn't seem to be anymore 'interest' and/or it appears to be adequately 'answered'. And anyone can choose to 're-open' that thread by 'unmarking' the marked 'answer'. It's just a 'housekeeping' matter to help focus limited resources (the time and energy of volunteers) to the active and unanswered threads.

It appeared that Mani provided an adequate explanition for the behavior, and it seemed to me to be doubtful if there could be a 'solution' posted for your question. It appears that there to be a 'anomoly' involved in this situation -concerning a C++ interaction with the datastore, but I haven't been able to locate a declared 'bug' about the issue. (Doens't mean it's not there, just that I haven't devoted enough time to find it if it exists.) Mani's response satisfied the need to provide information to subsequent readers of the thread.

My mistake was in not encouraging you to post a 'potential' bug. I'll correct that now.

Please go to: http://connect.microsoft.com/sqlserver and post your observations and concerns. With everyone's keen interest and help to provide feedback and critique, the SQL Server will only continue to get better.

If you want this thread to stay 'open' to see if additional comments are forthcoming, that is perfectly fine.

As a postscript, let me add that there is often an issue of 'the' answer. We work on the concept of helping the questioner find a way to solve his/her problem. At some point, one has to say, ok, maybe there is a 'better' answer, but what is here now works, so lets move on.

|||

I have found more information on the issue:

First to clarify, the issue is not restricted to C++ interaction. The code I show above is C# and can be used to demonstrate the behavior when NaN or Infinity is stored in the database. Furthermore, I was surprised to find out that the data can be read out with a query from a C# program. My code and example were run against Sql Server 2000.

Looking on Microsoft Connections, I found an interesting item. It appears that MS has chosen to no longer support NaN and Infinity in the database in SQL Server 2005. In fact, a request had been made to restore the 2000 functionality. There is, at least, one organization which is using SQL Server to store engineering data and who has the need to occassionally store Infinities in the database. Evidently they are not performing any ad hoc queries or calculation within the database.

I plan to post a recommendation that MS embrace full support for the IEEE floating point numbers rather than simply disallowing them. It seems like the best, long term, behavior for the database and would be keeping in line with industry trends. Ideally, some form of patch would be made available for SQL 2000 to more gracefully handle NaN or Infinity when it is entered in the database.

|||Yes, this is a behavior change from SQL Server 2000. We now validate float values and Unicode data when you pass them via RPC calls or Bulk Copy APIs. So for float even though values like NaN and Infinity is allowed in the IEEE 754 spec (floating point implementation) we do not allow it now. Upon upgrade to SQL Server 2005 from older versions you need to modify the data in your tables to use them within TSQL. TSQL does not support any way to query these type of data or manipulate it explicitly - older SQL Server versions used to just allow them to be stored but you can only manipulate it on the client-side.|||

Is there a strong reason to not implement full support for the IEEE 754 spec? It specifies the rules for all operations involving the extra values. Some external representation of the values would be needed, but that has been solved for C# already.

It is a little surprising that it is not easier to support them than to prevent them. I assume that much of the Server software is written in a high level language that supports IEEE floats.

With competing products already supporting IEEE floats, the change seem inevitable.

Thanks for your input.

|||

I agree with your assessment. It is really unfortunate that we do not support it yet. Did you vote on the connect bug at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674? I would also encourage anyone reading this thread to vote on this bug. The issue today is that several built-ins in SQL Server (those that work with floating point data) and other facilities cannot handle these values. I will follow-up with the owner of this bug and see if we can do anything about it in SQL Server 2008.

sql

not a GROUP BY expression??

Why am I get this error message when I execute this query?

SELECT ODisciplina.BOUI FROM ODisciplina , Disciplina$student
WHERE ((Disciplina$student.PARENT$(+)=ODisciplina.BOUI))
GROUP BY (ODisciplina.BOUI )HAVING( Count(CHILD$)=0 )
ORDER BY (ODisciplina.NAME)

without the 3rd line it works and without the last line it works too.Tks, I've found the error. It should be:

SELECT ODisciplina.BOUI FROM ODisciplina , Disciplina$student
WHERE ((Disciplina$student.PARENT$(+)=ODisciplina.BOUI))
GROUP BY (ODisciplina.BOUI, ODisciplina.NAME )HAVING( Count(CHILD$)=0 )
ORDER BY (ODisciplina.NAME)

Not a BUF latch. Error Re-Indexing

We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
Advanced Server.
Weekly we have a job that drops all indexes on the database and recreates
them.
This job runs at the same time the daily backup and transaction log execute.
We received following error caused by the re-index:
"Time out occurred while waiting for buffer latch type 4..."
Please help me resolve these latch errors.
Thanks,
SQL Server errorlog
WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
continuing to wait.
Waiting for type 0x2, current count 0x10002a, current owning EC 0x962263F0.
It not good practice to run such major maintenance operations as dropping
and recreating all indexes (I hope you use DBCC DBREINDEX btw) at the same
time as a backup. In general running the operations in parallel will not be
much quicker than running them in series (because your resources, specially
disk I/O, are limited). And if your backup finishes before the reindexing
finishes, part of your indexes will be reindexed and part not, but you don't
know exactly which.
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AA424678-958D-478E-B6FF-574A7885455B@.microsoft.com...
> We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
> Advanced Server.
> Weekly we have a job that drops all indexes on the database and recreates
> them.
> This job runs at the same time the daily backup and transaction log
> execute.
> We received following error caused by the re-index:
> "Time out occurred while waiting for buffer latch type 4..."
> Please help me resolve these latch errors.
> Thanks,
>
> SQL Server errorlog
> WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
> 1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
> continuing to wait.
> Waiting for type 0x2, current count 0x10002a, current owning EC
> 0x962263F0.
>
>
|||Hi
What does select @.@.version return for the SQL Server?
Have a look at:
http://support.microsoft.com/kb/310834/en-us
http://support.microsoft.com/kb/309093/EN-US/
Regards
Mike
"Joe K." wrote:

> We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
> Advanced Server.
> Weekly we have a job that drops all indexes on the database and recreates
> them.
> This job runs at the same time the daily backup and transaction log execute.
> We received following error caused by the re-index:
> "Time out occurred while waiting for buffer latch type 4..."
> Please help me resolve these latch errors.
> Thanks,
>
> SQL Server errorlog
> WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
> 1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
> continuing to wait.
> Waiting for type 0x2, current count 0x10002a, current owning EC 0x962263F0.
>
>

Not a BUF latch. Error Re-Indexing

We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
Advanced Server.
Weekly we have a job that drops all indexes on the database and recreates
them.
This job runs at the same time the daily backup and transaction log execute.
We received following error caused by the re-index:
"Time out occurred while waiting for buffer latch type 4..."
Please help me resolve these latch errors.
Thanks,
SQL Server errorlog
WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
continuing to wait.
Waiting for type 0x2, current count 0x10002a, current owning EC 0x962263F0.It not good practice to run such major maintenance operations as dropping
and recreating all indexes (I hope you use DBCC DBREINDEX btw) at the same
time as a backup. In general running the operations in parallel will not be
much quicker than running them in series (because your resources, specially
disk I/O, are limited). And if your backup finishes before the reindexing
finishes, part of your indexes will be reindexed and part not, but you don't
know exactly which.
--
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AA424678-958D-478E-B6FF-574A7885455B@.microsoft.com...
> We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
> Advanced Server.
> Weekly we have a job that drops all indexes on the database and recreates
> them.
> This job runs at the same time the daily backup and transaction log
> execute.
> We received following error caused by the re-index:
> "Time out occurred while waiting for buffer latch type 4..."
> Please help me resolve these latch errors.
> Thanks,
>
> SQL Server errorlog
> WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
> 1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
> continuing to wait.
> Waiting for type 0x2, current count 0x10002a, current owning EC
> 0x962263F0.
>
>|||Hi
What does select @.@.version return for the SQL Server?
Have a look at:
http://support.microsoft.com/kb/310834/en-us
http://support.microsoft.com/kb/309093/EN-US/
Regards
Mike
"Joe K." wrote:
> We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
> Advanced Server.
> Weekly we have a job that drops all indexes on the database and recreates
> them.
> This job runs at the same time the daily backup and transaction log execute.
> We received following error caused by the re-index:
> "Time out occurred while waiting for buffer latch type 4..."
> Please help me resolve these latch errors.
> Thanks,
>
> SQL Server errorlog
> WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
> 1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
> continuing to wait.
> Waiting for type 0x2, current count 0x10002a, current owning EC 0x962263F0.
>
>

Not a BUF latch. Error Re-Indexing

We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
Advanced Server.
Weekly we have a job that drops all indexes on the database and recreates
them.
This job runs at the same time the daily backup and transaction log execute.
We received following error caused by the re-index:
"Time out occurred while waiting for buffer latch type 4..."
Please help me resolve these latch errors.
Thanks,
SQL Server errorlog
WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF latch.
Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
continuing to wait.
Waiting for type 0x2, current count 0x10002a, current owning EC 0x962263F0.It not good practice to run such major maintenance operations as dropping
and recreating all indexes (I hope you use DBCC DBREINDEX btw) at the same
time as a backup. In general running the operations in parallel will not be
much quicker than running them in series (because your resources, specially
disk I/O, are limited). And if your backup finishes before the reindexing
finishes, part of your indexes will be reindexed and part not, but you don't
know exactly which.
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:AA424678-958D-478E-B6FF-574A7885455B@.microsoft.com...
> We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
> Advanced Server.
> Weekly we have a job that drops all indexes on the database and recreates
> them.
> This job runs at the same time the daily backup and transaction log
> execute.
> We received following error caused by the re-index:
> "Time out occurred while waiting for buffer latch type 4..."
> Please help me resolve these latch errors.
> Thanks,
>
> SQL Server errorlog
> WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF
> latch.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
> 1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
> continuing to wait.
> Waiting for type 0x2, current count 0x10002a, current owning EC
> 0x962263F0.
>
>|||Hi
What does select @.@.version return for the SQL Server?
Have a look at:
http://support.microsoft.com/kb/310834/en-us
http://support.microsoft.com/kb/309093/EN-US/
Regards
Mike
"Joe K." wrote:

> We have a SQL Server 2000 Enterprise Edition database on a Windows 2000
> Advanced Server.
> Weekly we have a job that drops all indexes on the database and recreates
> them.
> This job runs at the same time the daily backup and transaction log execut
e.
> We received following error caused by the re-index:
> "Time out occurred while waiting for buffer latch type 4..."
> Please help me resolve these latch errors.
> Thanks,
>
> SQL Server errorlog
> WARNING: EC 8a0459e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 9653c3f0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5d4ad9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 45c0b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 462df9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 5a9619e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 8a05b9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cbb9e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 6a9d19e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 89cb39e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC 84cc79e0, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> WARNING: EC b7fd35c8, 0 waited 300 sec. on latch 813f42c8. Not a BUF latc
h.
> Waiting for type 0x4, current count 0xa, current owning EC 0x6A9659E0.
> Time out occurred while waiting for buffer latch type 2,bp 0x3527140, page
> 1:1), stat 0xb, object ID 6:99:0, EC 0x6F6A9520 : 0, waittime 300. Not
> continuing to wait.
> Waiting for type 0x2, current count 0x10002a, current owning EC 0x962263F0
.
>
>

No-Sync Replication and Future Schema changes

I have the responsibility of setting up SQL2000 (merge and
transactional) replication of a 30GB database between 3 physical
locations (within 100 miles), over 256KB pipes.
I have done this with no-sync before (backup publisher to USB HD and
restore at subscriber), but the downside of that is that future table
schema changes (using sp_repladdcolumn etc) will not propagate to the
subscriber.
Question: Is there a way that I can setup no-sync replication in such
a way that I can also propagate future schema changes "over the wire"?
Not with merge replication. With transactional replication it is possible to
trick the publisher into thinking that it is dealing with a "automatic"
subscriber.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve Campbell" <dukeytoo@.gmail.com> wrote in message
news:1183993715.504073.91200@.c77g2000hse.googlegro ups.com...
>I have the responsibility of setting up SQL2000 (merge and
> transactional) replication of a 30GB database between 3 physical
> locations (within 100 miles), over 256KB pipes.
> I have done this with no-sync before (backup publisher to USB HD and
> restore at subscriber), but the downside of that is that future table
> schema changes (using sp_repladdcolumn etc) will not propagate to the
> subscriber.
> Question: Is there a way that I can setup no-sync replication in such
> a way that I can also propagate future schema changes "over the wire"?
>
|||On Jul 9, 11:31 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Not with merge replication. With transactional replication it is possible to
> trick the publisher into thinking that it is dealing with a "automatic"
> subscriber.
Any idea how?
|||Hi Hilary,
I too am interested in knowing the answer to this question (see my recent
thread in this NG title Add additional Subscribers) using a similar approach
(backup existing Subscriber DB, and restore it to a new Subscriber using
nosync) but it appears that would require the future schema changes to be
applied to the subscriber(s) through scripting (sp_addsubscription), and no
longer possible through the Publication Properties, Articles tab ?
I'm using Transactional under SQL 2000 SP4, 1 Publisher/Distributor and 3
PUSH Subscribers; and I need to ADD 2 more PUSH Subscribers to the topology
in the same well connected environment.
If the answer is nestled away in your book somewhere, I do own a copy...just
point me in the right direction.
Many thanks !
Regards,
Frank
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u%23PkYakwHHA.600@.TK2MSFTNGP05.phx.gbl...
> Not with merge replication. With transactional replication it is possible
> to trick the publisher into thinking that it is dealing with a "automatic"
> subscriber.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Steve Campbell" <dukeytoo@.gmail.com> wrote in message
> news:1183993715.504073.91200@.c77g2000hse.googlegro ups.com...
>
|||This is how I do it.
select * from syssubscriptions where sync_type=2--locate your no sync
subscription.
update syssubscriptions set sync_type=1 where dest_db='nosyncdatabase'
sp_refreshsubscriptions 'your publication name'
Put it back when you are done.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve Campbell" <dukeytoo@.gmail.com> wrote in message
news:1184005859.811318.34520@.n60g2000hse.googlegro ups.com...
> On Jul 9, 11:31 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Any idea how?
>
sql

NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.
On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 mins
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amount
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Isnt
> this suppoed to stop some sort of integrity scan on all other backup sets in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.
|||yes but if theres a LOT of backup sets surely this will add a delay?
"amish" wrote:

> On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
> wrote:
> Noskip/skip does not make any difference on Backup time. It just
> checks expiration date of backup sets. I just guess that the reason
> may be that your disk gets fragmented after number of backups and that
> may cause delay.
>
|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:

>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amount
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isnt
>this suppoed to stop some sort of integrity scan on all other backup sets in
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.
|||thanks for your response Roy.
if you go to server objects >backup devices you can configure a virtual
'tape' that is in effect a file on the physical disk. this is what im backing
up to. as far as I can tell the only differnece between using this method and
backing up direct to disk is that you get to use a shorter name in the backup
job command rather than a full file path.
anyhoo
may be this ISNT the only differnce. REWIND is default right? so maybe SQL
is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
what happens in 2 backups time (20 mins.)
cheers for the pointer
"Roy Harvey" wrote:

> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> <Methodology@.discussions.microsoft.com> wrote:
>
|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.
|||fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
implies the former
thanks
"amish" wrote:

> On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> I dont have much idea about tapes but in BOL it says that
> NOREWIND implies NOUNLOAD, and these options are incompatible within a
> single BACKUP statement
> and Methodology has specified also NOUNLOAD in the script.
>
|||NOUNLOAD/UNLOAD and REWIND/NOREWIND are tape-specific commands. If
you are not using tape they should not be in the BACKUP command at
all.
However, I am paranoid when it comes to dealing with backups. I don't
know that anyone has ever confirmed that there are no side effects to
using tape-specific features with a disk backup. So while I would
suggest removing the tape-specific parameters if you are not using
tape, I would combine that with starting a fresh backup file. It
would not hurt to backup to another device or file for the rest of the
month.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:02:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>thanks for your response Roy.
>if you go to server objects >backup devices you can configure a virtual
>'tape' that is in effect a file on the physical disk. this is what im backing
>up to. as far as I can tell the only differnece between using this method and
>backing up direct to disk is that you get to use a shorter name in the backup
>job command rather than a full file path.
>anyhoo
>may be this ISNT the only differnce. REWIND is default right? so maybe SQL
>is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
>what happens in 2 backups time (20 mins.)
>cheers for the pointer
>"Roy Harvey" wrote:
|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
[vbcol=seagreen]
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
|||I guess the answer is not to write so many backups to a single file.
Personally I don't think I would set up a backup scheme with one
monthly database backup and log backups every ten minutes. If a
database is active enough to justify a ten-minute interval for the
logs, I would backup the enitre database at least weekly, but more
likely daily. Of course I have no way of knowing your environment and
specific factors that went into the design.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:40:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote: