Dynamic Sql Syntax Check

May 14, 2008

CREATE TABLE #XYZ(
NTUser varchar(20),
FullNTName varchar(50),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(40),
Rolename varchar(30))

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??

View 2 Replies


ADVERTISEMENT

Check The Syntax

Oct 12, 2004

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

View 1 Replies View Related

Check SQL Syntax

Oct 27, 2004

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 ?????)

View 5 Replies View Related

Check The Syntax

Jan 10, 2008

default is tha constraint?
if i want to add default value to my column this is corect?

alter table tab1 alter column a add default 0

View 2 Replies View Related

Please Check This SP Syntax - Need More Eyes!

Sep 13, 2007

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

@MyResult = 0
end

View 3 Replies View Related

How SQL Server Do Syntax Check?

Aug 23, 2007

declare @b nvarchar(1)
set @b = '1'


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.

View 5 Replies View Related

Transact SQL :: Syntax Check For Archiving A Table

May 12, 2015

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 =

[code]....

View 8 Replies View Related

Syntax Check Needed On Case Statement

Feb 26, 2008

Hello experts!

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.


Any help is appreciated.

View 3 Replies View Related

T-SQL Syntax To Check For Nulls And Empty In One Statement

May 5, 2008

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).

IF @agent IS NULL OR @agent = ''



Thanks!

View 1 Replies View Related

Dynamic SQL Syntax H_ll Please Help

May 12, 2005

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.

View 2 Replies View Related

Syntax Error On Dynamic SP

Feb 14, 2004

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....

ALTER PROCEDURE sp_gener_queue2
@ccr char(20),
@techchar(10),
@zonechar(5),
@statuschar(5),
@beginchar(10),
@endchar(10),
@sort char(10)

AS

DECLARE @SQLString varchar(5000)

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

exec(@SQLString)

View 6 Replies View Related

What Is Dynamic Sql Syntax Of This Query

Feb 27, 2008

Hi,

I am using a simple if staement in a dynamic sql query what is its synax.

if @docType ='Doctor'
begin
select * from @tablename
end

Ranjeet Kumar Singh

View 3 Replies View Related

MDX Dynamic Parameter Syntax

Nov 12, 2007



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

{ ([Division_Plant]].[Hierarchy]].[Plant Desc]].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(([

+ " Parameters ! DivisionPlantDivisionDesc.Value + "[), CONSTRAINED) ) ON COLUMNS FROM [New GOLD]])

WHERE ( IIF( STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value

+ " [), CONSTRAINED).Count = 1, STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value + " [),

CONSTRAINED), [Division_Plant]].[Division Desc]].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS'

View 6 Replies View Related

How To Check If Results Of Dynamic Query Exist

Dec 27, 2001

Hello,

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?

Thanks.

Regards,
Katya

View 1 Replies View Related

Check Multiple Parameter Instead Of Dynamic Query?

Oct 20, 2014

how to check multiple parameter instead of dynamic query.

View 1 Replies View Related

Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)

Nov 1, 2007

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.


CREATE PROCEDURE [dbo].[sp_try]

@TempTable varchar(25)


AS

DECLARE @SQL nvarchar(MAX)

DECLARE @SQLINSERT nvarchar(MAX)



BEGIN


--create temp table

SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +

'(

ContactName varchar (40) NOT NULL ,

ContactId varchar (30) NOT NULL ,

ContactrMessage varchar (100) NOT NULL,



)'

EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable


SELECT @sql= 'Select * from table'




SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )

SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition

EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable

View 8 Replies View Related

SQL Server 2008 :: Dynamic Script Error - Incorrect Syntax Near Keyword FOR

Apr 17, 2015

declare @sql nvarchar(MAX)
SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
quotename(s.name) + '.' + quotename(o.name) +
' WITH FULLSCAN; ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)

The below Dynamic TSQL throws Error:

Error:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FOR'.

USE master

GO
DECLARE @str varchar(max), @sql nvarchar(MAX), @dbName nvarchar(max);

SET @dbName = 'user_db';

PRINT N'CHECKING DATABASE ' + @dbName;
SET @sql = 'USE ' + @dbname + ';' + '(SELECT '+'''UPDATE STATISTICS ''' + '+ ' + 'quotename(s.name)'+ '+' + '''.''' + '+' + 'quotename(o.name)' + '+' + '''WITH FULLSCAN; ''' + ' AS [text()]
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type ' +'= ' + '''U'' FOR XML PATH('' ''),TYPE).value(''.'''+ ','+ '''nvarchar(MAX)'''+')'
Print @sql
EXEC (@sql)

Not sure, why this is an error at FOR...

View 7 Replies View Related

Data Access :: How To Check All Connection Automatically During Routine Check By Using Batch File

May 20, 2015

I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.

View 5 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

Dec 14, 2003

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

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()


End Sub

Thankyou in advance.

View 3 Replies View Related

Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?

Mar 31, 2008

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?

Thanks!

View 4 Replies View Related

Converting Rrom Access Syntax To Sql Syntax

Sep 23, 2007


Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',

)
)
)
)
) 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.






Your input will be appreciated

Thank you

View 5 Replies View Related

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

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

View 12 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

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

View 3 Replies View Related

Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?

May 2, 2007

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?

View 4 Replies View Related

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

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]....

View 1 Replies View Related

Mixing Dynamic SQL With Non-Dynamic In Stored Proc

Mar 24, 2007

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.

View 10 Replies View Related

Dynamic Cursor/ Dynamic SQL Statement

Oct 24, 2004

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 ':'.

View 2 Replies View Related

Dynamic Source And Dynamic Destination

Apr 15, 2008

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

View 14 Replies View Related

SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output

Mar 2, 2014

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.

View 3 Replies View Related

Help Please (Check File Exists/ Archive File/ Check If File Empty)

Mar 10, 2008



Hello World,

I'm new to SSIS and would like a little assistance getting started, if possible...


Here is what I want to do:


Check if file exist (C:DTS UpgradeFilexxx.txt) --->

Archive file (C:DTS UpgradeArchive) --->

Check if file has data (true or false)


AND/OR

If there are any good website that have good direction, let me know


Thanks in advance for your help!!!

View 5 Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

May 27, 2008

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)
 
End Sub

View 2 Replies View Related

Dynamic Columns For Dynamic SQL

Mar 9, 2007

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!



Please advise.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved