Execute SQL Outputting To A File

Apr 25, 2007

I continue to try and find "easy" solutions to what should be a straightforward problem of outputting the results from a stored procedure to a file.



I tried using both XML task and file system task with the thought that one of those would actually be able to output a file from a variable, but both of those tasks threw fits when I tried using different variable types (file system required a string, but the XML result set never seemed to throw anything but an object) so I decided to just try a script task and do everything "manually".



So my latest gyrations have been thus:

1) Set execute sql task to output XML and push to a script task to write a file

2) Set execute sql task to output a full result set and push to a script task to write a file



Number 1 was the only one I could get working, because I kept getting this error with Number 2 that said the variable wasn't a recordset (maybe it was null?)



I can actually create files now via the script task, but it seems like the variable that should get the results from the stored procedure isn't getting anything. I tried using a MsgBox to see what was actually being passed to the script task, and all I got was the number 0 which I'm assuming is the default for the object type.



What's the best way to debug this? The package runs without errors, and I'm not familiar with debugging in SSIS. How can I tell if the stored procedure is returning results into the result set variable?

View 22 Replies


ADVERTISEMENT

Best Practices Question For Outputting

Sep 19, 2005

Hey guys,

Little bit of a newbie question here...I have a database with about 20
or so tables in a relational model.  I am now working on an output
scheme and had a quick question regarding best practices for
outputting.  Would it be best to

1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?

Thanks for the help!

e...

View 2 Replies View Related

Outputting Duplicate Strings

Aug 1, 2007

I have a problem when returning address information from a table that contains foreign/extended characters. The results combine different words as one result, e.g. 'Angouleme' and 'Angoulême' are returned as a single result. Is there a way to ensure that these two values are returned separately?

thanks

View 3 Replies View Related

Outputting Data From Stored Procedure

Jul 20, 2005

Hi AllIm generally a vb programmer and am used to referencing multiple recordsreturned from a query performed on an sql database and im trying to movesome functions of my software into sql stored procedures. So far ive beenable to move the functions relatively easily but im unsure about how tooutput multiple values from an sql stored procedure. By this i mean forexample one of the stored procedures may take your username and return thecontents of a single field in a record of one of the tables, but i wouldlike to be able to return for arguement sake the contents of a single fieldfrom two records if possible. Under VB im used to referencing the recordsetwith a (1) after it to reference the corresponding record from the query. Iwas wondering if there is a way to do something similar to this with storedprocedures if possible ?Thanks for any help

View 15 Replies View Related

Outputting Results - Removing Dash From Zip Codes

Sep 10, 2013

I'm trying to construct a query that takes results from a table (zip codes, essentially) and strips out the results so that it's just the first 4 digits (rather than the first 4 digits + '-.....'). Which function would allow me to do this in management studio?

View 3 Replies View Related

Outputting Selected Multivalued Parameter To Textbox

Jun 22, 2007

Hello everyone



I have a multivalued parameter called param_state



I am trying to output the values off what is selected to a textbox so the user can see what was selected



I have entered the following expression in a text box



= "Sales rep for " & Parameters!Param_State.Value



I get an error for this and if I enter it as



= "Sales rep for " & Parameters!Param_State.Value(0)



I get only the first state.



Is there a way to show what was selected ?



thanks

View 3 Replies View Related

Script Component. Outputting To Columns Without Knowing Their Name

Feb 22, 2008



Hi Folks,

Always sorry to have to ask what is most likely such a simple question. However I'm in no way a programmer, I'm just patching something together using scripts I've found all over the shop.

I'm reading in an excel source using a dataflow script component. I build up a SQL String in Vb.Net using the output column names and query the spreadsheet via microsoft.Jet.OLEDB.4.0 then processing it in code. I don't want to use the Excel Source task btw, more for the fact I want to learn from this as well as other less important reasons.

This all works fine. Adding new columns to the output means the query string dynamically changes without the script ever having those columns defined in code. Easy stuff so far.

The issue I have is writing back into the outputbuffer the results without explicitly mapping the result to the output column names.

An easy example to see is..

Excel Spreadsheet Looks like..

ServerName
Server1
Server2
Server3

Output Column Names..
ServerName

Code Snippet..


While Reader.Read

With OutputBuffer

.AddRow()

.ServerName = Reader.GetString(0)

End With

End While

Instead of defining what the column name (servername) is, I want to map back by matching the source column header to the output column name. By doing this I won't ever need to change any code when I add or remove output columns.

Could someone lend a hand with this, and I'll buy you a virtual beer or two. I've already spent more time searching for an answer than I have writing the code thus far, and I think my heads getting more and more muddled by it.

Many thanks,
Jode

View 1 Replies View Related

Outputting Status To Job Log From SSIS ActiveX Component

Apr 29, 2008



I have an ActiveX component in my SSIS package, and it is written in VB. Something is going wrong, and I'm not sure what. I works fine when run from Visual Studio, but when I move it to our server and try to run it from a job there, it fails. I'd like to know WHERE in the component it is failing, so I'd like to send output to something as it goes along, so I can see how far it is getting.

How do I send text to an output or log file?

I can't use MsgBox, because, of course, when it runs as a job, it is putting that message box up on the server, where there is no-one to respond to it, so it hangs. I'm in the process of converting it from a DTS to SSIS, and it does have several instances of MsgBox now. And it's locking up -- on one of them.

I have logging turned on for the job step, and it is writing to a dbo.sysdtslog90 table, but all it tells me is that it is starting that ActiveX script task.


What can I replace the MsgBox with, so that it outputs somewhere to a file? Is there a simple command, like WScript.Echo or Console.WriteLine (neither of which I can get to work)?

Thank you for any help you can provide.

View 7 Replies View Related

Looping Through Data And Outputting Text Files

Mar 20, 2008

I have this following code here...






Code Snippet

SET @SQL = 'Select * FROM IdentipassNew.dbo.CBORD_Interface_Final'
SET @BCPBody = 'bcp "' + @SQL + '" queryout "d:smartcardcbordudfcbordbody.txt" -T -fc:cpbody.fmt'

Problem is, there is over 85,000 records in that set and that is too big for the text file, so I was wondering if it would be possible to select like 30,000 records output those to a text file, then select the next 30,000 and create another file, then finally get the remaing records and put that in another text file. Can someone point me in the right direction as to how to accomplish this?


Thanks in advance.

View 3 Replies View Related

T-SQL (SS2K8) :: Outputting Sproc Result Set To A New Physical Table

Aug 3, 2015

I need to output a sproc into a new physical table, so the column definitions match the output.

Select Into DbName.NewTableName
Followed by an
Insert Into DbName.NewTableName
From (SprocNameHere),

View 9 Replies View Related

Outputting Large Rows (>1000 Chars) Via Osql Or Isql?

Jan 25, 2001

Iam attempting to generate files containing more than 1000 characters per line by outputting the results of a stored procedure via osql to a flat file. Osql (and isql) appear to force a newline after 1000 characters, even when specifiying a -w2000 parameter.

I have also tried to output the results of the stored procedure via DTS and this appears to do the same thing!

Does anybody know how to prevent osql (or isql) from forcing the newline?

View 1 Replies View Related

SQL 2012 :: Selecting And Outputting Data To Multiple Tabs In Excel Spreadsheet?

Jun 17, 2015

writing data extracted from sql server to mutliple tabs within an Excel Spreadsheet?

View 1 Replies View Related

VBS File To Execute SQL Job

Jan 13, 2006

I was wondering if anyone out there has any code to automate the execution of a SQL Job? The purpose of this is to run the vbs file locally (on local machine), connect to SQL server and run the SQL Job. I would appreciate this help. While I can run DTS packages, I am unable to "find" the SQL object to run the SQL Job. Again thanks.

View 2 Replies View Related

Using Rs.exe To Execute .rss File

Aug 27, 2007


Hi,


I am calling xp_cmdshell from inside a stored procedure to generate reports dynamically.


EXEC master.dbo.xp_cmdshell 'rs.exe -i "C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesScript SamplesSample1.rss" -s http://localhost/reportserver -v param1="10" -l 0 -e mgmt2000 -t -b'


In the .rss file, i use the following :

Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "s_no"
parameters(0).Value = param1


My question is how do I pass dynamic values in param1. I have a variable in my stored proc called @s_no which i try to pass like this, however it is not working.


EXEC master.dbo.xp_cmdshell 'rs.exe -i "C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesScript SamplesSample1.rss" -s http://localhost/reportserver -v param1=@s_no -l 0 -e mgmt2000 -t -b'


Can anyone suggest how to do this? Over here, I want to pass the value of param1 dynamically.

Thanks.

View 7 Replies View Related

Execute SQL From .sql File

Jan 22, 2008

Hi all,

I am trying to get an SSIS package to execute some sql in a .sql file or ideally all .sql files in a folder. Is there any way to do that? Thanks

View 10 Replies View Related

Execute Batch File

Mar 31, 2000

Hi,
I would like to ask someone who could help me with one small problem:

I would like to run SQL server task on one of my servers, that will connect
to all other servers (including ones that are not SQL servers, ie: Exchange,
Test Server etc) one at the time, and that will execute one batch file (update.bat) that is residing on each server under C: drive.

Batch file will copy some things from all of the servers to one server.

Thanks, in advance.

Regards,
Vilko

View 1 Replies View Related

How Can I Execute The .SQL File In SQL Server

Jun 13, 2002

hi
i have some couple of .sql files. each file contains some tables creation and some stored procedures creation, i don't want to go to Query Analyzer open each SQL file and Execute, i want to execute all .SQL files at a time, how can i do this one.


Thanks in Advance..


Madhavi

View 1 Replies View Related

How To Execute An Excel File Within DTS?

Oct 28, 2005

Hello All.

I need to execute an excel file (built with macro) whenever a date change is detected between 2 fields in my sql table.

if @Date1<>@Date2
begin
DECLARE @command varchar(1000)
SET @command='d:Refresh_ABC.bat'
exec xp_cmdshell @command
.
.
.

In my Refresh_ABC.bat, I have the following line
Start Excel.exe "D:ABC.xls"

When I tested the above script (from Declare... to @command) in query analyzer, nothing seems to happen. It shows The command(s) completed successfully. But my excel file was not refreshed at all. When I ran the batch file Refresh_ABC.bat on it's own, ABC.xls was refreshed so the batch file is working.

Next test, I placed the line Start Excel.exe "D:ABC.xls" on SET @command = ......... line. Same problem, nothing happened.

Any idea what went wrong. Please advise. Thank you.


Best regards
Teck Boon

View 1 Replies View Related

Execute Batch File As Sql Job

Apr 5, 2006

I'm trying schedule a batch file to run as a job from sql server agent. The batch file copies files from one server directory to a directory on another server. The batch file works properly when executed directly. The job is being executed under the sql service account login. I've given the service account access to both the source and destination directories.

When I try to run the job it fails with an "Access is denied" error on both the source and destination directories (as read from job history).

Any ideas?

View 4 Replies View Related

Open More Than 1 .sql File Then Execute It Once

May 17, 2008

I'm using SQL Server 2005
I'm made 4 stored procedure and save it as
sp_process1.sql, sp_process2.sql, sp_process3.sql and sp_process4.sql

i want to deploy all above SP to another machine. So far, i open it 1 by 1 and click execute in SQL Query windows 1 by 1.

How to open all .sql files but execute it once. Is it possible? Anybody know the way?

What the best way to deploy stored procedure into another machine?

View 2 Replies View Related

Execute A Batch File

Jul 25, 2007

Hi All,
I would like to embed some stored procedures in a batch file and execute it from the command prompt on windows.
I have no idea of how to embed a stored proc in a .bat file.
Can you please redirect me to the solution to this?

Thanks in advance
vishu

View 1 Replies View Related

Execute Script From A File

Mar 14, 2008

This script shows how to load a SQL script file into a nvarchar(max) local variable and then execute it.


-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
The script must not contain a GO Statement
Runs in SQL Server 2005
*/

set nocount on
if object_id('tempdb..#FileData','U') is not null
begin drop table #FileData end

create table #FileData ( FileData nvarchar(max) )

declare @FileName varchar(255)
declare @SQLLoad nvarchar(max)
declare @Script nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:MyDirMyScriptFile.sql'

-- Create command to load the file into temp table #FileData
set @SQLLoad =
N'insert into #FileData
select fd.*
from openrowset(bulk ''' + @FileName + ''',SINGLE_CLOB ) as FD'

-- Load file data into temp table #FileData
exec sp_executesql @SQLLoad

-- Load SQL into a local variable from temp table #FileData
select top 1 @Script = FileData from #FileData

-- Execute the script
exec sp_executesql @Script





CODO ERGO SUM

View 9 Replies View Related

Using Sql To Execute A Batch File

Aug 9, 2007

Is it possible to use sql to execute a batch file? I would like toexecute the following "C:BTWartend.exe /f=C:BTWToolboxFormatscarnum.btw /p", 6Thanks,Matt

View 6 Replies View Related

How To Execute Script (.sql) File?

Feb 12, 2008



Hi,

I have published a database on a client computer.

Now, I have generated a script file to make some changes to the database (like adding new columns etc)

How do I instruct my program to execute the script file ? The script file is a .sql file

Please provide the code in Visual Basic Language

Thanks

View 5 Replies View Related

Execute A Vb.net File When I Make Some Changes In A Table

Mar 7, 2007

I want to execute a vb.net 
file

When I make some changes in a table.

 

How can I handle this situation?

 I am using sql server
2005 express edition sujith 

 

View 1 Replies View Related

Execute A *.sql File From One Aspx Page

Mar 12, 2004

Hi,
like subject, is it possible?

bye

View 3 Replies View Related

How Can I Execute A N Exe File From Stored Procedure?

Aug 24, 2004

hi to all,

i have a stored procedure and i want to run a program from it..
i think that i need use api functions but how can i do that..
if there is a nother ways please tell me..

thanx..

View 3 Replies View Related

How Do I Create A SQL Server Job To Execute A .exe File?

Jun 10, 1999

I am having trouble getting a job step to execute an executable file. One of the options for a job step is to enter an operating system command. I have tried entering the following operating system command to execute a file:

'start c:myfile.exe'

For some reason this doesn't work. How can I get SQL Server to execute an executable file?

View 3 Replies View Related

Execute A Batch File From Within Sql Server

Jul 25, 2001

Hi, and thanks for your help. I have created a simple batch that xcopy files from one directory to another shared directory (in another server). Here is the code:
xcopy c:OUT_TRANSIT E:BACKUP_CESWEB /y

the E:BACKUP_CESWEB is in another server where I mapped the BACKP_CESWEB folder and make it shared.

the location of the batch file is in c:code .
When I double click the batch, the files are moved into E:BACKUP_CESWEB But when I use the window 2000 schedule or try to run the batch code from within sql server analyser, I get no results.

Any idea how to solve this.
The bottom line is that I want to copy files from one server to another server.Thanks for your help

Ali

View 5 Replies View Related

Create BAT File And Execute Same In Trigger?

May 9, 2013

I am situation, where we have a table named as Project, columns for the table as follows:

Code:
------------------------------------------
ID | ClientCode | ProjectName
------------------------------------------
1 | AAA | Dubai Airport Phase I
2 | AAA | Dubai Airport Phase II
3 | ARC | Salala
4 | MIZ | UMBC Building
------------------------------------------

Now my task was, whenever a project name and other details being created, then a Folder will be created in a server itself in the path E:ProjectFolder in following way:

E:ProjectFolderAAAAAA1
E:ProjectFolderAAAAAA2
E:ProjectFolderARCARC3
E:ProjectFolderMIZMIZ4

You can see here Folder and sub-folder is being created with that following project - client code & ID

I used following trigger to do the same:

Code:
CREATE TRIGGER [dbo].[CreateFolderName]
ON [dbo].[Project]
after INSERT
AS
SET NOCOUNT ON
BEGIN
declare @chkdirectory as nvarchar(4000), @folderName varchar(100), @mainfolderName varchar(100)
declare @folder_exists as int
SET @mainfolderName = (SELECT ClientCode AS Project FROM INSERTED)
SET @folderName = (SELECT (ClientCode + cast(ID as varchar(10))) AS Project FROM INSERTED)
set @chkdirectory = 'E:ProjectFolder' + @mainfolderName + '' + @folderName

[code].....

This worked like a charm, now my next task is using same trigger, I have to create a BAT file inside that SubFolder - T-SQL for creation of BAT File as follows:

Code:
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('E:ProjectFolder[red](select ClientCode from INSERTED)[/red][red](select ClientCode + cast(ID as varchar(10)) from INSERTED)[/red]xcopy_'+ (SELECT cast(ID as varchar(10)) FROM INSERTED) +'.bat','/','-')
SET @bcpCommand = 'bcp "[red]SELECT 'xcopy "E:ProjectFolder' + clientCode + '" "10.0.0.35ProjectFolder" /T /E /I' FROM INSERTED[/red]" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U SQLServerUsername -P SQLServerPassword -c'
EXEC master..xp_cmdshell @bcpCommand

Here I am not understanding how to insert the above T-SQL in the Trigger as well as the above T-SQL is not right, what's wrong in this?

Last query that will be included in the trigger is to execute the newly created bat file.

View 1 Replies View Related

How Do I Use Execute Process Task To Zip A File/fo

Mar 12, 2008

hi,

i have designed DTS packages, I have a script component that picks up the 'Path' of a file stored.
The Path is a column in database and that obtained from OLEDB source.

now i need to zip the files in the path given above and than store it in some location. How do I do that?

View 3 Replies View Related

How To Execute SQL Scripts Using Batch File?

Oct 10, 2007

Hi,How can we execute SQL Scripts using Batch file???i think Batch file should contain Username,Password,Database andScripts...Using that file scripts should run...How can i give UserName,Password,Database and all those things?Plz send me details how to do that...if possible with example...Thanx in advance,RR...

View 2 Replies View Related

Is It Possible To Execute A Container Regardless Of The Checkpoint File?

Feb 23, 2006

I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?

Here's the scenario:

I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints.
The package is setup to use checkpoints so that if a task fails the package will restart from that failed task
TaskA is insignificant here.
TaskB fetches some data and puts it in a raw file
TaskC inserts that raw file data into a table.

Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.

So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.

How do I configure the package in order to execute TaskB again? Is it even possible?

Regards

-Jamie

View 17 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved