Dynamicall Execute SP Without Dynamic Sql
Jul 23, 2005How would you go about dynamically executing a stored proc dependent on
a variable? I cannot use dynamic sql.
ADVERTISEMENT
Dynamic SQL.....Problem With Execute Permission
Jan 2, 2002Iam giving permisions to the users to execute procedure which reads and writes
the data into the table. And the users cannot directly update the tables.
It works if stored procedure code doesnot have dynamic SQL but gives permision problems when the code contains dynamic SQL .
One limitation of ownership chains is the exec() call. While normal statements in procedures will work with ownership chains, the following one won't
alter proc lsp_deleteorders @OrderID int
as
begin
declare @Sql varchar(200)
set @SQL = 'update orders set active = 0 where OrderID = ' + convert(varchar, @OrderID)
exec(@SQL)
end
Can anyone tell the workoaround for this.
Thanks,
Sajai
Execute Package Task - Dynamic Name
Nov 7, 2007Hi,
Is there anyway where i can pass the package name to an "execute package task" and run it through loop. My loop task will pass the package name which is assigned on a variable and the execute package task should pickup the name and run, is this possible, if yes how?
How To Execute A Dynamic SQL With Integer Parameter For Stored Procedure?
Sep 17, 2007I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.
Error: Unterminated String Constant.
What is the problem?
Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);
T-SQL (SS2K8) :: Dynamic Join On EXECUTE Table Column
Jun 23, 2015Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e
select * from (select table1.theSql,table1.userid from table1 ) as a
inner join (execute a.thesql) as b
on a.userId=b.userid
Execute Big Dynamic SQL In Stored Procedure To Create View
Jul 20, 2005I am trying to create a dynamic SQL statement to create a view.I have a stored procedure, which based on the parameters passed callsdifferent stored procedures. Each of this sub stored procedure createsa string of custom SQL statement and returns this string back to themain stored procedure.This SQL statements work fine on there own. The SQL returned from thesub stored procedure are returned fine. The datatype of the variablethat this sql is stored in Varchar(I have tried using nvarchar alsosame problem).If I have more that 6 SQL statements concated then the main SQL getscut off. It doesnt matter in what sequence I create the main SQL.Here is the Stored procedure/**********************************************//*Main Stored Procedure *//**********************************************/CREATE PROC sp_generate_invoice1 @prev_date NVarchar(1000) ,@prev_month NVarchar(32)ASDECLARE invoice_driver_cur CURSOR FORSelect driversid From Invoice_driversOpen invoice_driver_curDeclare@C VARCHAR(8000),@L_args Varchar(8000),@@sqlstmt Varchar(8000),@L_driverid Int,@L_rowcount IntSET QUOTED_IDENTIFIER ONSET TEXTSIZE 32768Set @L_rowcount = 0-- Drop the previous ViewIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_NAME = 'custom_invoice')DROP VIEW custom_invoiceFetch Next From invoice_driver_curInto @L_driverid-- Create the new ViewSet @L_args = N'Create View custom_invoice As'--Select @L_driveridWHILE( @@FETCH_STATUS = 0)BEGINSet @L_rowcount = @L_rowcount + 1select @L_driveridIf @L_driverid = 2BeginExec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @L_args + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 3BeginExec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 4BeginExec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 5BeginExec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 6BeginExec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 7BeginExec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 8BeginExec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 10BeginExec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,@@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndPrint @CFetch Next From invoice_driver_curInto @L_driveridContinueEndClose invoice_driver_curDeAllocate invoice_driver_curExec (@C)--EXEC sp_executesql @CGO/**********************************************//*Sub Procedure sp_invoice_driver2 *//**********************************************/CREATE PROC sp_invoice_driver2 @args NVarchar(1000), @prev_monthNVarchar(100),@sqlstmt Varchar(8000) OutputASSET QUOTED_IDENTIFIER ONSET @sqlstmt = ' Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''andfee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''GO/**********************************************/This is what the Print Statement give:/**********************************************/Create View custom_invoice As Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September ' Union Select 2,(SELECT Drivers.Description),(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))/ 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September' andDrivers.Driversid = 3 Union Select 3,(Select Drivers.Description From Drivers Where DriversID = 4),Count(*) * Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 4 )As decimal(6,2)) / 12,(Select Drivers.CurrencyFrom Drivers Where DriversID = 4)From Fund Union Select 4,(Select Drivers.Description From Drivers Where DriversID = 5),(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +BillingReport.Man_Reg_Transexch +BillingReport.Man_Allo_Purch +BillingReport.Man_Allo_Red +BillingReport.Man_Allo_Transexch +BillingReport.Man_Allo_Adj_Purch +BillingReport.Man_Allo_Adj_Red +BillingReport.Man_Allo_Adj_Transexch +BillingReport.Man_Adj_Purch +BillingReport.Man_Adj_Red +BillingReport.Man_Adj_Transexch ) ) +(Select Sum(Cast(satuscnt As int ))From Awd_stubWhere CurrentMonth = 'September'))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 5 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 5)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 5,(Select Drivers.Description From Drivers Where DriversID = 6),( Sum( BillingReport.Auto_Reg_Purch +BillingReport.Auto_Reg_Red +BillingReport.Auto_Reg_TRansexch +BillingReport.Auto_Allo_Purch+BillingReport.Auto_Allo_Red +BillingReport.Auto_Allo_Transexch+BillingReport.Auto_Allo_Adj_Purch+BillingReport.Auto_Allo_Adj_Red+BillingReport.Auto_Allo_Adj_transexch+BillingReport.Auto_Adj_Purch+BillingReport.Auto_Adj_Red+BillingReport.Auto_Adj_Transexch )+(Select Sum(Cast(Processed_msg As Int))From XML_messagingWhere CurrentMonth = 'September'))*(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 6 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 6)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 6,(Select Drivers.Description From Drivers Where DriversID = 7),( ( a.Accountholder_Active_Accounts -(select accountholder_active_accounts from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ) )+( a.Accountholder_Zero_Balance -(select accountholder_zero_balance from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 7 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 7)FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,(Select Drivers.Description From Drivers Where DriversID = 8),( Select telephone From cfxbill Where currentmonth = 'September') *(Cast((select feThanks for any helpMD
View 4 Replies View RelatedHow To Be Dynamic The Argument Property Of The Execute Process Task
Mar 17, 2008Hi All,
I have the following issue, i want to make dynamic the Argument property of the Execute Process Task but i couldn't able to do so, i have posted several times , hopefully i will get a solution today. I am using the following Argument to execute (winzip) program.
Argument: /e C:FTPFile1.zip C:FTPUnzipped
This Argument works but since File1.zip gets change every time, i mean it will be File2.zip, thus i have to change it manually on the Execute Process Task every time, however i want it to change by itself.
To make it clear, the /e mean winzip usage for extract file, the C:FTPFile1.zip is where the zipped file is, and the C:FTPUnzipped is where i dumped the unzipped files. as you see the file name will change every time like File1.zip, File2.zip, File3.zip, etc... Thus i want to make it daynamic, for example if i execute today File1.zip, then i have to execute tomorrow File2.zip automatically, and then File3.zip etc, that means the source file is changing every time, how do i make it then to be Dynamic.
Please help, i need your input badly.
Thank you in advance,
SQL Server Admin 2014 :: Execute Dynamic Query 1 As A Transaction
Feb 7, 2015I have this command :
Exec 'update .... insert ..... delete ..... insert ...'
I Execute these command in one execution.
exec ('...')
Are these commands act as a transaction? If one of them create error , another commands run or rull backed?
Execute Dynamic SQL Stored Proc Without Specifying Table Level Permissions
Sep 17, 2007I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query
Dec 6, 2006Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task
Apr 19, 2007I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
SSIS Execute Package With Execute Out Of Process = True Causes ProductLevelToLow Error
Mar 6, 2008
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns
Aug 25, 2007Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Nov 23, 2007Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?
May 2, 2007If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot
Mar 9, 2015I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
Conditional Execute By Execute SQL Task Return Value?
Jun 25, 2007I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
Mixing Dynamic SQL With Non-Dynamic In Stored Proc
Mar 24, 2007I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
Dynamic Cursor/ Dynamic SQL Statement
Oct 24, 2004I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.
Dynamic Source And Dynamic Destination
Apr 15, 2008I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output
Mar 2, 2014I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Execute A SP In The Execute SQL Task
Jan 25, 2007I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load_Gs_Modifier_1
SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
Dynamic Columns For Dynamic SQL
Mar 9, 2007I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.
Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.
I have come to understand that a dynamic anything is a moving target!
Please advise.
Trigger Not Execute Some Data Or Insert Not Execute A Trigger For Some Data
Mar 3, 2008I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.
Using Execute
Sep 24, 2002I am trying to return a recordset to an ASP page from a stored procedure.
The stored procedure creates a temporary table and then builds an SQL statement into a string declared with the SP. The string contains an INSERT INTO statement to insert into the temporary table and followed by a select of the rows I wish to insert into the table.
I then use the EXECUTE method to execute the SQL string. After this I have a SELECT which should select from the temporary table and return the rows i am interested in from the stored procedure.
For some reason this does not seem to work. If I execute the SP from query analyzer I get the resultset returned. However when I try to call the stored procedure from my ASP no recordset is returned. I have done this type of thing before and it has worked perfectly. Does anyone have any ideas why this may not be working? Many Thanks.
Execute SQL From EM
Oct 28, 2001Newbie Question: I'm in EM and I want to execute a simple SQL statement (e.g. exec a sproc). How do I do it? Do I have to fire up QA? or is there a simpler way?
View 1 Replies View RelatedExecute (hex)
Oct 4, 2004I don't remember where I saw it, but it looked something like this: ...exec (0x0A738... The result was identical to executing: select getdate() Anybody seen it anywhere?
View 4 Replies View RelatedHow To Execute SP
Mar 26, 2004hi all,
i am having stored procedure like the below one:
create procedure pro_emplname
@name varchar
as
select * from employee where ename=@name
i have tried like this to execute:
exec pro_emplname kamal
but i am not getting the output....
could any one tell.....
thanks
How Can I Execute *.sgl?
May 29, 2004HI
i want to rite a sysntax in StoreProcedure that execute sysntaxes in My_file.sql .
How can i do this?
thanks
Execute As 'sa'
Apr 15, 2008I have two instances running on a machine...
The following code will run on the named instance but not on the default instance.
On the default instance I get the following error:
Cannot execute as the user 'SA', because it does not exist or you do not have permission.
However if I specify another account it does work.
use dbReport
go
DROP procedure dbo.clarktest
GO
create procedure dbo.clarktest
WITH EXECUTE AS 'SA' -- 'DomainSqlCmdShellDev'
as
EXEC xp_cmdshell 'dir'
go
grant exec on clarktest to [spexec]
exec clarktest
use master
go
Any assistance is appreciated as the developers code needs to be coded generically i.e. "sa" this is to prevent code manipulation during migrations...
Thank you
You can do anything at www.zombo.com
Execute A Job From DOS
Jun 10, 2006I want to start of run a job from DOS. I have MS SQL 2000 using Enterprise Manager.
Thank You,
Ernie
How To Execute
Dec 1, 2006hi,this is my stored procedure:
CREATE PROCEDURE loop As
declare @fromage bigint
begin
select @fromage=max(age) from emp1
end
begin
insert into loop values(@fromage)
end
how to run thi spc in sqlserver,please tell me
Execute Dts
Aug 6, 2007hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"' +
' Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
error is:
Option "Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1databasename" is not valid.
please note I just retyped the data source name here.