June 9, 2011 at 9:46 am
Hello
I´m using Nigel´s audit script for audit. I´m changing my Zope server from Windows server 2003 to Windows 2008 R2, I thougth it was correct, many consults were working but when I tried to do some updates all go to the hell 🙁
(1) http://www.nigelrivett.net/AuditTrailTrigger.html
After examined the problem I found it´s in
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + cu.COLUMN_NAME + ' = d.' + cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND cu.TABLE_NAME = pk.TABLE_NAME
AND cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
For discover the problem I put this code before the code I mentioned
select @archivo = 'test1.txt'
select @inicial = inicial from inserted
select @sql = @inicial + ', pkcols=' + COALESCE(@PKCols + ' and', ' on') + ', pkselect=' + COALESCE(@PKSelect + ' and', ' on')
EXECUTE spWriteStringToFile @sql,'c:\temp\sql',@archivo
When I ran the code the result is:
from old server: 10266630, pkcols= on, pkselect= on
from new server: 10266630, pkcols= and, pkselect= and
How can see it´s not the result correct it the new server, so the question is, why?
It looks like pkcols and pkselect are not null but why? It is the SAME DATABASE SERVER just change the application server.
In windows 2003 work fine but in 2008 r2 is not working in the same way. Both zope are the same, I just copy all my configuration from the old server to the new one, it´s easy do it with zope. Of couse both zope server connect to the same database server (sql server 2000).
Ohh I almost forgot, both applications (are the same) connect using the follow ODBC connection string:
DRIVER={SQL Server};SERVER=X.X.X.X;DATABASE=ctrl_emi;UID=aficon;Pwd=XXXXXX;Regional=Yes;AnsiNPW=No;QuotedId=No;
June 9, 2011 at 10:04 am
You should place that second query after the first query you mentioned - not before it. The reason your variables are null is due to them not being set to anything by the time you ran that query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 11:11 am
Thanks SQLRNNR for respond.
I did in this way because I expect a NULL value the first time. It´s for debug.
The second query to run (first I mentioned ) get all the fields of the primary key, how PKCOLS is null at start PKCOLS must start with ON, on the second record add "AND" so the result must be something like
ON i.id = d.id AND i.year = d.year
but I obtain
AND i.id = d.id AND i.year = d.year
I have 2 application servers that connect to the same database server but the execution (trigger) in the databa server are diffent ar runtime. :S
June 13, 2011 at 9:01 am
I made other test. I ran a simple query with intersting result.
.
* SQL Server 2000
* Server 1:
windows 2003
ODBCD Driver: sql server
2000.86.3959.00
sqlsrv32.dll
17/02/2077
* Server 2:
windows 2008
ODBCD Driver: sql server
6.01.7601.17514
sqlsrv32.dll
20/11/2010
* Connection string used in both servers :
DRIVER={SQL Server};SERVER=10.28.1.4;DATABASE=XXXX;UID=XXXX;Pwd=XXXX;Regional=Yes;AnsiNPW=No;QuotedId=No;
* QUERY:
select null as vnull, null + '1' as nullstring, COALESCE(null + '1', 'on') as coalesceaa
Here is the intersting:
SERVER 1:
Vnull Nullstring Coalesceaa
on
Server 2
Vnull Nullstring Coalesceaa
1 1
Even it's the same query, when I concate a NULL value plus a string value the result is different.
do exist some parameter to change this behavior?
June 13, 2011 at 10:14 am
Yeaahhhh
I found the solution 😀
I just added those options in my script
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
I still without know why it work in windows 2003 but not work in windows 2008.
Now I going to re-add all the Nigel's script with those options
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply