MS SQL 2005 Questions

[Ben E Lou]

1. How do I create a script for someone that requires them to enter a parameter (a date)?

2. Is it possible to create a SELECT INTO query that pulls from a db on one server and writes to a db on another? If so, how?

[cartman]

1. How do I create a script for someone that requires them to enter a parameter (a date)?

2. Is it possible to create a SELECT INTO query that pulls from a db on one server and writes to a db on another? If so, how?

For #1, you create a stored procedure that uses a variable. Here's an example:


create procedure sp_example
@input datetime /* or whatever datatype the input is going to be */

as

select blah from table where variabletable = @input

go


If you need multiple inputs, separate them with a comma. The @ symbol is what declares the variable. So to run it, you capture the date, and then pass to SQL Server 'sp_example captureddate' and it will use the date in the query.

For #2, it is possible, but a bit more complicated. You have to use a fully qualified name for the tables, and the account you are using has to have permissions on both database, and the second server has to be set up as a linked server on the first server. The format of a fully qualified name is [server].[database].[schema].[table], ie. [server1].[BensTable].[dbo].[draftclass]. So instead of 'select * from draftclass', you'd use 'select * from [server1].[BensTable].[dbo].[draftclass]. The brackets are required.

Ping me if you need more in depth info.

[lordscarlet]

1. You want to create a "Stored Procedure" if I am understanding the question correctly. Presumably you want it to return a set of rows? If just a value, you may want a function. If I'm completely misunderstanding, it may be something else. SQL Server Stored Procedures (http://databases.about.com/od/sqlserver/a/storedprocedure.htm)

2. Between servers? Hm. I've never done it, but this seems to indicate you can: SQL Server 2005 : Access Tables / Entities across Servers. « Script(s) (http://gchandra.wordpress.com/2008/02/18/sql-server-2005-access-tables-entities-across-servers/)