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'm getting the following error messages: Incorrect syntax near the keyword 'in'. Server: Msg 156, Level 15, State 1, Line 38 Incorrect syntax near the keyword 'group'.
/* create temp tables */ select distinct d_vst_id as 'DRW_ID' ,d_vst_instid as 'DRW_INSTID' into temp_tb1 from dnr_vst_db_rec where d_vst_instid = '' and d_vst_dontyp = 'WB' and d_vst_status = 'DN' and d_vst_date between 20020301 and 20030228 order by d_vst_id
Select distinct n_per_id as 'ID1' ,n_per_gender as 'GENDER' ,n_per_birth as 'BIRTH1' ,d_bty_abo + d_bty_rhesus as 'ABO1' ,n_adr_city as 'CITY1' ,n_adr_zip as 'ZIP1' into temp_tb3 from temp_tb1 right outer join nat_per_db_rec on DRW_ID = n_per_id right outer join dnr_bty_db_rec on DRW_ID = d_bty_id right outer join nat_adr_db_rec on DRW_ID = n_adr_id where DRW_INSTID = '' order by n_per_id
select distinct getdate() ,d_aaa_insthdg , case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as 'AGE' ,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1' ,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL' from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a where a.d_vst_instid = '' and a.d_vst_instid = d_aaa_instid and a.d_vst_id = ID1 and a.d_vst_instid = n_per_instid and a.d_vst_id = n_per_id and n_per_gender = 'M' and a.d_vst_btcdte between 20020301 and 20030228 and a.d_vst_btcdte = (Select max(b.d_vst_btcdte) from dnr_vst_db_rec b where b.d_vst_instid = '' and b.d_vst_status = 'DN' and b.d_vst_dontyp = 'WB' and b.d_vst_id = a.d_vst_id and b.d_vst_btcdte between 20020301 and 20030228)
group by d_aaa_insthdg ,case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as
First of all, hello and good morning, my question is, you can check SQL syntax in SQL Server with secondary button mouse or "Check SQL" button in toolbar (Microsoft Management Console 1.2). I´d like to know if there´s a way to use these Server tools from Visual Basic 6 SP6, something like APIs ... If there´s no solution, can anybody give me an idea of how to check SQL syntax in VB. The application wants the users to make their own SQL sentences, (they just can write whatever they want ?????)
when I try to create this SP I get: "incorrect syntax near @MyResult" I have tried INT and different variable names, but get same error.
CREATE PROCEDURE sp_IsValidLogon @UserName varchar(16), @Password varchar(16) , @MyResult varchar(3) OUTPUT As if exists(Select * From User_Table Where UserName = @UserName And Password = @Password) begin @MyResult = 1 end else begin INSERT INTO FailedLogons(UserName, Password) values(@UserName, @Password)
declare @totalFails int Select @totalFails = Count(*) From FailedLogons Where UserName = @UserName And dtFailed > GetDate()-1
if (@totalFails > 5) UPDATE User_Table Set Active = 0 Where UserName = @UserName
if(@b='1') begin select * into #example from example select * from #example drop table #example end else begin select * into #example from example select * from #example drop table #example end With syntax check, I always get "'#example already exist'" But why? Just because of two "select into temp table" operation? I am confused. Thank you.
I am trying to write a SQL Server query that archives x-days old data from [Archive].[TestToDelete] to [Working].[TestToDelete]table. I want to check that if the records on ArchiveTable do not exist then insert then deleted...which will be converted to a proc later.. archives x-days old data from [Working].[TestToDelete] to [Archive].[TestToDelete] table */Here is the table definition, it is the same for both working and archive tables. There are indexes on: IpAddress, Logdate, Server, User and Sysdate (clustered).
CREATE TABLE [Archive].[TestToDelete]([Server] [varchar](16) NOT NULL,[Logdate] [datetime] NOT NULL,[IpAddress] [varchar](16) NOT NULL,[Login] [varchar](64) NULL,[User] [varchar](64) NULL,[Sysdate] [datetime] NULL,[Request] [text] NULL,[Status] [varchar](64) NULL,[Length] [varchar](128) NULL,[Referer] [varchar](1024) NULL,[Agent] [varchar](1024) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]/* the syntax, which will be converted to proc is: */SET NOCOUNT ON;DECLARE @Time DATETIME,@R INT,@ErrMessage NVARCHAR(1024)SET @R = 1/* set @Time to one day old data */SET @Time =
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).
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 DECLARE @RtnValue smallintSET @RtnValue = 0 DECLARE @RIndex smallintSET @RIndex = 20 DECLARE @ColumnName varchar(10)SET @ColumnName = 'Column0' DECLARE @MySelectString varchar(200)SET @MySelectString0 = 'SELECT @RtnValue = ( SELECT ' + @ColumnName + ' FROM MyTable WHERE RowIndex = ' + @RIndex + ' )'--SET @MySelectString1 = 'SELECT @RtnValue = ( SELECT ' + @ColumnName + ' FROM MyTable WHERE RowIndex = 1 )' EXEC( @MySelectString0 )--EXEC( @MySelectString1 ) @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. Thank you in advance.
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
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
I have a dynamic query in the stored procedure, and the code looks something like this:
SET @section_test = 'SELECT sectioncode FROM ' + @tablename + ' WHERE sectioncode = "' + @condition + '"' EXEC (@section_test)
What I need to do is try to check if the query returns any values using EXISTS (possibly), but at the same time I don't want to return the results of that dynamic query's select statement in my stored procedure. Is it possible?
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!