Declaring as variables

[makumbi]

Hi members is there away i can code this code into various variables that are dynamic in nature example
declaring the server, declaring the user name so that in case the computer name is changed, the system automatically reads the current computer and user name

2.i also get a problem when trying to save this stored procedure ie it produces error 7405 iam using sqlserver 2000

insert into testtbl(phone,address,city)
SELECT phone,address,city
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=kabojja6;UID=makumbi;PWD=',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname

 

[TomKane]

Hi,

In answer to your first question there are functions such as @@ServerName will return the server and suser_name() will return the user name. I'm using SQL 2005 - maybe just double check those are available to you.

In answer to your second question, try putting the following at the start of your stored procedure and see if it makes any differnce.

GO
SET ANSI_WARNINGS OFF
GO
SET ANSI_NULLS ON
GO

Regards,
Tom

 

[makumbi]

I have tried using what you had suggested in (2) but i still get the same problem when i try to save the storedprodure

on the question of declaring valuables, how can i insert those valuables after declaration because i have found out thouse functions of @@servername and username

GO
SET ANSI_WARNINGS OFF
GO
SET ANSI_NULLS ON
GO

insert into testtbl(phone,address,city)
SELECT phone,address,city
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=kabojja6;UID=makumbi;PWD=',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname

 

[TomKane]

OK, I'll do a bit more investigating for that. For the variables thing - if I have understood your question properly this is how you can store the values for use in queries etc (apologies if I have misunderstood).

DECLARE @Server VARCHAR(30)
DECLARE @User VARCHAR(30)

SELECT @Server = @@SERVERNAME
SELECT @User = suser_name()

SELECT @Server, @User