Integrated Security: case

Showing posts with label case. 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
>>
>>
>>
>

Friday, March 23, 2012

Interesting SQL Server behaviour

Hi,
The following code describes a problem I'm having when I'm selecting
data via a Case statement.
The data I'm selecting is from a table with 2 varchar columns and 1
int column. When I select this data I get the following error:
Server: Msg245, Level 16,State 1, Line 1
Syntax error converting the varchar values 'A' to a column of datatype
int.
If I cast the int to a varchar it works, but I would prefer not to
cast if possible. SQL server seems to look at all the datatypes in the
case statement and if a int data type appears, it seems to be trying
to insert into a int column. Is this some sort of temp table? Anyone
know why it assigned an int column here?
Any ideas what I can do
Thanks,
Tom
- --
- -- Set up the table
- --
create table Tom
(charcol1 varchar(2)
,charcol2 varchar(2)
,intcol1 int
)
insert into tom
(charcol1
,intcol1
,charcol2
)
values
('A',1,'C')
- ---
- -- Code
- ---
SELECT
CASE numlist.list
WHEN 1 THEN
Tom.charcol1
WHEN 2 THEN
--cast (Tom.intcol1 as varchar(5))
Tom.intcol1
WHEN 3 THEN
Tom.charcol2
END AS result
FROM Tom
CROSS JOIN (select 1 as list
union all
select 2 as list
union all
select 3 as list
) as numlistThis is how CASE work, result from CASE expression is of the same datatype.
Output is determined as
per "datatype precedence" documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Staggly" <tomstagg@.gmail.com> wrote in message
news:e646e15f.0502090622.8a26408@.posting.google.com...
> Hi,
> The following code describes a problem I'm having when I'm selecting
> data via a Case statement.
> The data I'm selecting is from a table with 2 varchar columns and 1
> int column. When I select this data I get the following error:
> Server: Msg245, Level 16,State 1, Line 1
> Syntax error converting the varchar values 'A' to a column of datatype
> int.
> If I cast the int to a varchar it works, but I would prefer not to
> cast if possible. SQL server seems to look at all the datatypes in the
> case statement and if a int data type appears, it seems to be trying
> to insert into a int column. Is this some sort of temp table? Anyone
> know why it assigned an int column here?
> Any ideas what I can do
> Thanks,
> Tom
> - --
> - -- Set up the table
> - --
> create table Tom
> (charcol1 varchar(2)
> ,charcol2 varchar(2)
> ,intcol1 int
> )
> insert into tom
> (charcol1
> ,intcol1
> ,charcol2
> )
> values
> ('A',1,'C')
> - ---
> - -- Code
> - ---
> SELECT
> CASE numlist.list
> WHEN 1 THEN
> Tom.charcol1
> WHEN 2 THEN
> --cast (Tom.intcol1 as varchar(5))
> Tom.intcol1
> WHEN 3 THEN
> Tom.charcol2
> END AS result
> FROM Tom
> CROSS JOIN (select 1 as list
> union all
> select 2 as list
> union all
> select 3 as list
> ) as numlist

Wednesday, March 21, 2012

Interesting case of "Invalid attempt to MetaData when reader is closed."

I was going to post a long message but I found the problem. Yet I do not know how to correct it efficiently.

I have a request to my DB using that kind of code :

With SQL_Commande
valiSqlConnection(True)
' Configure SQL_Commande.
.Connection = SQL_Connection
.CommandText = "_Fonctions_valiUtilInfo"
.CommandType = Data.CommandType.StoredProcedure
.Parameters.Clear()
Dim SQL_Lecteur As SqlDataReader = SQL_Commande.ExecuteReader
If SQL_Lecteur.Read And Not IsDBNull(SQL_Lecteur(0)) Then Return SQL_Lecteur(0) Else Return Nothing
valiSqlConnection(False, SQL_Lecteur)
End With

My problem is that I need use a function within the "With" section, and that function calls a request on the DB then closes the query, and when it gets back at executing the remaining part of the code, it stop saying that the query is closed and cannot be excuted anymore. How can I nest a query within an other one without having this problem?

Adding a more user friendly structure explaination :

SQL Request #1
Function A ( Extracted Value #1 )
Extracted Value #2
Extracted Value #3
Closing SQL Resquest #1

Function A ( Value #1 )
SQL Request #A
Returning Extracted Value #A
Closing SQL Request #A

It closes both request. I need to keep the request open. Of course I could change the order, but that is just by passing the problem. As well I know I could redesign the TSQL request to correct the problem, but for now, I do not have the time to do that since the request is very large and would need close to complete redesign.

Thank you!

|||

Finaly, due to lack of answer I remade the entire SQL request.

interesting case for "SQL Server does not exist or access denied." error

My connection string is :

server=localhost;Database=INFOSEC;Integrated Security=true

It works for the login page, but it does not work for the registration page, and give the "SQL Server does not exist or access denied." error.

Any IDEA?

You shouls check your connection string properly and make sure you are calling proper database for registration form.Otherwise can you give the code here.So i can findout the error.

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?
> >
> >
>

Sunday, February 19, 2012

Integrating data from two Tables into one

Hi all,

In case I have two tables. In them there is data and I want to integrate all this data into one table.

that is to say Table A and Table B has data (Both A and B are from the same database)

I want to integrate all this data into Table c.

How do i go about this?

Regards,

Ronaldlee

Can you be more specific? How exactly do you want to "integrate" this data?

-Jamie

|||

Hi Jamie, I want to use SSIS 2005 to get data from Table A and Table B into table C.

At the end of the day Table C will act as my staging table.

Ronald

|||

Do tableA & tableB have the same structure as each other?|||

Table A has three columns and Table B has two columns.

Table A and B are in the same database and table C is in the different database.

Ronald

|||

My god this is like pulling teeth!!!!!

What transformation do you want to perform on TableA & TableB in order to get the data into tableC?|||

No men, you are not rude.

Okay, I do not have any idea about the JOIN or UNION Transformation. May be you can recommend.

I need table C to have all the five columns from table A(3Columns) and B(2Columns).

Table A and Table B are the source

Table C is the target.

I need to find a way to join Table A and B.(Is it possible?)

Ronald

|||

Ronaldlee Ejalu wrote:

No men, you are not rude.
Okay, I do not have any idea about the JOIN or UNION Transformation. May be you can recommend.
I need table C to have all the five columns from table A(3Columns) and B(2Columns).

OK, that's useful!

Ronaldlee Ejalu wrote:

I need to find a way to join Table A and B.(Is it possible?)

Only you can answer that. Is there a field in each of those two tables that means the same thing for both tables? If so, you could join on that field in the MERGE JOIN component.

Joining two tables that don't have a relationship between them is an incredibly uncommon thing to do and hence SSIS doesn't contain anything to do it. There is possibly a way to do it but i suspect that that is not what you want to do!

-Jamie

|||

Thanx Jamie,

Could you tell me the other way. May i Could come across it in future.

Thanx alot

Regards,

Ronald

|||

The "other way" I spoke of is to do the joining in a script component or custom component that you build yourself. Those options mean writing code!

-Jamie

integrated windows authentication

Hello,
In my report application, I had to remove anonymous
authentication and set integrated windows authentication
in IIS manager, in this case,
http://localhost/ReportServer?%2fReport+Project1%
2fReport1&rs%3aClearSession=true&rc%3aView=9d451448-9098-
4b71-8511-c7e08926c50a
works fine, but in the same computer,
http://myserverName/ReportServer?%2fReport+Project1%
2fReport1&rs%3aClearSession=true&rc%3aView=9d451448-9098-
4b71-8511-c7e08926c50a
asks me use id and password when I use myServerName
instead of localhost. Wow can I get windows authentication
works without asking username password?
Thanks,
Jim.Hi Jim,
JIM.H. wrote:
> http://localhost/ReportServer?%2fReport+Project1%
> 2fReport1&rs%3aClearSession=true&rc%3aView=9d451448-9098-
> 4b71-8511-c7e08926c50a
> works fine, but in the same computer,
> http://myserverName/ReportServer?%2fReport+Project1%
> 2fReport1&rs%3aClearSession=true&rc%3aView=9d451448-9098-
> 4b71-8511-c7e08926c50a
> asks me use id and password when I use myServerName
> instead of localhost. Wow can I get windows authentication
> works without asking username password?
I believe that localhost works because it is considered an Intranet site.
You could try adding http://myserverName to your list of Local Intranet
sites (IE, Tools | Internet Options, Security tab, Local Intranet, Sites,
Advanced). That should suppress the dialog and pass your Windows
authentication through automatically.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]|||Thanks Jake,
It works fine when myServerName is in trusted list. Now, I
have another problem, I have a report application:
http://myServerName/ReportServer?%2fReport+Project1%
2fReport1&rs:Command=Render. This is working fine in any
computer. I also have an asp.net application that has this
report embedded as a report viewer, so
http://myServerName/App/WebForm1.aspx works fine and bring
report in myServerName server. However when I do
http://myServerName/App/WebForm1.aspx in any other
computer other than myServerName, report viewer is not
showing in IE.
I am not sure what repoet viewer parameters should be but
I am using ReportPath as /Report Project1/Report1,
ServerUrl as http:// myServerName /reportserver.
What is problem?
Thanks,
Jim.
>--Original Message--
>Hi Jim,
>JIM.H. wrote:
>> http://localhost/ReportServer?%2fReport+Project1%
>> 2fReport1&rs%3aClearSession=true&rc%3aView=9d451448-
9098-
>> 4b71-8511-c7e08926c50a
>> works fine, but in the same computer,
>> http://myserverName/ReportServer?%2fReport+Project1%
>> 2fReport1&rs%3aClearSession=true&rc%3aView=9d451448-
9098-
>> 4b71-8511-c7e08926c50a
>> asks me use id and password when I use myServerName
>> instead of localhost. Wow can I get windows
authentication
>> works without asking username password?
>I believe that localhost works because it is considered
an Intranet site.
>You could try adding http://myserverName to your list of
Local Intranet
>sites (IE, Tools | Internet Options, Security tab, Local
Intranet, Sites,
>Advanced). That should suppress the dialog and pass your
Windows
>authentication through automatically.
>--
>Regards,
>Jake Marx
>MS MVP - Excel
>www.longhead.com
>[please keep replies in the newsgroup - email address
unmonitored]
>.
>

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."

Friday, March 23, 2012

Interesting SQL Server behaviour

Wednesday, March 21, 2012

Interesting case of "Invalid attempt to MetaData when reader is closed."

interesting case for "SQL Server does not exist or access denied." error

Interesting CASE behavior -- bug?

Monday, March 19, 2012

Interesting CASE behavior -- bug?

Sunday, February 19, 2012

Integrating data from two Tables into one

integrated windows authentication

Integrated Security

Blog Archive

About Me