So here's what happened:
I was running out of space on my DB partition (there was a sudden surge in
DB size due to a lot of scanned documents being attached in a short period
of time). To free up space for a day or two while I waited for an
additional drive to add to the RAID set, I decided to temporarily move an
older, infrequently-accessed database to another volume. Here's what I did:
- I stopped the server and server agent services for the database I was
moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
folder, lock stock and barrel, to a network share on another server.
- When the new drive arrived a day later, I added it to the RAID set and
increased the size of the volume.
- I rebooted the SQL server for reasons unrelated to this issue. On
startup, it reported that a driver or service had failed because the Old-DB
had tried to start, but of course it's MSSQL folder wasn't there.
- I copied the MSSQL folder for Old-DB back to its original location on the
SQL server.
Now I get a 1053 error when trying to start the SQL Server, and a 1068 error
when trying to start the SQL Server Agent. Enterprise Manager now shows a
(local) database that's not started, with the message (Connection failed,
check SQL Server Registration Properties) under it.
If I understand correctly, my big worry shouldn't be about the data (which
is probably fine), but about the user logins. Is that right?
What's my next step to getting this DB back online? It's not urgent, but it
IS important that we retain or regain access to the historical data in this
DB.
Thanks in advance!
BJYou should perform a full system restore to recover to the state you were in
before you started moving files.
Then start again and do the job "correctly" i.e. by following the documented
procedures (see Books Online) for moving databases.
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
> So here's what happened:
> I was running out of space on my DB partition (there was a sudden surge in
> DB size due to a lot of scanned documents being attached in a short period
> of time). To free up space for a day or two while I waited for an
> additional drive to add to the RAID set, I decided to temporarily move an
> older, infrequently-accessed database to another volume. Here's what I
> did:
> - I stopped the server and server agent services for the database I was
> moving -- I'll call it Old-DB -- , then simply copied its entire MSSQL
> folder, lock stock and barrel, to a network share on another server.
> - When the new drive arrived a day later, I added it to the RAID set and
> increased the size of the volume.
> - I rebooted the SQL server for reasons unrelated to this issue. On
> startup, it reported that a driver or service had failed because the
> Old-DB had tried to start, but of course it's MSSQL folder wasn't there.
> - I copied the MSSQL folder for Old-DB back to its original location on
> the SQL server.
> Now I get a 1053 error when trying to start the SQL Server, and a 1068
> error when trying to start the SQL Server Agent. Enterprise Manager now
> shows a (local) database that's not started, with the message (Connection
> failed, check SQL Server Registration Properties) under it.
> If I understand correctly, my big worry shouldn't be about the data (which
> is probably fine), but about the user logins. Is that right?
> What's my next step to getting this DB back online? It's not urgent, but
> it IS important that we retain or regain access to the historical data in
> this DB.
> Thanks in advance!
> BJ
>|||I have a full disk image of the SQL server that was taken just prior to
retiring the database and migrating to the new version of the application.
I'll restore that image to temporary hardware, verify that I can access the
DB, and then use Books Online to lookup and follow an accepted procedure for
moving or restoring that database to a new server.
Thanks for cutting to the chase. :-)
BJ
"Mark Yudkin" <DoNotContactMe@.boingboing.org> wrote in message
news:uwx0LO8NHHA.4172@.TK2MSFTNGP04.phx.gbl...
> You should perform a full system restore to recover to the state you were
> in before you started moving files.
> Then start again and do the job "correctly" i.e. by following the
> documented procedures (see Books Online) for moving databases.
> "Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
> news:e3%235claNHHA.2232@.TK2MSFTNGP02.phx.gbl...
>
Saturday, February 25, 2012
MY SQL server can not connect with my PC
HI
I have a peoblem when I make connect the SQL server with my PC
and this message apper
{
cannot connect to PC.......\SQLEXPRESS
Additional information:
An error has occurred while establishing a connection to the serves . When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection.( provider: SQL Network Interface , error:26 - Error locating
Server/ Instance Specified) ( Microsoft SQL Server )
}
can you help me pleas .....
You need to explicitly enable remote connections for SQL Express since they are off by default. You can enable them during install using the DISABLENETWORKPROTOCOLS flag:
(From template.ini)
;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On
; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2
or after install using TSQL or following this blog:
http://support.microsoft.com/kb/914277
Thanks,
Sam Lester (MSFT)
I have been struggling with this for about a week now. For some reason today when I searched I found this thread relatively quickly, and this reply held the solution.
For me, it was the single line of text "Turn on the SQL Server Browser Service." that solved the problem.
SQL Server Browser Service was not running on my server. I had to use the SQL Server Surface Area Configuration Tool to enable and start the SQL Server Browser Service. As soon as that worked, I had no problems.
Thanks.
Jim Sparks
My SQL Server Agent
hi,
my sql server agent, in the properties window, mail
profile is not enabled?
can somebody help me, what is the problem, i am new to
this area.
thanks
sathyathanks Jens >--Original Message--
>Hello Sathya,
>you have to use a MAPI E-Mail Client to use the integrated Mail functions of
>SQL Server. For example installing Outlook and adding a mail profile to the
>server will make you able to use the Mail-Profile specified for the
>computer.
>HTH, Jens S=FC=DFmeyer.
>Don=B4t know how to do this?
>Just read:
>http://groups.google.de/groups?
q=3Dmapi+sql+server&hl=3Dde&lr=3D&ie=3DUTF-8&oe=3DUTF-8
>&selm=3D3b4939ad_2%40news.nwlink.com&rnum=3D1
>
>> my sql server agent, in the properties window, mail
>> profile is not enabled?
>
>.
>
my sql server agent, in the properties window, mail
profile is not enabled?
can somebody help me, what is the problem, i am new to
this area.
thanks
sathyathanks Jens >--Original Message--
>Hello Sathya,
>you have to use a MAPI E-Mail Client to use the integrated Mail functions of
>SQL Server. For example installing Outlook and adding a mail profile to the
>server will make you able to use the Mail-Profile specified for the
>computer.
>HTH, Jens S=FC=DFmeyer.
>Don=B4t know how to do this?
>Just read:
>http://groups.google.de/groups?
q=3Dmapi+sql+server&hl=3Dde&lr=3D&ie=3DUTF-8&oe=3DUTF-8
>&selm=3D3b4939ad_2%40news.nwlink.com&rnum=3D1
>
>> my sql server agent, in the properties window, mail
>> profile is not enabled?
>
>.
>
My SQL Provider
Hello to all,
I have a problem in connecting in a MySQL database. I can create some ODBC
connection, but I can't use it to manipulate data.
I like Know if Existe a MySQL Provider for SQL Server 2005 that alow me to
create connections and manipulate date withe this connections.
Thank you in advance.
It's not clear but I would guess you are trying to connect
from SQL Server and query MySQL?
First - what do you mean you can create an odbc connection
but can't manipulate data? Where did you create the
connection - linked server or what?
What errors did you get trying to do this? Can you execute
any selects?
To download MySQL drivers and providers, try the drivers and
connectors section at following site:
http://www.mysql.org/downloads/
-Sue
On Wed, 25 Apr 2007 07:08:01 -0700, malek_che
<malekche@.discussions.microsoft.com> wrote:
>Hello to all,
>I have a problem in connecting in a MySQL database. I can create some ODBC
>connection, but I can't use it to manipulate data.
>I like Know if Existe a MySQL Provider for SQL Server 2005 that alow me to
>create connections and manipulate date withe this connections.
>Thank you in advance.
I have a problem in connecting in a MySQL database. I can create some ODBC
connection, but I can't use it to manipulate data.
I like Know if Existe a MySQL Provider for SQL Server 2005 that alow me to
create connections and manipulate date withe this connections.
Thank you in advance.
It's not clear but I would guess you are trying to connect
from SQL Server and query MySQL?
First - what do you mean you can create an odbc connection
but can't manipulate data? Where did you create the
connection - linked server or what?
What errors did you get trying to do this? Can you execute
any selects?
To download MySQL drivers and providers, try the drivers and
connectors section at following site:
http://www.mysql.org/downloads/
-Sue
On Wed, 25 Apr 2007 07:08:01 -0700, malek_che
<malekche@.discussions.microsoft.com> wrote:
>Hello to all,
>I have a problem in connecting in a MySQL database. I can create some ODBC
>connection, but I can't use it to manipulate data.
>I like Know if Existe a MySQL Provider for SQL Server 2005 that alow me to
>create connections and manipulate date withe this connections.
>Thank you in advance.
My SQL Provider
Hello to all,
I have a problem in connecting in a mysql database. I can create some ODBC
connection, but I can't use it to manipulate data.
I like Know if Existe a mysql Provider for SQL Server 2005 that alow me to
create connections and manipulate date withe this connections.
Thank you in advance.It's not clear but I would guess you are trying to connect
from SQL Server and query MySQL?
First - what do you mean you can create an odbc connection
but can't manipulate data? Where did you create the
connection - linked server or what?
What errors did you get trying to do this? Can you execute
any selects?
To download mysql drivers and providers, try the drivers and
connectors section at following site:
http://www.mysql.org/downloads/
-Sue
On Wed, 25 Apr 2007 07:08:01 -0700, malek_che
<malekche@.discussions.microsoft.com> wrote:
>Hello to all,
>I have a problem in connecting in a mysql database. I can create some ODBC
>connection, but I can't use it to manipulate data.
>I like Know if Existe a mysql Provider for SQL Server 2005 that alow me to
>create connections and manipulate date withe this connections.
>Thank you in advance.
I have a problem in connecting in a mysql database. I can create some ODBC
connection, but I can't use it to manipulate data.
I like Know if Existe a mysql Provider for SQL Server 2005 that alow me to
create connections and manipulate date withe this connections.
Thank you in advance.It's not clear but I would guess you are trying to connect
from SQL Server and query MySQL?
First - what do you mean you can create an odbc connection
but can't manipulate data? Where did you create the
connection - linked server or what?
What errors did you get trying to do this? Can you execute
any selects?
To download mysql drivers and providers, try the drivers and
connectors section at following site:
http://www.mysql.org/downloads/
-Sue
On Wed, 25 Apr 2007 07:08:01 -0700, malek_che
<malekche@.discussions.microsoft.com> wrote:
>Hello to all,
>I have a problem in connecting in a mysql database. I can create some ODBC
>connection, but I can't use it to manipulate data.
>I like Know if Existe a mysql Provider for SQL Server 2005 that alow me to
>create connections and manipulate date withe this connections.
>Thank you in advance.
My SQL is rusty
Hi Guys,
Im really rusty on SQL queries so a little help would be appreciated. I have
a users table
UserID Int
UserName String
Authoriser Int
I am trying to work out one query to return the authorisers name given the
users ID. Can this be done in one query string ?
Best Regards
The Inimitable Mr NewbieHope I'm not wrong here, but just a simple select statement would suffice.
Select Authoriser
From users
Where UserID = <whatever userid is passed>
"Mr Newbie" wrote:
> Hi Guys,
> Im really rusty on SQL queries so a little help would be appreciated. I ha
ve
> a users table
> UserID Int
> UserName String
> Authoriser Int
> I am trying to work out one query to return the authorisers name given the
> users ID. Can this be done in one query string ?
>
> --
> Best Regards
> The Inimitable Mr Newbie o?o
>
>|||
select Authorisers.AuthoriserName
from Users,
Authorisers
where UserID = @.UserId (variable or literal)
and Authorisers.Authoriser = Users.Authoriser
Assuming you have a table "Authorisers"
Authoriser int
AuthoriserName String
"Todd S" <Todd S@.discussions.microsoft.com> wrote in message
news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
> Hope I'm not wrong here, but just a simple select statement would suffice.
> Select Authoriser
> From users
> Where UserID = <whatever userid is passed>
>
> "Mr Newbie" wrote:
>|||Actually, I only have the one table so I was looking for a way to select the
Authorisers name using the ID stored in same table.
Best Regards
The Inimitable Mr Newbie
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:u6CvscU6FHA.808@.TK2MSFTNGP09.phx.gbl...
>
> select Authorisers.AuthoriserName
> from Users,
> Authorisers
> where UserID = @.UserId (variable or literal)
> and Authorisers.Authoriser = Users.Authoriser
> Assuming you have a table "Authorisers"
> Authoriser int
> AuthoriserName String
>
> "Todd S" <Todd S@.discussions.microsoft.com> wrote in message
> news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
>|||SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
HTH,
Stu|||That looks right.
Thanks very much for your help, I will test it out and post a feedback post.
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||That does not work it returns all the rows, its very odd
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||Try
SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
Where Users.UserID= [the value of the user]
"Mr Newbie" wrote:
> That does not work it returns all the rows, its very odd
> --
> Best Regards
> The Inimitable Mr Newbie o?o
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
>
>|||Perfect !!!
Many Thanks Mike. - Appreciated Greatly
Best Regards
The Inimitable Mr Newbie
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:939CA4BD-3119-4013-B35C-F56B972904A3@.microsoft.com...
> Try
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> Where Users.UserID= [the value of the user]
>
> "Mr Newbie" wrote:
>|||Sorry, I misread your original post. I thought you were tyring to
return all rows.
Stu
Im really rusty on SQL queries so a little help would be appreciated. I have
a users table
UserID Int
UserName String
Authoriser Int
I am trying to work out one query to return the authorisers name given the
users ID. Can this be done in one query string ?
Best Regards
The Inimitable Mr NewbieHope I'm not wrong here, but just a simple select statement would suffice.
Select Authoriser
From users
Where UserID = <whatever userid is passed>
"Mr Newbie" wrote:
> Hi Guys,
> Im really rusty on SQL queries so a little help would be appreciated. I ha
ve
> a users table
> UserID Int
> UserName String
> Authoriser Int
> I am trying to work out one query to return the authorisers name given the
> users ID. Can this be done in one query string ?
>
> --
> Best Regards
> The Inimitable Mr Newbie o?o
>
>|||
select Authorisers.AuthoriserName
from Users,
Authorisers
where UserID = @.UserId (variable or literal)
and Authorisers.Authoriser = Users.Authoriser
Assuming you have a table "Authorisers"
Authoriser int
AuthoriserName String
"Todd S" <Todd S@.discussions.microsoft.com> wrote in message
news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
> Hope I'm not wrong here, but just a simple select statement would suffice.
> Select Authoriser
> From users
> Where UserID = <whatever userid is passed>
>
> "Mr Newbie" wrote:
>|||Actually, I only have the one table so I was looking for a way to select the
Authorisers name using the ID stored in same table.
Best Regards
The Inimitable Mr Newbie
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:u6CvscU6FHA.808@.TK2MSFTNGP09.phx.gbl...
>
> select Authorisers.AuthoriserName
> from Users,
> Authorisers
> where UserID = @.UserId (variable or literal)
> and Authorisers.Authoriser = Users.Authoriser
> Assuming you have a table "Authorisers"
> Authoriser int
> AuthoriserName String
>
> "Todd S" <Todd S@.discussions.microsoft.com> wrote in message
> news:5C2ED2DF-BAEB-4DD4-A7A6-4B060E96C2E8@.microsoft.com...
>|||SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
HTH,
Stu|||That looks right.
Thanks very much for your help, I will test it out and post a feedback post.
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||That does not work it returns all the rows, its very odd
Best Regards
The Inimitable Mr Newbie
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> HTH,
> Stu
>|||Try
SELECT Users.UserName,
Authorisers.UserName as AuthoriserName
FROM Users JOIN Users as Authorisers ON Users.Authoriser =
Authorisers.UserID
Where Users.UserID= [the value of the user]
"Mr Newbie" wrote:
> That does not work it returns all the rows, its very odd
> --
> Best Regards
> The Inimitable Mr Newbie o?o
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1131993036.132049.243520@.z14g2000cwz.googlegroups.com...
>
>|||Perfect !!!
Many Thanks Mike. - Appreciated Greatly
Best Regards
The Inimitable Mr Newbie
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:939CA4BD-3119-4013-B35C-F56B972904A3@.microsoft.com...
> Try
> SELECT Users.UserName,
> Authorisers.UserName as AuthoriserName
> FROM Users JOIN Users as Authorisers ON Users.Authoriser =
> Authorisers.UserID
> Where Users.UserID= [the value of the user]
>
> "Mr Newbie" wrote:
>|||Sorry, I misread your original post. I thought you were tyring to
return all rows.
Stu
My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and more
I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'
ALTER PROCEDURE [domainname\myaccountname].[Categories_I]
AS
BEGIN
/* delete contents from Category table and reseed
Cannot use truncate on a table which contains constraints therefore
use DBCC to reset the seed and DELETE to clear the records
*/
DELETE dbo.Category
DBCC CHECKIDENT ('Category', RESEED, 0)
-- Now, insert the initial 'All' Root Record
ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1
PRINT 'Dropped FK'
SET IDENTITY_INSERT
INSERT INTO dbo.Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
PRINT 'Inserted All Record'
INSERT INTO dbo.Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
PRINT 'Inserted Store Record'
SET IDENTITY_INSERT Category OFF
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO dbo.Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END
PRINT 'Inserted Rest of Category Records'
PRINT 're-create FK Call'
ALTER TABLE dbo.Category
ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY
(
ParentCategoryID
) REFERENCES Category (
CategoryID
)
PRINT 'create FK2'
END
Other errors:
(1 row(s) affected)
Checking identity information: current identity value '2', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3728, Level 16, State 1, Line 6
'Category_Category_FK1' is not a constraint.
Msg 3727, Level 16, State 0, Line 6
Could not drop constraint. See previous errors.
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.
Schemas & Data:
http:
http:
http:
http:
1) To prevent error generation for deleting constraint which may not exists use simple check like below:
if exists(select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name = 'table_name' and constraint_name = 'constraint_name')
alter table dbo.table_name drop constraint constraint_name
2) Just make this select with no insert and you would probably see that either there are no rows in the table that meet your criteria or there are no rows in the table at all.
3) This error message tells you that you are trying to insert duplicate; what is unclear about that?
My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and mor
I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'
ALTER PROCEDURE [domainname\myaccountname].[Categories_I]
AS
BEGIN
/* delete contents from Category table and reseed
Cannot use truncate on a table which contains constraints therefore
use DBCC to reset the seed and DELETE to clear the records
*/
DELETE dbo.Category
DBCC CHECKIDENT ('Category', RESEED, 0)
-- Now, insert the initial 'All' Root Record
ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1
PRINT 'Dropped FK'
SET IDENTITY_INSERT
INSERT INTO dbo.Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
PRINT 'Inserted All Record'
INSERT INTO dbo.Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
PRINT 'Inserted Store Record'
SET IDENTITY_INSERT Category OFF
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO dbo.Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END
PRINT 'Inserted Rest of Category Records'
PRINT 're-create FK Call'
ALTER TABLE dbo.Category
ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY
(
ParentCategoryID
) REFERENCES Category (
CategoryID
)
PRINT 'create FK2'
END
Other errors:
(1 row(s) affected)
Checking identity information: current identity value '2', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3728, Level 16, State 1, Line 6
'Category_Category_FK1' is not a constraint.
Msg 3727, Level 16, State 0, Line 6
Could not drop constraint. See previous errors.
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.
Schemas & Data:
http:
http:
http:
http:
1) To prevent error generation for deleting constraint which may not exists use simple check like below:
if exists(select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name = 'table_name' and constraint_name = 'constraint_name')
alter table dbo.table_name drop constraint constraint_name
2) Just make this select with no insert and you would probably see that either there are no rows in the table that meet your criteria or there are no rows in the table at all.
3) This error message tells you that you are trying to insert duplicate; what is unclear about that?
My Sql Instance is gone
Heeelllpp,
My Sql instance is gone from the SQL server manager. I think the problem came up after I installed Mc Afee Virus scan and updated microsoft patches (due to the problems that we have with worms recently).
After that I can't even invoke the service at all. I tried to restart everything (uninstalled the server and reinstall it again) but since the server couldn't be connected, the install process got terminated.
Can somebody help me with this?
thx
gue,
Please supply OS and SQL Server versions.
Is there SQL Server service still there in the Services applet in control
panel? Look for MSSQLServer. Installing McAfee should not remove SQL Server,
however there is the potential for virus scanners to cause problems
occasionally - but this is normally down to configuration issues, not with
the virus scanner per se.
When you say you "uninstalled the server" - what exactly do you mean? Did
you rebuild the OS, or just reinstall SQL Server? Also, what do you mean by
"the server couldn't be connected"?
It would also be very helpful if you could supply the EXACT error messages
you are getting.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"gue" <anonymous@.discussions.microsoft.com> wrote in message
news:96CF2A1A-923F-4A52-9A38-927FCFC13D28@.microsoft.com...
> Heeelllpp,
> My Sql instance is gone from the SQL server manager. I think the problem
came up after I installed Mc Afee Virus scan and updated microsoft patches
(due to the problems that we have with worms recently).
> After that I can't even invoke the service at all. I tried to restart
everything (uninstalled the server and reinstall it again) but since the
server couldn't be connected, the install process got terminated.
> Can somebody help me with this?
> thx
My Sql instance is gone from the SQL server manager. I think the problem came up after I installed Mc Afee Virus scan and updated microsoft patches (due to the problems that we have with worms recently).
After that I can't even invoke the service at all. I tried to restart everything (uninstalled the server and reinstall it again) but since the server couldn't be connected, the install process got terminated.
Can somebody help me with this?
thx
gue,
Please supply OS and SQL Server versions.
Is there SQL Server service still there in the Services applet in control
panel? Look for MSSQLServer. Installing McAfee should not remove SQL Server,
however there is the potential for virus scanners to cause problems
occasionally - but this is normally down to configuration issues, not with
the virus scanner per se.
When you say you "uninstalled the server" - what exactly do you mean? Did
you rebuild the OS, or just reinstall SQL Server? Also, what do you mean by
"the server couldn't be connected"?
It would also be very helpful if you could supply the EXACT error messages
you are getting.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"gue" <anonymous@.discussions.microsoft.com> wrote in message
news:96CF2A1A-923F-4A52-9A38-927FCFC13D28@.microsoft.com...
> Heeelllpp,
> My Sql instance is gone from the SQL server manager. I think the problem
came up after I installed Mc Afee Virus scan and updated microsoft patches
(due to the problems that we have with worms recently).
> After that I can't even invoke the service at all. I tried to restart
everything (uninstalled the server and reinstall it again) but since the
server couldn't be connected, the install process got terminated.
> Can somebody help me with this?
> thx
My Sql Instance is gone
Heeelllpp
My Sql instance is gone from the SQL server manager. I think the problem came up after I installed Mc Afee Virus scan and updated microsoft patches (due to the problems that we have with worms recently)
After that I can't even invoke the service at all. I tried to restart everything (uninstalled the server and reinstall it again) but since the server couldn't be connected, the install process got terminated
Can somebody help me with this
thxgue,
Please supply OS and SQL Server versions.
Is there SQL Server service still there in the Services applet in control
panel? Look for MSSQLServer. Installing McAfee should not remove SQL Server,
however there is the potential for virus scanners to cause problems
occasionally - but this is normally down to configuration issues, not with
the virus scanner per se.
When you say you "uninstalled the server" - what exactly do you mean? Did
you rebuild the OS, or just reinstall SQL Server? Also, what do you mean by
"the server couldn't be connected"?
It would also be very helpful if you could supply the EXACT error messages
you are getting.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"gue" <anonymous@.discussions.microsoft.com> wrote in message
news:96CF2A1A-923F-4A52-9A38-927FCFC13D28@.microsoft.com...
> Heeelllpp,
> My Sql instance is gone from the SQL server manager. I think the problem
came up after I installed Mc Afee Virus scan and updated microsoft patches
(due to the problems that we have with worms recently).
> After that I can't even invoke the service at all. I tried to restart
everything (uninstalled the server and reinstall it again) but since the
server couldn't be connected, the install process got terminated.
> Can somebody help me with this?
> thx
My Sql instance is gone from the SQL server manager. I think the problem came up after I installed Mc Afee Virus scan and updated microsoft patches (due to the problems that we have with worms recently)
After that I can't even invoke the service at all. I tried to restart everything (uninstalled the server and reinstall it again) but since the server couldn't be connected, the install process got terminated
Can somebody help me with this
thxgue,
Please supply OS and SQL Server versions.
Is there SQL Server service still there in the Services applet in control
panel? Look for MSSQLServer. Installing McAfee should not remove SQL Server,
however there is the potential for virus scanners to cause problems
occasionally - but this is normally down to configuration issues, not with
the virus scanner per se.
When you say you "uninstalled the server" - what exactly do you mean? Did
you rebuild the OS, or just reinstall SQL Server? Also, what do you mean by
"the server couldn't be connected"?
It would also be very helpful if you could supply the EXACT error messages
you are getting.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"gue" <anonymous@.discussions.microsoft.com> wrote in message
news:96CF2A1A-923F-4A52-9A38-927FCFC13D28@.microsoft.com...
> Heeelllpp,
> My Sql instance is gone from the SQL server manager. I think the problem
came up after I installed Mc Afee Virus scan and updated microsoft patches
(due to the problems that we have with worms recently).
> After that I can't even invoke the service at all. I tried to restart
everything (uninstalled the server and reinstall it again) but since the
server couldn't be connected, the install process got terminated.
> Can somebody help me with this?
> thx
My Sql Instance is gone
Heeelllpp,
My Sql instance is gone from the SQL server manager. I think the problem cam
e up after I installed Mc Afee Virus scan and updated microsoft patches (due
to the problems that we have with worms recently).
After that I can't even invoke the service at all. I tried to restart everyt
hing (uninstalled the server and reinstall it again) but since the server co
uldn't be connected, the install process got terminated.
Can somebody help me with this?
thxgue,
Please supply OS and SQL Server versions.
Is there SQL Server service still there in the Services applet in control
panel? Look for MSSQLServer. Installing McAfee should not remove SQL Server,
however there is the potential for virus scanners to cause problems
occasionally - but this is normally down to configuration issues, not with
the virus scanner per se.
When you say you "uninstalled the server" - what exactly do you mean? Did
you rebuild the OS, or just reinstall SQL Server? Also, what do you mean by
"the server couldn't be connected"?
It would also be very helpful if you could supply the EXACT error messages
you are getting.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"gue" <anonymous@.discussions.microsoft.com> wrote in message
news:96CF2A1A-923F-4A52-9A38-927FCFC13D28@.microsoft.com...
> Heeelllpp,
> My Sql instance is gone from the SQL server manager. I think the problem
came up after I installed Mc Afee Virus scan and updated microsoft patches
(due to the problems that we have with worms recently).
> After that I can't even invoke the service at all. I tried to restart
everything (uninstalled the server and reinstall it again) but since the
server couldn't be connected, the install process got terminated.
> Can somebody help me with this?
> thx
My Sql instance is gone from the SQL server manager. I think the problem cam
e up after I installed Mc Afee Virus scan and updated microsoft patches (due
to the problems that we have with worms recently).
After that I can't even invoke the service at all. I tried to restart everyt
hing (uninstalled the server and reinstall it again) but since the server co
uldn't be connected, the install process got terminated.
Can somebody help me with this?
thxgue,
Please supply OS and SQL Server versions.
Is there SQL Server service still there in the Services applet in control
panel? Look for MSSQLServer. Installing McAfee should not remove SQL Server,
however there is the potential for virus scanners to cause problems
occasionally - but this is normally down to configuration issues, not with
the virus scanner per se.
When you say you "uninstalled the server" - what exactly do you mean? Did
you rebuild the OS, or just reinstall SQL Server? Also, what do you mean by
"the server couldn't be connected"?
It would also be very helpful if you could supply the EXACT error messages
you are getting.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"gue" <anonymous@.discussions.microsoft.com> wrote in message
news:96CF2A1A-923F-4A52-9A38-927FCFC13D28@.microsoft.com...
> Heeelllpp,
> My Sql instance is gone from the SQL server manager. I think the problem
came up after I installed Mc Afee Virus scan and updated microsoft patches
(due to the problems that we have with worms recently).
> After that I can't even invoke the service at all. I tried to restart
everything (uninstalled the server and reinstall it again) but since the
server couldn't be connected, the install process got terminated.
> Can somebody help me with this?
> thx
My Sql Express is so slow?
Has anyone else noticed delays with SQL Express? I'm not really talking about delays on the queries but just delays in general response. For example: everything is running great, then for about 2 minutes I get connection timeouts etc can't even open stuff in the management studio without getting timeouts ... then as strangely as it started everything goes back to normal and requests are served again.
The server has nothing on except 1 website, its Win 2003 Server. 512MB Ram on a PIV. The memory usage is low and during the "lockups" the machine isn't showing any processor usage and SQL mem usage is around 40Megs.
I am not using User Instances either. Nothing in the event logs. What is odd, is its happening on 3 of my machines .... all with different sites, the only thing in common between them is SQL.
thanks,
-c
Are you closing and destroying your connection objects?
If you dont close and set them to nothing they will sit in the application pool taking up resources.
Just a though
my SQL cant open window when installation
Is there anyone can help me to run window when after done with installation, i wana open but i got message" this installation package could not be opened. verify that the package exists and that you can access it. or contact the application vedor to verify that this is a valid Windows Installer package"
My comp is running w/ Win home XP.
thanks
My SQL application on Windows Mobile 5
Hi
Ok Darren
My application :
On PC : Access application + Access DB
On PocketPC : Embedded Visual Basic 3.0 application + PocketAccess DB
I synchronise my DBs through ActivSync.
It runs good before Windows Mobile 5.
My customers asked me for new PocketPC with this application, so I bought some new PocketPCs : but as I see, syncronisation with Access is no more supported.
So what are my solutions ?
My customer wait for there PocketPC, so I need a quick solution, I don't want to rewrite all my application now.
My application run before WM5, so now I don't want to have to pay for a Visual Studio .Net, some SQL Server, etc ....
Please, what are the solutions that won't cost me more than the time that have already lost with this new Windows Mobile 5 ?
Jo?l
Joel,
You're at the end of the road with eVB. You are going to need to move your mobile application's code either to the .NET Compact Framework or use Visual C++ for Mobile Devices (in VS2005) to achieve Windows Mobile 5 compliance. I recommend using SQL Mobile for your on-device database as a replacement for Pocket Access. Sorry there isn't better news, but eVB and Access have reached the end of their support time horizon for current and future Windows Mobile platforms.
-Darren
|||Ok Darren
So if I move my application to .Net et use SQL Mobile 2005 on my PocketPC, what do I need to synchronise my PocketPC's database (so SQL Mobile) and my PC Database ?
Which database on PC ?
Each PocketPC will have his own PC and his own database, and it have to run my application on Windows XP Home.
What solution for synchronisation ?
Thanks for your explanations
Jo?l
|||Any solution ?
Jo?l
|||
Joel,
SQL Mobile comes with two options for data synchronization, Remote Data Access and Merge Replication. Both of these require IIS as the broker or gateway through which this synchronization occurs. In your situation, you may want to write a simple synchronization method that detects when the device is connected and uses System.Data.SqlClient to synchronize between your device database and SQL Server 2005.
-Darren
|||Ok Darren, thks.Is there a place where I can find some example of this method ?
My SQL application on Windows Mobile 5
Hi
My application :
On PC : Access application + Access DB
On PocketPC : Embedded Visual Basic 3.0 application + PocketAccess DB
I synchronise my DBs through ActivSync.
It runs good before Windows Mobile 5.
My customers asked me for new PocketPC with this application, so I bought some new PocketPCs : but as I see, syncronisation with Access is no more supported.
So what are my solutions ?
My customer wait for there PocketPC, so I need a quick solution, I don't want to rewrite all my application now.
My application run before WM5, so now I don't want to have to pay for a Visual Studio .Net, some SQL Server, etc ....
Please, what are the solutions that won't cost me more than the time that have already lost with this new Windows Mobile 5 ?
Jo?l
Joel,
You're at the end of the road with eVB. You are going to need to move your mobile application's code either to the .NET Compact Framework or use Visual C++ for Mobile Devices (in VS2005) to achieve Windows Mobile 5 compliance. I recommend using SQL Mobile for your on-device database as a replacement for Pocket Access. Sorry there isn't better news, but eVB and Access have reached the end of their support time horizon for current and future Windows Mobile platforms.
-Darren
|||
Ok Darren
So if I move my application to .Net et use SQL Mobile 2005 on my PocketPC, what do I need to synchronise my PocketPC's database (so SQL Mobile) and my PC Database ?
Which database on PC ?
Each PocketPC will have his own PC and his own database, and it have to run my application on Windows XP Home.
What solution for synchronisation ?
Thanks for your explanations
Jo?l|||Any solution ?
Jo?l|||
Joel,
SQL Mobile comes with two options for data synchronization, Remote Data Access and Merge Replication. Both of these require IIS as the broker or gateway through which this synchronization occurs. In your situation, you may want to write a simple synchronization method that detects when the device is connected and uses System.Data.SqlClient to synchronize between your device database and SQL Server 2005.
-Darren
|||Ok Darren, thks.
Is there a place where I can find some example of this method ?
My SQL and win98
Is my SQL compatible with win98? if so which version because the one i got d
oesnt work with win98 (think i downloaded SQL Server 2000)just to reiterfy i ment to say SQL server not mysql sorry|||You basically can't run any full version of SQL server on Win 98. The only
versions that you can run is SQL Server 2000 Personal Edition or the MSDE
engine for SQL 2000, otherwise you can only install the client tools, not
the actual database server. SQL 2000 Personal Edition has a lot of the
functionality, but some of the features are stripped out. If you want to
know the difference between the versions, go to:
http://www.microsoft.com/sql/techin...esKChooseEd.asp
"Nikki" <anonymous@.discussions.microsoft.com> wrote in message
news:871990A3-69DE-487B-9808-6C88E40BBE78@.microsoft.com...
> Is my SQL compatible with win98? if so which version because the one i got
doesnt work with win98 (think i downloaded SQL Server 2000)
oesnt work with win98 (think i downloaded SQL Server 2000)just to reiterfy i ment to say SQL server not mysql sorry|||You basically can't run any full version of SQL server on Win 98. The only
versions that you can run is SQL Server 2000 Personal Edition or the MSDE
engine for SQL 2000, otherwise you can only install the client tools, not
the actual database server. SQL 2000 Personal Edition has a lot of the
functionality, but some of the features are stripped out. If you want to
know the difference between the versions, go to:
http://www.microsoft.com/sql/techin...esKChooseEd.asp
"Nikki" <anonymous@.discussions.microsoft.com> wrote in message
news:871990A3-69DE-487B-9808-6C88E40BBE78@.microsoft.com...
> Is my SQL compatible with win98? if so which version because the one i got
doesnt work with win98 (think i downloaded SQL Server 2000)
My SPID is sleeping but in a transaction holding resources!
Greetings Gurus.
Summary:
Q224453 doesn't help me resolve my sleeping SPID that's in a transaction and holding a resource, but with no wait type or wait time.
Details:
I come to this list after much investigation into a blocking problem I have been experiencing. After reading many of the posts here I'm optomistic of finding a lead to a resolution.
My investigations lead me to Q224453 (INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems) which was of great benefit, but did not help me fix my problem.
I am experiencing a Q224453 Scenario 2, or 'Blocking Caused by a Sleeping SPID That Has Lost Track of the Transaction Nesting Level'. In other words, I'm experiencing a spid in a transaction (trans_count>0) holding onto a resource, with no wait type (0x0000) or wait time(0). So it just sits there, SLEEPING, and majorly blocking other transactions.
When this block occurrs with no timeout values set, SQL Server does not resolve the block. No deadlock is found. (Possibly a Q239753 BUG: 1205 - Deadlock Situation Not Detected by SQL Server ? )
Q224453 says that in this case the cause is one of two things. Firstly, that the controlling app has lost track of the transaction after possibly, an unhandled error. I'm using COM+ to manage my transactions, and I have checked with caution the error handling in those components. (See platform summary below)
Secondly, a performance issue - a slow running query holding on to locks a little too long? When I look for slow performing queries using SQL trace, I find that the longest query takes 1140 ms. I'm pretty sure performance is not the issue. This said, I do notice that one of the SPID's within a blocked transaction (not the blocking one) may occasionally timeout (EventClass +Lock:Timeout). This occurs even when I have not set any timeout value on the connection, in the stored procedure or on the COM+ component.
Does anyone have any ideas where I should look next?
I'm curious to see if it's a Q239753 (BUG: 1205 - Deadlock Situation Not Detected by SQL Server) as I know that COM+ is using multiple SPIDs within a transaction boundary.
I have also noticed that occasionally I get an indication that the last wait type on the blocking SPID was a transaction control block (XCB). I dont know if this has implications or not.
Any help or suggestions would be greatly appreciated. Im happy to go and look again at anything.
Many thanks in advance,
James.
Quick Platform summary:
Windows 2000
VB6
Sql Server 7 sp4
COM+ handles transactions. Components call parametrized stored procedures.
mdac2.5sp1
One physical multi-processor server (2GB RAM) with transactions spanning two databases on the server, occasionally a transactable MSMQ takes part in a transaction.Hi, Sometimes a process will hang if the calling application is waiting for a response like a click to a dialog box. Perhaps the COM object has an unhandled error of some sore. If this is the case, the transaction is hanging waiting for an error confirmation from the caller.
Have fun,
LM:p|||Thanks LM.
I capture and log all errors that occur in COM+ so I'm pretty sure that that's not the issue though I'll triple check. I've even tried setting the components to run with unattended execution - meaning that all the errors would be written to a log and would not wait for user input.
The more peole I talk to about this the more I hear
"Gee, COM+ is clunky isn't it"
or
"We just moved all our transactions out of COM+ and into stored procedures".
Has anyone else had problem with transaction implemented in COM+? I've successfully written 2 apps which use COM+ with no problem, but this particular app requires much more server resources than the others...
Perhaps this note belongs on a number of lists?
Thanks all!
Summary:
Q224453 doesn't help me resolve my sleeping SPID that's in a transaction and holding a resource, but with no wait type or wait time.
Details:
I come to this list after much investigation into a blocking problem I have been experiencing. After reading many of the posts here I'm optomistic of finding a lead to a resolution.
My investigations lead me to Q224453 (INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems) which was of great benefit, but did not help me fix my problem.
I am experiencing a Q224453 Scenario 2, or 'Blocking Caused by a Sleeping SPID That Has Lost Track of the Transaction Nesting Level'. In other words, I'm experiencing a spid in a transaction (trans_count>0) holding onto a resource, with no wait type (0x0000) or wait time(0). So it just sits there, SLEEPING, and majorly blocking other transactions.
When this block occurrs with no timeout values set, SQL Server does not resolve the block. No deadlock is found. (Possibly a Q239753 BUG: 1205 - Deadlock Situation Not Detected by SQL Server ? )
Q224453 says that in this case the cause is one of two things. Firstly, that the controlling app has lost track of the transaction after possibly, an unhandled error. I'm using COM+ to manage my transactions, and I have checked with caution the error handling in those components. (See platform summary below)
Secondly, a performance issue - a slow running query holding on to locks a little too long? When I look for slow performing queries using SQL trace, I find that the longest query takes 1140 ms. I'm pretty sure performance is not the issue. This said, I do notice that one of the SPID's within a blocked transaction (not the blocking one) may occasionally timeout (EventClass +Lock:Timeout). This occurs even when I have not set any timeout value on the connection, in the stored procedure or on the COM+ component.
Does anyone have any ideas where I should look next?
I'm curious to see if it's a Q239753 (BUG: 1205 - Deadlock Situation Not Detected by SQL Server) as I know that COM+ is using multiple SPIDs within a transaction boundary.
I have also noticed that occasionally I get an indication that the last wait type on the blocking SPID was a transaction control block (XCB). I dont know if this has implications or not.
Any help or suggestions would be greatly appreciated. Im happy to go and look again at anything.
Many thanks in advance,
James.
Quick Platform summary:
Windows 2000
VB6
Sql Server 7 sp4
COM+ handles transactions. Components call parametrized stored procedures.
mdac2.5sp1
One physical multi-processor server (2GB RAM) with transactions spanning two databases on the server, occasionally a transactable MSMQ takes part in a transaction.Hi, Sometimes a process will hang if the calling application is waiting for a response like a click to a dialog box. Perhaps the COM object has an unhandled error of some sore. If this is the case, the transaction is hanging waiting for an error confirmation from the caller.
Have fun,
LM:p|||Thanks LM.
I capture and log all errors that occur in COM+ so I'm pretty sure that that's not the issue though I'll triple check. I've even tried setting the components to run with unattended execution - meaning that all the errors would be written to a log and would not wait for user input.
The more peole I talk to about this the more I hear
"Gee, COM+ is clunky isn't it"
or
"We just moved all our transactions out of COM+ and into stored procedures".
Has anyone else had problem with transaction implemented in COM+? I've successfully written 2 apps which use COM+ with no problem, but this particular app requires much more server resources than the others...
Perhaps this note belongs on a number of lists?
Thanks all!
My SP works on 2005 bit not on 2000 it doesn''t step through my code.
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
-My Code Stops here
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.
Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.
|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?Without seeing the entire SP and sample data, there is no telling what is causing your problem.
|||
Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.
Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.
Here is all the code for my SP.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MemberUploader]
AS
DECLARE @.CurrentValue INT
DECLARE @.numValues INT
DECLARE @.MaxValue INT
SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)
FROM StageMemberUploading
WHILE @.numValues <> 0
BEGIN
DECLARE @.DateOfBirth DATETIME
DECLARE @.Male VARCHAR(50)
DECLARE @.Female VARCHAR(50)
DECLARE @.Single VARCHAR(50)
DECLARE @.Married VARCHAR(50)
DECLARE @.Divorced VARCHAR(50)
DECLARE @.Widowed VARCHAR(50)
DECLARE @.Height VARCHAR(50)
DECLARE @.Weight VARCHAR(50)
DECLARE @.Absentism VARCHAR(50)
DECLARE @.UsergroupID INT
DECLARE @.ClientID INT
SELECT @.DateOfBirth = [Date of birth],
@.Male = [Male],
@.Female = [Female],
@.Single = [Single],
@.Married = [Married],
@.Divorced = [Divorced],
@.Widowed = [Widowed],
@.Height = [Height],
@.Weight = [Weight],
@.Absentism = [Absentism],
@.UsergroupID = UsergroupID,
@.ClientID = ClientID
FROM StageMemberUploading
WHERE ID = @.numValues
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
IF @.Single = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 8)
END
IF @.Married = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 7)
END
IF @.Divorced = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 9)
END
IF @.Widowed = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 10)
END
IF @.Height = '1.22' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 3, 85)
END
IF @.Weight = 'x' BEGIN
INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 4, @.Weight)
END
IF @.Absentism = '1' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 158)
END
IF @.Absentism = '2' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 159)
END
IF @.Absentism = '3' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 160)
END
IF @.Absentism = '4' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 161)
END
SET @.numValues = @.numValues - 1
END
Kind Regards
Carel Greaves
|||As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.
I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.
PRINT @.NumValues
PRINT @.CurrentValue
PRINT @.Absentism
....
|||My first guess is your values for @.Male, @.Female, etc are NULL.Add some commands like this and see what you get:
IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'
My SP works on 2005 bit not on 2000 it doesn''t step through my code.
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
-My Code Stops here
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.
Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.
|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?Without seeing the entire SP and sample data, there is no telling what is causing your problem.
|||
Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.
Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.
Here is all the code for my SP.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MemberUploader]
AS
DECLARE @.CurrentValue INT
DECLARE @.numValues INT
DECLARE @.MaxValue INT
SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)
FROM StageMemberUploading
WHILE @.numValues <> 0
BEGIN
DECLARE @.DateOfBirth DATETIME
DECLARE @.Male VARCHAR(50)
DECLARE @.Female VARCHAR(50)
DECLARE @.Single VARCHAR(50)
DECLARE @.Married VARCHAR(50)
DECLARE @.Divorced VARCHAR(50)
DECLARE @.Widowed VARCHAR(50)
DECLARE @.Height VARCHAR(50)
DECLARE @.Weight VARCHAR(50)
DECLARE @.Absentism VARCHAR(50)
DECLARE @.UsergroupID INT
DECLARE @.ClientID INT
SELECT @.DateOfBirth = [Date of birth],
@.Male = [Male],
@.Female = [Female],
@.Single = [Single],
@.Married = [Married],
@.Divorced = [Divorced],
@.Widowed = [Widowed],
@.Height = [Height],
@.Weight = [Weight],
@.Absentism = [Absentism],
@.UsergroupID = UsergroupID,
@.ClientID = ClientID
FROM StageMemberUploading
WHERE ID = @.numValues
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
IF @.Single = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 8)
END
IF @.Married = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 7)
END
IF @.Divorced = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 9)
END
IF @.Widowed = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 10)
END
IF @.Height = '1.22' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 3, 85)
END
IF @.Weight = 'x' BEGIN
INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 4, @.Weight)
END
IF @.Absentism = '1' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 158)
END
IF @.Absentism = '2' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 159)
END
IF @.Absentism = '3' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 160)
END
IF @.Absentism = '4' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 161)
END
SET @.numValues = @.numValues - 1
END
Kind Regards
Carel Greaves
|||As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.
I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.
PRINT @.NumValues
PRINT @.CurrentValue
PRINT @.Absentism
....
|||My first guess is your values for @.Male, @.Female, etc are NULL.Add some commands like this and see what you get:
IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'
My SP works on 2005 bit not on 2000 it doesn''t step through my code.
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
-My Code Stops here
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.
Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.
|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?Without seeing the entire SP and sample data, there is no telling what is causing your problem.
|||
Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.
Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.
Here is all the code for my SP.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MemberUploader]
AS
DECLARE @.CurrentValue INT
DECLARE @.numValues INT
DECLARE @.MaxValue INT
SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)
FROM StageMemberUploading
WHILE @.numValues <> 0
BEGIN
DECLARE @.DateOfBirth DATETIME
DECLARE @.Male VARCHAR(50)
DECLARE @.Female VARCHAR(50)
DECLARE @.Single VARCHAR(50)
DECLARE @.Married VARCHAR(50)
DECLARE @.Divorced VARCHAR(50)
DECLARE @.Widowed VARCHAR(50)
DECLARE @.Height VARCHAR(50)
DECLARE @.Weight VARCHAR(50)
DECLARE @.Absentism VARCHAR(50)
DECLARE @.UsergroupID INT
DECLARE @.ClientID INT
SELECT @.DateOfBirth = [Date of birth],
@.Male = [Male],
@.Female = [Female],
@.Single = [Single],
@.Married = [Married],
@.Divorced = [Divorced],
@.Widowed = [Widowed],
@.Height = [Height],
@.Weight = [Weight],
@.Absentism = [Absentism],
@.UsergroupID = UsergroupID,
@.ClientID = ClientID
FROM StageMemberUploading
WHERE ID = @.numValues
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
IF @.Single = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 8)
END
IF @.Married = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 7)
END
IF @.Divorced = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 9)
END
IF @.Widowed = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 10)
END
IF @.Height = '1.22' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 3, 85)
END
IF @.Weight = 'x' BEGIN
INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 4, @.Weight)
END
IF @.Absentism = '1' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 158)
END
IF @.Absentism = '2' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 159)
END
IF @.Absentism = '3' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 160)
END
IF @.Absentism = '4' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 161)
END
SET @.numValues = @.numValues - 1
END
Kind Regards
Carel Greaves
|||As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.
I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.
PRINT @.NumValues
PRINT @.CurrentValue
PRINT @.Absentism
....
|||My first guess is your values for @.Male, @.Female, etc are NULL.Add some commands like this and see what you get:
IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'
My SP works on 2005 bit not on 2000 it doesn''t step through my code.
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
-My Code Stops here
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.
Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.
|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?Without seeing the entire SP and sample data, there is no telling what is causing your problem.
|||
Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.
Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.
Here is all the code for my SP.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MemberUploader]
AS
DECLARE @.CurrentValue INT
DECLARE @.numValues INT
DECLARE @.MaxValue INT
SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)
FROM StageMemberUploading
WHILE @.numValues <> 0
BEGIN
DECLARE @.DateOfBirth DATETIME
DECLARE @.Male VARCHAR(50)
DECLARE @.Female VARCHAR(50)
DECLARE @.Single VARCHAR(50)
DECLARE @.Married VARCHAR(50)
DECLARE @.Divorced VARCHAR(50)
DECLARE @.Widowed VARCHAR(50)
DECLARE @.Height VARCHAR(50)
DECLARE @.Weight VARCHAR(50)
DECLARE @.Absentism VARCHAR(50)
DECLARE @.UsergroupID INT
DECLARE @.ClientID INT
SELECT @.DateOfBirth = [Date of birth],
@.Male = [Male],
@.Female = [Female],
@.Single = [Single],
@.Married = [Married],
@.Divorced = [Divorced],
@.Widowed = [Widowed],
@.Height = [Height],
@.Weight = [Weight],
@.Absentism = [Absentism],
@.UsergroupID = UsergroupID,
@.ClientID = ClientID
FROM StageMemberUploading
WHERE ID = @.numValues
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
IF @.Single = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 8)
END
IF @.Married = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 7)
END
IF @.Divorced = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 9)
END
IF @.Widowed = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 10)
END
IF @.Height = '1.22' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 3, 85)
END
IF @.Weight = 'x' BEGIN
INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 4, @.Weight)
END
IF @.Absentism = '1' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 158)
END
IF @.Absentism = '2' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 159)
END
IF @.Absentism = '3' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 160)
END
IF @.Absentism = '4' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 161)
END
SET @.numValues = @.numValues - 1
END
Kind Regards
Carel Greaves
|||As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.
I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.
PRINT @.NumValues
PRINT @.CurrentValue
PRINT @.Absentism
....
|||My first guess is your values for @.Male, @.Female, etc are NULL.Add some commands like this and see what you get:
IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'
My SP works on 2005 bit not on 2000 it doesn''t step through my code.
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
-My Code Stops here
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.
Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.
|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?Without seeing the entire SP and sample data, there is no telling what is causing your problem.
|||
Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.
Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.
Here is all the code for my SP.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MemberUploader]
AS
DECLARE @.CurrentValue INT
DECLARE @.numValues INT
DECLARE @.MaxValue INT
SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)
FROM StageMemberUploading
WHILE @.numValues <> 0
BEGIN
DECLARE @.DateOfBirth DATETIME
DECLARE @.Male VARCHAR(50)
DECLARE @.Female VARCHAR(50)
DECLARE @.Single VARCHAR(50)
DECLARE @.Married VARCHAR(50)
DECLARE @.Divorced VARCHAR(50)
DECLARE @.Widowed VARCHAR(50)
DECLARE @.Height VARCHAR(50)
DECLARE @.Weight VARCHAR(50)
DECLARE @.Absentism VARCHAR(50)
DECLARE @.UsergroupID INT
DECLARE @.ClientID INT
SELECT @.DateOfBirth = [Date of birth],
@.Male = [Male],
@.Female = [Female],
@.Single = [Single],
@.Married = [Married],
@.Divorced = [Divorced],
@.Widowed = [Widowed],
@.Height = [Height],
@.Weight = [Weight],
@.Absentism = [Absentism],
@.UsergroupID = UsergroupID,
@.ClientID = ClientID
FROM StageMemberUploading
WHERE ID = @.numValues
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
IF @.Single = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 8)
END
IF @.Married = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 7)
END
IF @.Divorced = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 9)
END
IF @.Widowed = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 10)
END
IF @.Height = '1.22' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 3, 85)
END
IF @.Weight = 'x' BEGIN
INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 4, @.Weight)
END
IF @.Absentism = '1' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 158)
END
IF @.Absentism = '2' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 159)
END
IF @.Absentism = '3' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 160)
END
IF @.Absentism = '4' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 161)
END
SET @.numValues = @.numValues - 1
END
Kind Regards
Carel Greaves
|||As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.
I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.
PRINT @.NumValues
PRINT @.CurrentValue
PRINT @.Absentism
....
|||My first guess is your values for @.Male, @.Female, etc are NULL.Add some commands like this and see what you get:
IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'
My SP works on 2005 bit not on 2000 it doesn''t step through my code.
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
-My Code Stops here
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.
Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.
|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?Without seeing the entire SP and sample data, there is no telling what is causing your problem.
|||
Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.
Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.
Here is all the code for my SP.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[csp_MemberUploader]
AS
DECLARE @.CurrentValue INT
DECLARE @.numValues INT
DECLARE @.MaxValue INT
SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)
FROM StageMemberUploading
WHILE @.numValues <> 0
BEGIN
DECLARE @.DateOfBirth DATETIME
DECLARE @.Male VARCHAR(50)
DECLARE @.Female VARCHAR(50)
DECLARE @.Single VARCHAR(50)
DECLARE @.Married VARCHAR(50)
DECLARE @.Divorced VARCHAR(50)
DECLARE @.Widowed VARCHAR(50)
DECLARE @.Height VARCHAR(50)
DECLARE @.Weight VARCHAR(50)
DECLARE @.Absentism VARCHAR(50)
DECLARE @.UsergroupID INT
DECLARE @.ClientID INT
SELECT @.DateOfBirth = [Date of birth],
@.Male = [Male],
@.Female = [Female],
@.Single = [Single],
@.Married = [Married],
@.Divorced = [Divorced],
@.Widowed = [Widowed],
@.Height = [Height],
@.Weight = [Weight],
@.Absentism = [Absentism],
@.UsergroupID = UsergroupID,
@.ClientID = ClientID
FROM StageMemberUploading
WHERE ID = @.numValues
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @.numValues
SET @.CurrentValue = (SELECT SCOPE_IDENTITY())
IF @.ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)
END
IF @.DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 1, @.DateOfBirth)
END
IF @.Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 1)
END
IF @.Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 2, 3)
END
IF @.Single = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 8)
END
IF @.Married = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 7)
END
IF @.Divorced = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 9)
END
IF @.Widowed = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 6, 10)
END
IF @.Height = '1.22' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 3, 85)
END
IF @.Weight = 'x' BEGIN
INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)
VALUES (@.CurrentValue, 4, @.Weight)
END
IF @.Absentism = '1' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 158)
END
IF @.Absentism = '2' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 159)
END
IF @.Absentism = '3' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 160)
END
IF @.Absentism = '4' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@.CurrentValue, 15, 161)
END
SET @.numValues = @.numValues - 1
END
Kind Regards
Carel Greaves
|||As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.
I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.
PRINT @.NumValues
PRINT @.CurrentValue
PRINT @.Absentism
....
|||My first guess is your values for @.Male, @.Female, etc are NULL.Add some commands like this and see what you get:
IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'
my SP slows after 1 minute
Hello
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
Boston
Hi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish
|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) during
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cache?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it means
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushed
> out.
> --
> Thanks
> Yogish
>
|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston
|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it again
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server as
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) during
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
I have a large query that takes 2:05 secs to run 1st time. If I rerun the
query within a minute or so, the subsequent runs take only :30 secs. If I
wait over a minute, it reruns at 2:05 again.
I have run the Query Tuning Wiz on this code and it recommended (and I
implemented) some addl indexes. Not much improvement here, only a few secs.
The machine has 4GB Mem and SQL Server is using well over 3. Additionally, I
have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
Can someone give me some tips on where to go from here? Is the data being
paged back out? The cache hit ratio is >90%
Jim
Boston
Hi JimMC,
Probably after a minute, the procedure is getting flushed out from the cache?
Step 1:
(a) Run the SP and note the duration
(b) Run the SP again immediately and note the duration. It should be the
same duration as in (a), as mentioned by you.
(c) Issue DBCC FREEPROCCACHE.
(d) Run the SP and note the duration
Execute all the above within a minute. If (d) is taking more time, it means
that the SP is getting flused out of cache after a minute, in this case.
Am guessing that one of the other reason could be data itself being flushed
out.
Thanks
Yogish
|||I will be very reluctant to run DBCC FREEPROCACHE on a production server as
this temporarily degrades the performance of all stored procedures server
wide.
I will however perform the following
- check the execution plan of the query - when it runs fast
switch on the following
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_ALL
and establish if the correct indexes are being used during the execution
- also U need to find out (through STATISTICS io) to find out if physical
reads are higher (indicating that buffercache might have been flushed) during
when it runs slow in the space of 1minute.
- I also monitor buffer cache hit ratio and find out what the percentage is.
ie. what percentage of data is found in buffer cache?)
HTH
"Yogish" wrote:
> Hi JimMC,
> Probably after a minute, the procedure is getting flushed out from the cache?
> Step 1:
> (a) Run the SP and note the duration
> (b) Run the SP again immediately and note the duration. It should be the
> same duration as in (a), as mentioned by you.
> (c) Issue DBCC FREEPROCCACHE.
> (d) Run the SP and note the duration
> Execute all the above within a minute. If (d) is taking more time, it means
> that the SP is getting flused out of cache after a minute, in this case.
> Am guessing that one of the other reason could be data itself being flushed
> out.
> --
> Thanks
> Yogish
>
|||Maybe Optimizing Query's in the SP will be a better Idea.
It seems that therer not so much thngs to do with it.
"JimMc" <JimMc@.discussions.microsoft.com> wrote in message
news:35D535B9-5DDA-4C25-BE59-691D1E6B93C2@.microsoft.com...
> Hello
> I have a large query that takes 2:05 secs to run 1st time. If I rerun the
> query within a minute or so, the subsequent runs take only :30 secs. If I
> wait over a minute, it reruns at 2:05 again.
> I have run the Query Tuning Wiz on this code and it recommended (and I
> implemented) some addl indexes. Not much improvement here, only a few
secs.
> The machine has 4GB Mem and SQL Server is using well over 3. Additionally,
I
> have run SP_RECOMPILE against my SP but no addtl improvement on new runs.
> Can someone give me some tips on where to go from here? Is the data being
> paged back out? The cache hit ratio is >90%
> Jim
> Boston
|||Thank you ALL!
your suggestions and experience helped. The issue actually cured itself.
After I did the SP_RECOMPILE then after an additional 10 min I tried it again
and it's ET was reduced to 9 seconds vs the initial 30. I think that when I
ran it the first time after the SP_RECOMPILE I still was provided a stale
copy of the SP. But after waiting the addl time SQL Server seems to have
given me the newer copy.
Thanks again Very Much!
Jim
"Olu Adedeji" wrote:
[vbcol=seagreen]
> I will be very reluctant to run DBCC FREEPROCACHE on a production server as
> this temporarily degrades the performance of all stored procedures server
> wide.
> I will however perform the following
>
> - check the execution plan of the query - when it runs fast
> switch on the following
> SET STATISTICS IO
> SET STATISTICS PROFILE
> SET SHOWPLAN_ALL
> and establish if the correct indexes are being used during the execution
> - also U need to find out (through STATISTICS io) to find out if physical
> reads are higher (indicating that buffercache might have been flushed) during
> when it runs slow in the space of 1minute.
> - I also monitor buffer cache hit ratio and find out what the percentage is.
> ie. what percentage of data is found in buffer cache?)
>
> HTH
>
>
>
> "Yogish" wrote:
Subscribe to:
Posts (Atom)