I am trying to write a dynamic sql insert statement in c#, but given this is my first run at creating sql statements dynamically I am having issues. My reason for creating the sql statement dynamically is because I do not want to insert any items that are null. Will I have to have a separate string for each section of the statement? For example:
sql1 = "Insert Into Table [test]";
sql += "(Column1, Column2, Column3," ;
sql2 = "Values" ;
sql2 += "(field1, field2, field3";
and then a third section if I were to use parameters. My thinking here is how do you append to a string in numerous locations? I will populate the sql strings first with those columns and fields that I know will not be null, and then use if statements to add to each sql string if the field in question != null. in other words, little help.
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.
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 ':'.
Hi, I try to get the dynamic insert statement script.
See the below statement I'm getting syntax error. How can change this right way script?.
select * into pubs.dbo.employee_temp from pubs.dbo.employee where emP_id<>emP_id
Declare @cmd varchar(8000)
set @cmd =N'insert into employee_temp(emp_id,fname,minit,lname)'+char(13)+ 'values '+'('+select ''''+emp_id+''''+','+''''+fname+''''+','+''''+mini t+''''+','+''''+lname+''''+')' from pubs.dbo.employee EXECUTE sp_executesql @cmd
I would like to know if it is possible to pass a table name to the from section of a sql select statement? Something like: Declare @paramTable as nvarchar(10) Set @paramTable = TableName Select firstname, surname from @paramTable
I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext. How would I go about building this efficiently? Would I Declare a bit value in the sp called WhereSet = 0 IF @client IS NOT NULL IF @WhereSet = 0 SET @Where = 'WHERE ClientID=@client' SET @SetWhere = 1 ELSE SET @Where = @Where + ' AND CleintID=@client' . . .... Or would this be a lot easier using adhoc SQL instead of a Stored Procedure? (note: I am using a SQL DataSource) Please help, I am going bald from pulling my hair our...
Is there a better way to do this? or is basically how you would write a dynamic SQL Stored Procedure? Also, is this code vulnerable to SQL injection? All of the Parameters are being passed in by a SQL DataSource. set ANSI_NULLS ONset QUOTED_IDENTIFIER ON go -- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- ============================================= CREATE PROCEDURE [dbo].[pe_getAppraisals] -- Add the parameters for the stored procedure here@PType nvarChar(50),@Client nvarChar(50),@City nvarChar(50),@ApptDate nvarChar(50),@OrderDate nvarChar(50),@Status nvarChar(50),@AType nvarChar(50),@Text nvarChar(50),@OrderBy nvarChar(50),@SortDir nvarChar(4),@PageSize INT,@PageNum INT AS DECLARE @l_Select nvarChar(4000),@l_From nvarChar(4000),@l_SetWhere bit,@l_PType nvarChar(100),@l_Client nvarChar(100),@l_City nvarChar(100),@l_ApptDate nvarChar(100),@l_OrderDate nvarChar(100),@l_Status nvarChar(100),@l_AType nvarChar(100),@l_Text nvarChar(4000),@l_SortDir nvarChar(4),@l_TotalRecords INT BEGIN -- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON; IF @OrderBy IS NULL SET @OrderBy = 'OrderDate' IF @SortDir IS NULL SET @SortDir = 'DESC' IF @SortDir = 'DESC' SET @l_SortDir = 'ASC'ELSE SET @l_SortDir = 'DESC' --Initialize SetWhere to test if a parameter has Added the keyword WHERE SET @l_SetWhere = 0 --Create WHERE portion of the SQL SELECT Statement IF (@PType IS NOT NULL)BEGIN SET @l_PType = ' WHERE o.PropertyTypeID=' + @PType SET @l_SetWhere = 1EndELSE SET @PType = '' IF (@Client IS NOT NULL)BEGIN IF @l_SetWhere = 0 BEGIN SET @l_Client = ' WHERE o.ClientID=' + @Client SET @l_SetWhere = 1 END ELSE SET @l_Client = ' AND o.ClientID=' + @Client ENDELSE SET @l_Client = '' IF (@City IS NOT NULL)BEGIN IF @l_SetWhere = 0 BEGIN SET @l_City = ' WHERE o.City=''' + @City + '''' SET @l_SetWhere = 1 END ELSE SET @l_City = ' AND o.City=''' + @City + ''''ENDELSE SET @l_City = ''IF (@ApptDate IS NOT NULL)BEGIN IF @l_SetWhere = 0 BEGIN SET @l_ApptDate = ' WHERE o.ApptDate= ''' + @ApptDate + '''' SET @l_SetWhere = 1 END ELSE SET @l_ApptDate = ' AND o.ApptDate= ''' + @ApptDate + ''''ENDELSE SET @l_ApptDate = '' IF (@OrderDate IS NOT NULL)BEGINIF @l_SetWhere = 0 BEGIN SET @l_OrderDate = ' WHERE o.OrderDate=''' + @OrderDate + '''' SET @l_SetWhere = 1 END ELSE SET @l_OrderDate = ' AND o.OrderDate=''' + @OrderDate + ''''ENDELSE SET @l_OrderDate = '' IF (@Status IS NOT NULL)BEGINIF @l_SetWhere = 0 BEGIN SET @l_Status = ' WHERE o.StatusID=' + @Status SET @l_SetWhere = 1 END ELSE SET @l_Status = ' AND o.StatusID=' + @Status ENDELSE SET @l_Status = '' IF (@AType IS NOT NULL)BEGIN IF @l_SetWhere = 0 BEGIN SET @l_AType = ' WHERE o.ReportID=' + @AType SET @l_SetWhere = 1 END ELSE SET @l_AType = ' AND o.ReportID=' + @ATypeENDELSE SET @l_AType = '' IF (@Text IS NOT NULL)BEGIN IF @l_SetWhere = 0 BEGIN SET @l_Text = ' WHERE (o.FileNumber LIKE ''' + @Text + '%''' + ' OR o.LoanOfficer LIKE ''' + @Text + '%''' + ' OR o.Borrower LIKE ''' + @Text + '%''' + ' OR o.StreetAddrA LIKE ''' + @Text + '%''' + ' OR o.State LIKE ''' + @Text + '%''' + ' OR o.ContactName LIKE ''' + @Text + '%'')' SET @l_SetWhere = 1ENDELSE SET @l_Text = ' AND (o.FileNumber LIKE ''' + @Text + '%''' + ' OR o.LoanOfficer LIKE ''' + @Text + '%''' + ' OR o.Borrower LIKE ''' + @Text + '%''' + ' OR o.StreetAddrA LIKE ''' + @Text + '%''' + ' OR o.State LIKE ''' + @Text + '%''' + ' OR o.ContactName LIKE ''' + @Text + '%'')'ENDELSE SET @l_Text = '' --Build the SQL SELECT Statement SET @l_Select = 'o.OrderID AS OrderID, o.FileNumber AS FileNumber, o.OrderDate AS OrderDate, o.ClientID AS ClientID, o.ClientFileNumber AS ClientFileNumber, o.PropertyTypeID AS PropertyTypeID, o.EstimatedValue AS EstimatedValue, o.PurchaseValue AS PurchaseValue, o.LoanOfficer AS LoanOfficer, o.ReportFee AS ReportFee, o.FeeBillInd AS FeeBillInd, o.FeeCollectInd AS FeeCollectInd, o.CollectAmt AS CollectAmt, o.Borrower AS Borrower, o.StreetAddrA AS StreetAddrA, o.StreetAddrB AS StreetAddrB, o.City AS City, o.State AS State, o.Zip AS Zip, o.ContactName AS ContactName, o.PhoneA AS PhoneA, o.PhoneB AS PhoneB, o.ApptDate AS ApptDate, o.ApptTime AS ApptTime, o.AppraiserID AS AppraiserID, o.InspectionDate AS InspectionDate, o.DateMailed AS DateMailed, o.TrackingInfo AS TrackingInfo, o.ReviewedBy AS ReviewedBy, o.StatusID AS StatusID, o.Comments AS Comments, o.SpecialNotes AS SpecialNotes, o.EmailInd AS EmailInd, o.MgmtName AS MgmtName, o.MgmtContactName AS MgmtContactName, o.MgmtAddress AS MgmtAddress, o.MgmtPhone AS MgmtPhone, o.MgmtFax AS MgmtFax, o.MgmtFee AS MgmtFee, o.MgmtNotes AS MgmtNotes, o.LoginName AS LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc AS StatusDesc, ot.ReportDesc AS ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq AS ReportSeq, pc.PriceDesc AS PriceDesc, pt.PropertyTypeDesc AS PropertyTypeDesc, l.LoginName AS AppraiserName, l2.LoginName As ClientName' SET @l_From = 'Orders AS o LEFT OUTER JOINOrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOINOrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOINOrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOINOrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOINPriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOINPropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOINLogins AS l ON o.AppraiserID = l.LoginID LEFT OUTER JOINLogins AS l2 ON o.ClientID = l.LoginID' SET @l_TotalRecords = @PageSize * @PageNum PRINT ' ORDER BY ' + @OrderBy + ' ' + @l_SortDir + ') ORDER BY ' + @OrderBy + ' ' + @SortDir Execute('SELECT TOP(' + @PageSize + ') * FROM (SELECT TOP(' + @l_TotalRecords + ') ' + @l_Select + ' FROM ' + @l_From + @l_PType + @l_Client + @l_City + @l_ApptDate + @l_OrderDate + @l_Status + @l_AType + @l_Text + ' ORDER BY ' + @OrderBy + ' ' + @l_SortDir + ') AS rsltTbl ORDER BY ' + @OrderBy + ' ' + @SortDir) END Thank You, Jason
For whatever reason, the following when executed does not take the context of the supplied Database name. Any ideas as to why? More importantly is there a work around. I am trying to create a database level batch job.
I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:
select * from Table where D10 > midDaypct * 2 and DOW6 > 0 union select * from Table where DOW6 >midDOWpct * 2 and D10 > 0
We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow.
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
I am using dynamic sql in my report. I cannot see the sql the report is generating in sql profiler. Is there somewhere else I can view it? Thanks, Linda
Would 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
In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.
I am trying to use this statement in a variable, including another variable:
"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + @[User::LastTimestamp]
But the variable value insists on giving me this error:
The expression for variable "VariableName" failed evaluation. There was an error in the expression.
I cast the columntype "timestamp" to int, and the variable "LastTimestamp is stored as int32, and has a default value of 0. I simply can't grasp what it is I am missing.
Is it because the expression is part string and part integer? If so, how is that avoided?
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
Hi, I rewrote my working Sql statement to prevent Sql Injections. I copied some code I used in another project but this time I can't get it to work, possibly because it's an update statement and not an Insert one, which I used before. Sorry for the boring question, but does anyone have a clue what's wrong with the syntax? Here's the original code (I changed the parameter names for clarity and security): Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim strSQL As String = "Update MyTable Set " & typ & num & " = '" & pname & "' WHERE personID = " & fid Dim cmd As SqlCommand = New SqlCommand(strSQL, conn) Here's the code from codebehind: Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim strSQL As String = "Update MyTable Set (" & typ & num & ") Values (@" & typ & num & ") WHERE personID = " & fid Dim cmd As New SqlCommand(strSQL, conn) With cmd.Parameters .Add(New SqlParameter("@" & typ & num, pname)) End With TestLabel.Text = strSQL & " " & pname cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() Here's my test message; first the sql, then the new string to be inserted: Update MyTable Set (picturename) Values (@pname) WHERE personID = 2 2_adin.jpg Here's my error code: Line 1: Incorrect syntax near '('. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('. Source Error: Line 489: TestLabel.Text = strSQL & " " & pnameLine 490: cmd.Connection.Open()Line 491: cmd.ExecuteNonQuery()Line 492: cmd.Connection.Close()Line 493: End If I could understand it if I'd get an error in VWD Express for using dynamic variables, but they work correctly in the text message so I'm clueless. Any help is deeply appreciated! Pettrer (VB, Sql Server 2000)
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble; ALTER PROCEDURE [dbo].[stream_UserFind] ( @userName varchar(100), @subCategoryID INT, @regionID INT )AS declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName' if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID ' exec sp_executesql @StaticStr )
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.
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.
I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows. 1234 2345 3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT) INSERT INTO #TempStatusUpdate SELECT ListID FROM DListing WHERE Status = 2 SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2' DROP TABLE #TempStatusUpdate
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton
I would like to provide the names of columns in an insert statement from a schema table, so that when running through a number of Bus Rule checks I can reference the schema table and only maintain the columns in the schema table rather than maintain named columns in multiple insert statements. So my query for one check looks like below. I'm using dynamic sql to execute the insert statement. My question is, is there a better way or different way to do this without using dynamic sql? Ie, Is there a way that I can use the columns parameter like this instead?
Declare @columns as nvarchar(max); Declare @InvSQL as nvarchar(max); SELECT @columns = STUFF (( SELECT ', [' + name + ']' FROM syscolumns WHERE id = OBJECT_ID('dbo.table_pvt')
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
In my source query I want to change the value of Source table ,Destination table and BBX expression dynamically on the basis of input file.
Purpose of making dynamic is that I have created separate sp for all the input, my clients want to have sungle dynamic sp which will execute on the basis of input file.this input file name I wil get fromm variable which i have created in SSIS Package.
Lets consider @File_name is the variable in package which store the file name
if file name is CCTFB then my query should take the Source table ,Destination table and BBX expression value from file master table.
Like that I have 100 of source query and evry query have diffrent number of columns. How can I change the column number in uodate and insert statement dynamically on run time.
CAST(SUBSTRING(Col001,1,6) + SUBSTRING(Col002,1,10) AS varchar(100)) :-It creates a key for comparing, this value i can take it from filemaster HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003,Col004) AS varchar(max))) -here numberv of column need to be changed . (SUBSTRING(SOURCE.Col001,1,6) + SUBSTRING(SOURCE.Col002,1,10)) this condition also i can take it from file master.
[Code] ....
I am able to get inserted and updated rowcount, but not able to get the matching records count.
I have to generate a report on the fly. The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more.
From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly.
So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time.
Following is the stored procedure iam trying to create.Here i am trying to
First create a table with the table name passed as parameter Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows. Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.
This table would be further used with cursor. Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.
I 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],
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable ( Name varchar(50), GA int, GB int, startdate DATETIME, enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate Pavan 261/1/20151/1/2015 Hema 561/1/20151/1/2015 Surya 501/1/20151/1/2015 Pavan 811/2/20151/8/2015 Hema 311/2/20151/8/2015 Surya 121/2/20151/8/2015 Pavan 1041/9/20151/15/2015 Hema 301/9/20151/15/2015 Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal Total 8 11 5 24 GA 2 5 5 12 GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal Total 9 4 3 16 GA 8 3 1 12 GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal Total 14 3 19 36 GA 10 3 6 19 GB 4 0 13 17
I have created a trigger on a table that get's too many updates so to analyze what records and what columns get updates I have coded a trigger that inserts into a table the record key and a flag on each column to let me know what is updates. Now that I am presenting the data to the developers I have been asked to provide the update / insert or delete statement
So on my trigger I have tried using dynamic management views but its returning the actual trigger code not the parent SQL Script that triggered it.
This is what I am using.
select@dDate as dDate, dest.[dbid], dest.[objectid], dest.[number], dest.[encrypted], case when sder.[statement_start_offset] > 0 --the start of the active command is not at the beginning of the full command text thencasesder.[statement_end_offset]
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
