This forum has helped me get to this point a couple times with this project BUT I just can't seem to get the syntax correct. Either one of these two statements do exactly what I want them to do:SELECT @RtnValue = Column0 FROM MyTable WHERE RowIndex = @RIndexSELECT @RtnValue = (SELECT Column0 FROM MyTable WHERE RowIndex = @RIndex)
@RtnValue is the value the program will work with.
The only problem is that Column0 and @RIndex need to be dynamic so I can index through each Column and Row of the table.
This is the code I am trying to use to do this dynamically, naturally it will be in two while loop to index through each Column and Row
@MySelectString0 produces this error:Server: Msg 245, Level 16, State 1, Line 23Syntax error converting the varchar value 'SELECT @RtnValue = ( SELECT Column0 FROM MyTable WHERE RowIndex = ' to a column of data type smallint.
@MySelectString1 produces this error:Server: Msg 137, Level 15, State 1, Line 1Must declare the variable '@RtnValue'.
I have tried many different combinations of syntax but can not seem to get the magic combination. Can someone tell me the correct syntax to get this to work.
Still need help ! Can ANYONE tell me why this does not work ?? ********This Works when hard codedDECLARE @RtnValue smallintSET @RtnValue = 0 SELECT @RtnValue = (SELECT MyColumn0 FROM MyTable WHERE RowIndex = 700)******** ????????This DOES NOT WorkDECLARE @RtnValue smallintSET @RtnValue = 0 DECLARE @MySelectString varchar(200)SET @MySelectString = 'SELECT @RtnValue = ( SELECT MyColumn0 FROM MyTable WHERE RowIndex = 700)'PRINT @MySelectString EXEC ( @MySelectString )???????? !!!!!!!!This is the error message when I run in SQL Query AnalyzerSELECT @RtnValue = ( SELECT MyColumn0 FROM MyTable WHERE RowIndex = 700)Server: Msg 137, Level 15, State 1, Line 1Must declare the variable '@RtnValue'.!!!!!!!!! I PRINT the @MySelectString to varify and it look correct but when used in the EXEC() it does not work Please help !
I am some what new to stored procedures. Anyhow, I am attempting one and I am getting errors. I have overlooked my code numerious times and I can't figure out where I am going wrong....
SET @SQLString = 'SELECT wo_id, status, priority, customer_id, customer_name_1, customer_street_1, customer_city, customer_ac_1, customer_ph_1, customer_ac_2, customer_ph_2, customer_ac_3, customer_ph_3, access, call_type, eta_hour, eta_min, van_id, usr_create, tech_id, lname as tech_name, mfst_comment FROM (wo_master LEFT JOIN usr ON tech_id = usr_id) LEFT JOIN mfst_remarks ON wo_id = mfst_wo_id'
if @ccr <> '' AND @tech = '' AND @zone = '' AND @status = '' begin SET @SQLString = @SQLString + 'WHERE usr_create ='+ @ccr +' AND date_est_run BETWEEN dbo.std2jul('+@begin+') AND dbo.std2jul('+@end+') ORDER BY '+ @sort end
DECLARE @NAME AS varchar(1000) DECLARE @ADDRESS AS varchar(1000) Declare CUR_C Cursor For Select Rolename From DCJ_SecurityRole Open CUR_C Fetch Next From CUR_C into @NAME While @@fetch_status =0 Begin IF @NAME not in('All','PUBLIC') Begin SET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com' INSERT INTO #UserDetail EXEC (' SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME, initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY (ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM ''''LDAP://DC=XYZ,DC=com'''' WHERE MemberOf=''''' + @ADDRESS +''''' '') ') END Fetch Next From CUR_C into @NAME END CLOSE CUR_C DEALLOCATE CUR_C
When I executes this code I am getting some syntax error like Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'DCJAppDev'.where APJPRD is one of the role, Can anyone suggest what dynamic sql part has error??
I have a SSRS report that I'm passing a dynamic parameter to another SSRS report. The datasourse is a SSAS cube. Can anyone tell me the if following syntax is correct for receiveing report. I'm having trouble creating the datasource for the second report. I'm new at SSRS so any help would be appreciated.
When I parse this code in management Studio I get the following An MDX Statement was expected. An MDX expression was specified.
'= [SELECT NON EMPTY { [Measures]].[Qty Paid]], [Measures]].[US Cost]], [Measures]].[Qty Recd]] } ON COLUMNS, NON EMPTY
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 keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring")) Dim cmdSQL As New SqlCommand(strSQL, myConnection)
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
) ) ) ) ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql): select ID, FName, LName if(SUBSTRING(FirstName, 1, 4)= 'Mike') Begin Replace(FirstNam,'Mike','MikeTest') if(SUBSTRING(LastName, 1, 4)= 'Kong') Begin Replace(LastNam,'Kong,'KongTest') if(SUBSTRING(Address, 1, 4)= '1245') Begin ......... End End
end
Case Statement might be the solution but i could not do it.
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
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.
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),
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.
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _ & "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0")) buildDD(sql, ddlPerNames)
I 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!
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT 2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end 3 FROM abc 4 Anyone can help? Thanks a lot.
Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:
User ID FirstName LastName Department Site i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results? Any help would be appreciated. Thank you.
Hello, I have at sp that return a value:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_getLastActivityDate] ( @userid nvarchar(256)
) AS DECLARE @ret nvarchar(256) SET @ret = (SELECT CAST(LastActivityDate AS nvarchar) FROM aspnet_Users WHERE UserName = @userid) RETURN @ret The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret? // Tomas
I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.
ALTER PROCEDURE dbo.AddItemToCart ( @Cart nvarchar(50), @ItmID int, @Qty int, @ItmTyp char ) As
Declare @AllItmCount Int SELECT @AllItmCount = Count(ItmID) FROM ShoppingCart WHERE Cart = @Cart
IF @AllItmCount = 0 /*new shoppong cart*/ INSERT INTO ShoppingCart ( Cart, Qty, ItmID, ItmTyp, Line ) VALUES ( @Cart, @Qty, @ItmID, @ItmTyp, 1)
ELSE /*some item entered */
DECLARE @CountItems int
SELECT @CountItems = Count(ItmID) FROM ShoppingCart WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
IF @CountItems > 0 /* same item entered before then add together */
UPDATE ShoppingCart SET Qty = (@Qty + ShoppingCart.Qty) WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
ELSE /* Find the last line number */
Declare @LastLine Int SELECT @LastLine = Max(Line) FROM ShoppingCart WHERE Cart = @Cart
Hi all, I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use: SELECT ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies, (SELECT MAX(AddedDate) FROM (SELECT ForumID, AddedDate, Author FROM dbo.Forums_Topic UNION ALL SELECT ForumId, AddedDate, Author FROM dbo.Forum_Reply) AS dates WHERE (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM dbo.Forums Any suggestion on how to show the author of the last message as well? Thanks in advance.
I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?
SELECT TimeZone.LongZone, SiteMain.SiteNumber, SiteType.SiteTypeDescription FROM SiteType INNER JOIN TimeZone ON SiteType.id = TimeZone.id INNER JOIN SiteMain ON TimeZone.id = SiteMain.TimeZone AND SiteType.id = SiteMain.SiteType
