Front Office Football Central - MS SQL 2005 Questions

Front Office Football Central (https://forums.operationsports.com/fofc//index.php)

- FOFC Archive (https://forums.operationsports.com/fofc//forumdisplay.php?f=27)

- - MS SQL 2005 Questions (https://forums.operationsports.com/fofc//showthread.php?t=72804)

MS SQL 2005 Questions

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?

Quote:

Originally Posted by Ben E Lou (Post 2041503)

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

Code:

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.

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

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)