Any way to pass command line variables to osql

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Is it possible to do this.if ur parameter is positioned 2nd then add this code in your command file.

<varname> is some variable name that you want to use to trap the parameter. lets say empid for instance

SET empid=%2

after this, the place where u wud be calling ur stored procedure use this syntax.

osql -S servername -l 60 -n -E -d dbname
-Q"EXEC sp123 @.Var1 = '%empid%'"

Any T-SQL command can show the description of specified table columns?

I want to ask something because i need.

Any SQL/T-SQL command inside MsSQL Server 2000 can show the description of {all table columns or specified table columns} of specified table inside specified database?

can you teach me how to do and any example(s)?Try checking out the INFORMATION_SCHEMA.COLUMNS view:

SELECT
 *
FROM
 INFORMATION_SCHEMA.COLUMNS
WHERE
 TABLE_CATALOG = 'myDatabase' AND
 TABLE_NAME = 'myTable'

Terri|||You can also use the following:

exec sp_columns 'table_name'

This will return the list of columns for the table you specified.

Any SQL wizard can help? Reformat the input file and transfer into SQL server

I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'path\filename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.

Thank you for the help in advance!

Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ..... 1/1/05 ... 1/1/05 .... 1/1/05 ....

This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 .....
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 .....
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 .....
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ....."I am trying to transfer 200 txt files into SQL server by using query analyzer."
--DTS might be more appropriate.

"I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily."
--Faith is a powerful thing.

"Here is the raw data layout, which is comma delimited."
--What you posted is not comma delimited.

"This is the desired output after load into the table, which is tacking each repeating block on top of each other."
--You are going to need to load this data into a staging table and normalize it before loading into your production tables. The process will be complex, involving several passes through the data.

If at all possible, try to get your source data in a better format. Practically any other format would be preferable to what you posted.|||Blindman,
Thank you for your reply.
You are right... I forgot to put "," in my sample file layout.
I am using another source provider to request time series in excel. This is the most efficient way I can utilize excel ability (256 columns and over 65,000 rows). That's why the raw data layout looks wired. However, I have to stick to it.

I was thinking to load these files into a table to normalize but I am not sure if I know SQL well enough to say this is the best solution. I think I got the answer from you.

What is staging db. I assume it is one of defualt DB in in enterprise manager, however, I did not see it. Or this is the name you gave?

Thank you again for the help.
Shiparsons|||Not "Staging DB". "Staging TABLE."

A staging table is basically an table that has the same structure as your input data, with additional columns added as needed to keep track of records as they are being processed. I always add an "Imported" column that defaults to getdate(), and an ImportErrors column that I populate as necessary during processing.

Your staging table should have no Primary Keys or constraints (unless you add a surrogate PKey for processing...), so that your import process never fails because the data does not match what is expected.

Once the data is in the staging table you cleans it and make sure it satisfies all the business rules required by your production tables. Then you make as many passes through the staging table as necessary to update the various production tables it feeds, starting with the top-level tables.|||Thank you for the explanation.
What datatype I should use when I create my staging table? I assume this is nonconstraints type since my raw data contains text, datetime, and float.

Thank you,
Qing|||You should try to match the datatype to the type of the data being entered, though some people just make all staging table columns varchar by default. I don't do this, as a rule, but you may have no other choice since your import file is actually a mix of different layouts. String fields are the only column types that will accept any input type.|||Blindman,
Thank you for the help.

I will try.

shiparsons

Any idea why the update won't run?

I have this store proc that ends at the update command. No matter where I
move it to, the whole thing seems to quit there.
CREATE PROCEDURE sp_LPModUser
@.ModFName NVarChar(100),
@.ModLName NVarChar(100),
@.ModLogon NVarChar(100),
@.ModPassword NVarChar(100),
@.ModLPUserID Int,
@.ModPageAccess Int,
@.ModChangePW Int,
@.Database NVarChar(100)
AS
Declare @.OldLogon nvarchar(100)
set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
Declare @.Check integer
Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
@.ModLogon)
if @.Check = 0
Begin
exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
exec sp_grantdbaccess @.ModLogon
exec sp_addrolemember 'db_datareader', @.ModLogon
exec sp_addrolemember 'db_datawriter', @.ModLogon
End
If @.OldLogon != @.ModLogon
Begin
exec sp_revokedbaccess @.OldLogon
exec sp_droplogin @.OldLogon
End
Begin
exec sp_password NULL, @.ModPassword, @.ModLogon
update lpusers set fname = @.ModFName, lname = @.ModLName, login = @.Modlogon,
[password] = @.ModPassword,
PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
@.ModLPUserID
print 'test'
endNevermind, I got it.
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:uXUEsGg6FHA.564@.TK2MSFTNGP10.phx.gbl...
>I have this store proc that ends at the update command. No matter where I
>move it to, the whole thing seems to quit there.
> CREATE PROCEDURE sp_LPModUser
> @.ModFName NVarChar(100),
> @.ModLName NVarChar(100),
> @.ModLogon NVarChar(100),
> @.ModPassword NVarChar(100),
> @.ModLPUserID Int,
> @.ModPageAccess Int,
> @.ModChangePW Int,
> @.Database NVarChar(100)
> AS
> Declare @.OldLogon nvarchar(100)
> set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
> Declare @.Check integer
> Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
> @.ModLogon)
> if @.Check = 0
> Begin
> exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
> exec sp_grantdbaccess @.ModLogon
> exec sp_addrolemember 'db_datareader', @.ModLogon
> exec sp_addrolemember 'db_datawriter', @.ModLogon
> End
> If @.OldLogon != @.ModLogon
> Begin
> exec sp_revokedbaccess @.OldLogon
> exec sp_droplogin @.OldLogon
> End
> Begin
> exec sp_password NULL, @.ModPassword, @.ModLogon
> update lpusers set fname = @.ModFName, lname = @.ModLName, login =
> @.Modlogon, [password] = @.ModPassword,
> PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
> @.ModLPUserID
> print 'test'
> end
>

Any command list the tables in the Databsae

Please help. In my Enterprise Manager, there is some local database, some
other (is in another network)
IN SQL Analyzer , what command I can list out the tablelist for each
database'
ThanksSee:
http://groups-beta.google.com/group...1d?dmode=source
Anith|||check for stored procedure sp_tables in BOL
or u can make use of sysobjects table available in Master Database
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||You can use an undocumented sp (not recommended in production dbs) or declar
e
a cursor to traverse databases and execute stored procedure sp_tables in eac
h
db context.
use northwind
go
exec sp_msforeachdb 'select ''?''; use [?]; exec sp_tables @.table_type =
'''table''
go
declare @.sql nvarchar(4000)
declare @.db sysname
declare dbs_cursor cursor local fast_forward
for
select
catalog_name
from
information_schema.schemata
open dbs_cursor
while 1 = 1
begin
fetch next from dbs_cursor into @.db
if @.@.error != 0 or @.@.fetch_status != 0 break
select @.db
set @.sql = N'use ' + quotename(@.db) + N' exec sp_tables @.table_type =
'''table''
print @.sql
exec sp_executesql @.sql
end
close dbs_cursor
deallocate dbs_cursor
go
AMB
"Agnes" wrote:

> Please help. In my Enterprise Manager, there is some local database, some
> other (is in another network)
> IN SQL Analyzer , what command I can list out the tablelist for each
> database'
> Thanks
>
>