Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.
Thank you
I'm building a select string on the fly based on criteria selected by the user. The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel. Everything is working fine, except when the Name has an ' in it. For example O'Kelly or St. John's. I know I can take all the 's out of the database, but I'd rather keep the data authentic. Is there a way to manipulate a select string built on the fly accounting for an embedded '?
For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up.
Any ideas?
Sample Code:<code> For Each SelectedIndex In rsc.SelectedIndexes Counter=Counter + 1 dgAssociates.SelectedIndex = SelectedIndex If Counter = 1 then SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'" Else SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'" End If Next If Counter > 0 then SelectString &= ")" End If</code>
Hi all
I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID
etc etc
Here's the cursor
DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod
OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1
-- DO SOME FUNKY STUFF
FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
hi, how to retrieve row count from an insert statement in dynamic sql?
CREATE PROCEDURE [dbo].[procTest]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @nsql NVARCHAR(MAX)
DECLARE @i INT
SET @nsql = 'select * into tbl_user_test from tbl_user;
select @i = @@rowcount;'
exec sp_executesql @nsql;
END
The code doesn't work..
declare @i int;
Exec procTest, N'@i int output',@i=@i output
select @i;
I need to implement a "dynamic" select query, in which I want that the type and the number of columns are variable/changeable. For example using the parameters of a stored procedures. I would have a client code (written in VB .NET) in which I would choose the columns that I want to display in a DataGridView.
How should I modify the SELECT line or the SP in general to solve the problem??
The code I need to modify is this:
Code Snippet
CREATE PROCEDURE uspSelect1
@iDataInit datetime = '01 gen 1753',
@iDataEnd datetime = '31 dic 9999',
@iArticle nvarchar(50) = N'%',
@iMachineNum smallint,
@iTypeMaterial nvarchar(50) = N'%',
@iNominalCount float,
@iOperator nvarchar(50) = N'%',
@iCustomer varchar(50) = N'%',
@iComments nvarchar(1000) = N'%',
@iLanguage nvarchar(50) = N'%'
AS
SELECT i.IDsample, i.Date, i.Article, i.MachineNum, i.TypeMaterial, i.NominalCount, i.Operator, i.Customer, i.Comments, i.Language, r.Um, r.CVm
FROM Identification i JOIN Reports r
ON (i.IDsample = r.IDsample)
WHERE i.Date BETWEEN @iDataInit AND @iDataEnd
AND i.Article LIKE @iArticle
AND i.MachineNum = ISNULL(@iMachineNum, i.MachineNum)
AND i.TypeMaterial LIKE @iTypeMaterial
AND i.NominalCount = ISNULL(@iNominalCount, i.NominalCount)
AND i.Operator LIKE @iOperator
AND i.Customer LIKE @iCustomer
AND i.Comments LIKE @iComments
AND i.Language LIKE @iLanguage
ORDER BY i.IDsample
Thank you.
Gabriele
Hi,
I have a table with 20 column 10 with the name in1,in2,in3... and 10 more with the name out1,out2,out3...
what i'm trying to do is a loop that will substract all column like this
sum = out1-in1
sum = sum + (out2-in2)
etc etc..
what is your suggestion for the best way doing that.
thanks.
I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.
Can someone help me out on this.
EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp
In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB.
Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand .
How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit?
thanks a lot.
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:TESTDrivercommunication %>"
SelectCommand="usp_reports_shortages" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblDriver" Name="date1" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="lblTODate" Name="date2" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DDlDriver" Name="driver" PropertyName="SelectedValue"
Type="Int32" />
<asp:SessionParameter Name="week" SessionField="s_week" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form. I have a GridView control and a SqlDataSource. The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page. I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause. I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?
View 5 Replies View RelatedWould it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable
Just curious.
Thanks,
Steve Hanzelman
I am trying to assign @sql variable to @total, where @sql is a sqlstatement stored on the database, however what I am getting is itsstring value and not its calcuation. Could anybody help?DECLARE my_cursor CURSOR FORSELECT sqlstatement from Sn_SalesReportdeclare @sql varchar(255), @total varchar(20)OPEN my_cursorFETCH NEXT FROM my_cursor INTO @sql-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGINSET @total = (@sql)print @total-- This is executed as long as the previous fetch succeeds.FETCH NEXT FROM my_cursorINTO @sqlENDCLOSE my_cursorDEALLOCATE my_cursorI tried this SET @total = EXEC (@sql), but no successRod
View 9 Replies View RelatedI'm looking for a way to use SSRS to display the contents of a returned dataset with 1-n columns. The number of columns is unknown at design time and the datasetview is using a stored procedure with 1 parameter to return the data. Does any one know if RDL supports wildcard(*) characters for field names or if there is another method.
I have this working via a web based custom aspx page but it would be VERY helpful to utilize all the document conversion features the SSRS reports provide via the web.
Thanks for any help.
IT Publisher
I am trying to select from 2 cubes. in one condition - select .. From CubeX, and in sec Condition Select .. From CubeY.
I tried to put the name of the Cube as parameter:
SELECT ...
FROM @CurrentCube
in ssrs source and i got the message:
------------------------------
Failed to parse the query to detect if it is MDX or DMX. Error: 'Query (148, 6) Parser: The syntax for '@CurrentCube' is incorrect.' (MDXQueryGenerator)
------------------------------
Also tried to get the name of the Cube as Member (I have a property with the name of the cube),and than using the member to the select :
WITH MEMBER [CurrentCube] AS
...[dim name].CURRENTMEMBER.PROPERTIES("Cube")
SELECT ...
FROM [CurrentCube]
It didnt work too.. Is it Possible at all make the Cube-name dynamic?!
I'm expecting to revamp some stored procs so that their selects are executed on a dynamic string that always returns the same columns but varies the sources.
I'm concerned that the bread and butter of products like RS and SSIS is the ability to predict what columns, and what column types to expect from a query, but that introducing dynamic sql will complicate using them.
I'm motivated not to use temp tables or table vars if possible. I'm also somewhat motivated to learn of a solution that works equally well in 2000 and 2005.
I've tried wrapping dynamic calls in a select as shown below but to no avail...
After€¦
declare @sqlString nvarchar(4000)
set @sqlString = 'select * from [' + @dbName + '].[dbo].[activity]'
I€™ve already tried things like€¦.
select a.* from exec sp_executesql @sqlString a
and
select * from exec (@sqlString)
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.
declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.
The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
BTW - this would be a stored procedure...
Any suggestions would be greatly appreciated.
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256)
DECLARE @TargetDate smalldatetime
DECLARE @TLevel int
DECLARE @n int
SET @TargetDate = '2004-05-24'
SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' +
CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' +
CONVERT(varchar(3), @TLevel) + '))'
exec (@SQLCmd)
if @n > 0
print 'yep'
else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@n'.
nope
Thoughts?
Hi,
i have problem executing the dynamic query.
I have straight forward query as below and works fine
declare @count bigint
declare @varcount varchar(max)
set @varcount ='5'
If convert(bigint, @varcount) <> 4
print ' not 4 '
else
print 'Its 4'
Here is my dynamic query. The number of records (@No ) and the table name ( @table ) will be available for me as parameters to my stoped proc
declare @count bigint
declare @varcount varchar(max)
declare @tempTable varchar(max)
declare @vsSql varchar(max)
declare @No bigint
set @No = 5
set @table = 'sam'
set @varcount = ''
select @vsSql = 'declare @varcount varchar(max); select @varcount = count(*) from ' + @table + '; If convert(bigint,@varcount) <> ' + @No + ' raiserror(' +'mismatch, 11,1' +')'
When executed it throws the follwing error
Msg 8114, Level 16, State 5, Line 10
Error converting data type varchar to bigint.
Can anyone point out what to change in the query to work
~mohan
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
View 3 Replies View RelatedIn one query, I would like to query every user table in a specified database for
SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC
How is this possible?
Please give examples, I am a beginner.
Assume every table has the same structure for columns event count, target ip, and time.
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
View 4 Replies View RelatedI can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hi 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
Hi,
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
If 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?
I 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]....
I 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.
I'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 ':'.
I 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
I 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.