I have a stored procedure that processes an individual file from a
directory and archives it in a subdirectory.Now, the problem is, when i
execute it , it will only process one file. What i want to do is to check
to see if there are any files in the folder, and if there are , process
them all, and once done, go to the next part in a DTS package, if there are
no files, simply go to the next part in the DTS package. I tried an activex
script that would get the filecount in the folder, and if there were more
than 0 files in the folder, then DTS-sUCCESS and on "success" workflow , it
would run the stored procedure, and thus it woould process one file, then
"on completion" the workflow connected it back to the activeX script(thus
looping), which would count the files again. Now if there were 0 files, it
would report DTS_FAILIURE, and I had it set up ,"on failiure" to go to the
next step in the package, but it wouldn't run.
I have a stored procedure which adds a color + colortype to a table. I then need to select from a separate table all tools that support the colortype. Finally I need to insert into a separate table the tools + colortype. I would like to do this all in one stored procedure.My rough code is:INSERT INTO ColorsTable (color, colortype) VALUES (@color, @colortype);SELECT tools FROM ToolsTable WHERE colortype = @colortype[For Each tool] INSERT INTO MyTable tool, @colortypeMy problem is that I don't understand how to do a For Each Thank-you for any advice.
Hi, I need to code a stored procedure. It will select all the rows from a table. I then need some sort of looping mechanism to filter through all the rows. It is too difficult to handle the filtering with a simple 'WHERE' clause. I will have to do some data manipulation on fields within the rows to determine which ones I could use. I need for the SP to return all the rows which qualify. Could someone provide me with a link and/or some skeleton code to give me somewhere to start on how to code the SP?
select distinct userid from allrecords where stage=1 and datediff(day,0,mydatestage1)=datediff(day,0,getdate()-1)
then i need to take this and loop through the results and do queries based on the results.
like
select @username=username from users where userid=@userid select @totalrecords=count(id) from allrecords where stage=1 and datediff(day,0,mydatestage1)=datediff(day,0,getdate()-1)
and more
how can i do this looping through the users and then running sub queries?
I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure. Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.The XML File is layed out as:<Values> <value> <value>1</value> <key>My_Field</key> </value> <value> <value>3523.2</value> <key>My_other_Field</key> </value></Values>I basically need to go through it row by row, find out what table I need to insert the value into using the key field.Any help with this would rock. I'm using SQL 2005.
I have a table [myOrders] with three columns. One of the columnscontains text output data [myText] nvarchar(500), one of them containsa filename [myFileName] nvarchar(50), one of the columns is a bit torecord if it has been output yet[isOutput] bit default value = 0.I am creating a SQL Agent job that needs to look at a recordset of[myOrders] where [isOutput] = 0 and create a seperate text file foreach row using [myFileName] as the filename.Then I need to mark [isOutput] of each record in [myOrders] as 1.Ok, so that's the task...What I'm thinking is I construct a stored procedure that starts with aselect statement:Create PROCEDURE JustDoItASset nocount onSELECTmyText, myFileNameFROMmyOrdersWHERE(isOutput = 0)THEN I USE BCP to create the file looping through the recordset above.THIS IS THE PART I AM CLUELESS ABOUT!/* NEED TO LOOP HERE */DECLARE @ReturnCode intDECLARE @ExportCommand varchar(255)DECLARE @FileName nvarchar(50)DECLARE @FileText nvarchar (500)SELECT @FileName = myFileName/*THIS SYNTAX IS PROBABLY TOTALLY OUTA WHACK:)SET @ExportCommand ='BCP @FileText queryout "c:' +@FileName +'" -T -c -S ' + @@SERVERNAMEEXEC @ReturnCode = master..xp_cmdshell @ExportCommand/* NEED TO EXIT LOOP HERE */Then I update all records in [myOrders] to 1BEGIN TRANSACTIONUPDATEmyOrdersSET isOutput = 1WHERE(isOutput = 0)/* err checking here */COMMIT TRANSACTIONI'm hoping someone can help me construct this.Thanks,lq
I have a stored procedure that I want to basically pull in a code/codes which correspond to people. I then convert the code to the persons actual name that I then want to send back and add to a listbox. Right now with the code I have, it returns just one name. I know I am doing something wrong, but I am not sure if what I want is possible with a stored procedure or if I am on the right track. my code is as follows.
The proceedure I am running is as follows...
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int) AS BEGIN
DECLARE @First as varchar(200) DECLARE @Middle as varchar(50) DECLARE @Last as varchar(200) DECLARE @Code as varchar(200)
Select @Code = [SectyCode] From tblMain2 as M LEFT JOIN tblSectyData as S ON M.Code = S.Code WHERE M.Code = @id
SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = @Code SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = @Code SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = @Code
I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.
I have table "dbo.instances" with list of instances. I have databases from "sys.databases".
How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
I created a dts package and I can execute it. I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell. I have sql integration services and mssql 2005 services running under a domain account. I have saved the package as a FILE System stored package. I just can't find a reason why it won't execute from stored procedure.....
I have a DTS package written to import data from a txt file into a SQL Server 7.0 table but before importing the data i need to delete the existing data from the table... for this i need to use the stored procedure to delete data first... how do i run the dts package from the stored procedure..does anyone know the command for this...
I am running a DTS Package from a stored procedure using xpcmdshell. The DTS Package begins with a SQL Task to delete records from 2 tables (this works fine), but the data transfer task for importing records from a SQL Anywhere 5.0 database gives me the error 'Unable to connect to database server: Unable to start database engine'. the weird thing is that from Enterprise Manager I can execute the DTS Package and it works fine. What am I missing here?????
Hello friends! I have one query regarding execution of SSIS package through Stored Procedure.
I have created SSIS package which extract data from database and put that into various text files.Here I am using two global variables one is for Department ID and another is path where I wanna to place my text files as per departments.When I ran it through command prompt it works fine but now I want that dtsx package to run from stored procedure with same input parameters
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2007-02-22 11:31:37.32 Code: 0xC0011002 Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8} Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loadin g a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
And also I am not understand where i should pass my two input parameters which I used in SSIS package variables???????
I'm trying to create a stored procedure which will run 2 SSIS packages before it runs some other SQL code. I read [url=http://msdn2.microsoft.com/en-us/library/ms162810.aspx]this[/url] article. I'm trying to use the package from the file system.
Here is the my code:
CREATE PROCEDURE usp_participant_limits_report AS dtexec /f "C:....Activity_Limits.dtsx" GO
The error message says it doesn't like the "/". Anyone?
The DTS package runs fine through Enterprise manager successfully.However, when scheduled through a job that runs the dts through thefollowing code:DECLARE @findfile intExec @findfile = master.dbo.xp_cmdShell 'dir\ServerNamefolderfilename.xls', no_outputIF (@findfile=0)BEGINExec master.dbo.xp_cmdshell 'dtsrun -E -ServerInstance-N"DataImport"'ENDThe servername specified in the above statement in a different serverthan the server that the package resides on.This is the error that I get when I try to run the same code usingquery analyzer:DTSRun: Loading...DTSRun: Executing...DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: DTSStep_DTSDataPumpTask_1DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147024893(80070003)Error string: The system cannot find the path specified.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 1100Error Detail Records:Error: -2147024893 (80070003); Provider Error: 0 (0)Error string: The system cannot find the path specified.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 1100Error: -2147024893 (80070003); Provider Error: 0 (0)Error string: Cannot open a log file of specified name. The systemcannot find the path specified.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 4700DTSRun OnFinish: DTSStep_DTSDataPumpTask_1Error: -2147220440 (80040428); Provider Error: 0 (0)Error string: Package failed because Step'DTSStep_DTSDataPumpTask_1' failed.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlpHelp context: 700NULLThe job is owned by the SQLService account(Windows account) that hasSystem Admin rights and also is part of the Domain Admin User group.The Domain Admin User group has full rights on the file that the DTS istrying to access.Any help in trying to figure out why the schedule job cannot find thefile path would be appreciated.ThanksKR
Hello,I'm currently working on debugging a very large DTS package that wascreated by someone else for the purpose of importing data into mycompany's database. The data is mainly user/contact-related data forour customer base.We ran into problems when one import, of about 40,000 rows, tookupwards of six hours to complete. Many of the stored procedures usedby this package were written using XML. I've re-written many of themusing native SQL to see if that improves the performance, but I'mgetting some errors that I haven't been able to diagnose.Instead of asking about my specific errors, I'd like to know moregenerally what ways are there to debug DTS packages and storedprocedures? I'm aware of, and experienced with SQL Profiler but it'snot giving me the info I need. I need the ability to see exactly whatvalues are being passed to every call to a stored procedure fromwithin the DTS package or another stored procedure.I've used it very successfully to debug .asp, .aspx, .vb and the like,but right now I'm running it while running this huge stored procedurethat is called by the DTS package and does the lion's share of thework, including multiple updates and inserts into about 10 tables.The problem is, I see the calls to the "sub-procedures" from the mainone, but I can't see the values of any of the input or outputparameters. Instead ofInsert_Contact 'John', 'Q', 'Smith', '333-333-3333'......etc.I seeInsert_Contact @FirstName, @Initial, @LastName, @PhoneNumber......etc.My trace includes Stored Procedure events:RPC: CompletedRPC: StartingSP: StartingSP: StmtCompletedSP: StmtStartingand TSQL:Exec Prepared SQLPrepare SQLSQL: BatchCompletedSQL: StmtStartingI figured with these I would've covered the bases but I don't see anyof the parameters, which is critical for my debugging, as some of themare not being properly set.Any ideas or help would be greatly appreciated!TIA,Mike
In SQL Server 2005 I need a stored procedure that will execute an SSIS Package for me. There is some earlier stuff on the board but I don't understand it. I don't want to create a Job to do it if I don't have to.
Hey guys, I've got a problem here. I need to send the query result to a csv file then transfer the file to a website. I thought this is a good candidate for a SSIS package. The package is ready now but I don't know how can I execute it from within a stored procedure. I thought sp_OA family of extended procedure would be helpfull. After following steps: EXEC @hr1 = sp_OACreate 'DTS.Package', @oPKG OUT
EXEC @hr1 = sp_OAMethod @oPKG, 'exec' EXEC @hr1 = sp_OADestroy @oPKG it tells me command execute successfully. But no package actually gets executed and I can see no results Thanks
On my site I have a search engine which is used by potential customers to find out what courses we have on offer. Everytime something is written into the search engine the search criteria are logged in a table.
All the courses are held in a different table and I have enabled full text search on this table. Only two cloumns are searched for matches this is the course title and keywords.
What I want to be able to do is write a stored procedure which tells me which search criteria did not match any course titles or keywords. So that I can then assess what word people are using to search for courses and then add any which didn't match any courses to the keywords if they are relevant.
What I have done so far is create a view which contains a distinct list of search criteria. All i need to do now is create a stored procedure which loops through the courses table searching for each distinct search criteria.
I know this is possible to do using asp.net but I would like to do this on the database side to speed the process up a bit.
Any help on this matter would be much appreciated as I seem o be goinbg round and round in circles!!!
I have a site that will run a DTS package from a stored procedure when a user pushes a button. I know the stored procedure works and exectures. I need the .net code on how to execute this particular type of SQL statement. Any help would be AWESOME. THX This is the stored procedure stored in EM: CREATE PROCEDURE spExportData AS Begin TransactionSet nocount onEXEC master..xp_cmdshell 'dtsrun /Ssql-06-dba /NAdultExport -E'CommitGO The code below is what I am currently using and is not working.Protected Sub btnUpdateList_Clicked(Sender As Object, E As System.EventArgs) Dim strProc As String = "spExportData" Dim MyConnClass as New SQLConns Dim sqlConn as SqlConnection Dim myCommand As New SqlCommand sqlConn = MyConnClass.OpenConn("ConnAdultEd") MyCommand.CommandType = CommandType.StoredProcedure MyCommand.CommandText = strProc MyCommand.Connection = sqlConn MyCommand.ExecuteNonQuery() MyConnClass.CloseConn(sqlConn) statuslbl.text = "File updated" End Sub
