Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts

Wednesday, March 28, 2012

Passing native SQL to Teradata

I want to add a step to an SSIS package to passthrough native Teradata sql statements so that they can be executed on Teradata. Does anyone know if I can use an "Execute SQL Task" to accomplish this? It should be very similar to passing native Oracle SQL to an Oracle server.

Hi SigEta!

Yes, you should be able to use the Execute SQL Task to do precisely that.

-David

|||

Didn't want to spend hours writing SQL to find out that I was planning a picnic for a rainy day! I'll be back if we can't figure this out... Thanks!!

Monday, March 26, 2012

Passing Index Value

I am attempting to string together insert statements in a stored procedure...

In tblCatalogue there is all the information on catalogues.

In tblArticles there is all the information on articles.

There is a key between the articles table and the catalogues, that reflect which catalogue a specific article belongs in.

I am trying to add a new article and column at the same time in a stored procedure. If a new record is created in the Catalogue table can that KeyCol value then be passed into the Insert statement for the Article table? Would this be done with a "SELECT KeyCol FROM INSERTED" trigger on the Catalogue table? If so how do you trap that output in a variable to be passed to the article insert?

All help and comments are appreciated.I'm not clear on the relationship between articles and catalogs. Must an article be in a catalog? Can an article be in more than one catalog?

It would also help to know what database engine you are targeting (Db2, Oracle, MS-SQL, etc). This can influence many design choices.

-PatP|||Apologies. I am using MSSQL 2000. An article can only appear in one catalog and must appear in a catalog.

The key here is that I am calling a SP, usp_ins_Article. Passed into the SP are two values, ArticleName, and CatalogName. The stored procedure checks to see if the CatalogName exists and if not it calls usp_ins_Catalog, passing in the catalog name. Once that is complete I need to have the value of the KeyCol for the new record. This is then inserted into the table (tblArticles) as a foreign key along with the ArticleName.

What I am hoping for is to have the Catalog SP return the KeyCol value into a variable in the Articles SP.

I hope this makes sense, and thanks for your quick reply.|||If your insert stored procedure concocts the KeyCol value using code, just keep a copy. If it uses an IDENTITY column, recover the value using @.@.IDENTITY.

In both your CREATE PROCEDURE paramenter list and your EXECUTE parameter list for the insert procedure, include the OUTPUT modifier for a parameter to return the value to the caller.

That should get you on your way!

-PatP

Wednesday, March 21, 2012

Passing back more than 1 output parameter to VBA code

I have a stored procedure which has 2 output parameters, namely @.RecCnt and
@.RetCode. In the stored procedure, I am using the SET statements to pass the
data back. I am calling the stored procedure from my VBA code. I use
objCmd.Execute options:=adExecuteNoRecords.
I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
Could any of you tell me what is wrong?
VBA Code:
--
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_addback_selectcount_CorpAcctCtr"
.NAME = "sp_addback_selectcount_CorpAcctCtr"
.CommandType = adCmdStoredProc
'Create parameter list for objCmd
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
3, vstrCorp)
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
5, vstrAcct)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
vstrCtr)
.Parameters.Append .CreateParameter("RecCnt", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
.Execute options:=adExecuteNoRecords
End With
If objCmd.Parameters("RetCode").Value Then
rlngRecCnt = objCmd.Parameters("RecCnt").Value
fnGetAddbackCnt = True
End If
--Store Procedure
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GOSoooorrryyy...Goofed up the code and stored procedure...
Here is the correct one:
VBA Code:
......
.....
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_lotusdata_load_from_recon"
.NAME = "sp_lotusdata_load_from_recon"
.CommandType = adCmdStoredProc
'Create parameter list for oCmd
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
Len(vstrAcct), vstrAcct)
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
Len(vstrCorp), vstrCorp)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
Len(vstrCtr), vstrCtr)
.Parameters.Append .CreateParameter("RecsAffected", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
'-- execute the proc
.Execute options:=adExecuteNoRecords
'-- return success if stored proc is successful
If .Parameters("RetCode").Value = True Then
rintRecsAffected = .Parameters("RecsAffected")
fnLoadLotusData = True
End If
....
...
Stored Procedure Code:
--
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GO
Sorry for the error.
Regards,
Paddy
"Paddy" wrote:

> I have a stored procedure which has 2 output parameters, namely @.RecCnt an
d
> @.RetCode. In the stored procedure, I am using the SET statements to pass t
he
> data back. I am calling the stored procedure from my VBA code. I use
> objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
> VBA Code:
> --
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
> .NAME = "sp_addback_selectcount_CorpAcctCtr"
> .CommandType = adCmdStoredProc
> 'Create parameter list for objCmd
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> 3, vstrCorp)
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> 5, vstrAcct)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
5,
> vstrCtr)
> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> .Execute options:=adExecuteNoRecords
> End With
>
> If objCmd.Parameters("RetCode").Value Then
> rlngRecCnt = objCmd.Parameters("RecCnt").Value
> fnGetAddbackCnt = True
> End If
> --Store Procedure
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO|||Paddy (Paddy@.discussions.microsoft.com) writes:
> I have a stored procedure which has 2 output parameters, namely @.RecCnt
> and @.RetCode. In the stored procedure, I am using the SET statements to
> pass the data back. I am calling the stored procedure from my VBA code.
> I use objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
How do you conclude that you can not retriev the RecCnt value?
I don't think it should really matter, but it is a good idea to align
the names in the parameters collection with the actual parameters names.
Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
@.RetCode. Furthermore there is one parameter missing. That is, each
stored procedure has a return value, which in ADO you declare as a the
first parameter and as adParamReturnValue. Then again, I think it's find
to not include that paraemeter.

> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
The sp_ prefix is reserved for system objects, and SQL Server first
looks in the master database for these. Don't use it, in your own code.

> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
I think adParamInputOutput are more appropriate as that is what they
are.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The above code always return RecsAffected value as 0. When I run the sp in
Query Analyser it shows the correct data.
Please help.
Regards,
Paddy
"Paddy" wrote:
> Soooorrryyy...Goofed up the code and stored procedure...
> Here is the correct one:
> VBA Code:
> ......
> .....
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_lotusdata_load_from_recon"
> .NAME = "sp_lotusdata_load_from_recon"
> .CommandType = adCmdStoredProc
> 'Create parameter list for oCmd
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> Len(vstrAcct), vstrAcct)
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> Len(vstrCorp), vstrCorp)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
> Len(vstrCtr), vstrCtr)
> .Parameters.Append .CreateParameter("RecsAffected", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> '-- execute the proc
> .Execute options:=adExecuteNoRecords
> '-- return success if stored proc is successful
> If .Parameters("RetCode").Value = True Then
> rintRecsAffected = .Parameters("RecsAffected")
> fnLoadLotusData = True
> End If
> ....
> ...
> Stored Procedure Code:
> --
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO
> Sorry for the error.
> Regards,
> Paddy
> "Paddy" wrote:
>|||Hi, Erland,
Please read my 2nd and 3rd message. I copied the wrong code in the message.
I posted the code which has problem in my second message.
I debugged the VBA code and know it is returning 0.
I knew about sp_ prefix, but used it for easily distinguish stored
procedures. I know there is some performance penalties.
Anyway, is there anything wrong in the way I am setting the output parameter
in the stored procedure?
Thanks.
Paddy
"Erland Sommarskog" wrote:

> Paddy (Paddy@.discussions.microsoft.com) writes:
> How do you conclude that you can not retriev the RecCnt value?
> I don't think it should really matter, but it is a good idea to align
> the names in the parameters collection with the actual parameters names.
> Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
> @.RetCode. Furthermore there is one parameter missing. That is, each
> stored procedure has a return value, which in ADO you declare as a the
> first parameter and as adParamReturnValue. Then again, I think it's find
> to not include that paraemeter.
>
> The sp_ prefix is reserved for system objects, and SQL Server first
> looks in the master database for these. Don't use it, in your own code.
>
> I think adParamInputOutput are more appropriate as that is what they
> are.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Paddy (Paddy@.discussions.microsoft.com) writes:
> The above code always return RecsAffected value as 0. When I run the sp in
> Query Analyser it shows the correct data.
But RetCode is still True then?
When you run from QA, I suspect that you run as as or dbo, but how
do run the application? Does that account have CREATE TABLE permissions?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, March 9, 2012

pass variables to the sql

Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SMYou can do this by saving all your update statments to .sql or .txt file.
so when ever u need to run the update just call the .sql or .txt file from Sql prompt

SQl>@.xx.sql or
SQl>@.c:\orawin\bin\aa.txt; Make sure you call the file from correct place.

If you want the values to be changed every time.U can do this as follws

UPDATE PS_TEST_TBL
SET TESTFIELD1 = &TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A; &TEST1A for numeric values and '&TEST1A' for char

So the system will prompt :Enter value for TEST1A

Originally posted by meelagupta
Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SM|||Another way would be to create a stored procedure with the update statements, and pass the values in as parameters.

Saturday, February 25, 2012

Pass DatabaseName as a Parameter - dynamic DB selection

Hi Eralper,
Thank you for the quick reply. But I think I am confused now as how or where
to write those statements.
The way I have been using reporting server is to create a dataset, create a
datasource in the dataset (or use a shared datasource), either write text
query or exec SP there. Then write the parameters used in the query in the
'Report Parameters' tab in VS.NET.
I am not sure if I followed it correctly, but if you are saying to create a
new DATASOURCE.. and write the source as:
= "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
In my situation, that is giving me errors.
On the other hand, running this " Exec
[SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
solve my problem as here we are giving DBName (Pubs) and that is static.
What I am trying is ... create a pull-don menu in the report and give the
user an option to select the particular database. Then the query is run and
results are returned for the query for the particular database selected.
I may not have understood your explanation but am still confused.
Thank you for your time and patience.
GJ
"eralper" wrote:
> Hi,
>
> I'm using a similar method for maintaining many report servers on different
> locations and each connecting to their own databases.
>
> I use the below text as dataset source
>
> = "Exec " & Parameters!LSQLServerName.Value & "." &
> Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> Parameters!ParameterOne.Value
>
> LSQLServerName parameter is the Linked SQL Server. So even on the same
> location you can change your sql server machine.
> Note that you will not need this parameter if your reporting database is on
> the same server with your datasource. Otherwise you have to declare a linked
> server on the server running reporting server databases.
>
> DatabaseName parameter is the catalog name in the database server.
>
> Then you pass the query (or sp with parameters)
>
> Actually you send at the end a similar query:
>
> " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
>
> I hope this helps.
>
> Eralper Yilmaz
> http://www.eralper.com
> http://www.kodyaz.com/default.aspx
>
>
> "GJ" wrote:
>
> > I have 10 Databases with same query to extract same info from all databases.
> > One thing I can do is to create 10 different datasets to get my results or
> > join 10 queries in one big query.
> >
> > But what I really would like to do is to create one dataset and pass a
> > database-name parameter so users can select the particular database from pull
> > down menu and view the results from that database. So basically it will be
> > running query for one database at a time. I tried to do it the same way you
> > create all parameters but of no use. I will appreciate any kind of help.
> > Example:
> >
> > Select manufacturer, sales
> > From @.DatabaseName.dbo.product FDP
> > Where
> > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> >
> > I can define and use @.ReportMonth and @.ReportYear but database name
> > parameter @.DatabaseName is not working.
> >
> > Thanks in advance.It seems like you are missing a closing quote at the end of the expression
which would result in a compilation error during report publishing.
= "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@.microsoft.com...
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or
where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create
a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create
a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run
and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on
different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is
on
> > the same server with your datasource. Otherwise you have to declare a
linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all
databases.
> > > One thing I can do is to create 10 different datasets to get my
results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database
from pull
> > > down menu and view the results from that database. So basically it
will be
> > > running query for one database at a time. I tried to do it the same
way you
> > > create all parameters but of no use. I will appreciate any kind of
help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.|||I had that problem a while back, but with some help, I was able to get it
working. My situation is like yours where we have 5 databases with same
tables, schema, and fields. Instead of creating 5 different reports, I
wanted to creat just one report and have parameters for each database.
Here's the steps to how I got it to work. Hope this helps.
1) In the Dataset section type the following conditional expression:
=iif(Parameters!Database.Value = "DatabaseName1", "DatabaseName1.dbo.SP_1",
iif(Parameters!Database.Value = "DatabaseName2", "DatabaseName2.dbo.SP_1",
iif(Parameters!Database.Value = "DatabaseName3", "DatabaseName3.dbo.SP_1",
"DatabaseName4.dbo.SP_1")
What this conditional expression will do is that if it's DatabaseName1 then
run the stored procedure from that database, else run it from DatabaseName2,
etc..
2) Now go to the layout section of your reporting services. Then click in
the ReportParameters where the Properties is. Remember the databases? Add a
Parameter and use "Database" from (Parameters!Database.Value). The Database
is the Database Name and Prompt, just put whatever you want.
Then for Label and Value you will want to put your database names.
Ex:
DatabaseName1, DatabaseName1
DatabaseName2, DatabaseName2
DatabaseName3, DatabaseName3
DatabaseName4, DatabaseName4
Make sure that "Non-Queried" is select.
That's all you need to do. Also make sure that the stored procedure is in
each database. If you have any questions, post here.
"Robert Bruckner [MSFT]" wrote:
> It seems like you are missing a closing quote at the end of the expression
> which would result in a compilation error during report publishing.
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName"
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "GJ" <GJ@.discussions.microsoft.com> wrote in message
> news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@.microsoft.com...
> > Hi Eralper,
> >
> > Thank you for the quick reply. But I think I am confused now as how or
> where
> > to write those statements.
> >
> > The way I have been using reporting server is to create a dataset, create
> a
> > datasource in the dataset (or use a shared datasource), either write text
> > query or exec SP there. Then write the parameters used in the query in the
> > 'Report Parameters' tab in VS.NET.
> >
> > I am not sure if I followed it correctly, but if you are saying to create
> a
> > new DATASOURCE.. and write the source as:
> >
> > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> >
> > In my situation, that is giving me errors.
> >
> > On the other hand, running this " Exec
> > [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
> doesn't
> > solve my problem as here we are giving DBName (Pubs) and that is static.
> >
> > What I am trying is ... create a pull-don menu in the report and give the
> > user an option to select the particular database. Then the query is run
> and
> > results are returned for the query for the particular database selected.
> >
> > I may not have understood your explanation but am still confused.
> >
> > Thank you for your time and patience.
> >
> > GJ
> >
> > "eralper" wrote:
> >
> > > Hi,
> > >
> > > I'm using a similar method for maintaining many report servers on
> different
> > > locations and each connecting to their own databases.
> > >
> > > I use the below text as dataset source
> > >
> > > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > > Parameters!ParameterOne.Value
> > >
> > > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > > location you can change your sql server machine.
> > > Note that you will not need this parameter if your reporting database is
> on
> > > the same server with your datasource. Otherwise you have to declare a
> linked
> > > server on the server running reporting server databases.
> > >
> > > DatabaseName parameter is the catalog name in the database server.
> > >
> > > Then you pass the query (or sp with parameters)
> > >
> > > Actually you send at the end a similar query:
> > >
> > > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> > >
> > > I hope this helps.
> > >
> > > Eralper Yilmaz
> > > http://www.eralper.com
> > > http://www.kodyaz.com/default.aspx
> > >
> > >
> > > "GJ" wrote:
> > >
> > > > I have 10 Databases with same query to extract same info from all
> databases.
> > > > One thing I can do is to create 10 different datasets to get my
> results or
> > > > join 10 queries in one big query.
> > > >
> > > > But what I really would like to do is to create one dataset and pass a
> > > > database-name parameter so users can select the particular database
> from pull
> > > > down menu and view the results from that database. So basically it
> will be
> > > > running query for one database at a time. I tried to do it the same
> way you
> > > > create all parameters but of no use. I will appreciate any kind of
> help.
> > > > Example:
> > > >
> > > > Select manufacturer, sales
> > > > From @.DatabaseName.dbo.product FDP
> > > > Where
> > > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > > >
> > > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > > parameter @.DatabaseName is not working.
> > > >
> > > > Thanks in advance.
>
>|||We have that problem in the past, but got it to work now. You can use
conditional expressions to get it to work.
In the Data section, in the Generic Query Designer type this down.
=iif(Parameters!Database.Value = "ACCT", "ACCT.dbo.SP_1",
iif(Parameters!Database.Value = "HR", "HR.dbo.SP_1",
iif(Parameters!Database.Value = "FIN", "FIN.dbo.SP_1", "ENG.dbo.SP_1")))
This just says that if the parameter "Database" is "ACCT" then run SP_1 in
that database, if parameter is "HR" then run SP_1 in HR database, etc.
Now go to the Layout tab and click on the ReportParameters. Add a parameter
and name it "Database". For Prompt you can use whatever. Make sure that
"Non-queried" is selected and then type in the Label and Value.
Example:
Accounting, ACCT
Human Resource, HR
Finance, FIN
Engineering, ENG
That should get you going. Post if you have any more questions.
Regards,
Chang
"Robert Bruckner [MSFT]" wrote:
> It seems like you are missing a closing quote at the end of the expression
> which would result in a compilation error during report publishing.
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName"
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "GJ" <GJ@.discussions.microsoft.com> wrote in message
> news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@.microsoft.com...
> > Hi Eralper,
> >
> > Thank you for the quick reply. But I think I am confused now as how or
> where
> > to write those statements.
> >
> > The way I have been using reporting server is to create a dataset, create
> a
> > datasource in the dataset (or use a shared datasource), either write text
> > query or exec SP there. Then write the parameters used in the query in the
> > 'Report Parameters' tab in VS.NET.
> >
> > I am not sure if I followed it correctly, but if you are saying to create
> a
> > new DATASOURCE.. and write the source as:
> >
> > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> >
> > In my situation, that is giving me errors.
> >
> > On the other hand, running this " Exec
> > [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
> doesn't
> > solve my problem as here we are giving DBName (Pubs) and that is static.
> >
> > What I am trying is ... create a pull-don menu in the report and give the
> > user an option to select the particular database. Then the query is run
> and
> > results are returned for the query for the particular database selected.
> >
> > I may not have understood your explanation but am still confused.
> >
> > Thank you for your time and patience.
> >
> > GJ
> >
> > "eralper" wrote:
> >
> > > Hi,
> > >
> > > I'm using a similar method for maintaining many report servers on
> different
> > > locations and each connecting to their own databases.
> > >
> > > I use the below text as dataset source
> > >
> > > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > > Parameters!ParameterOne.Value
> > >
> > > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > > location you can change your sql server machine.
> > > Note that you will not need this parameter if your reporting database is
> on
> > > the same server with your datasource. Otherwise you have to declare a
> linked
> > > server on the server running reporting server databases.
> > >
> > > DatabaseName parameter is the catalog name in the database server.
> > >
> > > Then you pass the query (or sp with parameters)
> > >
> > > Actually you send at the end a similar query:
> > >
> > > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> > >
> > > I hope this helps.
> > >
> > > Eralper Yilmaz
> > > http://www.eralper.com
> > > http://www.kodyaz.com/default.aspx
> > >
> > >
> > > "GJ" wrote:
> > >
> > > > I have 10 Databases with same query to extract same info from all
> databases.
> > > > One thing I can do is to create 10 different datasets to get my
> results or
> > > > join 10 queries in one big query.
> > > >
> > > > But what I really would like to do is to create one dataset and pass a
> > > > database-name parameter so users can select the particular database
> from pull
> > > > down menu and view the results from that database. So basically it
> will be
> > > > running query for one database at a time. I tried to do it the same
> way you
> > > > create all parameters but of no use. I will appreciate any kind of
> help.
> > > > Example:
> > > >
> > > > Select manufacturer, sales
> > > > From @.DatabaseName.dbo.product FDP
> > > > Where
> > > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > > >
> > > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > > parameter @.DatabaseName is not working.
> > > >
> > > > Thanks in advance.
>
>|||Sorry for the repeat. I thought it didn't sent through, so I reply again.
THey're both the same. I've spent quite a while trying to figure this out,
but I think this will help you.
"chang" wrote:
> We have that problem in the past, but got it to work now. You can use
> conditional expressions to get it to work.
> In the Data section, in the Generic Query Designer type this down.
> =iif(Parameters!Database.Value = "ACCT", "ACCT.dbo.SP_1",
> iif(Parameters!Database.Value = "HR", "HR.dbo.SP_1",
> iif(Parameters!Database.Value = "FIN", "FIN.dbo.SP_1", "ENG.dbo.SP_1")))
> This just says that if the parameter "Database" is "ACCT" then run SP_1 in
> that database, if parameter is "HR" then run SP_1 in HR database, etc.
> Now go to the Layout tab and click on the ReportParameters. Add a parameter
> and name it "Database". For Prompt you can use whatever. Make sure that
> "Non-queried" is selected and then type in the Label and Value.
> Example:
> Accounting, ACCT
> Human Resource, HR
> Finance, FIN
> Engineering, ENG
> That should get you going. Post if you have any more questions.
> Regards,
> Chang
>
> "Robert Bruckner [MSFT]" wrote:
> > It seems like you are missing a closing quote at the end of the expression
> > which would result in a compilation error during report publishing.
> > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName"
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "GJ" <GJ@.discussions.microsoft.com> wrote in message
> > news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@.microsoft.com...
> > > Hi Eralper,
> > >
> > > Thank you for the quick reply. But I think I am confused now as how or
> > where
> > > to write those statements.
> > >
> > > The way I have been using reporting server is to create a dataset, create
> > a
> > > datasource in the dataset (or use a shared datasource), either write text
> > > query or exec SP there. Then write the parameters used in the query in the
> > > 'Report Parameters' tab in VS.NET.
> > >
> > > I am not sure if I followed it correctly, but if you are saying to create
> > a
> > > new DATASOURCE.. and write the source as:
> > >
> > > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> > >
> > > In my situation, that is giving me errors.
> > >
> > > On the other hand, running this " Exec
> > > [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
> > doesn't
> > > solve my problem as here we are giving DBName (Pubs) and that is static.
> > >
> > > What I am trying is ... create a pull-don menu in the report and give the
> > > user an option to select the particular database. Then the query is run
> > and
> > > results are returned for the query for the particular database selected.
> > >
> > > I may not have understood your explanation but am still confused.
> > >
> > > Thank you for your time and patience.
> > >
> > > GJ
> > >
> > > "eralper" wrote:
> > >
> > > > Hi,
> > > >
> > > > I'm using a similar method for maintaining many report servers on
> > different
> > > > locations and each connecting to their own databases.
> > > >
> > > > I use the below text as dataset source
> > > >
> > > > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > > > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > > > Parameters!ParameterOne.Value
> > > >
> > > > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > > > location you can change your sql server machine.
> > > > Note that you will not need this parameter if your reporting database is
> > on
> > > > the same server with your datasource. Otherwise you have to declare a
> > linked
> > > > server on the server running reporting server databases.
> > > >
> > > > DatabaseName parameter is the catalog name in the database server.
> > > >
> > > > Then you pass the query (or sp with parameters)
> > > >
> > > > Actually you send at the end a similar query:
> > > >
> > > > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> > > >
> > > > I hope this helps.
> > > >
> > > > Eralper Yilmaz
> > > > http://www.eralper.com
> > > > http://www.kodyaz.com/default.aspx
> > > >
> > > >
> > > > "GJ" wrote:
> > > >
> > > > > I have 10 Databases with same query to extract same info from all
> > databases.
> > > > > One thing I can do is to create 10 different datasets to get my
> > results or
> > > > > join 10 queries in one big query.
> > > > >
> > > > > But what I really would like to do is to create one dataset and pass a
> > > > > database-name parameter so users can select the particular database
> > from pull
> > > > > down menu and view the results from that database. So basically it
> > will be
> > > > > running query for one database at a time. I tried to do it the same
> > way you
> > > > > create all parameters but of no use. I will appreciate any kind of
> > help.
> > > > > Example:
> > > > >
> > > > > Select manufacturer, sales
> > > > > From @.DatabaseName.dbo.product FDP
> > > > > Where
> > > > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > > > >
> > > > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > > > parameter @.DatabaseName is not working.
> > > > >
> > > > > Thanks in advance.
> >
> >
> >|||Hi,
I sent a reply to your answer but it was on the other thread.
I'm adding the content below.
Your desired sp is below.
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
First of all, create the @.DatabaseName parameter in the report parameters
screen
Then go to Data tab, and write the following statement
Select manufacturer, sales From OneOfYourDatabases.dbo.product FDP
Replace OneOfYourDatabases with a database name. And execute the query.
Running this query will populate the fields list for this dataset so you can
use these fields in the Layout tab. (You can also add field names later on
the Field Tab if necessary)
The third step will be rearrainging the query. Replace it with :
= "Select manufacturer, sales
From " & Parameters!DatabaseName.value & ".dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportMonth.value & "')
and
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportYear.value & "')"
Lastly, click "Refresh" button (somehow this solved problems a few times),
then save.
If I'm not wrong, these four steps should work as you want.
"GJ" wrote:
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is on
> > the same server with your datasource. Otherwise you have to declare a linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all databases.
> > > One thing I can do is to create 10 different datasets to get my results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database from pull
> > > down menu and view the results from that database. So basically it will be
> > > running query for one database at a time. I tried to do it the same way you
> > > create all parameters but of no use. I will appreciate any kind of help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.