Query

Hi!

I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3

table 1

id	Col1	Col2	maxValues
1	aa	bb	10
2	cc	dd	10
3	ee	ff	10
4	gg	hh	10
5	jj	kk	10

table 2

id	Col1	Col2	CurrentValue
1	cc	dd	7
2	gg	hh	3
3	jj	kk	5

table 3

id	Col1	Col2	AvailableEntries
1	aa	bb	10
2	cc	dd	7
3	ee	ff	10
4	gg	hh	3
5	jj	kk	5

Thanks for your help.

If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:
select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you

Query

Hi
I need this statement to print to the same log.txt when running xp_cmdshell
PRINT 'Upgrading.... ' + @.DataBaseName
How do I fit it into this command?
set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Thanks
JacoHi
Use
PRINT 'Upgrading.... ' + DB_NAME()
instead.
John
"Jaco" wrote:

> Hi
> I need this statement to print to the same log.txt when running xp_cmdshel
l
> PRINT 'Upgrading.... ' + @.DataBaseName
> How do I fit it into this command?
> set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
> c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
> exec master..xp_cmdshell @.command
> Thanks
> Jaco
>|||On Tue, 20 Sep 2005 03:16:05 -0700, Jaco wrote:

>Hi
>I need this statement to print to the same log.txt when running xp_cmdshell
>PRINT 'Upgrading.... ' + @.DataBaseName
>How do I fit it into this command?
>set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
>c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
>exec master..xp_cmdshell @.command
>Thanks
>Jaco
Hi Jaco,
If you can change the contents of c:\temp\Complete(7-9).sql, then add
this command at the beginning:
PRINT 'Upgrading.... ' + DB_NAME()
If you can't change c:\temp\Complete(7-9).sql, then use this:
set @.command = 'echo Upgrading.... ' + @.DataBaseName
+ ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

query

hi
i have some models that end with +SP e.g. 1234+SP, abcd+SP I want to remove
only the +SP from these models. May i know how can i do it. Thank you
On Fri, 2 Dec 2005 01:38:03 -0800, Tiffany wrote:

>hi
>i have some models that end with +SP e.g. 1234+SP, abcd+SP I want to remove
>only the +SP from these models. May i know how can i do it. Thank you
Hi Tiffany,
If you mean that the +SP is part of the data in your table and
you want to show the data without the +SP, then use
SELECT CASE
WHEN YourColumn LIKE '%+SP'
THEN LEFT(YourColumn, LEN(YourColumn - 3))
ELSE YourColumn
END
FROM ...
If that's not what you want, then please check out www.aspfaq.com/5006,
an excellent description of what information you need to provide if you
want to help us help you.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

query

Hi
I have a column in an SQL table. the column is all numeric
data from 100 to more than 400. I would like to convert
the data in this column to something else. e.g.
micro = <200
mini = between 200 and 300
midi = between 300 and 400
full > 400
can anyone advise me how to write a query to establish the
above task.
Thank you.
Freger
On Thu, 13 May 2004 02:18:23 -0700, Freger wrote:

>Hi
>I have a column in an SQL table. the column is all numeric
>data from 100 to more than 400. I would like to convert
>the data in this column to something else. e.g.
>micro = <200
>mini = between 200 and 300
>midi = between 300 and 400
>full > 400
>can anyone advise me how to write a query to establish the
>above task.
>Thank you.
>Freger
Hi Freger,
This can be accomplished using CASE:
SELECT CASE
WHEN (NumericData <= 200)
THEN 'Micro'
WHEN (NumericData <= 300)
THEN Mini'
WHEN (NumericData <= 400)
THEN 'Midi'
ELSE 'Full'
END AS ColumnAlias
FROM YourTable
Replace column and table names as appropriate.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you, Hugo.[vbcol=seagreen]
>--Original Message--
>On Thu, 13 May 2004 02:18:23 -0700, Freger wrote:
numeric[vbcol=seagreen]
the
>Hi Freger,
>This can be accomplished using CASE:
> SELECT CASE
> WHEN (NumericData <= 200)
> THEN 'Micro'
> WHEN (NumericData <= 300)
> THEN Mini'
> WHEN (NumericData <= 400)
> THEN 'Midi'
> ELSE 'Full'
> END AS ColumnAlias
> FROM YourTable
>Replace column and table names as appropriate.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this ?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this ?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>

Query

Hi

I am trying to execute the below query in Query analyzer, the same query is executed in Sybase with the corresponding columns.

select (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End as onlineaccessids, a.id as AcctInfoId, BankNo, RegionNo, OfficeNo, AcctNo,AcctShortNm ,SecLending, q.id as UsersId
from AcctInfo AS a INNER JOIN "104030" AS p ON a.AdminOfficer = p.CrmCode
inner join Users as q ON p.CrmDesc = q.OnlineaccessId union select
OnlineAccessIds = (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End,q.id, a.Id, BankNo, RegionNo,
OfficeNo, AcctNo,AcctShortNm, SecLending
from AcctInfo as a
inner join users as q on
(q.onlineaccessid = substring(onlineaccessids, 1, 3))

The error message is

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'onlineaccessids'..

It is able to recognize the field "onlineaccessids" and giving me the results in Sybase database and giving the error in SQL Server.
Try to help me out.

Thanx in advanceFirst, double check that both the AcctInfo and users tables have an attribute "onlineaccessids".

Secondly, add an alias to:

(q.onlineaccessid = substring(a/q.onlineaccessids, 1, 3))

query

hi
i have a doubt in sqlserver.
how to delete the duplicate rows with a query in sqlserver
thank youOne choice is to use Cursor.
But remember that Cursors are very expensive to SQL Server.

Else,

You may try to use the UNION (not UNION ALL), with the same table. Put all the returned data into a temporary Table, then you delete your data from original table and insert the values from the temporary table into it.|||First of all, what is structure of your table?
If your table does not have a primary key - too bad... Use Diogo's advice and then check a normalization rules.
If it does - you can try next method:

drop table test
go
create table test (id int primary key,
code varchar(10))
go
insert test values(1,'code1')
insert test values(2,'code2')
insert test values(3,'code1')
insert test values(4,'code3')
insert test values(5,'code4')
insert test values(6,'code5')
insert test values(7,'code6')
insert test values(8,'code3')

select *
--delete
from test
where id in
(select min(Id) from test group by code having count(*)>1)

May be it needs to run last query couple times....

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this ?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this ?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Dariusz
Yes , if you use LIKE '%test%' so SQL Server probably will choose
TABLE/INDEX SCAN to perfom the query , however using LIKE 'test'% will
INDXEX/CLUSTERED INDEX SEEK. Well , obviously it depends on many things and
we don't know how do you run the queries?
I can't say that you are going to gain some benefits (in terms of
perfomance) of using FTI due to not using this feature for long time , so
maybe someone else can provide more info.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23uECaDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||In most cases it will. If you have a restriction the restriction will be
applied after the results set comes back from the query of the full text
catalog. If you are returning a large number of rows this will be
expensive.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23uECaDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Please don't multipost , I have just answered the question in .programming
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||ok, sorry
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euvWZNZfGHA.1856@.TK2MSFTNGP03.phx.gbl...
> Please don't multipost , I have just answered the question in .programming
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>

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...
>
>
|||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...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:

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'collatio
n
> 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'collatio
n
> 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 remo
te
> 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...
>
>|||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...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>