Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Declaring as variables

Status
Not open for further replies.

makumbi

Programmer
Mar 15, 2011
12
UG
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
 
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

 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top