Increase performance of SQL Server by using RAM

Hi,
I have MSSQL-Server 2000 installed on a PIII 1.2 GHz server running with 256 MB of RAM.
The server is starting to run slower and slower at the peak times.
When ever I check the task manager performance, the processor is always bussy with red indicator, while the RAM is calm and running under 50%.
Is there any configuration in MSSQL-Server, I can do, so I can use the RAM to take some of the load on the processor?

I will really appreciate your help.
ThanksSQL server does all its actions in RAM. So if you insert more RAM the performance should (technically) increase. But first check how much RAM is available for SQL server and monitor the harddisk activity.|||Could you please help me, how to check for the amount of RAM reserved for MS SQL server?

Thanks|||Start SQL Enterprise manager
1
Connect to the appropiate SQL server

2
Right click on de name of the server in the right panel of the screen and select properties.

3
Choose the Tab memory

The memory must be configured as Dynamically with a minimum of 0 and a maximum of the total amount of RAM availleble.

You did determine that the process sqlservr.exe is consuming the bulk of the RAM and of the CPU time? If not than another proces is responsible for the slow response time!|||If you've sufficient memory allocated to SQL Server (Microsoft recommends don't restrict sql to a particular amount of memory, let it acquire memory on its own, hence it is preferred to allocated all memory dedicated to sql server.) If this is setted properly go to 'Performance Monitor', and under System object see '%Processor Time' which should be well below 80% . At the same time also see 'Processor Queue Length' which should be below 2 or equal to 2.

If above conditions are not satisfied you have processor bottleneck and must upgrade your processor

Incorrect Time Zone

I have just setup up SQL Notification Services 2005 at my workplace and running an instance of it. The problem that I am facing is with the deliveryrequesttime and sent time in the notification distribution view. These times are ahead of the server time by 5 hours. The server is set correctly to the local time and time zone. I would like to synchronize the deliveryrequesttime and senttime to the server time.

TIA

SSNS processes everything in UTC time. I'm guessing you are offset from the UTC by 5 hours.|||How do I go about solving this problem? Is there a setting that I modify?|||I understand from your original post that you'd like to make SSNS process things in accordance with your local time zone. But SSNS works in terms of UTC so that it can process scheduled subscriptions for multiple time zones.

What are you trying to accomplish? If it's for reporting purposes, you can calculate the offset.

HTH...

Joe|||

This was the concrete answer i was looking for.

Thanks

Tha

Incorrect Time Zone

I have just setup up SQL Notification Services 2005 at my workplace and running an instance of it. The problem that I am facing is with the deliveryrequesttime and sent time in the notification distribution view. These times are ahead of the server time by 5 hours. The server is set correctly to the local time and time zone. I would like to synchronize the deliveryrequesttime and senttime to the server time.

TIA

SSNS processes everything in UTC time. I'm guessing you are offset from the UTC by 5 hours.|||How do I go about solving this problem? Is there a setting that I modify?|||I understand from your original post that you'd like to make SSNS process things in accordance with your local time zone. But SSNS works in terms of UTC so that it can process scheduled subscriptions for multiple time zones.

What are you trying to accomplish? If it's for reporting purposes, you can calculate the offset.

HTH...

Joe|||

This was the concrete answer i was looking for.

Thanks

Tha

sql

Incorrect Time Zone

I have just setup up SQL Notification Services 2005 at my workplace and running an instance of it. The problem that I am facing is with the deliveryrequesttime and sent time in the notification distribution view. These times are ahead of the server time by 5 hours. The server is set correctly to the local time and time zone. I would like to synchronize the deliveryrequesttime and senttime to the server time.

TIA

SSNS processes everything in UTC time. I'm guessing you are offset from the UTC by 5 hours.|||How do I go about solving this problem? Is there a setting that I modify?|||I understand from your original post that you'd like to make SSNS process things in accordance with your local time zone. But SSNS works in terms of UTC so that it can process scheduled subscriptions for multiple time zones.

What are you trying to accomplish? If it's for reporting purposes, you can calculate the offset.

HTH...

Joe|||

This was the concrete answer i was looking for.

Thanks

Tha

Incorrect Time Zone

I have just setup up SQL Notification Services 2005 at my workplace and running an instance of it. The problem that I am facing is with the deliveryrequesttime and sent time in the notification distribution view. These times are ahead of the server time by 5 hours. The server is set correctly to the local time and time zone. I would like to synchronize the deliveryrequesttime and senttime to the server time.

TIA

SSNS processes everything in UTC time. I'm guessing you are offset from the UTC by 5 hours.|||How do I go about solving this problem? Is there a setting that I modify?|||I understand from your original post that you'd like to make SSNS process things in accordance with your local time zone. But SSNS works in terms of UTC so that it can process scheduled subscriptions for multiple time zones.

What are you trying to accomplish? If it's for reporting purposes, you can calculate the offset.

HTH...

Joe|||

This was the concrete answer i was looking for.

Thanks

Tha

Incorrect syntax near the keyword 'Close'

When I created a SQL Server database by running a script, it gave me a
few errors like the following:
Incorrect syntax near the keyword 'KEY'.
Incorrect syntax near the keyword 'Close'.
Incorrect syntax near the keyword 'Open'.
Is this because those words (Key, CLose and Open) are reserved words ?
Thanks.We would definitely need to view the script in order to help you out here .
Could you post the script ?
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||Hi
Look at the subject "Reserved Keywords" in Books online all the words you
list are keywords. It is possible to use delimited identifiers if you want
to keep the keyword as an identifier see the topics
John
"Using Reserved Keywords" and "Delimited Identifiers" in Books online.
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||If you use keywords( documented in Books on line) as the names of ANY
objects in SQL you must brace them if
create table [OPEN]
([Key] int not null)
It is a good idea NOT to use reserve words if you can avoid it, because
you'll be forgetting to use the brackets and re-doing code over and
over(kind of annoying.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
enclose identifiers in double quotes. This alternative to square brackets
is the ANSI-standard method. The best practice is to avoid reserved words,
though.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "OPEN"
("Key" int NOT NULL)
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.com...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.|||CREATE TABLE tblA (
Price varchar(50) NULL,
close varchar(50) NULL,
group1 varchar(50) NULL,
Cost varchar(50) NULL
)
go
CREATE TABLE tblB (
Product varchar(50) NULL,
open datetime NULL,
close datetime NULL
)
go
CREATE TABLE tblC (
key varchar(50) NULL,
First_name varchar(50) NULL
)
go
When I replaced "Close" to "Close1", "Open" to "Open1" and "key" to
"key1", the error did not appear anymore.
Thanks.
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:<uEm9gM1oEHA.1900@.TK2MSFTNGP10.phx.gbl>...
> We would definitely need to view the script in order to help you out here .
> Could you post the script ?
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.com...
> > When I created a SQL Server database by running a script, it gave me a
> > few errors like the following:
> >
> > Incorrect syntax near the keyword 'KEY'.
> > Incorrect syntax near the keyword 'Close'.
> > Incorrect syntax near the keyword 'Open'.
> >
> > Is this because those words (Key, CLose and Open) are reserved words ?
> >
> > Thanks.|||Thank you.
If I use square brackets or double quotes on the colum name, do I access
that column with the square brackets or double quotes also ?
For example:
create table tblA ( [open] varchar(50) )
When I want to select column [open], do I do the following sql statement:
select open from tblA
OR
select [open] from tblA ?
create table tblA ( "open" varchar(50) )
When I want to select column "open", do I do the following sql statement:
select "open" from tblA
OR
select "open" from tblA ?
Thank you very much.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
> enclose identifiers in double quotes. This alternative to square brackets
> is the ANSI-standard method. The best practice is to avoid reserved
words,
> though.
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE "OPEN"
> ("Key" int NOT NULL)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.com...
> > When I created a SQL Server database by running a script, it gave me a
> > few errors like the following:
> >
> > Incorrect syntax near the keyword 'KEY'.
> > Incorrect syntax near the keyword 'Close'.
> > Incorrect syntax near the keyword 'Open'.
> >
> > Is this because those words (Key, CLose and Open) are reserved words ?
> >
> > Thanks.
>|||Enclosures are required when you use a reserved word but it doesn't matter
whether you use square brackets or double quotes. You can mix both.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:eHXDZNDpEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> If I use square brackets or double quotes on the colum name, do I access
> that column with the square brackets or double quotes also ?
> For example:
> create table tblA ( [open] varchar(50) )
> When I want to select column [open], do I do the following sql statement:
> select open from tblA
> OR
> select [open] from tblA ?
> create table tblA ( "open" varchar(50) )
> When I want to select column "open", do I do the following sql statement:
> select "open" from tblA
> OR
> select "open" from tblA ?
> Thank you very much.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
>> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
>> enclose identifiers in double quotes. This alternative to square
>> brackets
>> is the ANSI-standard method. The best practice is to avoid reserved
> words,
>> though.
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TABLE "OPEN"
>> ("Key" int NOT NULL)
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "fniles" <fiefieniles@.yahoo.com> wrote in message
>> news:2067fd92.0409251452.60e065d7@.posting.google.com...
>> > When I created a SQL Server database by running a script, it gave me a
>> > few errors like the following:
>> >
>> > Incorrect syntax near the keyword 'KEY'.
>> > Incorrect syntax near the keyword 'Close'.
>> > Incorrect syntax near the keyword 'Open'.
>> >
>> > Is this because those words (Key, CLose and Open) are reserved words ?
>> >
>> > Thanks.
>>
>

Incorrect syntax near the keyword 'Close'

When I created a SQL Server database by running a script, it gave me a
few errors like the following:
Incorrect syntax near the keyword 'KEY'.
Incorrect syntax near the keyword 'Close'.
Incorrect syntax near the keyword 'Open'.
Is this because those words (Key, CLose and Open) are reserved words ?
Thanks.
We would definitely need to view the script in order to help you out here .
Could you post the script ?
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||Hi
Look at the subject "Reserved Keywords" in Books online all the words you
list are keywords. It is possible to use delimited identifiers if you want
to keep the keyword as an identifier see the topics
John
"Using Reserved Keywords" and "Delimited Identifiers" in Books online.
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||If you use keywords( documented in Books on line) as the names of ANY
objects in SQL you must brace them if
create table [OPEN]
([Key] int not null)
It is a good idea NOT to use reserve words if you can avoid it, because
you'll be forgetting to use the brackets and re-doing code over and
over(kind of annoying.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
enclose identifiers in double quotes. This alternative to square brackets
is the ANSI-standard method. The best practice is to avoid reserved words,
though.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "OPEN"
("Key" int NOT NULL)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"fniles" <fiefieniles@.yahoo.com> wrote in message
news:2067fd92.0409251452.60e065d7@.posting.google.c om...
> When I created a SQL Server database by running a script, it gave me a
> few errors like the following:
> Incorrect syntax near the keyword 'KEY'.
> Incorrect syntax near the keyword 'Close'.
> Incorrect syntax near the keyword 'Open'.
> Is this because those words (Key, CLose and Open) are reserved words ?
> Thanks.
|||Thank you.
If I use square brackets or double quotes on the colum name, do I access
that column with the square brackets or double quotes also ?
For example:
create table tblA ( [open] varchar(50) )
When I want to select column [open], do I do the following sql statement:
select open from tblA
OR
select [open] from tblA ?
create table tblA ( "open" varchar(50) )
When I want to select column "open", do I do the following sql statement:
select "open" from tblA
OR
select "open" from tblA ?
Thank you very much.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> To add to Wayne's response, you can also SET QUOTED_IDENTIFIER ON and
> enclose identifiers in double quotes. This alternative to square brackets
> is the ANSI-standard method. The best practice is to avoid reserved
words,
> though.
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE "OPEN"
> ("Key" int NOT NULL)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "fniles" <fiefieniles@.yahoo.com> wrote in message
> news:2067fd92.0409251452.60e065d7@.posting.google.c om...
>
|||Enclosures are required when you use a reserved word but it doesn't matter
whether you use square brackets or double quotes. You can mix both.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:eHXDZNDpEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> If I use square brackets or double quotes on the colum name, do I access
> that column with the square brackets or double quotes also ?
> For example:
> create table tblA ( [open] varchar(50) )
> When I want to select column [open], do I do the following sql statement:
> select open from tblA
> OR
> select [open] from tblA ?
> create table tblA ( "open" varchar(50) )
> When I want to select column "open", do I do the following sql statement:
> select "open" from tblA
> OR
> select "open" from tblA ?
> Thank you very much.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OhJ6cs9oEHA.3728@.TK2MSFTNGP09.phx.gbl...
> words,
>

Incorrect syntax near keyword "function".

First a little history:
We have a Pentium II server running SQL 7.0. The SQL has a database that
our users connect to using a client front end app that was created by a
former programmer in our office. The file server name in the beginning was
called FRE3.
In the beginning the server was running NT4 and the users were on Win95
desktops.
We had a migration a few years ago where the server was upgraded to Windows
2000 server and the desktops were upgraded to Windows 2000 Professional. We
also had to make a server name change to fall in line with official naming
conventions. The server's name was changed to NTFRE. It was and still is
just a member server in our AD.
Of course, the client app had to be modified as it refers to the server name
and us non-programmers figured out how to change the name in the Visual Basic
code and recompiled a new executable and it worked fine. Just had to change
one instance in the code where it names the server.
We are now going through a new migration. We have a new box and have
installed Windows Server 2003 Enterprise on it. We have also installed SQL
2000 on it. Have updated all service packs and everything. Ran the copy
database wizard and successfully brought over the database from the SQL 7.0
server, as well as user logins. User destops are being migrated to Windows
XP as well.
So we broke into the code again, and changed the server name again like last
time, (new 2003 server is called S2K3-FRE-SQL1), and recompiled a new
executable.
Houston, we now have a problem. When I executed new executable on new XP
machine, I get this error message after logging in:
Run Time Error - '2147217900(80040e14)
Incorrect syntax near keyword 'function'
Does this error message mean anything to any of you gurus?
There is one bit of info that I need to relay, not sure if it matters or not:
The old server running 7.0, in Enterprise Manager, under SQL Server Group,
it states 2KFRE (Windows NT).
In the new server running 2000, in Enterprise Manager, under SQL Server
Group, it does not state the name of the server (S2K3-FRE-SQL1), it just
states Local (Windows NT).
Should I worry about this? Is the code looking for S2K3-FRE-SQL1 and seeing
the word Local instead, thus giving me my error message'?
Do you believe that the client code needs to be modified somewhere else now'
Is this error message a reflection of code built to address a SQL 7.0
installation and now it doesn't work as it's trying to talk to a SQL 2000
installation'Hi
function is a keyword so can't be used in the code. You may want to use the
scripting options of Enterprise Manager to script out the stored procedures
to try a textual search for function and see where it occurs. It could be
that you also have triggers which may contain the the key word. Alternatively
you could use SQL profiler to look at what SQL is being run on the server and
try and narrow down what is going on. If you want to look at
http://tinyurl.com/yejfye which is a MSDN webcast on using SQL 2000 Profiler,
there is also one for SQL 2005 and other tools.
You also have a lot of information in books online and it is certainly worth
your while browsing it.
HTH
John
"Rockitman" wrote:
> First a little history:
> We have a Pentium II server running SQL 7.0. The SQL has a database that
> our users connect to using a client front end app that was created by a
> former programmer in our office. The file server name in the beginning was
> called FRE3.
> In the beginning the server was running NT4 and the users were on Win95
> desktops.
> We had a migration a few years ago where the server was upgraded to Windows
> 2000 server and the desktops were upgraded to Windows 2000 Professional. We
> also had to make a server name change to fall in line with official naming
> conventions. The server's name was changed to NTFRE. It was and still is
> just a member server in our AD.
> Of course, the client app had to be modified as it refers to the server name
> and us non-programmers figured out how to change the name in the Visual Basic
> code and recompiled a new executable and it worked fine. Just had to change
> one instance in the code where it names the server.
> We are now going through a new migration. We have a new box and have
> installed Windows Server 2003 Enterprise on it. We have also installed SQL
> 2000 on it. Have updated all service packs and everything. Ran the copy
> database wizard and successfully brought over the database from the SQL 7.0
> server, as well as user logins. User destops are being migrated to Windows
> XP as well.
> So we broke into the code again, and changed the server name again like last
> time, (new 2003 server is called S2K3-FRE-SQL1), and recompiled a new
> executable.
> Houston, we now have a problem. When I executed new executable on new XP
> machine, I get this error message after logging in:
> Run Time Error - '2147217900(80040e14)
> Incorrect syntax near keyword 'function'
>
> Does this error message mean anything to any of you gurus?
> There is one bit of info that I need to relay, not sure if it matters or not:
> The old server running 7.0, in Enterprise Manager, under SQL Server Group,
> it states 2KFRE (Windows NT).
> In the new server running 2000, in Enterprise Manager, under SQL Server
> Group, it does not state the name of the server (S2K3-FRE-SQL1), it just
> states Local (Windows NT).
> Should I worry about this? Is the code looking for S2K3-FRE-SQL1 and seeing
> the word Local instead, thus giving me my error message'?
> Do you believe that the client code needs to be modified somewhere else now'
> Is this error message a reflection of code built to address a SQL 7.0
> installation and now it doesn't work as it's trying to talk to a SQL 2000
> installation'
>
>sql

Incorrect syntax near keyword "function".

First a little history:
We have a Pentium II server running SQL 7.0. The SQL has a database that
our users connect to using a client front end app that was created by a
former programmer in our office. The file server name in the beginning was
called FRE3.
In the beginning the server was running NT4 and the users were on Win95
desktops.
We had a migration a few years ago where the server was upgraded to Windows
2000 server and the desktops were upgraded to Windows 2000 Professional. W
e
also had to make a server name change to fall in line with official naming
conventions. The server's name was changed to NTFRE. It was and still is
just a member server in our AD.
Of course, the client app had to be modified as it refers to the server name
and us non-programmers figured out how to change the name in the Visual Basi
c
code and recompiled a new executable and it worked fine. Just had to change
one instance in the code where it names the server.
We are now going through a new migration. We have a new box and have
installed Windows Server 2003 Enterprise on it. We have also installed SQL
2000 on it. Have updated all service packs and everything. Ran the copy
database wizard and successfully brought over the database from the SQL 7.0
server, as well as user logins. User destops are being migrated to Windows
XP as well.
So we broke into the code again, and changed the server name again like last
time, (new 2003 server is called S2K3-FRE-SQL1), and recompiled a new
executable.
Houston, we now have a problem. When I executed new executable on new XP
machine, I get this error message after logging in:
Run Time Error - '2147217900(80040e14)
Incorrect syntax near keyword 'function'
Does this error message mean anything to any of you gurus?
There is one bit of info that I need to relay, not sure if it matters or not
:
The old server running 7.0, in Enterprise Manager, under SQL Server Group,
it states 2KFRE (Windows NT).
In the new server running 2000, in Enterprise Manager, under SQL Server
Group, it does not state the name of the server (S2K3-FRE-SQL1), it just
states Local (Windows NT).
Should I worry about this? Is the code looking for S2K3-FRE-SQL1 and seeing
the word Local instead, thus giving me my error message'?
Do you believe that the client code needs to be modified somewhere else now?
?
Is this error message a reflection of code built to address a SQL 7.0
installation and now it doesn't work as it's trying to talk to a SQL 2000
installation'Hi
function is a keyword so can't be used in the code. You may want to use the
scripting options of Enterprise Manager to script out the stored procedures
to try a textual search for function and see where it occurs. It could be
that you also have triggers which may contain the the key word. Alternativel
y
you could use SQL profiler to look at what SQL is being run on the server an
d
try and narrow down what is going on. If you want to look at
http://tinyurl.com/yejfye which is a MSDN webcast on using SQL 2000 Profiler
,
there is also one for SQL 2005 and other tools.
You also have a lot of information in books online and it is certainly worth
your while browsing it.
HTH
John
"Rockitman" wrote:

> First a little history:
> We have a Pentium II server running SQL 7.0. The SQL has a database that
> our users connect to using a client front end app that was created by a
> former programmer in our office. The file server name in the beginning wa
s
> called FRE3.
> In the beginning the server was running NT4 and the users were on Win95
> desktops.
> We had a migration a few years ago where the server was upgraded to Window
s
> 2000 server and the desktops were upgraded to Windows 2000 Professional.
We
> also had to make a server name change to fall in line with official naming
> conventions. The server's name was changed to NTFRE. It was and still i
s
> just a member server in our AD.
> Of course, the client app had to be modified as it refers to the server na
me
> and us non-programmers figured out how to change the name in the Visual Ba
sic
> code and recompiled a new executable and it worked fine. Just had to chan
ge
> one instance in the code where it names the server.
> We are now going through a new migration. We have a new box and have
> installed Windows Server 2003 Enterprise on it. We have also installed SQ
L
> 2000 on it. Have updated all service packs and everything. Ran the copy
> database wizard and successfully brought over the database from the SQL 7.
0
> server, as well as user logins. User destops are being migrated to Window
s
> XP as well.
> So we broke into the code again, and changed the server name again like la
st
> time, (new 2003 server is called S2K3-FRE-SQL1), and recompiled a new
> executable.
> Houston, we now have a problem. When I executed new executable on new XP
> machine, I get this error message after logging in:
> Run Time Error - '2147217900(80040e14)
> Incorrect syntax near keyword 'function'
>
> Does this error message mean anything to any of you gurus?
> There is one bit of info that I need to relay, not sure if it matters or n
ot:
> The old server running 7.0, in Enterprise Manager, under SQL Server Grou
p,
> it states 2KFRE (Windows NT).
> In the new server running 2000, in Enterprise Manager, under SQL Server
> Group, it does not state the name of the server (S2K3-FRE-SQL1), it just
> states Local (Windows NT).
> Should I worry about this? Is the code looking for S2K3-FRE-SQL1 and seei
ng
> the word Local instead, thus giving me my error message'?
> Do you believe that the client code needs to be modified somewhere else no
w'
> Is this error message a reflection of code built to address a SQL 7.0
> installation and now it doesn't work as it's trying to talk to a SQL 2000
> installation'
>
>

Incorrect Syntax Near '-'

Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackHi,
Put the server name in sqare brackets [].
[SQL-SERVER2]
Thanks
Hari
SQL Server MVP
"Jack Hwang" <jack_hc@.hotmail.com> wrote in message
news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SP that running every 20 min, the SP will update some tables from
> DB on another SQL server.
> ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> server name in SP, but during test SP, i got error Incorrect Syntax Near
> '-', seems i can't use '-' when refering server, is it normal?
> how could i correct this issue other than rename server?
> Appreicate your help.
> Jack
>|||Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a SP that running every 20 min, the SP will update some tables
from
> > DB on another SQL server.
> >
> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
> > server name in SP, but during test SP, i got error Incorrect Syntax Near
> > '-', seems i can't use '-' when refering server, is it normal?
> >
> > how could i correct this issue other than rename server?
> >
> > Appreicate your help.
> >
> > Jack
> >
> >
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Put the server name in sqare brackets [].
>> [SQL-SERVER2]
>> Thanks
>> Hari
>> SQL Server MVP
>> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
>> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a SP that running every 20 min, the SP will update some tables
> from
>> > DB on another SQL server.
>> >
>> > ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
>> > SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced
>> > the
>> > server name in SP, but during test SP, i got error Incorrect Syntax
>> > Near
>> > '-', seems i can't use '-' when refering server, is it normal?
>> >
>> > how could i correct this issue other than rename server?
>> >
>> > Appreicate your help.
>> >
>> > Jack
>> >
>> >
>>
>

Incorrect Syntax Near '-'

Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
Jack
Brilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
from
>
|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/artic...nventions.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>
sql

Incorrect Syntax Near '-'

Hi,
I have a SP that running every 20 min, the SP will update some tables from
DB on another SQL server.
ie, SQLSERVER1.abc table get update from SQLSERVER2.xyz table, now,
SQLSERVER2 changed name to SQL-SERVER2 due to OS upgrade, i replaced the
server name in SP, but during test SP, i got error Incorrect Syntax Near
'-', seems i can't use '-' when refering server, is it normal?
how could i correct this issue other than rename server?
Appreicate your help.
JackBrilliant! it works
Thanks Hari!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Put the server name in sqare brackets [].
> [SQL-SERVER2]
> Thanks
> Hari
> SQL Server MVP
> "Jack Hwang" <jack_hc@.hotmail.com> wrote in message
> news:%23cWIMfiRFHA.1528@.TK2MSFTNGP09.phx.gbl...
from[vbcol=seagreen]
>|||"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>|||You should always try to take care of some naming conventions in SQL Server
to
make life easier:
http://weblogs.asp.net/jamauss/arti...onventions.aspx
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack Hwang" <jack_hc@.hotmail.com> schrieb im Newsbeitrag
news:OV%23b1qiRFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Brilliant! it works
> Thanks Hari!
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ell2HiiRFHA.2356@.TK2MSFTNGP14.phx.gbl...
> from
>

incorrect syntax after server up for a few days

Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--
Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
--

There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?

Thanks for any hint!

I attach my codes below. Anything wrong with the way that I handled the ADODB?

------------------
Public Sub SendAllEmails()
Try
Dim cn As ADODB.Connection = openConn()
Dim rs As ADODB._Recordset
Dim rs2 As ADODB._Recordset
sqlstmt = "select * FROM EMAILTABLE"
rs = cn.Execute(sqlstmt)

While Not rs.EOF

Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString

sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
rs2 = cn.Execute(sqlstmt)

If Not rs2.EOF And ErrMsg = "" Then

ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)

If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
MAIL_DATE_SENT = Now.ToString
MAIL_STATUS = "S"

'wait to make sure the email is sent
System.Threading.Thread.Sleep(1000 * 30)
Else
MAIL_STATUS = "F"
End If

End If

rs2.Close()

sqlstmt = "update EMAILTABLE set " & _
" MAIL_STATUS = " & MAIL_STATUS & "," & _
" MAIL_ERRMSG = null" & _
" where EMAIL = " & MAIL_ADD

cn.Execute(sqlstmt)

rs.MoveNext()

End While

rs.Close()
cn.Close()

Catch e As Exception
EventLog1.WriteEntry("Exception: " & e.ToString)
End Try

End Sub
----------------------Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

--

Are you using VB.Net? Then how come Recordset come into existence...:S
See the BOL to use VB.net (ADO.Net)|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.|||Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

Thanks.

Check these...
sending mail (http://www.c-sharpcorner.com/UploadFile/sushmita_kumari/SendingMail101062006054220AM/SendingMail1.aspx?ArticleID=91ece6d8-eaaf-41ab-ac6f-533dc215eacf)

Ado.net stored proc use (http://aspalliance.com/673_CodeSnip_Calling_a_Stored_Procedure_from_ASPNE T_20)

All about Ado.net (http://aspalliance.com/articles/LearnADONET.aspx)

And you can always use Profiler to check the status of your server.And to kill process check this
Kill Process (http://msdn2.microsoft.com/en-us/library/ms173730.aspx)

Incorrect SET options all of a sudden?

We've been running a SQL Server based application for some time (Access
front-end). Suddenly, the application is reporting an error when running a
stored procedure to insert a new row in a specific table; there's an Exec
statement doing it. Here is the error:
--
Insert failed because the following SET options have incorrect settings:
ANSI_nulls
Quoted_identifier
Arith abort
--
Does anyone know what could have changed in SQL Server to cause this? We can
add a new record manually through Enterprise Manager. Thanks!!Is it possible that something in the app changed that issues different set
statments? Use SQL Profiler to take a look at what's being sent...
or... could it be that someone recompiled the procedure with different set
options in effect?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time (Access
> front-end). Suddenly, the application is reporting an error when running a
> stored procedure to insert a new row in a specific table; there's an Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this? We
can
> add a new record manually through Enterprise Manager. Thanks!!
>|||Hi,
I don't know if its possible. This is a production system that started to
exhibit this behaviour in the middle of the day. There's just one developer,
but he always works on a separate development database (same server
however). We'll keep checking. Any additional ideas are welcome.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:#TX7M5ydDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Is it possible that something in the app changed that issues different set
> statments? Use SQL Profiler to take a look at what's being sent...
> or... could it be that someone recompiled the procedure with different set
> options in effect?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> > We've been running a SQL Server based application for some time (Access
> > front-end). Suddenly, the application is reporting an error when running
a
> > stored procedure to insert a new row in a specific table; there's an
Exec
> > statement doing it. Here is the error:
> >
> > --
> > Insert failed because the following SET options have incorrect settings:
> > ANSI_nulls
> > Quoted_identifier
> > Arith abort
> > --
> >
> > Does anyone know what could have changed in SQL Server to cause this? We
> can
> > add a new record manually through Enterprise Manager. Thanks!!
> >
> >
>|||Perhaps someone created an index on a computed column or on a view that
references the table. This will require that the options listed be
turned on during update operations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time
(Access
> front-end). Suddenly, the application is reporting an error when
running a
> stored procedure to insert a new row in a specific table; there's an
Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect
settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this?
We can
> add a new record manually through Enterprise Manager. Thanks!!
>sql

Inconsistent Performance of Insert

I am experiencing inconsistent performance when running
insert statements.
Our application automatically generates and sequentially
executes SQL statements that build a results table. After
loading data and running this process, all statements
(about 125 in total) will execute in about 10 minutes. If
it is necessary to rerun the statements I truncate the
results table and rerun the job. In some cases this
second job runs in 10 minutes, and in other cases it runs
6 hours. In particular, one statement takes an inordinate
amount of time.
I've used PROFILER (although I'm not too experienced in
this) and can see that when statements are running
normally the log file updates in the 1000+ per second
range, but with the problematic statement updates are in
the 10 per second range. These updates seem to appear
after the commit statement is executed.
In various combinations I've tried restarting SQLServer,
updating statistics, defragging the hard drive, using
query optimizer, all with no luck.
Any ideas?
Here's one of the statements that seems to get hung up:
Table AV has 4 columns including a Record ID (RID), a
record name (FID) and a record value (Value). This table
will ultimately have approximately 4 million rows at the
completion of the job.
Table A contains a Record ID (RID) and a Process ID
(PID). This table has approx. 100,000 records.
INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
(ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
(ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
(ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
= ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
= 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID = ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
276, 277, 278, 279, 280, 281))
I recognize that the same table AV is being joined
multiple times, but it's my understanding that this should
not be a problem.
Thanks for any guidance on this.Not much to go on but have you looked to see if you are being blocked when
this is happening? How about disk and cpu queues?
--
Andrew J. Kelly SQL MVP
"Chuck Hardy" <chardy@.coreprofit.com> wrote in message
news:16c501c4a62e$f11bbd90$a601280a@.phx.gbl...
> I am experiencing inconsistent performance when running
> insert statements.
> Our application automatically generates and sequentially
> executes SQL statements that build a results table. After
> loading data and running this process, all statements
> (about 125 in total) will execute in about 10 minutes. If
> it is necessary to rerun the statements I truncate the
> results table and rerun the job. In some cases this
> second job runs in 10 minutes, and in other cases it runs
> 6 hours. In particular, one statement takes an inordinate
> amount of time.
> I've used PROFILER (although I'm not too experienced in
> this) and can see that when statements are running
> normally the log file updates in the 1000+ per second
> range, but with the problematic statement updates are in
> the 10 per second range. These updates seem to appear
> after the commit statement is executed.
> In various combinations I've tried restarting SQLServer,
> updating statistics, defragging the hard drive, using
> query optimizer, all with no luck.
> Any ideas?
> Here's one of the statements that seems to get hung up:
> Table AV has 4 columns including a Record ID (RID), a
> record name (FID) and a record value (Value). This table
> will ultimately have approximately 4 million rows at the
> completion of the job.
> Table A contains a Record ID (RID) and a Process ID
> (PID). This table has approx. 100,000 records.
> INSERT INTO AV(RID, FID, Value) SELECT A.RID, 325, ISNULL
> (ZZTEMPA.Value, 0)+ISNULL(ZZTEMPB.Value, 0)+ISNULL
> (ZZTEMPC.Value, 0)+ISNULL(ZZTEMPD.Value, 0)+ISNULL
> (ZZTEMPE.Value, 0) FROM A LEFT JOIN AV AS ZZTEMPA ON A.RID
> = ZZTEMPA.RID AND ZZTEMPA.FID = 496 LEFT JOIN AV AS
> ZZTEMPB ON A.RID = ZZTEMPB.RID AND ZZTEMPB.FID = 497 LEFT
> JOIN AV AS ZZTEMPC ON A.RID = ZZTEMPC.RID AND ZZTEMPC.FID
> = 499 LEFT JOIN AV AS ZZTEMPD ON A.RID = ZZTEMPD.RID AND
> ZZTEMPD.FID = 502 LEFT JOIN AV AS ZZTEMPE ON A.RID => ZZTEMPE.RID AND ZZTEMPE.FID = 504 WHERE (A.PID IN (275,
> 276, 277, 278, 279, 280, 281))
> I recognize that the same table AV is being joined
> multiple times, but it's my understanding that this should
> not be a problem.
> Thanks for any guidance on this.
>

Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.

Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.

Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".

|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?

|||

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps

Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.

Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.

Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".

|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?

|||

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps

Inconsistent OLE DB Failure when running SSIS packages

I have a series of SSIS packages which populate 12 different databases. Which data source & target they use is controlled by values passed down from the SQL Agent Scheduler Job Step using "Set Values"

These values are passed to Variables which are used in the Expressions in the connection manager for the database to change the connection string and initial catalog.

e.g. REPLACE( @.[User::ConString] , "Royalty", @.[User::RoyDb] )

These jobs run successfully the majority of the time but each day I get a significant number of failures where one or more target or error trapping table cant be found. They are all using the same connection manager and most of the tables in the database get updated correctly but the job fails on trying to access one or two of the tables, with the following message in the On Error event:

-1071636248,0x,Opening a rowset for "[dbo].[RIGDUE_DAY]" failed. Check that the object exists in the database.

This happens both when I schedule the jobs in parallel with other jobs running the same packages & when I run the job by itself using the right click, start at step option. e.g.

I had one fail last night, I ran it by itself this morning, it failed, I ran it again, it succeeded. Nothing concerning the data it was transforming had changed.

I have applied the hotfix to service pack one concerning the paralel use of variables in a package as referred to in the following link.

http://support.microsoft.com/kb/918091#appliesto

Any ideas welcome

The ideal patch level is 2153 (at this point), available at http://support.microsoft.com/kb/918222/.

Prior to the main data flow , it a useful diagnostic to log/print the connection strings in a script task (there are some examples on the forum of iterating through DTS.Connections). This shows the result of expression/configuration should it have occurred prior to that point. I realize your issue here is intermittent, but perhaps this step can narrowed down matters further.

Also, the access mode of Table/View vs. SQL Command has a different db activity pattern (there are some blog posts to this effect), and anyone can see as much using SQL Server profiler. If the problem persists, you may wish to switch from accessing the source via OPENROWSET mode ("Table/View") [dbo].[RIGDUE_DAY] to accessing via Sql Command access mode "select x,y,z from [dbo].[RIGDUE_DAY]".

|||Thanks for responding, I checked the patch level and we are up to date (2153) I will try inserting a diagnostic step to output the connection string. Re the method of accessing the source, the problem has been with the destination connections, would this still apply?|||I was having intermittent connections fail on one installation. We tracked it down to contention for server resources with other clients (info-point). When we kick out other clients, the server would respond reliably. Not all clients use resources and/or licenses appropriately when connecting to servers. Do you have other clients connecting?|||

I am having the same problem. I am connecting to a DB2 source and a SQL Server 2005 destination. I have 5 concurrent dataflow tasks running, 4 of those with only one table and a large amount of data, and the 5th with 22 tables with a small amount of data. All data flow tasks were created from Import Export Wizard and were copied into a single package. There is only 1 source and 1 destination connection manager. The data flow task that almost always fails is the one with 22 tables, but it has succeeded a couple times. Only two of the other data flow tasks have failed and they only fail rarely, but they only fail when the data flow with 22 tables fails.

All source components have AlwaysUseDefaultCodePage = true. Has anyone found the root cause of this problem?

|||

Hi for other reasons I had to divide my packages up using seqence containers, since I did this there has been no recurrence of the problem. So the task which sets up my dynamic variables is in a seperate container from that which contains the tasks using the connections.

Not 100% sure why this works but it seems to.

Hope this helps

incompatiable data sync

Hi,

We are going to be running two SQL Server 2000's from the same
physical server, with Windows Server 2003, and I need to have certain
tables between them syncronized. Database A is a backend to a website and
database B is going to be used by a different department. Both the
databases have certain data in common but it isn't stored in the same
format. I can't just auto sync one with the other, the data (records) has
to be manipulated before updates between them can take place.

So, what I need to do is convert certain data when A is updated and tranfer
that to B and vice-versa, in real-time. I've looked at Snapshot, that
isn't an option. I imagine some kind of Event Driven API is needed, where
do i being?

TIA"someguy" <inderjitrai@.yahoo.com> wrote in message
news:Xns9600EF1CC26FAinderjitraiyahoocom@.216.196.9 7.142...
> Hi,
> We are going to be running two SQL Server 2000's from the same
> physical server, with Windows Server 2003, and I need to have certain
> tables between them syncronized. Database A is a backend to a website and
> database B is going to be used by a different department. Both the
> databases have certain data in common but it isn't stored in the same
> format. I can't just auto sync one with the other, the data (records) has
> to be manipulated before updates between them can take place.
> So, what I need to do is convert certain data when A is updated and
> tranfer
> that to B and vice-versa, in real-time. I've looked at Snapshot, that
> isn't an option. I imagine some kind of Event Driven API is needed, where
> do i being?
> TIA

Have you considered replication? It sounds as if transactional replication
may be what you need, there's plenty of information in Books Online, and
there's also a newsgroup for replication -
microsoft.public.sqlserver.replication.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in news:4215a0ea$1_3@.news.bluewin.ch:

> Thanks Simone. Any books/online tutorial you would recommend?
> "someguy" <inderjitrai@.yahoo.com> wrote in message
> news:Xns9600EF1CC26FAinderjitraiyahoocom@.216.196.9 7.142...
>>
>> Hi,
>>
>> We are going to be running two SQL Server 2000's from the same
>> physical server, with Windows Server 2003, and I need to have certain
>> tables between them syncronized. Database A is a backend to a
>> website and database B is going to be used by a different department.
>> Both the databases have certain data in common but it isn't stored
>> in the same format. I can't just auto sync one with the other, the
>> data (records) has to be manipulated before updates between them can
>> take place.
>>
>> So, what I need to do is convert certain data when A is updated and
>> tranfer
>> that to B and vice-versa, in real-time. I've looked at Snapshot,
>> that isn't an option. I imagine some kind of Event Driven API is
>> needed, where do i being?
>>
>> TIA
> Have you considered replication? It sounds as if transactional
> replication may be what you need, there's plenty of information in
> Books Online, and there's also a newsgroup for replication -
> microsoft.public.sqlserver.replication.
> Simon|||"someguy" <inderjitrai@.yahoo.com> wrote in message
news:Xns9602CCEBFA3Binderjitraiyahoocom@.216.196.97 .142...
> "Simon Hayes" <sql@.hayes.ch> wrote in news:4215a0ea$1_3@.news.bluewin.ch:
>> Thanks Simone. Any books/online tutorial you would recommend?

<snip
As always with MSSQL, read through the relevant Books Online sections first;
next, set up a simple replication scenario and play around with it. By the
time you've done that, you'll have a better understanding of what you need
to know, and you'll be able to use sources like microsoft.com, Google and
newsgroups more efficiently.

I don't have much experience of replication myself, so if you feel you need
more detailed help on finding good information sources, then I would suggest
asking in the replication newsgroup. But I strongly suggest you read through
the Books Online information first, or else people will just refer you back
to it anyway.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> "someguy" <inderjitrai@.yahoo.com> wrote in message
> news:Xns9600EF1CC26FAinderjitraiyahoocom@.216.196.9 7.142...
>> We are going to be running two SQL Server 2000's from the same
>> physical server, with Windows Server 2003, and I need to have certain
>> tables between them syncronized. Database A is a backend to a website
>> and database B is going to be used by a different department. Both the
>> databases have certain data in common but it isn't stored in the same
>> format. I can't just auto sync one with the other, the data (records)
>> has to be manipulated before updates between them can take place.
>>
>> So, what I need to do is convert certain data when A is updated and
>> tranfer that to B and vice-versa, in real-time. I've looked at
>> Snapshot, that isn't an option. I imagine some kind of Event Driven
>> API is needed, where do i being?
> Have you considered replication? It sounds as if transactional replication
> may be what you need, there's plenty of information in Books Online, and
> there's also a newsgroup for replication -
> microsoft.public.sqlserver.replication.

Transactional replication certainly could be a possibility. But it seems
that the two databases have differnt layout. Replication is normally used
between identical databases. I believe that there are hooks in replication
to permit you to deviate from the pattern, but if there is to be trans-
formatation on a greater scale, the solution can be very complex.

An alternative would be to have triggers that writes data to events
table, and then you have a job running from SQL Server Agent that
pick up the events and relays them to the other server. With the very
brief information given here, I would rather investigate such a
solution, before I tried replication. (But then again, I haven't looked
at replication since 1998, and that was 6.5.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Inaccurate HTML Rendering

(RS 2000)
I have 6 rows of text boxes in my body header running the width of the
report. All look fine in the report designer and when rendered to Adobe.
When I output to HTML, there are an extra 2 blank rows between the 5 and 6
TB. All the TB properties are exactly the same; I have tried
deleting/recreating, shifting around, nothing seems to help.
Thanks for any suggestionsFor anyone else that has the same problem I ran into where the HTML output
looks different than the designer or other output: I found if I slightly
reduced the vertical height of all the text boxes it fixed the problem.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23zD8P27TGHA.5908@.TK2MSFTNGP14.phx.gbl...
> (RS 2000)
> I have 6 rows of text boxes in my body header running the width of the
> report. All look fine in the report designer and when rendered to Adobe.
> When I output to HTML, there are an extra 2 blank rows between the 5 and 6
> TB. All the TB properties are exactly the same; I have tried
> deleting/recreating, shifting around, nothing seems to help.
> Thanks for any suggestions
>