All,
SQL 2000, sp3, Server 2000 sp4
I have 3 servers, they are all set up as linked servers. The link to the
other servers works then all of a sudden you can't see the other
servers. Has anyone ever seen this?
I have even deleted the info in the client network utility and tried to
re-register the servers, to no avail.
Please help.
Thanks All,
snyper
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!hi,
mostly linked to physical network factor.
check that part out.
thanks
rahul
>--Original Message--
>All,
>SQL 2000, sp3, Server 2000 sp4
>I have 3 servers, they are all set up as linked servers.
The link to the
>other servers works then all of a sudden you can't see
the other
>servers. Has anyone ever seen this?
>I have even deleted the info in the client network
utility and tried to
>re-register the servers, to no avail.
>Please help.
>Thanks All,
>snyper
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>sql
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Friday, March 30, 2012
Loss of Connection
We have an application running on a server that does a connection
check on it's connection to the database on a SQL Server 2000.
Sometimes it looses it's connection and then is unable to restablish
the connection for over an hour. During the time that it looses
contact with the SQL Server there is some pretty heavy activity on the
SQL Server 2000 box.
Is there some setting I've overlooked ... or is this some weakness on
the part of SQL Server ? I don't think the application is doing a
query or anything, I think it's just some heartbeat kind of routine.
rls
Seattle, WAare you attaching via name or IP address? Without a WINS, DNS or ADS server,
attaching via name may be unrealizable. Try using the IP address.
--
J
www.urbanvoyeur.com
"brlarue" <ron.strouss@.westfarm.com> wrote in message
news:42b547894434e770528406949d17c5b5@.news.teranews.com...
> We have an application running on a server that does a connection
> check on it's connection to the database on a SQL Server 2000.
> Sometimes it looses it's connection and then is unable to restablish
> the connection for over an hour. During the time that it looses
> contact with the SQL Server there is some pretty heavy activity on the
> SQL Server 2000 box.
> Is there some setting I've overlooked ... or is this some weakness on
> the part of SQL Server ? I don't think the application is doing a
> query or anything, I think it's just some heartbeat kind of routine.
> rls
> Seattle, WA|||We have a DNS. I'll take a look at the possibility of using the IP
address. Here is the message coming from the application that looses
it's connection.
GENTRAN Notification: ConvertedNotification3 Oct 05 2003 07:29:20
EventID=55867 1-1-50009:ODBC: MFC database exception in
Program/RETCODE: Edimgr/-1State:08S01,Native:0,Origin:[Microsoft][ODBC
SQL Server Driver]
Communication link failure
-
On Mon, 6 Oct 2003 06:26:59 -0400, "UrbanVoyeur" <nospam@.nospam.com>
wrote:
>are you attaching via name or IP address? Without a WINS, DNS or ADS server,
>attaching via name may be unrealizable. Try using the IP address.
check on it's connection to the database on a SQL Server 2000.
Sometimes it looses it's connection and then is unable to restablish
the connection for over an hour. During the time that it looses
contact with the SQL Server there is some pretty heavy activity on the
SQL Server 2000 box.
Is there some setting I've overlooked ... or is this some weakness on
the part of SQL Server ? I don't think the application is doing a
query or anything, I think it's just some heartbeat kind of routine.
rls
Seattle, WAare you attaching via name or IP address? Without a WINS, DNS or ADS server,
attaching via name may be unrealizable. Try using the IP address.
--
J
www.urbanvoyeur.com
"brlarue" <ron.strouss@.westfarm.com> wrote in message
news:42b547894434e770528406949d17c5b5@.news.teranews.com...
> We have an application running on a server that does a connection
> check on it's connection to the database on a SQL Server 2000.
> Sometimes it looses it's connection and then is unable to restablish
> the connection for over an hour. During the time that it looses
> contact with the SQL Server there is some pretty heavy activity on the
> SQL Server 2000 box.
> Is there some setting I've overlooked ... or is this some weakness on
> the part of SQL Server ? I don't think the application is doing a
> query or anything, I think it's just some heartbeat kind of routine.
> rls
> Seattle, WA|||We have a DNS. I'll take a look at the possibility of using the IP
address. Here is the message coming from the application that looses
it's connection.
GENTRAN Notification: ConvertedNotification3 Oct 05 2003 07:29:20
EventID=55867 1-1-50009:ODBC: MFC database exception in
Program/RETCODE: Edimgr/-1State:08S01,Native:0,Origin:[Microsoft][ODBC
SQL Server Driver]
Communication link failure
-
On Mon, 6 Oct 2003 06:26:59 -0400, "UrbanVoyeur" <nospam@.nospam.com>
wrote:
>are you attaching via name or IP address? Without a WINS, DNS or ADS server,
>attaching via name may be unrealizable. Try using the IP address.
Losing odbc connection on install
We have a system which uses an ODBC connection to connect to SQL Server 2000
for Ceridian Prism--an application for HR departments. Now we are installing
a VB.Net application which uses MSDE (SQL 7). The problem is that for some
reason we are losing the original ODBC connection to 2000 when we install
MSDE. Now I realize that installing MSDE 2000 may help this issue, however
we really need to use SQL 7 for now. Does anyone have any ideas of what
might be causing the loss in the connection? Is the SQL 7 install
overwriting something that the ODBC needs for the 2000 connection? Is it
something with named instances? We are using the standard MSDE installation
from Microsoft.
Thanks.
My guess is that the MSDE 7 installation is installing an older version of
MDAC which is not ADO.Net compatible. It could also be an issue with named
instances as earlier version of MDAC (pre 2.5 I think) did not support named
instances.
Jim
"LisaConsult" <lisaconsult@.online.nospam> wrote in message
news:81EA76DC-A07B-4982-B9F2-CD31ACE1F0B0@.microsoft.com...
> We have a system which uses an ODBC connection to connect to SQL Server
> 2000
> for Ceridian Prism--an application for HR departments. Now we are
> installing
> a VB.Net application which uses MSDE (SQL 7). The problem is that for
> some
> reason we are losing the original ODBC connection to 2000 when we install
> MSDE. Now I realize that installing MSDE 2000 may help this issue,
> however
> we really need to use SQL 7 for now. Does anyone have any ideas of what
> might be causing the loss in the connection? Is the SQL 7 install
> overwriting something that the ODBC needs for the 2000 connection? Is it
> something with named instances? We are using the standard MSDE
> installation
> from Microsoft.
> Thanks.
|||Thanks for your response. Actually, we know that it is somehow SQL Server
related and not MDAC because once we uninstalled Server Manager and MSDE, the
connection worked fine again. As an aside, if they needed MDAC, we installed
2.6, but as I said, I don't believe this was the issue. Any other thoughts?
Thanks
"Jim Young" wrote:
> My guess is that the MSDE 7 installation is installing an older version of
> MDAC which is not ADO.Net compatible. It could also be an issue with named
> instances as earlier version of MDAC (pre 2.5 I think) did not support named
> instances.
> Jim
> "LisaConsult" <lisaconsult@.online.nospam> wrote in message
> news:81EA76DC-A07B-4982-B9F2-CD31ACE1F0B0@.microsoft.com...
>
>
|||Oops, my mistake, this app is actually still a VB6 app.
"LisaConsult" wrote:
> We have a system which uses an ODBC connection to connect to SQL Server 2000
> for Ceridian Prism--an application for HR departments. Now we are installing
> a VB.Net application which uses MSDE (SQL 7). The problem is that for some
> reason we are losing the original ODBC connection to 2000 when we install
> MSDE. Now I realize that installing MSDE 2000 may help this issue, however
> we really need to use SQL 7 for now. Does anyone have any ideas of what
> might be causing the loss in the connection? Is the SQL 7 install
> overwriting something that the ODBC needs for the 2000 connection? Is it
> something with named instances? We are using the standard MSDE installation
> from Microsoft.
> Thanks.
|||I still think that it is a problem with the data connection layer and not
SQL Server. Have you tried installing MDAC 2.8 after MSDE 7 is installed.
Jim
"LisaConsult" <lisaconsult@.online.nospam> wrote in message
news:2271E92F-AFE5-4C14-A8FA-2A05326227B6@.microsoft.com...[vbcol=seagreen]
> Thanks for your response. Actually, we know that it is somehow SQL Server
> related and not MDAC because once we uninstalled Server Manager and MSDE,
> the
> connection worked fine again. As an aside, if they needed MDAC, we
> installed
> 2.6, but as I said, I don't believe this was the issue. Any other
> thoughts?
> Thanks
> "Jim Young" wrote:
for Ceridian Prism--an application for HR departments. Now we are installing
a VB.Net application which uses MSDE (SQL 7). The problem is that for some
reason we are losing the original ODBC connection to 2000 when we install
MSDE. Now I realize that installing MSDE 2000 may help this issue, however
we really need to use SQL 7 for now. Does anyone have any ideas of what
might be causing the loss in the connection? Is the SQL 7 install
overwriting something that the ODBC needs for the 2000 connection? Is it
something with named instances? We are using the standard MSDE installation
from Microsoft.
Thanks.
My guess is that the MSDE 7 installation is installing an older version of
MDAC which is not ADO.Net compatible. It could also be an issue with named
instances as earlier version of MDAC (pre 2.5 I think) did not support named
instances.
Jim
"LisaConsult" <lisaconsult@.online.nospam> wrote in message
news:81EA76DC-A07B-4982-B9F2-CD31ACE1F0B0@.microsoft.com...
> We have a system which uses an ODBC connection to connect to SQL Server
> 2000
> for Ceridian Prism--an application for HR departments. Now we are
> installing
> a VB.Net application which uses MSDE (SQL 7). The problem is that for
> some
> reason we are losing the original ODBC connection to 2000 when we install
> MSDE. Now I realize that installing MSDE 2000 may help this issue,
> however
> we really need to use SQL 7 for now. Does anyone have any ideas of what
> might be causing the loss in the connection? Is the SQL 7 install
> overwriting something that the ODBC needs for the 2000 connection? Is it
> something with named instances? We are using the standard MSDE
> installation
> from Microsoft.
> Thanks.
|||Thanks for your response. Actually, we know that it is somehow SQL Server
related and not MDAC because once we uninstalled Server Manager and MSDE, the
connection worked fine again. As an aside, if they needed MDAC, we installed
2.6, but as I said, I don't believe this was the issue. Any other thoughts?
Thanks
"Jim Young" wrote:
> My guess is that the MSDE 7 installation is installing an older version of
> MDAC which is not ADO.Net compatible. It could also be an issue with named
> instances as earlier version of MDAC (pre 2.5 I think) did not support named
> instances.
> Jim
> "LisaConsult" <lisaconsult@.online.nospam> wrote in message
> news:81EA76DC-A07B-4982-B9F2-CD31ACE1F0B0@.microsoft.com...
>
>
|||Oops, my mistake, this app is actually still a VB6 app.
"LisaConsult" wrote:
> We have a system which uses an ODBC connection to connect to SQL Server 2000
> for Ceridian Prism--an application for HR departments. Now we are installing
> a VB.Net application which uses MSDE (SQL 7). The problem is that for some
> reason we are losing the original ODBC connection to 2000 when we install
> MSDE. Now I realize that installing MSDE 2000 may help this issue, however
> we really need to use SQL 7 for now. Does anyone have any ideas of what
> might be causing the loss in the connection? Is the SQL 7 install
> overwriting something that the ODBC needs for the 2000 connection? Is it
> something with named instances? We are using the standard MSDE installation
> from Microsoft.
> Thanks.
|||I still think that it is a problem with the data connection layer and not
SQL Server. Have you tried installing MDAC 2.8 after MSDE 7 is installed.
Jim
"LisaConsult" <lisaconsult@.online.nospam> wrote in message
news:2271E92F-AFE5-4C14-A8FA-2A05326227B6@.microsoft.com...[vbcol=seagreen]
> Thanks for your response. Actually, we know that it is somehow SQL Server
> related and not MDAC because once we uninstalled Server Manager and MSDE,
> the
> connection worked fine again. As an aside, if they needed MDAC, we
> installed
> 2.6, but as I said, I don't believe this was the issue. Any other
> thoughts?
> Thanks
> "Jim Young" wrote:
Wednesday, March 28, 2012
Loosing SQL connection while running ASP
We are running an ASP application. That is fairly simple.
It selects from one table (15.000 records), and updates into another table.
But when we execute the ASP script, it reports the following error, after
completing between 70%-80%. This varies each time it runs
"Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. "
The application is running in ths following server environment
Server 1
Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)
Windows 2003 Web Server (IIS)
Server 2
Fujitsu-Siemens RX300 Server (Dual 2,8 Xeon, 1 Gb Ram, 72 Gb SCSI Raid 1)
Windows 2003 Standart server
Microsoft SQL Server 2000Jesper Carstensen (jesper@.swush.com) writes:
> We are running an ASP application. That is fairly simple.
> It selects from one table (15.000 records), and updates into another
> table. But when we execute the ASP script, it reports the following
> error, after completing between 70%-80%. This varies each time it runs
>
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied. "
So how exactly do you retrieve the rows and submit the updates? For
retrieval I would recommend client-side cursors.
Is the table you update on the same server as the one you read from?
In such case, the best may be to have all the updating login in a stored
procedure, unless the logic is too complex for SQL.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are seeing that EXACT same error popup randomly on our web server
running Windows 2003 Server. Here's our setup:
Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates, etc.
Web Server 2 - Windows 2003 Server - All updates
SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
Now, we used to have both our web servers be 2000 server. We've
recently updated the 2nd one to be 2003 server. Ever since we have
been getting that same error (below) at random times. We seem to think
that it may fail under heavy load.
"Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. "
We've done just about everything we've seen suggested here in the
groups and on Microsoft's site without any success. We can't nail down
what is causing this problem. If ANYBODY can help point us in the
right direction we'd greatly apprecaite it. What's odd is that I've
seen many posts reporting this same problem without any solutions
given. It seems that maybe there's some kind of issue with 2003 that
hasn't been documented or exposed yet.|||This would be my checklist.. (1 and 2 are probably ruled out given that the
2000 server works with no problem)
one clue you may have to help you is the amount of time the web server takes
to throw that error when you access the page. Is it trying to connect and
then giving up after a few seconds, or is it failing immediately without
attempting to handshake with the server.
1) Check the DB server loading, increase connection timeout if its heavy for
extended periods.
2) continuously ping the DB server from the web server(s) to see if there
are connection outages/packet loss.
3) Try a different(older) version of MDAC on the web server, fiddle with the
client settings.
4) Try varying the connection type (SQL logon Windows logon Named Pipes
TCP/IP)
5) Run IIS under a different isolation level, fiddle with the application
settings
7) trap the error in the connection code and attempt a re-connect (3 strikes
and your out).
6) try something else ...
8) get the 2000 server back out
hope you find a solution to the problem, let us know if you do. :)
Mr Tea
http://mr-tea.blogspot.com
"Don Kitchen" <donkitchen@.gmail.com> wrote in message
news:1106327170.237818.86870@.c13g2000cwb.googlegro ups.com...
> We are seeing that EXACT same error popup randomly on our web server
> running Windows 2003 Server. Here's our setup:
> Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates, etc.
> Web Server 2 - Windows 2003 Server - All updates
> SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
> Now, we used to have both our web servers be 2000 server. We've
> recently updated the 2nd one to be 2003 server. Ever since we have
> been getting that same error (below) at random times. We seem to think
> that it may fail under heavy load.
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied. "
> We've done just about everything we've seen suggested here in the
> groups and on Microsoft's site without any success. We can't nail down
> what is causing this problem. If ANYBODY can help point us in the
> right direction we'd greatly apprecaite it. What's odd is that I've
> seen many posts reporting this same problem without any solutions
> given. It seems that maybe there's some kind of issue with 2003 that
> hasn't been documented or exposed yet.|||"Don Kitchen" <donkitchen@.gmail.com> wrote in message
news:1106327170.237818.86870@.c13g2000cwb.googlegro ups.com...
> We are seeing that EXACT same error popup randomly on our web server
> running Windows 2003 Server. Here's our setup:
> Web Server 1 - Windows 2000 Server - Latest SP, MDAC, All Updates,
etc.
> Web Server 2 - Windows 2003 Server - All updates
> SQL Server - Windows 2003 Server, SQL Server 2003 SP3a, All Updates
> Now, we used to have both our web servers be 2000 server. We've
> recently updated the 2nd one to be 2003 server. Ever since we have
> been getting that same error (below) at random times. We seem to
think
> that it may fail under heavy load.
> "Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> access denied. "
> We've done just about everything we've seen suggested here in the
> groups and on Microsoft's site without any success. We can't nail
down
> what is causing this problem. If ANYBODY can help point us in the
> right direction we'd greatly apprecaite it. What's odd is that I've
> seen many posts reporting this same problem without any solutions
> given. It seems that maybe there's some kind of issue with 2003
that
> hasn't been documented or exposed yet.
I've seen the same problem and I think it's specifically a memory
problem. It may be a more general "resource" issue correlated with
memory but it is hard to pin down.|||I'm happy to report that we have found the fix to our problems.
Without going into too much detail here, I can say that 2 registry keys
that we created in Windows 2003 fixed the problem.
See these two threads for more info about it:
http://groups-beta.google.com/group...57109e74649c7c8
http://groups-beta.google.com/group...075084961f5cdb7
Loosing ODBC connection
Hi all,
We recently converted an application from accecc97 to access2003.
We used to have 2 databases : 1 with all and only the data (axelD.mbd), 1 with all the forms, query's, ... (axelP.mdb)
We replaced the data (axelD.mdb) with an MSDE database, through UPSIZE and so on. That all worked fine.
This database is placed on the dataserver (DATA1), the converted programm is on the FRONT2 server (FRONT1 stil usess access97 until conversion is totally ready).
We are able to connect to the MSDE by using ADODB recordsets.
We are also perfect able to link the MSDEtables in our axelP.mbd database
We started to test some heavier parts of our application and started to get the following error:
"ERROR 3151"
"ODBC--connection to 'JOROSOFT' failed"
Nothing more.
This happened in a quite complex routine, whitch updates a lot of records, several times and so on.
We could reproduce the same error when executing the following code:
Public Function TestBestellingen()
Dim sql As String
Dim intervalset As DAO.Recordset
Dim planset As DAO.Recordset
Dim tellerke
x = Opendatabases()
tellerke = 0
sql = "Select bestelnummer from vkpbestellijn where bestelnummer > 100000"
Set planset = Db_AxelP.OpenRecordset(sql, dbOpenDynaset)
Do While Not planset.EOF
tellerke = tellerke + 1
Debug.Print tellerke & " - " & planset!Bestelnummer & " --> " & IsKlantStof(planset!Bestelnummer)
planset.MoveNext
Loop
End Function
Function IsKlantStof(p_bestelnummer)
Dim sql As String, rst As DAO.Recordset
Dim hulp_klantstof As Boolean
hulp_klantstof = False
x = Opendatabases()
sql = "select klantstof from vkpbestellijn where bestelnummer = " & p_bestelnummer
Set rst = Db_AxelP.OpenRecordset(sql, dbOpenSnapshot)
With rst
Do While Not .EOF
If !KlantStof = True Then
hulp_klantstof = True
.MoveLast
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
IsKlantStof = hulp_klantstof
End Function
Function Opendatabases()
If Db_AxelP Is Nothing Then
Set Db_AxelP = DBEngine(0)(0)
End If
End Function
I know this code makes no sence in a real-time environment, but it was to reproduce the error.
This code basically runs trough a 30.000 records and does a little check for certain fields
After 1981 records we get the above error.
(sometimes after 1979 or 1980 or 1982 records)
1977 - 100859 --> True
1978 - 100860 --> True
1979 - 100860 --> False
1980 - 100861 --> True
1981 - 100861 --> True
"vkpbestellijn" is a table that is linked in the AxelP.mdb trough an ODBC connection.
We get the same error (and after 1981 times) if I change the where to a totally other range of "bestelnummer"
I have installed the latest patches of Jet 4.0, MSDE-server, ...
I've been searching the internet for abour 3 days now, and just cant even find the smallest clue what could make this happen.
I hope someone here has an idea.
If you need more details, just ask.
tia,
Axel.
P.S. I'm from Belgium, so my english aint "correct to the point".MSDE auto closes when the last user disconnects.
ALTER DATABASE MyDB AUTO_CLOSE OFF might take care of this.|||I tried that, but it didn't changed a single thing.
The exact same problem still occurs. :eek: :eek:
Getting desperate here. :o|||Is there no one around who recognises the problem or came across that problem?
I've tried playing with the Query governor, but that didn't changed a single thing.
The strange thing is that when i try the same code on a identically server that is in use by users, i have it less frequent.
It looks like the MSDE server cant keep up with all the quesry's executed. And when the server has more users working, it has enough time to release some things.
does that makes sence top anyone?
We recently converted an application from accecc97 to access2003.
We used to have 2 databases : 1 with all and only the data (axelD.mbd), 1 with all the forms, query's, ... (axelP.mdb)
We replaced the data (axelD.mdb) with an MSDE database, through UPSIZE and so on. That all worked fine.
This database is placed on the dataserver (DATA1), the converted programm is on the FRONT2 server (FRONT1 stil usess access97 until conversion is totally ready).
We are able to connect to the MSDE by using ADODB recordsets.
We are also perfect able to link the MSDEtables in our axelP.mbd database
We started to test some heavier parts of our application and started to get the following error:
"ERROR 3151"
"ODBC--connection to 'JOROSOFT' failed"
Nothing more.
This happened in a quite complex routine, whitch updates a lot of records, several times and so on.
We could reproduce the same error when executing the following code:
Public Function TestBestellingen()
Dim sql As String
Dim intervalset As DAO.Recordset
Dim planset As DAO.Recordset
Dim tellerke
x = Opendatabases()
tellerke = 0
sql = "Select bestelnummer from vkpbestellijn where bestelnummer > 100000"
Set planset = Db_AxelP.OpenRecordset(sql, dbOpenDynaset)
Do While Not planset.EOF
tellerke = tellerke + 1
Debug.Print tellerke & " - " & planset!Bestelnummer & " --> " & IsKlantStof(planset!Bestelnummer)
planset.MoveNext
Loop
End Function
Function IsKlantStof(p_bestelnummer)
Dim sql As String, rst As DAO.Recordset
Dim hulp_klantstof As Boolean
hulp_klantstof = False
x = Opendatabases()
sql = "select klantstof from vkpbestellijn where bestelnummer = " & p_bestelnummer
Set rst = Db_AxelP.OpenRecordset(sql, dbOpenSnapshot)
With rst
Do While Not .EOF
If !KlantStof = True Then
hulp_klantstof = True
.MoveLast
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
IsKlantStof = hulp_klantstof
End Function
Function Opendatabases()
If Db_AxelP Is Nothing Then
Set Db_AxelP = DBEngine(0)(0)
End If
End Function
I know this code makes no sence in a real-time environment, but it was to reproduce the error.
This code basically runs trough a 30.000 records and does a little check for certain fields
After 1981 records we get the above error.
(sometimes after 1979 or 1980 or 1982 records)
1977 - 100859 --> True
1978 - 100860 --> True
1979 - 100860 --> False
1980 - 100861 --> True
1981 - 100861 --> True
"vkpbestellijn" is a table that is linked in the AxelP.mdb trough an ODBC connection.
We get the same error (and after 1981 times) if I change the where to a totally other range of "bestelnummer"
I have installed the latest patches of Jet 4.0, MSDE-server, ...
I've been searching the internet for abour 3 days now, and just cant even find the smallest clue what could make this happen.
I hope someone here has an idea.
If you need more details, just ask.
tia,
Axel.
P.S. I'm from Belgium, so my english aint "correct to the point".MSDE auto closes when the last user disconnects.
ALTER DATABASE MyDB AUTO_CLOSE OFF might take care of this.|||I tried that, but it didn't changed a single thing.
The exact same problem still occurs. :eek: :eek:
Getting desperate here. :o|||Is there no one around who recognises the problem or came across that problem?
I've tried playing with the Query governor, but that didn't changed a single thing.
The strange thing is that when i try the same code on a identically server that is in use by users, i have it less frequent.
It looks like the MSDE server cant keep up with all the quesry's executed. And when the server has more users working, it has enough time to release some things.
does that makes sence top anyone?
Loosing database connection when device power-off (native oledb code)
Hi,
I experienced this problems on both Windows Mobile 2003 SE and Windows Mobile 5.0.
Its native development (c++, oledb, atl and mfc).
It's quite simple to reproduce...
1. open a database
2. open a rowset on table A (whatever, valid of course and with both IOpenRowset and ICommandText), read datas and close rowset
3. power off
4. power on
5. try step 2 with another table (failed on openrowset with error 0x80004005) or try table A (sometimes working because of cached memory, sometims failed on Read Datas).
6. being stuck ;-)
Our work-around was, in case we loose our connection (identified by error 0x80004005 on openrowset), we close it and re-open database... ugly for sure, but working.
What I'm looking now is to use some kind of "detection method" like what people in .Net develoment are using "if ConnectionState.Open <> ...) for reopening my database only on demand...
Thanks in advance for any hints,
Fabien.
Your application should detect device going standby, closing connection which should be reestablished upon power up.
You've already found similar solution, you might as well continue doing that instead of making your application power state aware.
Note: that usually happens if database is on removable storage card.
|||Thanks for your answer.
You're right about the removable storage card (both using SD and CF on X50/X51).
Have you any hint how to detect device going stand by and how to detect power up ?
Is it normal notifications or should I implement an OleDB interface ?
Regards,
Thanks,
Fabien.
This might be of help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wceddk5/html/wce50lrfrequestpowernotifications.asp
|||Thanks a lot.
I'm just afraid that I've no other ways to go if I want to make my application Power aware ;-)
I had read those articles before.
In the meantime, my work-around seems to work so far, so I already won 10 days to implement the power features with no pressure.
I'm just sad that DBCONNECTION_STATUS is not implemented.
Again, thanks for your tips.
Monday, March 26, 2012
Looping through source connections
I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.
I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)
Thanks,
Pete
Let me make sure I understand what you are trying to do.
You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.
Is that correct?
You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1
Peter Cwik wrote:
Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.
Peter,
The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.
Can you explain why the Flat File example is not useful to you?
-Jamie
|||
Jamie,
The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.
I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.
Sorry for my little rant, I do need and want help on the specific steps.
Hope this helps,
Peter Cwik
|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||
Jamie,
The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.
I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.
Sorry for my little rant, I do need and want help on the specific steps.
Hope this helps,
Peter Cwik
Peter,
I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.
You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?
Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||
The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.
I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.
As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.
|||That was pretty close. Thank you.
I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.
Thanks again.
Looping through source connections
I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.
I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)
Thanks,
Pete
Let me make sure I understand what you are trying to do.
You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.
Is that correct?
You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1
Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.
Peter,
The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.
Can you explain why the Flat File example is not useful to you?
-Jamie
|||
Jamie,
The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.
I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.
Sorry for my little rant, I do need and want help on the specific steps.
Hope this helps,
Peter Cwik
|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||
Jamie,
The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.
I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.
Sorry for my little rant, I do need and want help on the specific steps.
Hope this helps,
Peter Cwik
Peter,
I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.
You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?
Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||
The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.
I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.
As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.
|||That was pretty close. Thank you.
I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.
Thanks again.
Friday, March 9, 2012
Looking up Dates
Hi, I have another problem with my MS SQL Database 7.0. I am connecting to it from Delphi 7.0 with an ADO connection, and I have a database table of the call details that come into the call center. When I try and run the following SQL query, the first column, which is CHAR Size 10, physical Length 11 comes with with garbage, instead of a number. Here is the SQL query:
select * from CallDetail where DATENAME(weekday, InitiatedDateTimeGmt) = 'sunday' and InitiatedDateTimeGmt between '10/10/2003 00:00' and '10/16/2003 00:00'
The interesting part of it is, when I delete the where part of the query, the table comes up fine, with the right values in the first column.
Does anyone have any ideas what could be wrong with it?When I try and run the following SQL query, the first column, which is CHAR Size 10, physical Length 11 comes with with garbage...
what do you mean by that?|||Originally posted by ms_sql_dba
what do you mean by that?
For the first record that came up, in the first column the value was aBN0I7NwVX even though I know that the value is not supposed to be that, because it is a number. When I open up a BDE connection to the database, and browse there in data tab, they all come up fine.|||Originally posted by aimtech
For the first record that came up, in the first column the value was aBN0I7NwVX even though I know that the value is not supposed to be that, because it is a number. When I open up a BDE connection to the database, and browse there in data tab, they all come up fine.
What happens when you do DBCC CHECKDB?|||Originally posted by Brett Kaiser
What happens when you do DBCC CHECKDB?
ummm how do I do that?
select * from CallDetail where DATENAME(weekday, InitiatedDateTimeGmt) = 'sunday' and InitiatedDateTimeGmt between '10/10/2003 00:00' and '10/16/2003 00:00'
The interesting part of it is, when I delete the where part of the query, the table comes up fine, with the right values in the first column.
Does anyone have any ideas what could be wrong with it?When I try and run the following SQL query, the first column, which is CHAR Size 10, physical Length 11 comes with with garbage...
what do you mean by that?|||Originally posted by ms_sql_dba
what do you mean by that?
For the first record that came up, in the first column the value was aBN0I7NwVX even though I know that the value is not supposed to be that, because it is a number. When I open up a BDE connection to the database, and browse there in data tab, they all come up fine.|||Originally posted by aimtech
For the first record that came up, in the first column the value was aBN0I7NwVX even though I know that the value is not supposed to be that, because it is a number. When I open up a BDE connection to the database, and browse there in data tab, they all come up fine.
What happens when you do DBCC CHECKDB?|||Originally posted by Brett Kaiser
What happens when you do DBCC CHECKDB?
ummm how do I do that?
looking up client side connection info
I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.
Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve
|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.
Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve
|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve
looking up client side connection info
I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
> > the very least. Is it possible to retrieve a OLE DB connection string
that
> > the client used to connect to the server in the first place? I know
that
> > the connection string wouldn't really be of any interest to SQL server
but I
> > was hoping that it would forward the info anyway.
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
> > the very least. Is it possible to retrieve a OLE DB connection string
that
> > the client used to connect to the server in the first place? I know
that
> > the connection string wouldn't really be of any interest to SQL server
but I
> > was hoping that it would forward the info anyway.
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve
looking up client side connection info
I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string tha
t
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but
I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I[vbcol=seagreen]
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string tha
t
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but
I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I[vbcol=seagreen]
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve
Subscribe to:
Posts (Atom)