|
Hello everyone.
Everything I have read says that operations within a stored procedure are executed in the order in which they are encountered, and the semi-colons and BEGIN-END blocks can be used to ensure that statements are executed before subsequent statements are performed.
However I am stumped by what is happening in my script. The intent of the script is to copy an order record and all associated table entries for the order from one database to another. I have stripped down the script code below to just the order table itself (and removed some passwords, server names etc).
Here is the code:
IF @@SERVERNAME <> 'Server1'
BEGIN
EXEC SP_ADDLINKEDSERVER
'Server1'
EXEC SP_ADDLINKEDSRVLOGIN
'Server1',
'False',
NULL,
'userX',
'passwordX'
END
go
DECLARE @mode VARCHAR(1);
DECLARE @orderNum INT;
SET @orderNum = 1338464;
SET @mode = 'R' -- set to 'R' for Report or 'U' for update
BEGIN TRANSACTION
DECLARE @sourceName VARCHAR(8);
DECLARE @okay CHAR(1);
DECLARE @user VARCHAR(20);
SET @sourceName = 'liveDB';
SET @user = SYSTEM_USER;
CREATE synonym sourcesummary FOR [Server1].[liveDB].[dbo].[order_summary];
SET @okay = 'Y'
IF @okay = 'Y'
AND @@SERVERNAME = 'Server1'
BEGIN
SELECT 'Cannot execute on live server - retry on the sever hosting the db that is to be copied to' [error]
SET @okay = 'N'
END
IF @okay = 'Y'
AND NOT EXISTS (SELECT order_num
FROM sourcesummary
WHERE order_num = @orderNum)
BEGIN
SELECT 'Unable to find order' [error],@sourceName [source db],@orderNum [order number];
SET @okay = 'N';
END
IF @okay = 'Y'
BEGIN
IF EXISTS (SELECT order_num
FROM order_summary
WHERE ordr_num = @orderNum)
BEGIN
PRINT 'deleting order_summary:';
DELETE order_summary
WHERE order_num = @orderNum;
END
BEGIN
PRINT 'copying order_summary:';
INSERT INTO order_summary
SELECT [order_num],[order_date], etc, etc, etc...
FROM sourcesummary
WHERE sourcesummary.order_num = @orderNum;
END
PRINT 'retreiving copied order_summary:';
SELECT 'copied' [order_summary],*
FROM order_summary
WHERE order_num = @orderNum;
END
DROP synonym sourcesummary;
IF @mode = 'U'
BEGIN
COMMIT TRANSACTION
SELECT 'Changes have been applied to the database' [notice]
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SELECT 'All changes have been rolled back' [notice]
END
And here is the result from MSSQL's Messages window:
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'Server1' already exists.
deleting order_summary:
(1 row(s) affected)
copying order_summary:
(1 row(s) affected)
retreiving copied order_summary:
(0 row(s) affected)
(1 row(s) affected)
The error at the top of the output (re Server1 already exists) I understand and that's okay.
However, notice that copying order_summary says that 1 row was affected, but the very next operation to display that row obtains no records. This is what I don't understand, and I can't move forward until I get this row to exist in the db.
Can anyone see some blatantly newbie faux-pas that is hidden to my blatantly newbie eyes?
Please?
Clive Pottinger
Victoria, BC
modified 6-Sep-12 14:28pm.
|
|
|
|
|
Try committing the transaction after deleting and inserting the rows into the database, but before retrieving them, I'm assuming that's what causing the problem
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Or add the WITH(NOLOCK) hint.
I also recall others having similar trouble when using GO , but that doesn't appear to be the problem here.
|
|
|
|
|
Thank you gentlemen. I found the cause of the issue.
The section that reads
PRINT 'copying order_summary:';
INSERT INTO order_summary
SELECT [order_num],[order_date], etc, etc, etc...
FROM sourcesummary
WHERE sourcesummary.order_num = @orderNum;
should have been
PRINT 'copying order_summary:';
SET IDENTITY_INSERT order_summary ON;
INSERT INTO order_summary ([order_num],[order_date], etc, etc, etc...)
SELECT [order_num],[order_date], etc, etc, etc...
FROM sourcesummary
WHERE sourcesummary.order_num = @orderNum;
SET IDENTITY_INSERT order_summary OFF;
"... to become an SQL expert? Oh, I figure 3 or 4 days reading. A week tops!"
Clive Pottinger
Victoria, BC
modified 12-Sep-12 12:05pm.
|
|
|
|
|
|
my query is
<pre lang="SQL">create proc sp_emprecord
as begin
select * from employe
end
begin try
execute sp_emprecord
end try
begin catch
select
error_message() as errormessage,
error_number() as erronumber,
error_state() as errorstate,
error_procedure() as errorprocedure,
error_line() as errorline;
end catch</pre>
|
|
|
|
|
You have already asked this vague question in QA
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
|
|
|
|
|
Use Raiserror [^] with a security-level lower than 20.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
That's probably not worth the effort; just catch the Exception in the calling code.
And don't prefix your procedures with sp_ -- that's for System Procedures.
|
|
|
|
|
Good day,
I want to retrieve records from sql server 2005 that belongs to today,
my datatype in sqlserver is : datetime and store date like : 9/5/2012 12:55:26 PM
So i want to know how can I write a query to retrieve only today's records.
Thanks in advance
|
|
|
|
|
0) I hope you're using a DATETIME field and not storing dates as strings. Otherwise you're hosed.
1) If this is a recent version of SQL Server you can try WHERE datefield >= CAST(GETDATE() AS DATE)
modified 5-Sep-12 23:28pm.
|
|
|
|
|
i have found it
WHERE LASTUPDATEON >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND LASTUPDATEON < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
|
|
|
|
|
WTE!
Which database system?
Why would you have to eliminate records that will be updated tomorrow?
"Simplify. Simpify." -- Thoreau
|
|
|
|
|
|
That is very very bad. Highly inefficient. You convert every (millions?) SaveDate value and then do a string compare -- which is much less efficient than the DATETIME compare would be.
<anecdote>
I once fixed a program that was written that way -- before I fixed it it took forty minutes to run, afterward it took only ten minutes.
</anecdote>
DON'T EVER DO THAT!!
|
|
|
|
|
Hello,
I am very green to sql, and normally program in c sharp. I have 2 GUI generated sql queries that I'd like to combine, then exclude the results (users) that are found in the second query, from the first query and finally return the new result set that has the second query data removed from the first data query.
Here are the queries:
Thanks for reading.
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE _QA.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _QA.ProcessID = 0) AS _SA
WHERE _SA.UserSID = AET.UserSID)
ORDER BY TimeDetected DESC,
UserName DESC
SELECT AED.ObjectName,
AED.ObjectCanonical,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AEM.EventMessage,
AET.TimeDetected,
ECS.SubsystemID,
AET.UserName,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = 'C37D85D6-B725-4E98-A338-B8917CA5784F'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
ORDER BY TimeDetected DESC,
UserName DESC
|
|
|
|
|
Read up on Union or Union All
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can use "Except Distinct" !
|
|
|
|
|
INTERSECT might also be useful for this scenario.
|
|
|
|
|
Hello,
I have tried inserting those commands into various locations in the query, without success. Could someone give me an example please?
|
|
|
|
|
use EXCEPT between two queries
|
|
|
|
|
Someone helped me come up with this code today, it appears to be producing the proper results, what does every one think?
It uses And UserName Not In, and brings in the second search to compare.
-- Mailbox size query users who are set to unlimited, distinct username field with disabled mailboxes removed from search
SELECT AED.ObjectName,
AED.ObjectCanonical,
AEM.EventMessage,
AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
AET.UserName,
AET.SeverityID,
AET.TimeDetected,
AET.ValueOld,
AET.ValueNew,
ECS.SubsystemID,
ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
EAN.ActionName,
ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
AET.ResultID,
AET.EventID,
ECS.ValueTypeID,
AET.MissingOld,
AET.MissingNew,
AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-08T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
AND NOT EXISTS(SELECT *
FROM (SELECT _QA.SID AS UserSID
FROM Query.[Account] AS _QA
WHERE _QA.QueryID = '2E84F194-03D0-4218-A346-76C50B98E36A'
AND _QA.ProcessID = 0) AS _SA
WHERE _SA.UserSID = AET.UserSID)
AND ValueNew = '<Not-Set>'
AND UserName NOT IN (
SELECT DISTINCT --AED.ObjectName,
--AED.ObjectCanonical,
-- AED.OrganizationalUnit AS DirectoryOrganizationalUnit,
-- AEM.EventMessage,
-- AET.TimeDetected,
-- ECS.SubsystemID,
AET.UserName--,
-- ISNULL(ECS.CustomEventClassName, ECS.EventClassName) AS EventClassName,
-- ISNULL(TSR.ServerName, TWP.MachineName) AS ServerName,
-- EAN.ActionName,
-- ISNULL(TDN.DomainName, TWP.WorkgroupName) AS DomainName,
-- AET.ResultID,
-- AET.EventID,
-- ECS.ValueTypeID,
-- AET.MissingOld,
-- AET.MissingNew,
-- AET.TimeZoneOffset
FROM Audit.Event AS AET
INNER JOIN [Event].Class AS ECS
ON AET.EventClassID = ECS.EventClassID
INNER JOIN Audit.EventMessage AS AEM
ON AET.EventID = AEM.EventID
INNER JOIN [Event].[Action] AS EAN
ON AET.ActionID = EAN.ActionID
LEFT OUTER JOIN Topology.[Server] AS TSR
ON AET.AgentID = TSR.ServerID
LEFT OUTER JOIN Topology.Domain AS TDN
ON TSR.DomainID = TDN.DomainID
LEFT OUTER JOIN Topology.Workgroup AS TWP
ON AET.AgentID = TWP.MachineID
LEFT OUTER JOIN Audit.EventDirectory AS AED
ON AET.EventID = AED.EventID
WHERE AET.TimeDetected >= '2012-06-07T04:00:00'
AND EXISTS(SELECT AET.EventID
FROM [Event].Class AS _EC
WHERE EXISTS(SELECT *
FROM Query.EventClass AS _ECQ
WHERE _ECQ.QueryID = 'C37D85D6-B725-4E98-A338-B8917CA5784F'
AND _ECQ.ProcessID = 0
AND StorageClassID = 0
AND _ECQ.EventClassID = _EC.EventClassID)
AND _EC.EventClassID = AET.EventClassID)
)
ORDER BY ObjectName
--ORDER BY TimeDetected DESC,
-- UserName DESC
|
|
|
|
|
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table.
Please reply ASAP
Thanks.
|
|
|
|
|
You will need to build a schema compare utility.
Make a connection to each of the databases you want to compare and you will have to walk through each one of the tables and compare the column definitions.
Look at sys.columns
http://msdn.microsoft.com/en-us/library/ms176106.aspx[^]
|
|
|
|
|
Then you need to add a ton of artificial intelligence and still guess wrong.
|
|
|
|
|