Tuesday, March 27, 2012
Dynamic Query Problem
If I try to run it with QA, I get the following message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FCST'.
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...
However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.
What could I be missing?
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
Dec_07=Dec_fcst
FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT,
Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst,
Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst
FROM PLAN_SHIP INNER JOIN BOD_HEADER
ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX
WHERE (SCEN_ID = 1) AND ([Year] = 2007)
) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
WHERE OPSPLAN.SRCPLAN = 'SHIP'I'd sic the SQL Profiler on this fella. My suspicion is that the UPDATE is being mis-parsed, possibly because of a syntax error within the previous SQL statement. Profiler ought to give you some clues if that is the case. No outright answers, just clues, but that's more than you have now.
-PatP|||OK, I read up on it in BOL, and figured out how to get profiler running.
I found the line where that particular statement is executing.
I'm looking at StmtStarting and StmtCompleted. Is there something in
particular that I should be looking for?|||Since the string "FCST" does not appear independently in your code, but only in conjuction with a month and an underscore character, I'd say that somewhere and underscore character is being dropped.
Set the dynamic sql statement to print rather than execute, and bump up the Max characters setting in the Query Analyzer Options. Then see what code is actually being executed.|||Yeah, that "FCST" was throwing me, too. All of the instances in my string
are "fcst" not "FCST". Anyway, after pouring through the sproc over and over, I found a PRINT statement that was causing the completely valild statement to display under the error message. It wasn't the cause at all,
it was a previous statement. After I eliminated that, it was easy to narrow it down.
Thanks
Thursday, March 22, 2012
Dynamic Parameter
I want to create a report with two parameters.
Parameter 1 Options: Employee, Department or Job
Parameter 2 Options: When user selects Employee from parameter 1, all
employee names will be made available to select in parameter 2, When user
selects department from parameter 1, all department names will be made
available to select in parameter 2.
How would one doe this? Any help is appreciated.I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
and data sets returned.
I changed the names of my data source and data sets. It should still work
against a Northwind DB.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds1">
<rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=servername;initial
catalog=Northwind</ConnectString>
</ConnectionProperties>
</DataSource>
</DataSources>
<rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
<Body>
<ReportItems>
<Textbox Name="textbox_2">
<Style>
<BackgroundColor>Yellow</BackgroundColor>
<TextAlign>Left</TextAlign>
<FontSize>9pt</FontSize>
</Style>
<Height>.5 in</Height>
<Width>3 in</Width>
<CanGrow>true</CanGrow>
<Value>=Fields!TestResult.Value
+ "********Parameters CustomerIDParam Value: " +
Parameters!CustomerIDParam.Value
+ "********Parameters EmployeeIDParam Value: " +
Str(Parameters!EmployeeIDParam.Value)
</Value>
<Top>.5 in</Top>
<Left>0 in</Left>
</Textbox>
<Textbox Name="textbox_1">
<Style>
<BackgroundColor>Red</BackgroundColor>
<FontSize>14pt</FontSize>
</Style>
<Height>.5 in</Height>
<Width>3 in</Width>
<CanGrow>true</CanGrow>
<Value>Test Result Below:</Value>
<Top>0 in</Top>
<Left>0 in</Left>
</Textbox>
</ReportItems>
<Height>1.705in</Height>
</Body>
<rd:DrawGrid>true</rd:DrawGrid>
<DataSets>
<DataSet Name="mydataset">
<Fields>
<Field Name="TestResult">
<DataField>TestResult</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>ds1</DataSourceName>
<CommandType>Text</CommandType>
<CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
WHERE EmployeeID = @.EmployeeIDParam AND CustomerID =@.CustomerIDParam</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<QueryParameters>
<QueryParameter Name="@.EmployeeIDParam">
<Value>=Parameters!EmployeeIDParam.Value</Value>
</QueryParameter>
<QueryParameter Name="@.CustomerIDParam">
<Value>=Parameters!CustomerIDParam.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
<DataSet Name="mydataset2">
<Fields>
<Field Name="EmployeeID">
<DataField>EmployeeID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>ds1</DataSourceName>
<CommandType>Text</CommandType>
<CommandText>SELECT DISTINCT EmployeeID FROM Employees
ORDER BY EmployeeID ASC</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="mydataset3">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>ds1</DataSourceName>
<CommandType>Text</CommandType>
<CommandText>SELECT DISTINCT CustomerID FROM Orders
WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<QueryParameters>
<QueryParameter Name="@.EmployeeIDParam">
<Value>=Parameters!EmployeeIDParam.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<Language>en-US</Language>
<RightMargin>1in</RightMargin>
<BottomMargin>1in</BottomMargin>
<TopMargin>1in</TopMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Width>5in</Width>
<LeftMargin>1in</LeftMargin>
<ReportParameters>
<ReportParameter Name="EmployeeID">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>1</Value>
</Values>
</DefaultValue>
<ValidValues>
<DataSetReference>
<DataSetName>mydataset2</DataSetName>
<ValueField>EmployeeID</ValueField>
<LabelField>EmployeeID</LabelField>
</DataSetReference>
</ValidValues>
<Prompt>Employee ID :</Prompt>
</ReportParameter>
<ReportParameter Name="EmployeeIDParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=Parameters!EmployeeID.Value</Value>
</Values>
</DefaultValue>
<Prompt> </Prompt>
</ReportParameter>
<ReportParameter Name="CustomerIDParam">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>VINET</Value>
</Values>
</DefaultValue>
<ValidValues>
<DataSetReference>
<DataSetName>mydataset3</DataSetName>
<ValueField>CustomerID</ValueField>
<LabelField>CustomerID</LabelField>
</DataSetReference>
</ValidValues>
<Prompt>Customer ID :</Prompt>
</ReportParameter>
</ReportParameters>
</Report>
--
| Thread-Topic: Dynamic Parameter
| thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==| X-WBNR-Posting-Host: 70.66.0.254
| From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
| Subject: Dynamic Parameter
| Date: Fri, 28 Jan 2005 14:15:01 -0800
| Lines: 12
| Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| My DB has 3 tables Employee, Department, Job
|
| I want to create a report with two parameters.
|
| Parameter 1 Options: Employee, Department or Job
|
| Parameter 2 Options: When user selects Employee from parameter 1, all
| employee names will be made available to select in parameter 2, When
user
| selects department from parameter 1, all department names will be made
| available to select in parameter 2.
|
| How would one doe this? Any help is appreciated.
||||It is not necessary to dive into this RDL code.
Go to the online help from Reporting Services. There is a chapter Create
Report (or similare) and there is a description of using Parameters. On the
end of this article is your question answered in the best way.
""Brad Syputa - MS"" wrote:
> I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> and data sets returned.
> I changed the names of my data source and data sets. It should still work
> against a Northwind DB.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="ds1">
> <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=servername;initial
> catalog=Northwind</ConnectString>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> <Body>
> <ReportItems>
> <Textbox Name="textbox_2">
> <Style>
> <BackgroundColor>Yellow</BackgroundColor>
> <TextAlign>Left</TextAlign>
> <FontSize>9pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Fields!TestResult.Value
> + "********Parameters CustomerIDParam Value: " +
> Parameters!CustomerIDParam.Value
> + "********Parameters EmployeeIDParam Value: " +
> Str(Parameters!EmployeeIDParam.Value)
> </Value>
> <Top>.5 in</Top>
> <Left>0 in</Left>
> </Textbox>
> <Textbox Name="textbox_1">
> <Style>
> <BackgroundColor>Red</BackgroundColor>
> <FontSize>14pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>Test Result Below:</Value>
> <Top>0 in</Top>
> <Left>0 in</Left>
> </Textbox>
> </ReportItems>
> <Height>1.705in</Height>
> </Body>
> <rd:DrawGrid>true</rd:DrawGrid>
> <DataSets>
> <DataSet Name="mydataset">
> <Fields>
> <Field Name="TestResult">
> <DataField>TestResult</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => @.CustomerIDParam</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.CustomerIDParam">
> <Value>=Parameters!CustomerIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> <DataSet Name="mydataset2">
> <Fields>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> ORDER BY EmployeeID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> <DataSet Name="mydataset3">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT CustomerID FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> </DataSets>
> <Language>en-US</Language>
> <RightMargin>1in</RightMargin>
> <BottomMargin>1in</BottomMargin>
> <TopMargin>1in</TopMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Width>5in</Width>
> <LeftMargin>1in</LeftMargin>
> <ReportParameters>
> <ReportParameter Name="EmployeeID">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>1</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset2</DataSetName>
> <ValueField>EmployeeID</ValueField>
> <LabelField>EmployeeID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Employee ID :</Prompt>
> </ReportParameter>
> <ReportParameter Name="EmployeeIDParam">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>=Parameters!EmployeeID.Value</Value>
> </Values>
> </DefaultValue>
> <Prompt> </Prompt>
> </ReportParameter>
> <ReportParameter Name="CustomerIDParam">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>VINET</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset3</DataSetName>
> <ValueField>CustomerID</ValueField>
> <LabelField>CustomerID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Customer ID :</Prompt>
> </ReportParameter>
> </ReportParameters>
> </Report>
> --
> | Thread-Topic: Dynamic Parameter
> | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> | X-WBNR-Posting-Host: 70.66.0.254
> | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> | Subject: Dynamic Parameter
> | Date: Fri, 28 Jan 2005 14:15:01 -0800
> | Lines: 12
> | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | My DB has 3 tables Employee, Department, Job
> |
> | I want to create a report with two parameters.
> |
> | Parameter 1 Options: Employee, Department or Job
> |
> | Parameter 2 Options: When user selects Employee from parameter 1, all
> | employee names will be made available to select in parameter 2, When
> user
> | selects department from parameter 1, all department names will be made
> | available to select in parameter 2.
> |
> | How would one doe this? Any help is appreciated.
> |
>|||I had a look at the reporting services books online and it had a description
on how to use a basiv dynamic parameter...my requirement is a bit more
advanced than the given example.
"Dev Main" wrote:
> It is not necessary to dive into this RDL code.
> Go to the online help from Reporting Services. There is a chapter Create
> Report (or similare) and there is a description of using Parameters. On the
> end of this article is your question answered in the best way.
>
> ""Brad Syputa - MS"" wrote:
> > I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> > and data sets returned.
> >
> > I changed the names of my data source and data sets. It should still work
> > against a Northwind DB.
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <DataSources>
> > <DataSource Name="ds1">
> >
> > <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>data source=servername;initial
> > catalog=Northwind</ConnectString>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> > <Body>
> > <ReportItems>
> > <Textbox Name="textbox_2">
> > <Style>
> > <BackgroundColor>Yellow</BackgroundColor>
> > <TextAlign>Left</TextAlign>
> > <FontSize>9pt</FontSize>
> > </Style>
> > <Height>.5 in</Height>
> > <Width>3 in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!TestResult.Value
> > + "********Parameters CustomerIDParam Value: " +
> > Parameters!CustomerIDParam.Value
> > + "********Parameters EmployeeIDParam Value: " +
> > Str(Parameters!EmployeeIDParam.Value)
> > </Value>
> > <Top>.5 in</Top>
> > <Left>0 in</Left>
> > </Textbox>
> > <Textbox Name="textbox_1">
> > <Style>
> > <BackgroundColor>Red</BackgroundColor>
> > <FontSize>14pt</FontSize>
> > </Style>
> > <Height>.5 in</Height>
> > <Width>3 in</Width>
> > <CanGrow>true</CanGrow>
> > <Value>Test Result Below:</Value>
> > <Top>0 in</Top>
> > <Left>0 in</Left>
> > </Textbox>
> > </ReportItems>
> > <Height>1.705in</Height>
> > </Body>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <DataSets>
> > <DataSet Name="mydataset">
> > <Fields>
> > <Field Name="TestResult">
> > <DataField>TestResult</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="CustomerID">
> > <DataField>CustomerID</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="EmployeeID">
> > <DataField>EmployeeID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>ds1</DataSourceName>
> > <CommandType>Text</CommandType>
> > <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> > WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => > @.CustomerIDParam</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > <QueryParameters>
> > <QueryParameter Name="@.EmployeeIDParam">
> > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > </QueryParameter>
> > <QueryParameter Name="@.CustomerIDParam">
> > <Value>=Parameters!CustomerIDParam.Value</Value>
> > </QueryParameter>
> > </QueryParameters>
> > </Query>
> > </DataSet>
> > <DataSet Name="mydataset2">
> > <Fields>
> > <Field Name="EmployeeID">
> > <DataField>EmployeeID</DataField>
> > <rd:TypeName>System.Int32</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>ds1</DataSourceName>
> > <CommandType>Text</CommandType>
> > <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> > ORDER BY EmployeeID ASC</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > </Query>
> > </DataSet>
> > <DataSet Name="mydataset3">
> > <Fields>
> > <Field Name="CustomerID">
> > <DataField>CustomerID</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>ds1</DataSourceName>
> > <CommandType>Text</CommandType>
> > <CommandText>SELECT DISTINCT CustomerID FROM Orders
> > WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > <QueryParameters>
> > <QueryParameter Name="@.EmployeeIDParam">
> > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > </QueryParameter>
> > </QueryParameters>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <Language>en-US</Language>
> > <RightMargin>1in</RightMargin>
> > <BottomMargin>1in</BottomMargin>
> > <TopMargin>1in</TopMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <Width>5in</Width>
> > <LeftMargin>1in</LeftMargin>
> > <ReportParameters>
> > <ReportParameter Name="EmployeeID">
> > <DataType>Integer</DataType>
> > <DefaultValue>
> > <Values>
> > <Value>1</Value>
> > </Values>
> > </DefaultValue>
> > <ValidValues>
> > <DataSetReference>
> > <DataSetName>mydataset2</DataSetName>
> > <ValueField>EmployeeID</ValueField>
> > <LabelField>EmployeeID</LabelField>
> > </DataSetReference>
> > </ValidValues>
> > <Prompt>Employee ID :</Prompt>
> > </ReportParameter>
> > <ReportParameter Name="EmployeeIDParam">
> > <DataType>Integer</DataType>
> > <DefaultValue>
> > <Values>
> > <Value>=Parameters!EmployeeID.Value</Value>
> > </Values>
> > </DefaultValue>
> > <Prompt> </Prompt>
> > </ReportParameter>
> > <ReportParameter Name="CustomerIDParam">
> > <DataType>String</DataType>
> > <DefaultValue>
> > <Values>
> > <Value>VINET</Value>
> > </Values>
> > </DefaultValue>
> > <ValidValues>
> > <DataSetReference>
> > <DataSetName>mydataset3</DataSetName>
> > <ValueField>CustomerID</ValueField>
> > <LabelField>CustomerID</LabelField>
> > </DataSetReference>
> > </ValidValues>
> > <Prompt>Customer ID :</Prompt>
> > </ReportParameter>
> > </ReportParameters>
> > </Report>
> > --
> > | Thread-Topic: Dynamic Parameter
> > | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> > | X-WBNR-Posting-Host: 70.66.0.254
> > | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> > | Subject: Dynamic Parameter
> > | Date: Fri, 28 Jan 2005 14:15:01 -0800
> > | Lines: 12
> > | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> > | MIME-Version: 1.0
> > | Content-Type: text/plain;
> > | charset="Utf-8"
> > | Content-Transfer-Encoding: 7bit
> > | X-Newsreader: Microsoft CDO for Windows 2000
> > | Content-Class: urn:content-classes:message
> > | Importance: normal
> > | Priority: normal
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | My DB has 3 tables Employee, Department, Job
> > |
> > | I want to create a report with two parameters.
> > |
> > | Parameter 1 Options: Employee, Department or Job
> > |
> > | Parameter 2 Options: When user selects Employee from parameter 1, all
> > | employee names will be made available to select in parameter 2, When
> > user
> > | selects department from parameter 1, all department names will be made
> > | available to select in parameter 2.
> > |
> > | How would one doe this? Any help is appreciated.
> > |
> >
> >|||Brad:
I copied the rdl into my designer anf get te following error:
Deserialization failed: The Report element was not found.
""Brad Syputa - MS"" wrote:
> I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> and data sets returned.
> I changed the names of my data source and data sets. It should still work
> against a Northwind DB.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="ds1">
> <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=servername;initial
> catalog=Northwind</ConnectString>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> <Body>
> <ReportItems>
> <Textbox Name="textbox_2">
> <Style>
> <BackgroundColor>Yellow</BackgroundColor>
> <TextAlign>Left</TextAlign>
> <FontSize>9pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Fields!TestResult.Value
> + "********Parameters CustomerIDParam Value: " +
> Parameters!CustomerIDParam.Value
> + "********Parameters EmployeeIDParam Value: " +
> Str(Parameters!EmployeeIDParam.Value)
> </Value>
> <Top>.5 in</Top>
> <Left>0 in</Left>
> </Textbox>
> <Textbox Name="textbox_1">
> <Style>
> <BackgroundColor>Red</BackgroundColor>
> <FontSize>14pt</FontSize>
> </Style>
> <Height>.5 in</Height>
> <Width>3 in</Width>
> <CanGrow>true</CanGrow>
> <Value>Test Result Below:</Value>
> <Top>0 in</Top>
> <Left>0 in</Left>
> </Textbox>
> </ReportItems>
> <Height>1.705in</Height>
> </Body>
> <rd:DrawGrid>true</rd:DrawGrid>
> <DataSets>
> <DataSet Name="mydataset">
> <Fields>
> <Field Name="TestResult">
> <DataField>TestResult</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => @.CustomerIDParam</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.CustomerIDParam">
> <Value>=Parameters!CustomerIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> <DataSet Name="mydataset2">
> <Fields>
> <Field Name="EmployeeID">
> <DataField>EmployeeID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> ORDER BY EmployeeID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> <DataSet Name="mydataset3">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>ds1</DataSourceName>
> <CommandType>Text</CommandType>
> <CommandText>SELECT DISTINCT CustomerID FROM Orders
> WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <QueryParameters>
> <QueryParameter Name="@.EmployeeIDParam">
> <Value>=Parameters!EmployeeIDParam.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
> </DataSet>
> </DataSets>
> <Language>en-US</Language>
> <RightMargin>1in</RightMargin>
> <BottomMargin>1in</BottomMargin>
> <TopMargin>1in</TopMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Width>5in</Width>
> <LeftMargin>1in</LeftMargin>
> <ReportParameters>
> <ReportParameter Name="EmployeeID">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>1</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset2</DataSetName>
> <ValueField>EmployeeID</ValueField>
> <LabelField>EmployeeID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Employee ID :</Prompt>
> </ReportParameter>
> <ReportParameter Name="EmployeeIDParam">
> <DataType>Integer</DataType>
> <DefaultValue>
> <Values>
> <Value>=Parameters!EmployeeID.Value</Value>
> </Values>
> </DefaultValue>
> <Prompt> </Prompt>
> </ReportParameter>
> <ReportParameter Name="CustomerIDParam">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>VINET</Value>
> </Values>
> </DefaultValue>
> <ValidValues>
> <DataSetReference>
> <DataSetName>mydataset3</DataSetName>
> <ValueField>CustomerID</ValueField>
> <LabelField>CustomerID</LabelField>
> </DataSetReference>
> </ValidValues>
> <Prompt>Customer ID :</Prompt>
> </ReportParameter>
> </ReportParameters>
> </Report>
> --
> | Thread-Topic: Dynamic Parameter
> | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> | X-WBNR-Posting-Host: 70.66.0.254
> | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> | Subject: Dynamic Parameter
> | Date: Fri, 28 Jan 2005 14:15:01 -0800
> | Lines: 12
> | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | My DB has 3 tables Employee, Department, Job
> |
> | I want to create a report with two parameters.
> |
> | Parameter 1 Options: Employee, Department or Job
> |
> | Parameter 2 Options: When user selects Employee from parameter 1, all
> | employee names will be made available to select in parameter 2, When
> user
> | selects department from parameter 1, all department names will be made
> | available to select in parameter 2.
> |
> | How would one doe this? Any help is appreciated.
> |
>|||Please describe your exact requirement. Becouse your sample is to solve with
the description in the online docu. Isn't it?
"SAcanuck" wrote:
> I had a look at the reporting services books online and it had a description
> on how to use a basiv dynamic parameter...my requirement is a bit more
> advanced than the given example.
> "Dev Main" wrote:
> > It is not necessary to dive into this RDL code.
> >
> > Go to the online help from Reporting Services. There is a chapter Create
> > Report (or similare) and there is a description of using Parameters. On the
> > end of this article is your question answered in the best way.
> >
> >
> >
> > ""Brad Syputa - MS"" wrote:
> >
> > > I did this for a Northwind DB. Here is how my RDL looked. Multiple queries
> > > and data sets returned.
> > >
> > > I changed the names of my data source and data sets. It should still work
> > > against a Northwind DB.
> > >
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <Report
> > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > > tion"
> > > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > <DataSources>
> > > <DataSource Name="ds1">
> > >
> > > <rd:DataSourceID>2122d420-3823-467b-8d49-89744c792684</rd:DataSourceID>
> > > <ConnectionProperties>
> > > <DataProvider>SQL</DataProvider>
> > > <ConnectString>data source=servername;initial
> > > catalog=Northwind</ConnectString>
> > > </ConnectionProperties>
> > > </DataSource>
> > > </DataSources>
> > > <rd:ReportID>48c77212-85fb-46c9-9347-e0ecf74aa394</rd:ReportID>
> > > <Body>
> > > <ReportItems>
> > > <Textbox Name="textbox_2">
> > > <Style>
> > > <BackgroundColor>Yellow</BackgroundColor>
> > > <TextAlign>Left</TextAlign>
> > > <FontSize>9pt</FontSize>
> > > </Style>
> > > <Height>.5 in</Height>
> > > <Width>3 in</Width>
> > > <CanGrow>true</CanGrow>
> > > <Value>=Fields!TestResult.Value
> > > + "********Parameters CustomerIDParam Value: " +
> > > Parameters!CustomerIDParam.Value
> > > + "********Parameters EmployeeIDParam Value: " +
> > > Str(Parameters!EmployeeIDParam.Value)
> > > </Value>
> > > <Top>.5 in</Top>
> > > <Left>0 in</Left>
> > > </Textbox>
> > > <Textbox Name="textbox_1">
> > > <Style>
> > > <BackgroundColor>Red</BackgroundColor>
> > > <FontSize>14pt</FontSize>
> > > </Style>
> > > <Height>.5 in</Height>
> > > <Width>3 in</Width>
> > > <CanGrow>true</CanGrow>
> > > <Value>Test Result Below:</Value>
> > > <Top>0 in</Top>
> > > <Left>0 in</Left>
> > > </Textbox>
> > > </ReportItems>
> > > <Height>1.705in</Height>
> > > </Body>
> > > <rd:DrawGrid>true</rd:DrawGrid>
> > > <DataSets>
> > > <DataSet Name="mydataset">
> > > <Fields>
> > > <Field Name="TestResult">
> > > <DataField>TestResult</DataField>
> > > <rd:TypeName>System.String</rd:TypeName>
> > > </Field>
> > > <Field Name="CustomerID">
> > > <DataField>CustomerID</DataField>
> > > <rd:TypeName>System.String</rd:TypeName>
> > > </Field>
> > > <Field Name="EmployeeID">
> > > <DataField>EmployeeID</DataField>
> > > <rd:TypeName>System.Int32</rd:TypeName>
> > > </Field>
> > > </Fields>
> > > <Query>
> > > <DataSourceName>ds1</DataSourceName>
> > > <CommandType>Text</CommandType>
> > > <CommandText>SELECT * , 'Test Passed' as "TestResult" FROM Orders
> > > WHERE EmployeeID = @.EmployeeIDParam AND CustomerID => > > @.CustomerIDParam</CommandText>
> > > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > <QueryParameters>
> > > <QueryParameter Name="@.EmployeeIDParam">
> > > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > > </QueryParameter>
> > > <QueryParameter Name="@.CustomerIDParam">
> > > <Value>=Parameters!CustomerIDParam.Value</Value>
> > > </QueryParameter>
> > > </QueryParameters>
> > > </Query>
> > > </DataSet>
> > > <DataSet Name="mydataset2">
> > > <Fields>
> > > <Field Name="EmployeeID">
> > > <DataField>EmployeeID</DataField>
> > > <rd:TypeName>System.Int32</rd:TypeName>
> > > </Field>
> > > </Fields>
> > > <Query>
> > > <DataSourceName>ds1</DataSourceName>
> > > <CommandType>Text</CommandType>
> > > <CommandText>SELECT DISTINCT EmployeeID FROM Employees
> > > ORDER BY EmployeeID ASC</CommandText>
> > > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > </Query>
> > > </DataSet>
> > > <DataSet Name="mydataset3">
> > > <Fields>
> > > <Field Name="CustomerID">
> > > <DataField>CustomerID</DataField>
> > > <rd:TypeName>System.String</rd:TypeName>
> > > </Field>
> > > </Fields>
> > > <Query>
> > > <DataSourceName>ds1</DataSourceName>
> > > <CommandType>Text</CommandType>
> > > <CommandText>SELECT DISTINCT CustomerID FROM Orders
> > > WHERE EmployeeID = @.EmployeeIDParam ORDER BY CustomerID ASC</CommandText>
> > > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > <QueryParameters>
> > > <QueryParameter Name="@.EmployeeIDParam">
> > > <Value>=Parameters!EmployeeIDParam.Value</Value>
> > > </QueryParameter>
> > > </QueryParameters>
> > > </Query>
> > > </DataSet>
> > > </DataSets>
> > > <Language>en-US</Language>
> > > <RightMargin>1in</RightMargin>
> > > <BottomMargin>1in</BottomMargin>
> > > <TopMargin>1in</TopMargin>
> > > <rd:SnapToGrid>true</rd:SnapToGrid>
> > > <Width>5in</Width>
> > > <LeftMargin>1in</LeftMargin>
> > > <ReportParameters>
> > > <ReportParameter Name="EmployeeID">
> > > <DataType>Integer</DataType>
> > > <DefaultValue>
> > > <Values>
> > > <Value>1</Value>
> > > </Values>
> > > </DefaultValue>
> > > <ValidValues>
> > > <DataSetReference>
> > > <DataSetName>mydataset2</DataSetName>
> > > <ValueField>EmployeeID</ValueField>
> > > <LabelField>EmployeeID</LabelField>
> > > </DataSetReference>
> > > </ValidValues>
> > > <Prompt>Employee ID :</Prompt>
> > > </ReportParameter>
> > > <ReportParameter Name="EmployeeIDParam">
> > > <DataType>Integer</DataType>
> > > <DefaultValue>
> > > <Values>
> > > <Value>=Parameters!EmployeeID.Value</Value>
> > > </Values>
> > > </DefaultValue>
> > > <Prompt> </Prompt>
> > > </ReportParameter>
> > > <ReportParameter Name="CustomerIDParam">
> > > <DataType>String</DataType>
> > > <DefaultValue>
> > > <Values>
> > > <Value>VINET</Value>
> > > </Values>
> > > </DefaultValue>
> > > <ValidValues>
> > > <DataSetReference>
> > > <DataSetName>mydataset3</DataSetName>
> > > <ValueField>CustomerID</ValueField>
> > > <LabelField>CustomerID</LabelField>
> > > </DataSetReference>
> > > </ValidValues>
> > > <Prompt>Customer ID :</Prompt>
> > > </ReportParameter>
> > > </ReportParameters>
> > > </Report>
> > > --
> > > | Thread-Topic: Dynamic Parameter
> > > | thread-index: AcUFhs+wyP3Cs1ONTxSLxYdNcM59XQ==> > > | X-WBNR-Posting-Host: 70.66.0.254
> > > | From: "=?Utf-8?B?U0FjYW51Y2s=?=" <SAcanuck@.discussions.microsoft.com>
> > > | Subject: Dynamic Parameter
> > > | Date: Fri, 28 Jan 2005 14:15:01 -0800
> > > | Lines: 12
> > > | Message-ID: <AE5AB9C9-7340-4ED3-9EC6-3F5DB22834AA@.microsoft.com>
> > > | MIME-Version: 1.0
> > > | Content-Type: text/plain;
> > > | charset="Utf-8"
> > > | Content-Transfer-Encoding: 7bit
> > > | X-Newsreader: Microsoft CDO for Windows 2000
> > > | Content-Class: urn:content-classes:message
> > > | Importance: normal
> > > | Priority: normal
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> > > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> > > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> > > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41264
> > > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > > |
> > > | My DB has 3 tables Employee, Department, Job
> > > |
> > > | I want to create a report with two parameters.
> > > |
> > > | Parameter 1 Options: Employee, Department or Job
> > > |
> > > | Parameter 2 Options: When user selects Employee from parameter 1, all
> > > | employee names will be made available to select in parameter 2, When
> > > user
> > > | selects department from parameter 1, all department names will be made
> > > | available to select in parameter 2.
> > > |
> > > | How would one doe this? Any help is appreciated.
> > > |
> > >
> > >
Monday, March 19, 2012
dynamic killer
I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:
declare @.id int
begin
set @.id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @.id
end
GO
It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@.id'."
Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?
Thanks in advance.
Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> Hi, guys!
> I want to build a scheduled job to kill any connections from server
> 'WS1187' built by some VB applications. The code reads something like
> this:
> declare @.id int
> begin
> set @.id = (select spid from sysprocesses where hostname='WS1187' and
> program_name='Visual Basic')
> kill @.id
> end
> GO
> It doesn't compile. The error message is
> "Server: Msg 170, Level 15, State 1, Line 5
> Line 4: Incorrect syntax near '@.id'."
> Is there any way we can kill a user process with a dynamic "spid"
> built based on certain business rules?
> Thanks in advance.
> Gary
Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:
exec('kill ' + convert(varchar, @.id))
Simon
Sunday, March 11, 2012
Dynamic From Clause
Q: Is there a way to make the table's name in the from clause dynamic?
I have a job (T-SQL statements) that makes copies of current month's
selected tables to our archive DB. All copies will have a time stamp like
TableName_yyyy_mm. What I always do is that I edit the T-SQL statements in
that job to reflect the current year and the current month for all statement
s
(find and replace of course).
This month will be,
Select * into archive.dbo.PEIF_2005_03 from data.dbo.PEIF
Next month will be,
Select * into archive.dbo.PEIF_2005_04 from data.dbo.PEIF
.. and so on
I have tried using variables for the time stamp, like:
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
Select * into archive.dbo.PEIF_@.yyyy_@.mm from data.dbo.PEIF (DOESN'T WORK)
Apparently @.variable only works for where clauses (at least in my case).
What I want (if possible) is that I don't need to update the job every month
and let the job does the work for me.
Please help,
Adrian T> Q: Is there a way to make the table's name in the from clause dynamic?
http://www.sommarskog.se/dynamic_sql.html|||you would want to look into the stored procedure sp_executesql in BOL.
Basically it allows you to say "exec sp_executesql @.mySQL" and whatever
is in the string of @.MySQL you've just built up it will generate an
execution plan for it, then run it.
Cheers
Will|||ur solution is almost there.
try this.. a small change.. hope this helps.
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
exec('Select * into archive.dbo.PEIF_' + @.yyyy + '_' + @.mm + ' from
data.dbo.PEIF')