Command line parameter passing

Command line parameter passing
Does anyone know if it is possible to pass a parameter to a script run
from the isql (Query Analyzer).
I have a large .SQL file that does a setup of a database and I would
like to pass it a parameter that is
A name of another database to create some views to.
If its not possible from the command line, Is it possible to set an
environment variable and check that
variable inside the .sql script.
Any help would be appreciated.You can use the OSQL command-line utility and pass a parameter as part of a
the query / command string (-q switch).
Lookup the OSQL syntax in Books Online.
David Portas
SQL Server MVP
--|||Thanks for the response.
I have been trying something like this.
isql -H%GSHost% -S%GSServer% -d%GSDB% -U%GSUser% -P%GSPassWord% -n -q
"declare @.Odb varchar(40);set @.Odb=' test ';print @.Odb" -i GsViews.sql
in the GSViews.sql I do
print @.Odb
So when I do this the first print (from the -q) works but the one in the
GSViews.sql (run by the -i) errors out stating that @.Odb does not exist.
No GSViews.sql is quite long and it needs to know what other database
name. Any idea?
David Portas wrote:

> You can use the OSQL command-line utility and pass a parameter as part of
a
> the query / command string (-q switch).
> Lookup the OSQL syntax in Books Online.
>

Command Line Arguments in Debug mode

Hi,

I am passing some /SET commands on the command line when I execute packages I created in SSIS and have run from the SQL Server Job manager. When I put these same /SET commands on the command line arguments section of the project properties page I would expect they would be passed into the executed package when it is run thru BIDS. But the variables still have their defined values when the packages executes.

Is there a trick to getting command line arguments to work in the debugger for SSIS/BIDS?

DaveM

No, I do not think the application (DTSDebugHost) that runs packages in the debug mode knows how to parse those arguments like DTExec does.

You can simply set those variables inside their window.

Thanks.

|||

Setting the variables inside their window doesn't allow for dynamic selection of values based on runtime values.... which is what I am doing, and trying to test/debug. It works fine except when inside Bids/Visual Studio 2005.

Is this something that will be fixed in SP2 or SP3?

Thanks

|||

DaveM wrote:

Setting the variables inside their window doesn't allow for dynamic selection of values based on runtime values.... which is what I am doing, and trying to test/debug. It works fine except when inside Bids/Visual Studio 2005.

Is this something that will be fixed in SP2 or SP3?

Thanks

SP2 is out and the behavior isn't changed there. I tried it. Seems to me like it should work. The documentation implies that it works the same as dtexec.

From BOL:
CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility.

comma separated list into stored procedure and order of records

I'm passing a comma separated list into a stored procedure
e.g. exec usp_returnProductsFromIdArray '5,4,1,99'
The comma list is being turned into a table (see usp_IntListToTable below)
Here's the proc that returns products that are in the list
CREATE procedure usp_returnProductsFromIdArray
@.prodIdList varchar(1000) = null
as
CREATE TABLE #prodIdTable (productId BIGINT)
if (@.prodIdList is not null)
EXEC usp_IntListToTable @.prodIdList,'#prodIdTable'
select productId, productTitle from products where productId in
(select productId from #prodIdTable)
drop table #prodIdTable
This works well, except I'd like the order of records returned to follow the
order of the IDs in the comma separated list, they aren't, they are in
ascending order (due to the key on the products table)
/*
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
*/
CREATE PROCEDURE dbo.usp_IntListToTable
@.cslist VARCHAR(8000),
@.tablename SYSNAME AS
BEGIN
DECLARE @.spot SMALLINT, @.str VARCHAR(8000), @.sql VARCHAR(8000)
WHILE @.cslist <> ''
BEGIN
SET @.spot = CHARINDEX(',', @.cslist)
IF @.spot>0
BEGIN
SET @.str = CAST(LEFT(@.cslist, @.spot-1) AS INT)
SET @.cslist = RIGHT(@.cslist, LEN(@.cslist)-@.spot)
END
ELSE
BEGIN
SET @.str = CAST(@.cslist AS INT)
SET @.cslist = ''
END
SET @.sql = 'INSERT INTO '+@.tablename+'
VALUES('+CONVERT(VARCHAR(100),@.str)+')'
EXEC(@.sql)
END
END
GOAdd a new column to the temp table to define the sequence. Increment an
integer value in the WHILE loop and insert that into the sequence
number column. Then do:
SELECT P.productid, P.producttitle
FROM products AS P
JOIN #prodIdTable AS T
ON P.productid = T.productid
ORDER BY T.sequence_no ;
David Portas
SQL Server MVP
--|||It works
Thanks very much!!!
www.xwords.co.uk
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129194249.222211.192330@.o13g2000cwo.googlegroups.com...
> Add a new column to the temp table to define the sequence. Increment an
> integer value in the WHILE loop and insert that into the sequence
> number column. Then do:
> SELECT P.productid, P.producttitle
> FROM products AS P
> JOIN #prodIdTable AS T
> ON P.productid = T.productid
> ORDER BY T.sequence_no ;
> --
> David Portas
> SQL Server MVP
> --
>