Integrated Security: situation

Showing posts with label situation. Show all posts

Monday, March 26, 2012

Intermittent "SQL Server does not exist or access denied."

I've seen this topic posted several times, but it seems my situation is
unique for every other case I've seen.
I have a web site that has been running for over 3 years. Recently, we've
hit a major spike in activity. Now, after a few days (sometimes less), the
site will start returning the following error:
SQL Server does not exist or access denied.
Here are my server specs:
WEB SERVER (Shuttle SK41G)
- Windows 2000 Server SP4 (IIS 5.0)
- Athlon XP 2100+
- 1GB RAM
- 80GB SATA HD (7200 RPM)
- All pages in ASP (traditional, not .NET)
- Realtek RTL8139 on-board NIC (*not* 1Gb) - used for external connection
- Zonet Gb PCI NIC - used for crossover connection to DB server
DATABASE SERVER (Dell PowerEdge SC420)
- Windows 2000 Server SP4
- MS SQL Server 2000 SP4
- Pentium 4 2.8 GHz
- 1GB RAM
- 2x 80GB SATA HD (7200 RPM), Raid 1
- Broadcom NetXtreme 5751 Gb on-board NIC
- Zonet Gb PCI NIC - used for crossover connection to web server
The two additional dedicated NICs were installed recently.
Here is a rough cycle of the problem. About a day or two after I installed
the two NICs, I started getting this error. But it wasn't intermittent in
that it would fix itself after a while -- once it started throwing the
error, it would fairly consistently throw it, only allowing a connection on
rare occasions. The only fix was to reboot the server. After rebooting,
everything seems find for a day or two, then it starts back up, another
reboot... repeat.
I have tested for connection issues between the two NICs, and pinging is
always successful. I have even completely ruled that out by disabling the
new NICs, and going back to the original shared connection, and even on that
connection it will start throwing the errors. I have an alias configured
using TCP/IP, but I have tried both using the alias and not using it (having
the IP configured directly in my connection string), and the results are the
same. I have tried using the machine name (which is different than the
alias), same results.
I read about disabling connection pooling causing this, but to my knowledge,
connection pooling is enabled.
I'm at a loss. I don't know why this is happening all of the sudden. I
thought it may be related to the new NICs, but after disabling them and
still getting the problems, that doesn't seem to be the case.
Please, any suggestions would be appreciated.
Thanks in advance.
JeradHi
You may want to look at other things such as DNS problems, incorrect routing
or firewall problems. Check out the logs for all of these to see if anything
corresponds to the time of your failure.
It is not clear from your post if you reboot the database server or the web
server, if the former then try restarting IIS or rebooting the web server
and see if that also cures it!
John
"Jerad Rose" <no@.spam.com> wrote in message
news:OuW4AM$AGHA.1216@.TK2MSFTNGP14.phx.gbl...
> I've seen this topic posted several times, but it seems my situation is
> unique for every other case I've seen.
> I have a web site that has been running for over 3 years. Recently, we've
> hit a major spike in activity. Now, after a few days (sometimes less),
> the site will start returning the following error:
> SQL Server does not exist or access denied.
> Here are my server specs:
> WEB SERVER (Shuttle SK41G)
> - Windows 2000 Server SP4 (IIS 5.0)
> - Athlon XP 2100+
> - 1GB RAM
> - 80GB SATA HD (7200 RPM)
> - All pages in ASP (traditional, not .NET)
> - Realtek RTL8139 on-board NIC (*not* 1Gb) - used for external connection
> - Zonet Gb PCI NIC - used for crossover connection to DB server
> DATABASE SERVER (Dell PowerEdge SC420)
> - Windows 2000 Server SP4
> - MS SQL Server 2000 SP4
> - Pentium 4 2.8 GHz
> - 1GB RAM
> - 2x 80GB SATA HD (7200 RPM), Raid 1
> - Broadcom NetXtreme 5751 Gb on-board NIC
> - Zonet Gb PCI NIC - used for crossover connection to web server
> The two additional dedicated NICs were installed recently.
> Here is a rough cycle of the problem. About a day or two after I
> installed the two NICs, I started getting this error. But it wasn't
> intermittent in that it would fix itself after a while -- once it started
> throwing the error, it would fairly consistently throw it, only allowing a
> connection on rare occasions. The only fix was to reboot the server.
> After rebooting, everything seems find for a day or two, then it starts
> back up, another reboot... repeat.
> I have tested for connection issues between the two NICs, and pinging is
> always successful. I have even completely ruled that out by disabling the
> new NICs, and going back to the original shared connection, and even on
> that connection it will start throwing the errors. I have an alias
> configured using TCP/IP, but I have tried both using the alias and not
> using it (having the IP configured directly in my connection string), and
> the results are the same. I have tried using the machine name (which is
> different than the alias), same results.
> I read about disabling connection pooling causing this, but to my
> knowledge, connection pooling is enabled.
> I'm at a loss. I don't know why this is happening all of the sudden. I
> thought it may be related to the new NICs, but after disabling them and
> still getting the problems, that doesn't seem to be the case.
> Please, any suggestions would be appreciated.
> Thanks in advance.
> Jerad
>|||Thanks for your response, John.
A few clarifcations. The database and webserver are connected to each other
directly via crossover cable, so there is no dns/firewall/routing in the
equasion. I have checked event logs on both web and database server, and
cannot see *anything* that would indicate new problems. I only see the
ongoing events that are normally there, such as SMTP routing problems
(nothing new). Also, I should have specified which server I reboot -- it is
the web server that is rebooted.
So I beleive I have ruled out most of the obvious problems. If there's any
other info you need that may help, let me know.
Thanks again.
Jerad
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23wekqiABGHA.976@.TK2MSFTNGP15.phx.gbl...
> Hi
> You may want to look at other things such as DNS problems, incorrect
> routing or firewall problems. Check out the logs for all of these to see
> if anything corresponds to the time of your failure.
> It is not clear from your post if you reboot the database server or the
> web server, if the former then try restarting IIS or rebooting the web
> server and see if that also cures it!
> John
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OuW4AM$AGHA.1216@.TK2MSFTNGP14.phx.gbl...
>> I've seen this topic posted several times, but it seems my situation is
>> unique for every other case I've seen.
>> I have a web site that has been running for over 3 years. Recently,
>> we've hit a major spike in activity. Now, after a few days (sometimes
>> less), the site will start returning the following error:
>> SQL Server does not exist or access denied.
>> Here are my server specs:
>> WEB SERVER (Shuttle SK41G)
>> - Windows 2000 Server SP4 (IIS 5.0)
>> - Athlon XP 2100+
>> - 1GB RAM
>> - 80GB SATA HD (7200 RPM)
>> - All pages in ASP (traditional, not .NET)
>> - Realtek RTL8139 on-board NIC (*not* 1Gb) - used for external connection
>> - Zonet Gb PCI NIC - used for crossover connection to DB server
>> DATABASE SERVER (Dell PowerEdge SC420)
>> - Windows 2000 Server SP4
>> - MS SQL Server 2000 SP4
>> - Pentium 4 2.8 GHz
>> - 1GB RAM
>> - 2x 80GB SATA HD (7200 RPM), Raid 1
>> - Broadcom NetXtreme 5751 Gb on-board NIC
>> - Zonet Gb PCI NIC - used for crossover connection to web server
>> The two additional dedicated NICs were installed recently.
>> Here is a rough cycle of the problem. About a day or two after I
>> installed the two NICs, I started getting this error. But it wasn't
>> intermittent in that it would fix itself after a while -- once it started
>> throwing the error, it would fairly consistently throw it, only allowing
>> a connection on rare occasions. The only fix was to reboot the server.
>> After rebooting, everything seems find for a day or two, then it starts
>> back up, another reboot... repeat.
>> I have tested for connection issues between the two NICs, and pinging is
>> always successful. I have even completely ruled that out by disabling
>> the new NICs, and going back to the original shared connection, and even
>> on that connection it will start throwing the errors. I have an alias
>> configured using TCP/IP, but I have tried both using the alias and not
>> using it (having the IP configured directly in my connection string), and
>> the results are the same. I have tried using the machine name (which is
>> different than the alias), same results.
>> I read about disabling connection pooling causing this, but to my
>> knowledge, connection pooling is enabled.
>> I'm at a loss. I don't know why this is happening all of the sudden. I
>> thought it may be related to the new NICs, but after disabling them and
>> still getting the problems, that doesn't seem to be the case.
>> Please, any suggestions would be appreciated.
>> Thanks in advance.
>> Jerad
>|||Hi
Can one assume that you have not installed any new software, updated
existing software or reconfigured any settings on either machine e.g
anti-virus
John
"Jerad Rose" <no@.spam.com> wrote in message
news:euHnTQBBGHA.4092@.TK2MSFTNGP09.phx.gbl...
> Thanks for your response, John.
> A few clarifcations. The database and webserver are connected to each
> other directly via crossover cable, so there is no dns/firewall/routing in
> the equasion. I have checked event logs on both web and database server,
> and cannot see *anything* that would indicate new problems. I only see
> the ongoing events that are normally there, such as SMTP routing problems
> (nothing new). Also, I should have specified which server I reboot -- it
> is the web server that is rebooted.
> So I beleive I have ruled out most of the obvious problems. If there's
> any other info you need that may help, let me know.
> Thanks again.
> Jerad
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23wekqiABGHA.976@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> You may want to look at other things such as DNS problems, incorrect
>> routing or firewall problems. Check out the logs for all of these to see
>> if anything corresponds to the time of your failure.
>> It is not clear from your post if you reboot the database server or the
>> web server, if the former then try restarting IIS or rebooting the web
>> server and see if that also cures it!
>> John
>>
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OuW4AM$AGHA.1216@.TK2MSFTNGP14.phx.gbl...
>> I've seen this topic posted several times, but it seems my situation is
>> unique for every other case I've seen.
>> I have a web site that has been running for over 3 years. Recently,
>> we've hit a major spike in activity. Now, after a few days (sometimes
>> less), the site will start returning the following error:
>> SQL Server does not exist or access denied.
>> Here are my server specs:
>> WEB SERVER (Shuttle SK41G)
>> - Windows 2000 Server SP4 (IIS 5.0)
>> - Athlon XP 2100+
>> - 1GB RAM
>> - 80GB SATA HD (7200 RPM)
>> - All pages in ASP (traditional, not .NET)
>> - Realtek RTL8139 on-board NIC (*not* 1Gb) - used for external
>> connection
>> - Zonet Gb PCI NIC - used for crossover connection to DB server
>> DATABASE SERVER (Dell PowerEdge SC420)
>> - Windows 2000 Server SP4
>> - MS SQL Server 2000 SP4
>> - Pentium 4 2.8 GHz
>> - 1GB RAM
>> - 2x 80GB SATA HD (7200 RPM), Raid 1
>> - Broadcom NetXtreme 5751 Gb on-board NIC
>> - Zonet Gb PCI NIC - used for crossover connection to web server
>> The two additional dedicated NICs were installed recently.
>> Here is a rough cycle of the problem. About a day or two after I
>> installed the two NICs, I started getting this error. But it wasn't
>> intermittent in that it would fix itself after a while -- once it
>> started throwing the error, it would fairly consistently throw it, only
>> allowing a connection on rare occasions. The only fix was to reboot the
>> server. After rebooting, everything seems find for a day or two, then it
>> starts back up, another reboot... repeat.
>> I have tested for connection issues between the two NICs, and pinging is
>> always successful. I have even completely ruled that out by disabling
>> the new NICs, and going back to the original shared connection, and even
>> on that connection it will start throwing the errors. I have an alias
>> configured using TCP/IP, but I have tried both using the alias and not
>> using it (having the IP configured directly in my connection string),
>> and the results are the same. I have tried using the machine name
>> (which is different than the alias), same results.
>> I read about disabling connection pooling causing this, but to my
>> knowledge, connection pooling is enabled.
>> I'm at a loss. I don't know why this is happening all of the sudden. I
>> thought it may be related to the new NICs, but after disabling them and
>> still getting the problems, that doesn't seem to be the case.
>> Please, any suggestions would be appreciated.
>> Thanks in advance.
>> Jerad
>>
>|||I have Windows auto-updates turned on, and it updates once a week, so
whatever has been installed there recently. Also, to do some network
monitoring, I have intalled two different monitoring apps (DUMeter and
Bandwidth Monitor Pro) -- maybe those are causing the disruption? I can try
turning those off once I start seeing problem again, but I wouldn't think
those would be my culprit. I haven't done anything else though -- no
configuration changes (at least, outside of potential changes from the
mentioned software) and no antivirus software has been installed.
Thanks again.
Jerad
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uUazFVBBGHA.1584@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can one assume that you have not installed any new software, updated
> existing software or reconfigured any settings on either machine e.g
> anti-virus
> John
> "Jerad Rose" <no@.spam.com> wrote in message
> news:euHnTQBBGHA.4092@.TK2MSFTNGP09.phx.gbl...
>> Thanks for your response, John.
>> A few clarifcations. The database and webserver are connected to each
>> other directly via crossover cable, so there is no dns/firewall/routing
>> in the equasion. I have checked event logs on both web and database
>> server, and cannot see *anything* that would indicate new problems. I
>> only see the ongoing events that are normally there, such as SMTP routing
>> problems (nothing new). Also, I should have specified which server I
>> reboot -- it is the web server that is rebooted.
>> So I beleive I have ruled out most of the obvious problems. If there's
>> any other info you need that may help, let me know.
>> Thanks again.
>> Jerad
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:%23wekqiABGHA.976@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> You may want to look at other things such as DNS problems, incorrect
>> routing or firewall problems. Check out the logs for all of these to see
>> if anything corresponds to the time of your failure.
>> It is not clear from your post if you reboot the database server or the
>> web server, if the former then try restarting IIS or rebooting the web
>> server and see if that also cures it!
>> John
>>
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OuW4AM$AGHA.1216@.TK2MSFTNGP14.phx.gbl...
>> I've seen this topic posted several times, but it seems my situation is
>> unique for every other case I've seen.
>> I have a web site that has been running for over 3 years. Recently,
>> we've hit a major spike in activity. Now, after a few days (sometimes
>> less), the site will start returning the following error:
>> SQL Server does not exist or access denied.
>> Here are my server specs:
>> WEB SERVER (Shuttle SK41G)
>> - Windows 2000 Server SP4 (IIS 5.0)
>> - Athlon XP 2100+
>> - 1GB RAM
>> - 80GB SATA HD (7200 RPM)
>> - All pages in ASP (traditional, not .NET)
>> - Realtek RTL8139 on-board NIC (*not* 1Gb) - used for external
>> connection
>> - Zonet Gb PCI NIC - used for crossover connection to DB server
>> DATABASE SERVER (Dell PowerEdge SC420)
>> - Windows 2000 Server SP4
>> - MS SQL Server 2000 SP4
>> - Pentium 4 2.8 GHz
>> - 1GB RAM
>> - 2x 80GB SATA HD (7200 RPM), Raid 1
>> - Broadcom NetXtreme 5751 Gb on-board NIC
>> - Zonet Gb PCI NIC - used for crossover connection to web server
>> The two additional dedicated NICs were installed recently.
>> Here is a rough cycle of the problem. About a day or two after I
>> installed the two NICs, I started getting this error. But it wasn't
>> intermittent in that it would fix itself after a while -- once it
>> started throwing the error, it would fairly consistently throw it, only
>> allowing a connection on rare occasions. The only fix was to reboot
>> the server. After rebooting, everything seems find for a day or two,
>> then it starts back up, another reboot... repeat.
>> I have tested for connection issues between the two NICs, and pinging
>> is always successful. I have even completely ruled that out by
>> disabling the new NICs, and going back to the original shared
>> connection, and even on that connection it will start throwing the
>> errors. I have an alias configured using TCP/IP, but I have tried both
>> using the alias and not using it (having the IP configured directly in
>> my connection string), and the results are the same. I have tried
>> using the machine name (which is different than the alias), same
>> results.
>> I read about disabling connection pooling causing this, but to my
>> knowledge, connection pooling is enabled.
>> I'm at a loss. I don't know why this is happening all of the sudden.
>> I thought it may be related to the new NICs, but after disabling them
>> and still getting the problems, that doesn't seem to be the case.
>> Please, any suggestions would be appreciated.
>> Thanks in advance.
>> Jerad
>>
>>
>|||Hi
This is not necessarily a problem with the web server and SQL Server it
could be that access is denied because of authentication problems. Try using
profiler to monitor logon attempts.
Check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328306
http://support.microsoft.com/default.aspx?scid=kb;en-us;827422
http://support.microsoft.com/default.aspx?scid=kb;en-us;821498
John
"Jerad Rose" <no@.spam.com> wrote in message
news:%233gceCCBGHA.1032@.TK2MSFTNGP11.phx.gbl...
>I have Windows auto-updates turned on, and it updates once a week, so
>whatever has been installed there recently. Also, to do some network
>monitoring, I have intalled two different monitoring apps (DUMeter and
>Bandwidth Monitor Pro) -- maybe those are causing the disruption? I can
>try turning those off once I start seeing problem again, but I wouldn't
>think those would be my culprit. I haven't done anything else though -- no
>configuration changes (at least, outside of potential changes from the
>mentioned software) and no antivirus software has been installed.
> Thanks again.
> Jerad
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uUazFVBBGHA.1584@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> Can one assume that you have not installed any new software, updated
>> existing software or reconfigured any settings on either machine e.g
>> anti-virus
>> John
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:euHnTQBBGHA.4092@.TK2MSFTNGP09.phx.gbl...
>> Thanks for your response, John.
>> A few clarifcations. The database and webserver are connected to each
>> other directly via crossover cable, so there is no dns/firewall/routing
>> in the equasion. I have checked event logs on both web and database
>> server, and cannot see *anything* that would indicate new problems. I
>> only see the ongoing events that are normally there, such as SMTP
>> routing problems (nothing new). Also, I should have specified which
>> server I reboot -- it is the web server that is rebooted.
>> So I beleive I have ruled out most of the obvious problems. If there's
>> any other info you need that may help, let me know.
>> Thanks again.
>> Jerad
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:%23wekqiABGHA.976@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> You may want to look at other things such as DNS problems, incorrect
>> routing or firewall problems. Check out the logs for all of these to
>> see if anything corresponds to the time of your failure.
>> It is not clear from your post if you reboot the database server or the
>> web server, if the former then try restarting IIS or rebooting the web
>> server and see if that also cures it!
>> John
>>
>> "Jerad Rose" <no@.spam.com> wrote in message
>> news:OuW4AM$AGHA.1216@.TK2MSFTNGP14.phx.gbl...
>> I've seen this topic posted several times, but it seems my situation
>> is unique for every other case I've seen.
>> I have a web site that has been running for over 3 years. Recently,
>> we've hit a major spike in activity. Now, after a few days (sometimes
>> less), the site will start returning the following error:
>> SQL Server does not exist or access denied.
>> Here are my server specs:
>> WEB SERVER (Shuttle SK41G)
>> - Windows 2000 Server SP4 (IIS 5.0)
>> - Athlon XP 2100+
>> - 1GB RAM
>> - 80GB SATA HD (7200 RPM)
>> - All pages in ASP (traditional, not .NET)
>> - Realtek RTL8139 on-board NIC (*not* 1Gb) - used for external
>> connection
>> - Zonet Gb PCI NIC - used for crossover connection to DB server
>> DATABASE SERVER (Dell PowerEdge SC420)
>> - Windows 2000 Server SP4
>> - MS SQL Server 2000 SP4
>> - Pentium 4 2.8 GHz
>> - 1GB RAM
>> - 2x 80GB SATA HD (7200 RPM), Raid 1
>> - Broadcom NetXtreme 5751 Gb on-board NIC
>> - Zonet Gb PCI NIC - used for crossover connection to web server
>> The two additional dedicated NICs were installed recently.
>> Here is a rough cycle of the problem. About a day or two after I
>> installed the two NICs, I started getting this error. But it wasn't
>> intermittent in that it would fix itself after a while -- once it
>> started throwing the error, it would fairly consistently throw it,
>> only allowing a connection on rare occasions. The only fix was to
>> reboot the server. After rebooting, everything seems find for a day or
>> two, then it starts back up, another reboot... repeat.
>> I have tested for connection issues between the two NICs, and pinging
>> is always successful. I have even completely ruled that out by
>> disabling the new NICs, and going back to the original shared
>> connection, and even on that connection it will start throwing the
>> errors. I have an alias configured using TCP/IP, but I have tried
>> both using the alias and not using it (having the IP configured
>> directly in my connection string), and the results are the same. I
>> have tried using the machine name (which is different than the alias),
>> same results.
>> I read about disabling connection pooling causing this, but to my
>> knowledge, connection pooling is enabled.
>> I'm at a loss. I don't know why this is happening all of the sudden.
>> I thought it may be related to the new NICs, but after disabling them
>> and still getting the problems, that doesn't seem to be the case.
>> Please, any suggestions would be appreciated.
>> Thanks in advance.
>> Jerad
>>
>>
>>
>

Wednesday, March 21, 2012

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string
building, and
it took me a while to figure it out. I think I can appreciate *why* it's do
ing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf
else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16),
@.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the r
esultant
expression, and determining that it can be coerced to a NCHAR(2). However,
somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR
-- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4)
(as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything wor
ks as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16)
, @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrL
fs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatyp
e
> only, and the datatype it returns is determined by looking at the datatype
s
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or
a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will b
e
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> string building, and
> doing this, but
> @.CrLf else N'' end +
> resultant
> However, somewhere
> NCHAR -- sort of
> NCHAR(4) (as per the
> works as
>

Monday, March 19, 2012

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string building, and
it took me a while to figure it out. I think I can appreciate *why* it's doing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the resultant
expression, and determining that it can be coerced to a NCHAR(2). However, somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR -- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4) (as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything works as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrLfs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatype
> only, and the datatype it returns is determined by looking at the datatypes
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I had a situation where I was getting some extraneous spaces in some
> string building, and
> > it took me a while to figure it out. I think I can appreciate *why* it's
> doing this, but
> > I wish it weren't.
> >
> > Consider the following:
> >
> >
> > declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> > declare @.String nvarchar(255) select @.String = N''
> >
> > select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
> @.CrLf else N'' end +
> > N'Test'
> > print N'"' + @.String + N'"'
> > print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
> >
> >
> > The output is:
> >
> >
> > " Test"
> > 0x20002000200020005400650073007400
> >
> >
> > It feels like, for some reason, the CASE is evaluating both "sides" of the
> resultant
> > expression, and determining that it can be coerced to a NCHAR(2).
> However, somewhere
> > along the lines, it gets confused and is introducing 2 bytes for every
> NCHAR -- sort of
> > like accidentally making it a CHAR(4) that then gets promoted to a
> NCHAR(4) (as per the
> > NULL bytes in every other character).
> >
> > If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
> works as
> > expected.
> >
> > It's very odd, and certainly smells like a bug to me.
> >
> > Thoughts?
> >
> >
>

Wednesday, March 7, 2012

Integration Services: ?Table refresh (UPDATE/INSERT)“

Hello

I have a question about the new Integration Services of the MS SQL Server 2005.

Situation:
- SQL Server 2005 (standard edition)

- 2 tables with identical structure (same attributes)

- the table ?TestSource“ will be constantly extend (new records & updates).

- the table ?TestDestination“ will just be refreshed by SSIS (Data Warehouse table)

I would like to create a Integration Service, witch refreshes the table ?TestDestination“ with the data from table ?TestSource“.

Existing records (ID already exists) should be updated (UPDATE), not existing records should be created (INSERT).

I would like to use the IS Data Flow Task, because in future i won’t just copy the data. I also will use Toolbox items like ?Data Conversion“, ?Derived Column“ and so on.

Alike I won’t use an easy SQL-Query, because it would be complicated to make changes and to Log the transactions.

Just clear and refill the whole table is not possible because of performance and availability requests (large data).

Question:
How can I implement this workflow as Data Flow in a Integration Service?
Witch components from the Toolbox do I need?

Greetings

Not sure if you can use this within SSIS, but the tablediff.exe that comes with sql server 2005 is pretty slick. I wrote a c# wrapper for it and I have a config file that holds all the table names that I want to sync as well as the source and destination servers. It then generates the sql change file as well as gives output of the number of rows that are out of sync. It works really well for me especially using the C# wrapper that I wrote.

Just a thought....

Monday, March 26, 2012

Intermittent "SQL Server does not exist or access denied."

Intermittent "SQL Server does not exist or access denied."

Intermittent "SQL Server does not exist or access denied."

Wednesday, March 21, 2012

Interesting CASE behavior -- bug?

Monday, March 19, 2012

Interesting CASE behavior -- bug?

Wednesday, March 7, 2012

Integration Services: ?Table refresh (UPDATE/INSERT)“

Integrated Security

Blog Archive

About Me