is this possible that i create a stored procedure that generates content of defined table to a path that i passed as its(stored procedure) parameter?
4
-
1You could just query the table in Management Studio, right click the results & choose Save As to save to a csv file.OMG Ponies– OMG Ponies2009-12-19 06:09:29 +00:00Commented Dec 19, 2009 at 6:09
-
he needs stored proc to do it from code etc.amantur– amantur2009-12-19 06:18:14 +00:00Commented Dec 19, 2009 at 6:18
-
1By content, do you me all of the rows or the table structure? Both are easily doable using dynamic SQL...Sparky– Sparky2009-12-19 06:18:39 +00:00Commented Dec 19, 2009 at 6:18
-
i want run t from stored procedure!Meysam Javadi– Meysam Javadi2009-12-19 06:20:52 +00:00Commented Dec 19, 2009 at 6:20
Add a comment
|
3 Answers
Try the code below
create procedure dbo.ShowAllRows (@tabName VARCHAR(200) )
as
begin
declare @Sql NVARCHAR(2000)
set @Sql = 'select * FROM '+@tabName
EXEC (@sql)
end
go
exec ShowAllRows 'sys.configurations'
I missed the path part, I assume you want the above type of code, with a second parameter, i.e. @outputFileName
If your SQL-server has access to the file path and you can run XP_CMDShell, you can do the following...
create procedure dbo.ShowAllRows (@tabName VARCHAR(200),@outPath VARCHAR(200) )
as
begin
declare @Sql NVARCHAR(2000)
set @sql = 'bcp '+@tabName+' out '+@outPath+' -U<user> -P<password> '
print @sql
EXEC xp_cmdShell @sql
end
You can also use the -T for trusted connection if you don't want the user name and password in the procedure
2 Comments
Meysam Javadi
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell'.....
Meysam Javadi
also. i will run this on a shared host!
If you mean the table structure, here is the SQL to get you started...
select column_name,data_type,is_nullable
from information_schema.columns
where table_name = '<table name>'
order by ordinal_position
In a procedure, simply do
create produce ShowColumnsforTable( @tabName VARCHAR(200) )
as
begin
select column_name,data_type,is_nullable
from information_schema.columns
where table_name = @tabName
order by ordinal_position
end
Comments
try this:
create proc spOutputData @tbl varchar(500)
as
declare @cmd varchar(4000)
SELECT @cmd = 'bcp ' + @tbl + ' out c:\OutFile.txt -c -q -Shpas -T'
exec xp_cmdshell @cmd
TEST:
spOutputData 'master.dbo.syslogins'
