db time conversion: sql2000

Showing posts with label sql2000. Show all posts

Monday, March 26, 2012

query

I want to remove SQLXML and SQL Mail from a production SQL
2000 server.
How do I do it without reinstalling SQL?You can remove SQLXML by uninstalling through Control Panel
-> Add/Remove Programs.
For SQL Mail, you can somewhat disable it by removing the
mail profile from Agent and SQL Mail and then uninstall the
MAPI client through Control Panel -> Add/Remove Programs.
-Sue
On Fri, 14 May 2004 03:51:29 -0700, "anurag"
<anonymous@.discussions.microsoft.com> wrote:

>I want to remove SQLXML and SQL Mail from a production SQL
>2000 server.
>How do I do it without reinstalling SQL?
>

Friday, March 23, 2012

Query

I want to remove SQLXML and SQL Mail from a production SQL
2000 server.
How do I do it without reinstalling SQL?
"Anurag" <anonymous@.discussions.microsoft.com> wrote in message
news:d0f401c439a1$4cc6ee80$a001280a@.phx.gbl...
> I want to remove SQLXML and SQL Mail from a production SQL
> 2000 server.
> How do I do it without reinstalling SQL?
The SqlXml stuff can be removed by running the installer and modifying your
installation. If you've installed SqlXml as a web release then you can just
run the SqlXml uninstaller.
Bryant

Wednesday, March 21, 2012

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkFirst, check the query plans. Is the query using the index? Your statistic
s
might not be up to date.
Second, is there something else on that machine using all the processor and
disk? If something else is hogging all of the resources, then your query is
just going to have to wait.
I hope this helps.
--
Russel Loski, MCSD.Net
"Mark Baldwin" wrote:

> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before comi
ng
> back with the data.
> Any help much appreciated...
> Mark
>
>|||Mark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before comi
ng
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minut
es
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query that
s
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/micr...7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minut
es
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query that
s
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blog

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkMark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
--
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
>> I am running SQL2000, occasionally queries take a huge amount of time to
>> process, for some reason they seem to pause for a couple of minutes. For
>> example, the following query normally executes in a couple of seconds...
>> SELECT count(*)
>> FROM object ob
>> JOIN quantity qt ON ob.objid=qt.objid
>> JOIN site st ON qt.siteid=st.siteid
>> JOIN cussite cs ON cs.siteid=st.siteid
>> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
>> but for some reason, today it is taking over 10 minutes!!
>> Running sp_lock2 reveals..
>> TABLE LOCKTYPE MODE
>> OBJECT TAB Sch-S
>> QUANTITY TAB Sch-S
>> SITE TAB Sch-S
>> CUSSITE TAB Sch-S
>> The query just sits there with those locks for over 10 minutes before
>> coming back with the data.
>> Any help much appreciated...
>> Mark
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/90a81b1ebd25891d/c7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blogsql

queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
Mark
First, check the query plans. Is the query using the index? Your statistics
might not be up to date.
Second, is there something else on that machine using all the processor and
disk? If something else is hogging all of the resources, then your query is
just going to have to wait.
I hope this helps.
Russel Loski, MCSD.Net
"Mark Baldwin" wrote:

> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
>
|||Mark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/90a81b1ebd25891d/c7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blog

Wednesday, March 7, 2012

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?
SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possibl
e
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Running SP with a different user

We are using SQL2000. User insert table in one database DB1 and trigger
insert the record into another database DB2. In this scenario, is it possible
have a trigger in DB1 to execute a stored procedure in DB2 with a different
user?SQL Server 2000 does not have the functionality to change user security
context. You might consider using cross-database chaining as I mentioned in
your other "q; user running trigger" thread.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A226A886-5748-456F-92F5-08C041CA54DB@.microsoft.com...
> We are using SQL2000. User insert table in one database DB1 and trigger
> insert the record into another database DB2. In this scenario, is it
> possible
> have a trigger in DB1 to execute a stored procedure in DB2 with a
> different
> user?

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?
You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?
|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

Saturday, February 25, 2012

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server => N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword => N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> >
> > Hi
> > I use the following SQL statements to create a link server.
> > RemoteServerName
> > is an SQL2000 and I am executing this in another machine which is SQL2005.
> >
> > Though the link server is created successfully, I am not able to se the
> > tables under it so could not query anything.
> >
> > --
> >
> > USE [master]
> > EXEC master.dbo.sp_addlinkedserver @.server => > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation compatible', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'data access', @.optvalue=N'true'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc',@.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc out', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'connect timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation name', @.optvalue=null
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'query timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'use remote collation', @.optvalue=N'true'
> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => > N'False',
> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
> >
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>> Jim
>> First of all , have you enabled remote connections on SQL Server 2005
>> server?
>> I did just testing on my machine and it works fine.
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
>> @.srvproduct=N'SQL Server'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> compatible', @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
>> access', @.optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc
>> out',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> name', @.optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use
>> remote
>> collation', @.optvalue=N'true'
>> GO
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
>> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
>> =>> N'blblbl'
>> GO
>>
>> --usage
>> select * from ServerName.dtabasename.dbo.tablename
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>> >
>> > Hi
>> > I use the following SQL statements to create a link server.
>> > RemoteServerName
>> > is an SQL2000 and I am executing this in another machine which is
>> > SQL2005.
>> >
>> > Though the link server is created successfully, I am not able to se the
>> > tables under it so could not query anything.
>> >
>> > --
>> >
>> > USE [master]
>> > EXEC master.dbo.sp_addlinkedserver @.server =>> > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation compatible', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'data access', @.optvalue=N'true'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc',@.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc out', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'connect timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation name', @.optvalue=null
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'query timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'use remote collation', @.optvalue=N'true'
>> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =>> > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =>> > N'False',
>> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>> >
>>

Monday, March 26, 2012

query

Friday, March 23, 2012

Query

Wednesday, March 21, 2012

queries hanging?

queries hanging?

queries hanging?

Wednesday, March 7, 2012

q; Running SP with a different user

q; Running SP with a different user

q; Running SP with a different user

q; Registration access denied

q; Registration access denied

q; Registration access denied

Saturday, February 25, 2012

q; Linked server from SQL2005 to SQL2000

q; Linked server from SQL2005 to SQL2000

q; Linked server from SQL2005 to SQL2000

db time conversion

Blog Archive

About Me