hi,
I'm using Borland's C++ Builder and InfoPower Components,
i need to passa the result of an execution of a filter dialog to a Cristal report,
better, i want to print, the dataset of a grid, based on a filter,
the fields displayed are the same ... just the filter change
how can i do this, at run time ??
thank's in advance
need awnser as fast as you can, urgent!!!Hi ,
I am trying for VB you try it out to ur code.
Use
CrystalReportViewerSL.SelectionFormula = Your Filter condition
Before preview the report.
eg.
Dim StrStoreValue As String
StrStoreValue = "STORE1"
CrystalReportViewerSL.SelectionFormula = = "{STOCK_LEDGER.STORE_CODE} = '" & StrStoreValue & "'"
It woll give the filtered output.
Thanks
Vilas|||i solve it other way
i can convert the result of the query in a string tha i just
(the string needs to be in crystal syntax ... )
CrpeReports->Selection->Formula->Clear();
CrpeReports->Selection->Formula->SetText(filtro.c_str());
as it is the only formula i need ... it works well ...
but thanks anyway
Saturday, February 25, 2012
pass filename to flatfilesource inside foreach loop
Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.
The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
Please note, this is a different question to my other post.
Many Thanks
Hi
create a flat file connection and in expression give connectionstring as the variabe you have created in for each loop
for give this flat file connection as flat file source
|||
Hi,
I've done the first part already.
Not sure what you mean by:
"for give this flat file connection as flat file source"
|||Are all of your files going to be the same record structure / column layout? If so then all you should have to do is add the filename variable to your connectionstring property of the flat file connection manager. (I would also put the full path into this expression).
If you have done this already, is it working? If not, what errors are you getting?
|||
Hi,
The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
You would create the filename variable at the package level and use it in both the flat file connection and the foreach loop.
It will set the variable in the loop and use the variable in the connection manager.
In other words, in the foreach loop, make sure that the foreach file enumerator is selected, choose the file name convention you would like to return (preferably fully qualified if you are using this with a flat file connection), go to the variables tab and choose your variable name (User::FileName which should be declared at the package level). In your flat file connection go to your expressions and choose connectionstring as the property to edit. Select the User::FileName as the variable.
That should do it.
pass filename
Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks
How do I pass this variable i.e. file name to a stored procedure?
Thanks
I've achived something similar to this by doing the following:
(i've assumed you've already got to the stage of populating the variable with the name of the file)
Within your loop:-
1) add an execute sql task and enter the following in your SQLStatement section: - EXEC yourproc ? (the ? is the placeholder for the parameter)
2) set up the parameter to your variable in the Parameter Mapping section remembering to set the correct type. I don't believe the parameter name is important in this scenario.
Hope that makes sense.
|||This is the error I get:
[Execute SQL Task] Error: Executing the query "exec uspMarketValuesUploadXMLfileReader ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
|||Did you fill out the parameter mapping tab?|||Yes. Still the same error.
These are my settings
SQLSourceType: DirectInput
ParameterMapping --> User::FileName
datatype: varchar
|||Use a "0" for the parameter name. Also, you won't be able to parse the query.|||Ah, so it looks like my comment about parameter name not mattering is not quite correct. Apologies. In the parameter mapping section, set the parameter name to be a valid sql parameter eg @.filepath. Hopefully that should do the trick.
Cheers
|||Solved.
Had to replace 0 with ?
Thanks guys.
|||Don't forget to mark posts as answered...
Good luck!
Pass fields as array to custom function?
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
Dim ar(1) As String
ar(0) = field1
ar(1) = field2
Return ar
End Function
You would need to create a function in the code window that accepts the values and inserts them into an array.
ex.
Code Snippet
Function CreateArray(ByVal field1 As String, ByVal field2 As String) As ArrayDim ar(1) As String
ar(0) = field1
ar(1) = field2
Return ar
End Function
I haven't tested it, but I think it will work.
Simone
|||You can use the Split function.
http://msdn2.microsoft.com/en-us/library/6x627e5f(VS.80).aspx
|||That is true but you would still need to create a string to split using the required fields. Depending on the data those fields contain, you would need to be careful if they hold the delimeter used in the split function.Pass datetime as parameter to SP
my SP like this
CREATE PROCEDURE dbo.sel_apinv_nonsettle
@.cocode varchar(10),
@.billcode varchar(10),
@.issuedate datetime,
@.branchid varchar(10),
@.accttype varchar(10)
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
sel_apinv_nonsettle, Line 0
Error converting data type nvarchar to datetime."
What's wrong 'Hi
Probably you need to put it this way
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate(),'HLSHK','APINV'
getdate() is a function
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Agnes" wrote:
> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>|||Stored Proc Parameters can only be constants, or T-SQL Variables... You cnno
t
directly pass an expression, or a function, to a stored proc... And even if
you could, the function getdate would require the open closed parenethses at
the end -- getdate()...
What you need to do is create a T-SQL Variable, set it to be equal to
getdate(), and then pass that variable.
Declare @.GDT DateTime
Set @.GDT = getdate() -- ("Current_TimeStamp" is ANSI-SQL Standard)
exec sel_apinv_nonsettle 'CNC','CNC',@.GDT,'HLSHK','APINV'
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
"Agnes" wrote:
> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>
CREATE PROCEDURE dbo.sel_apinv_nonsettle
@.cocode varchar(10),
@.billcode varchar(10),
@.issuedate datetime,
@.branchid varchar(10),
@.accttype varchar(10)
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
sel_apinv_nonsettle, Line 0
Error converting data type nvarchar to datetime."
What's wrong 'Hi
Probably you need to put it this way
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate(),'HLSHK','APINV'
getdate() is a function
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Agnes" wrote:
> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>|||Stored Proc Parameters can only be constants, or T-SQL Variables... You cnno
t
directly pass an expression, or a function, to a stored proc... And even if
you could, the function getdate would require the open closed parenethses at
the end -- getdate()...
What you need to do is create a T-SQL Variable, set it to be equal to
getdate(), and then pass that variable.
Declare @.GDT DateTime
Set @.GDT = getdate() -- ("Current_TimeStamp" is ANSI-SQL Standard)
exec sel_apinv_nonsettle 'CNC','CNC',@.GDT,'HLSHK','APINV'
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
"Agnes" wrote:
> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>
pass Date varible to XPath query
Hi,All
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"
i figure out myself. Thanks
"martin1" wrote:
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:
> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"
i figure out myself. Thanks
"martin1" wrote:
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"
|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:
> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>
pass Date varible to XPath query
Hi,All
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"i figure out myself. Thanks
"martin1" wrote:
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is n
ot
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql"
"
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='"
&
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:
> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>
I want to pass Date varible value to XPATH query (see code below), it is not
work, can anyone help this out? Thanks. Martin
Dim currentDate As Date = CDate("5/29/2006")
adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
>" & _
"<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
currentDate & "' ] </sql:xpath-query>" & _
" </Root>"i figure out myself. Thanks
"martin1" wrote:
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is n
ot
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root xmlns:sql=""urn:schemas-microsoft-com:xml-sql"
"
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='"
&
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||What was it? Did you need to add a time zone? Convert it to a string?
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
> Hi,All
>
> I want to pass Date varible value to XPATH query (see code below), it is
> not
> work, can anyone help this out? Thanks. Martin
> Dim currentDate As Date = CDate("5/29/2006")
> adoCmd.CommandText = "<Root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""
> "<sql:xpath-query mapping-schema = schema1.xsd/Node1[ Date ='" &
> currentDate & "' ] </sql:xpath-query>" & _
> " </Root>"|||you are right , convert date to string, then pass string to the query
"Mike C#" wrote:
> What was it? Did you need to add a time zone? Convert it to a string?
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:8C91EB5E-F34D-45CE-A0CC-0957D2B321CC@.microsoft.com...
>
>
Pass Date Range from VB to CR
Hi All,
i m Currently using VB,CR 9 and MS-Access
i have Create Cr report,
in my Vb form two text boxes Start date and end date,Simply i just want to display my report between two dates..
how can i do this..
AnyoneCan help me...
Thanx in Advance
Regards,
SabinaI am glad that you ask this question because I have the same problem and wanted to ask this to. I hope that someone can help us.
Greetings, Sjaaaf|||Create 2 global variables in module.bas
Public gstrFrom as String
Public gstrTo as String
Let say your form frmPrint.frm has textboxes txtFrom.text and txtTo.text
Put inside command button Print
Private Sub cmdPrint_Click()
gstrFrom = Trim(txtFrom.text)
gstrTo = Trim(txtTo.text)
Dim RptViewer As New frmRPrint 'frmRPrint.frm has a CRViewer object
RptViewer.Show
End Sub
Inside frmRPrint code section:
Option Explicit
Public Report As New ProdReport 'ProdReport.dsr is a Designer file
Private Sub Form_Load()
Dim adoc As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
Screen.MousePointer = vbHourglass
Set adoc = New ADODB.Command
Set conn = New ADODB.Connection
conn.Open ConnString 'Connection String to MS Access Database
adoc.ActiveConnection = conn
strSQL = "SELECT ItemID, Quantity" & _
" FROM Production" & _
" WHERE ProdDate BETWEEN '" & gstrFrom & "' AND '" & gstrTo & "'"
adoc.CommandText = strSQL
adoc.CommandType = adCmdText
Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName
Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")
conn.Close
Set conn = Nothing
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault
End Sub
Note:
You also can use
strSQL = "... WHERE ProdDate > '" & gstrFrom & "' AND ProdDate <'" & gstrTo & "'"
If your date is in date time format, concantenate the Todate with "11.59 pm" or "< gstrTo +1" so it will also include records on that day or else it will only display records until d/m/yy 12.00am|||hi
Thanx u very much..
If i get any error,than i will again ask u question.....again thnx
God with u always,
Regards,
Sabina|||hi
As u gave me Code of the passing Date range i did it
but when i use
Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName
Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")
this codding lines
it not show me database,AutoSetUnboundFieldSource crBMTName like these properties
my report is a .dsr file...if i have Crystal report (.rpt file)than wha can i do...?
tell me plz what wil i do ,where Database etc. properties not display ...othertings u sais it is fine
Thanx in Advance
Regards,
Sabina|||Hi..
Instead of doing that one, try this one
XSTART = txtFrom.text
XEND = txtEnd.txt
Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report
REPORT = APP.OpenReport("<your path>\<your reportfilename>.rpt")
REPORT.RecordSelectionFormula = "{<Table.Datefield>} >= #" & XSTART & "# AND {<Table.Datefield>} <= #" & XEND & "#"
Don't forget to Add the Crystal Report ActiveX Designer Run Time Library to your COM References ^_^|||Try this...
Place 2 Formula Fields in yr report and in the editor write "Date".
Then write the below code in yr VB codings and then execute the report.
CrystalReport1.Formulas(0) = "fdate='" & Format(DTPicker1.Value, "MMMM yyyy") & "'"
CrystalReport1.Formulas(1) = "tdate='" & Format(DTPicker2.Value, "MMMM yyyy") & "'"
Revert, if u still require assistance..
i m Currently using VB,CR 9 and MS-Access
i have Create Cr report,
in my Vb form two text boxes Start date and end date,Simply i just want to display my report between two dates..
how can i do this..
AnyoneCan help me...
Thanx in Advance
Regards,
SabinaI am glad that you ask this question because I have the same problem and wanted to ask this to. I hope that someone can help us.
Greetings, Sjaaaf|||Create 2 global variables in module.bas
Public gstrFrom as String
Public gstrTo as String
Let say your form frmPrint.frm has textboxes txtFrom.text and txtTo.text
Put inside command button Print
Private Sub cmdPrint_Click()
gstrFrom = Trim(txtFrom.text)
gstrTo = Trim(txtTo.text)
Dim RptViewer As New frmRPrint 'frmRPrint.frm has a CRViewer object
RptViewer.Show
End Sub
Inside frmRPrint code section:
Option Explicit
Public Report As New ProdReport 'ProdReport.dsr is a Designer file
Private Sub Form_Load()
Dim adoc As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection
Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2
Screen.MousePointer = vbHourglass
Set adoc = New ADODB.Command
Set conn = New ADODB.Connection
conn.Open ConnString 'Connection String to MS Access Database
adoc.ActiveConnection = conn
strSQL = "SELECT ItemID, Quantity" & _
" FROM Production" & _
" WHERE ProdDate BETWEEN '" & gstrFrom & "' AND '" & gstrTo & "'"
adoc.CommandText = strSQL
adoc.CommandType = adCmdText
Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName
Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")
conn.Close
Set conn = Nothing
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault
End Sub
Note:
You also can use
strSQL = "... WHERE ProdDate > '" & gstrFrom & "' AND ProdDate <'" & gstrTo & "'"
If your date is in date time format, concantenate the Todate with "11.59 pm" or "< gstrTo +1" so it will also include records on that day or else it will only display records until d/m/yy 12.00am|||hi
Thanx u very much..
If i get any error,than i will again ask u question.....again thnx
God with u always,
Regards,
Sabina|||hi
As u gave me Code of the passing Date range i did it
but when i use
Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName
Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")
this codding lines
it not show me database,AutoSetUnboundFieldSource crBMTName like these properties
my report is a .dsr file...if i have Crystal report (.rpt file)than wha can i do...?
tell me plz what wil i do ,where Database etc. properties not display ...othertings u sais it is fine
Thanx in Advance
Regards,
Sabina|||Hi..
Instead of doing that one, try this one
XSTART = txtFrom.text
XEND = txtEnd.txt
Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report
REPORT = APP.OpenReport("<your path>\<your reportfilename>.rpt")
REPORT.RecordSelectionFormula = "{<Table.Datefield>} >= #" & XSTART & "# AND {<Table.Datefield>} <= #" & XEND & "#"
Don't forget to Add the Crystal Report ActiveX Designer Run Time Library to your COM References ^_^|||Try this...
Place 2 Formula Fields in yr report and in the editor write "Date".
Then write the below code in yr VB codings and then execute the report.
CrystalReport1.Formulas(0) = "fdate='" & Format(DTPicker1.Value, "MMMM yyyy") & "'"
CrystalReport1.Formulas(1) = "tdate='" & Format(DTPicker2.Value, "MMMM yyyy") & "'"
Revert, if u still require assistance..
Pass Datasource Name as input parameter
hi,
how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?
Thanks,
shanthi
Hi,
Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
Pass Datasource Name as input parameter
hi,
how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?
Thanks,
shanthi
Hi,
Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
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.
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.
Pass data to a report?
Hi all,
We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,
Stephen.
Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?
Just an idea.
BobP
|||Hi Stephen-
I can think of two possibilities if you are using Reporting Services 2005:
(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way
(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.
If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.
Thanks, Jon
|||Reports use a store procedures to retrieve the data.
My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.
To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.
|||BobP - BIM wrote:
Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?
Just an idea.
BobP
Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,
Stephen.
Pass data to a report?
Hi all,
We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,
Stephen.
Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?
Just an idea.
BobP
|||Hi Stephen-
I can think of two possibilities if you are using Reporting Services 2005:
(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way
(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.
If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.
Thanks, Jon
|||Reports use a store procedures to retrieve the data.
My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.
To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.
|||Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?
Just an idea.
BobP
Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,
Stephen.
Pass data in script component when no transformation needed
Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.
The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?
Any input in this would be appreciated.
Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||
jwelch wrote:
As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.
To expand a little, as long as you set the script component to be a "transformation," this is true.
You'll be able to get at them by using Row.XXXX
Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.
The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.
Does this make sense?|||
Does this make sense?
Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.
Are you talking about something else ?|||
It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.
You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.
Pass data in script component when no transformation needed
Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.
The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?
Any input in this would be appreciated.
Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||
As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.
To expand a little, as long as you set the script component to be a "transformation," this is true.
You'll be able to get at them by using Row.XXXX
Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.
The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.
Does this make sense?|||
Does this make sense?
Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.
Are you talking about something else ?|||
It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.
You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.
pass data between two datasets
Is there anyway i can pass data between two datasets
ex:
dataset 1 has
select empid, fname,lname ,salary from employee
dataset2 has
select userid ,total/(dataset1.salary) from anothertable
here i want to use a value from dataset 1 in dataset 2.
Thanks in advnceLook into using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Sub reports ? but i dont want to go into the front end. i want to do it in
the backend itself.
you know pass data from one data set to another directly
Thanks
"Bruce L-C [MVP]" wrote:
> Look into using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> > Is there anyway i can pass data between two datasets
> >
> > ex:
> > dataset 1 has
> > select empid, fname,lname ,salary from employee
> >
> > dataset2 has
> > select userid ,total/(dataset1.salary) from anothertable
> >
> > here i want to use a value from dataset 1 in dataset 2.
> >
> > Thanks in advnce
>
>|||You can embed a sub report into the table of the first report passing it the
value from the field of the first one (the subreport should have a
parameter).
There is no way from within RS to tie one dataset to another. Either use
subreports or put the logic in a stored procedure (but since a stored
procedure can only return a single resultset, or rather RS will only use a
single resultset from a stored procedure) I don't think a stored procedure
will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:B4ED0F7F-4CCE-4484-B21F-8DD2B84EF0A3@.microsoft.com...
> Sub reports ? but i dont want to go into the front end. i want to do it in
> the backend itself.
> you know pass data from one data set to another directly
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Look into using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
>> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
>> > Is there anyway i can pass data between two datasets
>> >
>> > ex:
>> > dataset 1 has
>> > select empid, fname,lname ,salary from employee
>> >
>> > dataset2 has
>> > select userid ,total/(dataset1.salary) from anothertable
>> >
>> > here i want to use a value from dataset 1 in dataset 2.
>> >
>> > Thanks in advnce
>>|||Ramani,
You really need to get all the data you want for a data region into one
dataset. You need to redo the query, there must be a relationship
between ds1 and ds2, or if it's just a continuous list of data try the
Union command.
Sub-reports require much greater overhead it's better not to use them
in this scenario.
Maybe try something like this;
Select e.empid, e.fname, e.lname, e.salary, a.userid, a.total
from employee e
join anothertable a on e.empid = a.userid
It may generate lots of repeated data, but RS can deal with this much
more efficiently than using sub-reports.
Chris
Ramani wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Easiest way may be to create a view between the 2.
"Ramani" wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce
ex:
dataset 1 has
select empid, fname,lname ,salary from employee
dataset2 has
select userid ,total/(dataset1.salary) from anothertable
here i want to use a value from dataset 1 in dataset 2.
Thanks in advnceLook into using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Sub reports ? but i dont want to go into the front end. i want to do it in
the backend itself.
you know pass data from one data set to another directly
Thanks
"Bruce L-C [MVP]" wrote:
> Look into using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> > Is there anyway i can pass data between two datasets
> >
> > ex:
> > dataset 1 has
> > select empid, fname,lname ,salary from employee
> >
> > dataset2 has
> > select userid ,total/(dataset1.salary) from anothertable
> >
> > here i want to use a value from dataset 1 in dataset 2.
> >
> > Thanks in advnce
>
>|||You can embed a sub report into the table of the first report passing it the
value from the field of the first one (the subreport should have a
parameter).
There is no way from within RS to tie one dataset to another. Either use
subreports or put the logic in a stored procedure (but since a stored
procedure can only return a single resultset, or rather RS will only use a
single resultset from a stored procedure) I don't think a stored procedure
will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:B4ED0F7F-4CCE-4484-B21F-8DD2B84EF0A3@.microsoft.com...
> Sub reports ? but i dont want to go into the front end. i want to do it in
> the backend itself.
> you know pass data from one data set to another directly
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Look into using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
>> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
>> > Is there anyway i can pass data between two datasets
>> >
>> > ex:
>> > dataset 1 has
>> > select empid, fname,lname ,salary from employee
>> >
>> > dataset2 has
>> > select userid ,total/(dataset1.salary) from anothertable
>> >
>> > here i want to use a value from dataset 1 in dataset 2.
>> >
>> > Thanks in advnce
>>|||Ramani,
You really need to get all the data you want for a data region into one
dataset. You need to redo the query, there must be a relationship
between ds1 and ds2, or if it's just a continuous list of data try the
Union command.
Sub-reports require much greater overhead it's better not to use them
in this scenario.
Maybe try something like this;
Select e.empid, e.fname, e.lname, e.salary, a.userid, a.total
from employee e
join anothertable a on e.empid = a.userid
It may generate lots of repeated data, but RS can deal with this much
more efficiently than using sub-reports.
Chris
Ramani wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Easiest way may be to create a view between the 2.
"Ramani" wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce
Pass data between two data flow tasks
Hi Guys,
I have yet another question. How can i pass data b/w 2 data flow tasks? I'm trying to get some data from one sql server and then I want to pass this whole bunch of data to another data flow task which is going to get some data from second sql server. I would probably combine them using union all and then save that as a transaction in a third sql server?
Information regarding how to pass the data with some detailed discussion would be fine with me.
thanks
Gemma
Would the Merge Join transformation work for you? You could just have two sources within a single DataFlow Task and then merge their outputs.|||Hi karfast,
Thank you for your response. Yes you're right but how would i put a new column when i'm saving the data?
Gemma
|||Add a derived column / lookup column / join column... It depends on what you want in the new column.
For example if you want to add a timestamp you could just throw in a current timestamp from a derived column. If you want to add in information based off of a key in your original data you would use a lookup transform and input the column based off of the foreign key. etc....
Hope this helps.
|||
Hi Guys,
I have yet another question. How can i pass data b/w 2 data flow tasks?
Probably not relevant anymore but the answer to your original question is "Use raw files".
Raw file information
(http://blogs.conchango.com/tags/SSIS/raw+files/default.aspx)
-Jamie
PASS Conference in Seattle?
Anyone going? I am, would love to hang out with some of you SSAS experts.
Wino
Hi Wino,
Perusing the PASS Conference schedule, the Exhibit Hall Reception (6-8 PM on Wed. Nov. 15) might be a good opportunity for SSAS schmoozing - but I'm not sure if they're setting up special interest sections like that ...
http://passew.prod.web.sba.com/displaymod/detailevent.cfm?conference_id=15&event_id=588
>>
General: Exhibit Hall Reception
Pass concatenated string to SPROC
Hello,
We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.
My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?
Will I have to create Dynamic SQL to do this?...or... can I do something like this...
@.ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @.ConcatenatedString
Thanks for your help.you need to use IN.
hth|||You can to dynamically build an SQL string using the IN keyword and then execute it. Or, you can build a function to parse the list and return a table of category ids. Then use you IN clause with a select statement from the table or you could join to the table that's returned. I'd just do the dynamic SQL, but you could try both to see what's faster in your situation.
pass Column Name using Parameter in SQL Statement...
Hi,
Why would you do it in the first place? Given a well-designed database,
the request does not make very much sense. But if you have a less well-
designed database, you need to do:
>
You don't do this; it is bad programming. A well-designed module of
code returns a predictable result. What you have is what I call a
"Britney Spears, Squids and Automobiles" module, since it can return
anything of any data type!
>
Get a book on basic Software Engineering and read about coupling and
cohesion before you do any more programming in any language.
Maybe they weren't responsible for the database design, but are being
asked query from it due to business requirements or maybe it's not a
critical application and it's was easier to load a flat file into
Access than to design a proper normalized database. Regardless, it's
a legitimate question (as are your concerns about unpredictable
results), but to assume that Raghu doesn't know basic Software
Engineering is rude.|||<paulschultz54@.gmail.comwrote in message
news:1193191383.235885.139270@.y27g2000pre.googlegr oups.com...
>>
>You don't do this; it is bad programming. A well-designed module of
>code returns a predictable result. What you have is what I call a
>"Britney Spears, Squids and Automobiles" module, since it can return
>anything of any data type!
>>
>Get a book on basic Software Engineering and read about coupling and
>cohesion before you do any more programming in any language.
>
Maybe they weren't responsible for the database design, but are being
asked query from it due to business requirements or maybe it's not a
critical application and it's was easier to load a flat file into
Access than to design a proper normalized database. Regardless, it's
a legitimate question (as are your concerns about unpredictable
results), but to assume that Raghu doesn't know basic Software
Engineering is rude.
You know, Joe has written several books, SQL for Smarties comes to mind.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Or... Practical SQL Solutions in the Real World.
>>
>Maybe they weren't responsible for the database design, but are being
>asked query from it due to business requirements or maybe it's not a
>critical application and it's was easier to load a flat file into
>Access than to design a proper normalized database. Regardless, it's
>a legitimate question (as are your concerns about unpredictable
>results), but to assume that Raghu doesn't know basic Software
>Engineering is rude.
>
You know, Joe has written several books, SQL for Smarties comes to mind.
>
However, one book that I doubt he'll ever be asked to write is "Joe
Celko's Guide to Winning Friends and Influencing People."
>
:-)
>
>
>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html
>
>
I am trying to Pass Column Name(FieldName) using Parameter in SQL
Statement... But i am getting error...
how can i pass Column name using parameter?
Example:
in table i have fieldname ECountry...
Select @.FName='ECountry'
Select @.FName from Table...
How it works?
Thanx in Advance,
Regards,
Raghu...(raghutumma@.gmail.com) writes:
Quote:
Originally Posted by
I am trying to Pass Column Name(FieldName) using Parameter in SQL
Statement... But i am getting error...
>
how can i pass Column name using parameter?
>
Example:
>
in table i have fieldname ECountry...
>
Select @.FName='ECountry'
Select @.FName from Table...
>
How it works?
Why would you do it in the first place? Given a well-designed database,
the request does not make very much sense. But if you have a less well-
designed database, you need to do:
SELECT CASE @.paramname
WHEN 'thatfield' THEN thatfield
WHEN 'thisfield' THEN thisfield
WHEN 'leftfield' THEN leftfield
END
FROM tbl
--
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|||>how can i pass Column name using parameter? <<
You don't do this; it is bad programming. A well-designed module of
code returns a predictable result. What you have is what I call a
"Britney Spears, Squids and Automobiles" module, since it can return
anything of any data type!
Get a book on basic Software Engineering and read about coupling and
cohesion before you do any more programming in any language.|||On Oct 19, 9:06 am, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
how can i pass Column name using parameter? <<
>
You don't do this; it is bad programming. A well-designed module of
code returns a predictable result. What you have is what I call a
"Britney Spears, Squids and Automobiles" module, since it can return
anything of any data type!
>
Get a book on basic Software Engineering and read about coupling and
cohesion before you do any more programming in any language.
Maybe they weren't responsible for the database design, but are being
asked query from it due to business requirements or maybe it's not a
critical application and it's was easier to load a flat file into
Access than to design a proper normalized database. Regardless, it's
a legitimate question (as are your concerns about unpredictable
results), but to assume that Raghu doesn't know basic Software
Engineering is rude.|||<paulschultz54@.gmail.comwrote in message
news:1193191383.235885.139270@.y27g2000pre.googlegr oups.com...
Quote:
Originally Posted by
On Oct 19, 9:06 am, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
>how can i pass Column name using parameter? <<
>>
>You don't do this; it is bad programming. A well-designed module of
>code returns a predictable result. What you have is what I call a
>"Britney Spears, Squids and Automobiles" module, since it can return
>anything of any data type!
>>
>Get a book on basic Software Engineering and read about coupling and
>cohesion before you do any more programming in any language.
>
Maybe they weren't responsible for the database design, but are being
asked query from it due to business requirements or maybe it's not a
critical application and it's was easier to load a flat file into
Access than to design a proper normalized database. Regardless, it's
a legitimate question (as are your concerns about unpredictable
results), but to assume that Raghu doesn't know basic Software
Engineering is rude.
You know, Joe has written several books, SQL for Smarties comes to mind.
However, one book that I doubt he'll ever be asked to write is "Joe Celko's
Guide to Winning Friends and Influencing People."
:-)
Quote:
Originally Posted by
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Or... Practical SQL Solutions in the Real World.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.comwrote in message
news:13hubgltianpsb8@.corp.supernews.com...
Quote:
Originally Posted by
<paulschultz54@.gmail.comwrote in message
news:1193191383.235885.139270@.y27g2000pre.googlegr oups.com...
Quote:
Originally Posted by
>On Oct 19, 9:06 am, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
>>how can i pass Column name using parameter? <<
>>>
>>You don't do this; it is bad programming. A well-designed module of
>>code returns a predictable result. What you have is what I call a
>>"Britney Spears, Squids and Automobiles" module, since it can return
>>anything of any data type!
>>>
>>Get a book on basic Software Engineering and read about coupling and
>>cohesion before you do any more programming in any language.
>>
>Maybe they weren't responsible for the database design, but are being
>asked query from it due to business requirements or maybe it's not a
>critical application and it's was easier to load a flat file into
>Access than to design a proper normalized database. Regardless, it's
>a legitimate question (as are your concerns about unpredictable
>results), but to assume that Raghu doesn't know basic Software
>Engineering is rude.
>
You know, Joe has written several books, SQL for Smarties comes to mind.
>
However, one book that I doubt he'll ever be asked to write is "Joe
Celko's Guide to Winning Friends and Influencing People."
>
:-)
>
>
Quote:
Originally Posted by
>>
>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html
>
>
Pass back one value from UDF having found it in a table
How do I pass back only one value, such as TotalJobPrice from the tblJobRecords when I know which JobID to Select. All I want is the TotalJobPrice returned from the UDF, not a record from the table. I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)
Hope that is clear.
Thanks in advance,you should write your UDF as a scalar function.
Post your fnTotalJobPrice here.|||I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)I would like to write a function that would allow me to be able to get the TotalJobPrice out of the function. But I DO NOT KNOW HOW to get one field of data out of one record in a table using T-SQL. I am brand new at T-SQL and would like to know if there is a way to get one field of data out of one record so I can put that data into a local variable. I don't have fnTotalJobPrice to post. I want to learn how to do this one thing, then I feel I can write it. Thanks,|||declare @.i int
select @.i = myintcolumn from mytable where ...
Just make sure that the query returns only one row. if it returns multiple, which value you get is undefined.|||Thank you very much for your input on how to obtain a value from a table within a stored procedure or function. Here is my stored procedure, as I found out I did not need the function after all, but the same coding seems to hold true in both types of objects. I also noticed that I needed the SELECT in the SQL string. Any idea why? I thought is must be because I was not assigning the return of that SQL string to a local variable.ALTER PROC spCalculateTotalBillToDate
@.JobID as int
AS
BEGIN
DECLARE @.TotAmount AS Money
DECLARE @.BillOption AS varchar(3)
DECLARE @.BillCodeID AS int
DECLARE @.BillingStep as int
SET @.TotAmount = 0
SET @.BillCodeID = (SELECT Max(BillCodeID) FROM dbo.tblInvoice
WHERE JobID = @.JobID)
IF ISNULL(@.BillCodeID, ' ') = ' '
Begin
GOTO Return0
END
-- Bill Option
SET @.BillOption = (SELECT BillOption FROM dbo.tblBillOptions
WHERE BillOptionID =
(SELECT BillOptionID FROM dbo.tblProject
WHERE ProjectID =
(SELECT ProjectID FROM dbo.tblJob WHERE JobID = @.JobID)))
SET @.BillingStep = (SELECT max(BillingStep) FROM dbo.tblJobStatus
WHERE JobID = @.JobID AND JobStatusID <=
(SELECT JobStatusID FROM dbo.tblJob WHERE JobID = @.JobID))
--Calculate TotAmount
SET ANSI_WARNINGS OFF
SET @.TotAmount = (SELECT Sum(Amount) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob WHERE JobID = @.JobID)
AND BillCodeID <= @.BillCodeID)
SET ANSI_WARNINGS ON
IF @.BillingStep >= 89
BEGIN
--Print 'BillingStep is >= 89'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
ELSE
IF @.BillOption = '_'
BEGIN
--Print 'BillOption = underscore'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID,
(SELECT Sum([Percent]) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob
WHERE JobID = @.JobID) AND BillCodeID <= @.BillCodeID))
END
ELSE
IF @.BillOption = 'D'
BEGIN
--Print 'BillOption = "D"'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
Return0:
--print CAST(@.TotAmount AS VARCHAR)
SELECT @.TotAmount TotAmount
END|||not exactly sure what you are asking. There are several ways to return data from a proc. this article will educate you on all of them:
http://www.sommarskog.se/share_data.html|||What I was asking was about needing to use "SELECT" when obtaining the information from the table. The code you shared with me you did NOT use "SELECT", so I asked if this was different because I was using in within some other SQL, rather than just assigning the data to a local variable. If you notice within the stored procedure I posted, I was able to return what I wanted to the caller.
My original question was not how to return data FROM a function, but how to get data from a table to be used IN a function.
Again, thanks for your help.|||hmm.
in the code I posted for you, i *did* use "select" to get data from a table. I didn't type it in all caps though. sql keywords are not case sensitive. I don't know of any way to get data out of a table other than select...
in any case it sounds like you got the answer you were looking for. :)
Hope that is clear.
Thanks in advance,you should write your UDF as a scalar function.
Post your fnTotalJobPrice here.|||I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)I would like to write a function that would allow me to be able to get the TotalJobPrice out of the function. But I DO NOT KNOW HOW to get one field of data out of one record in a table using T-SQL. I am brand new at T-SQL and would like to know if there is a way to get one field of data out of one record so I can put that data into a local variable. I don't have fnTotalJobPrice to post. I want to learn how to do this one thing, then I feel I can write it. Thanks,|||declare @.i int
select @.i = myintcolumn from mytable where ...
Just make sure that the query returns only one row. if it returns multiple, which value you get is undefined.|||Thank you very much for your input on how to obtain a value from a table within a stored procedure or function. Here is my stored procedure, as I found out I did not need the function after all, but the same coding seems to hold true in both types of objects. I also noticed that I needed the SELECT in the SQL string. Any idea why? I thought is must be because I was not assigning the return of that SQL string to a local variable.ALTER PROC spCalculateTotalBillToDate
@.JobID as int
AS
BEGIN
DECLARE @.TotAmount AS Money
DECLARE @.BillOption AS varchar(3)
DECLARE @.BillCodeID AS int
DECLARE @.BillingStep as int
SET @.TotAmount = 0
SET @.BillCodeID = (SELECT Max(BillCodeID) FROM dbo.tblInvoice
WHERE JobID = @.JobID)
IF ISNULL(@.BillCodeID, ' ') = ' '
Begin
GOTO Return0
END
-- Bill Option
SET @.BillOption = (SELECT BillOption FROM dbo.tblBillOptions
WHERE BillOptionID =
(SELECT BillOptionID FROM dbo.tblProject
WHERE ProjectID =
(SELECT ProjectID FROM dbo.tblJob WHERE JobID = @.JobID)))
SET @.BillingStep = (SELECT max(BillingStep) FROM dbo.tblJobStatus
WHERE JobID = @.JobID AND JobStatusID <=
(SELECT JobStatusID FROM dbo.tblJob WHERE JobID = @.JobID))
--Calculate TotAmount
SET ANSI_WARNINGS OFF
SET @.TotAmount = (SELECT Sum(Amount) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob WHERE JobID = @.JobID)
AND BillCodeID <= @.BillCodeID)
SET ANSI_WARNINGS ON
IF @.BillingStep >= 89
BEGIN
--Print 'BillingStep is >= 89'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
ELSE
IF @.BillOption = '_'
BEGIN
--Print 'BillOption = underscore'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID,
(SELECT Sum([Percent]) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob
WHERE JobID = @.JobID) AND BillCodeID <= @.BillCodeID))
END
ELSE
IF @.BillOption = 'D'
BEGIN
--Print 'BillOption = "D"'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
Return0:
--print CAST(@.TotAmount AS VARCHAR)
SELECT @.TotAmount TotAmount
END|||not exactly sure what you are asking. There are several ways to return data from a proc. this article will educate you on all of them:
http://www.sommarskog.se/share_data.html|||What I was asking was about needing to use "SELECT" when obtaining the information from the table. The code you shared with me you did NOT use "SELECT", so I asked if this was different because I was using in within some other SQL, rather than just assigning the data to a local variable. If you notice within the stored procedure I posted, I was able to return what I wanted to the caller.
My original question was not how to return data FROM a function, but how to get data from a table to be used IN a function.
Again, thanks for your help.|||hmm.
in the code I posted for you, i *did* use "select" to get data from a table. I didn't type it in all caps though. sql keywords are not case sensitive. I don't know of any way to get data out of a table other than select...
in any case it sounds like you got the answer you were looking for. :)
Pass back error count to Parent pkg
I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.
|||
Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?
Thanks!
See the following topic:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1
|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.|||
Why don't you want to use the error information available through dts.*?
Here is a useful link (although it uses the dts.* approach):
http://www.developerdotstar.com/community/node/327
NOTE: you can then push these values to a variable and pass it up to the parent package...
|||OK, ignore any parent/child aspects of my question.
Let's say your package has a bunch of errors, and sometimes you get a warning message:
The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);
I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.
Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:
CurrentPackage.Errors.Count
But, there's no easy way to do that, which is why I'm here.
|||You could put something in the OnError eventhandler that simply increments a variable every time it executes.
If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]
-Jamie
|||The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.
I am not aware of any workaround beside Jamie's suggestion.
|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.
Thanks to all!
Pass back error count to Parent pkg
I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.
|||
Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?
Thanks!
See the following topic:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1
|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.|||
Why don't you want to use the error information available through dts.*?
Here is a useful link (although it uses the dts.* approach):
http://www.developerdotstar.com/community/node/327
NOTE: you can then push these values to a variable and pass it up to the parent package...
|||OK, ignore any parent/child aspects of my question.
Let's say your package has a bunch of errors, and sometimes you get a warning message:
The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);
I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.
Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:
CurrentPackage.Errors.Count
But, there's no easy way to do that, which is why I'm here.
|||You could put something in the OnError eventhandler that simply increments a variable every time it executes.
If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]
-Jamie
|||The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.
I am not aware of any workaround beside Jamie's suggestion.
|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.
Thanks to all!
PASS attendees ?
Any one of you guys flying into Orlando on Sunday especially with Jeanne in
town that day ? Do you think the MCO airport might be shut down ? Or are you
folks trying to leave early ? Im planning to arrive on Sunday and not too
sure if the airlines might even take off if the airport is closed down
there.. Any news ?
Ive got myself registered for the pre-conference on Monday and would not
like to miss it ..It's hard to say if it will be closed or not but I changed my ticket from
Sunday to Saturday and will have to leave a day earlier than planned. I
figured if it wasn't closed it was going to be a bumpy ride<g>.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> Any one of you guys flying into Orlando on Sunday especially with Jeanne
in
> town that day ? Do you think the MCO airport might be shut down ? Or are
you
> folks trying to leave early ? Im planning to arrive on Sunday and not too
> sure if the airlines might even take off if the airport is closed down
> there.. Any news ?
> Ive got myself registered for the pre-conference on Monday and would not
> like to miss it ..
>
>|||Well all flights have been cancelled into Orlando beginning tomorrow...This
is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday after
5pm...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> It's hard to say if it will be closed or not but I changed my ticket from
> Sunday to Saturday and will have to leave a day earlier than planned. I
> figured if it wasn't closed it was going to be a bumpy ride<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Any one of you guys flying into Orlando on Sunday especially with Jeanne
> in
> > town that day ? Do you think the MCO airport might be shut down ? Or are
> you
> > folks trying to leave early ? Im planning to arrive on Sunday and not
too
> > sure if the airlines might even take off if the airport is closed down
> > there.. Any news ?
> > Ive got myself registered for the pre-conference on Monday and would not
> > like to miss it ..
> >
> >
> >
>|||Where did you hear this? I just looked at Orlando Airports web site and
they state they have no plans to close MCO (Orlando International) due to
Jeanne. They have closed other airports in the area but not Orlando
according to their web site.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O83EWupoEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Well all flights have been cancelled into Orlando beginning
tomorrow...This
> is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday after
> 5pm...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> > It's hard to say if it will be closed or not but I changed my ticket
from
> > Sunday to Saturday and will have to leave a day earlier than planned. I
> > figured if it wasn't closed it was going to be a bumpy ride<g>.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > Any one of you guys flying into Orlando on Sunday especially with
Jeanne
> > in
> > > town that day ? Do you think the MCO airport might be shut down ? Or
are
> > you
> > > folks trying to leave early ? Im planning to arrive on Sunday and not
> too
> > > sure if the airlines might even take off if the airport is closed down
> > > there.. Any news ?
> > > Ive got myself registered for the pre-conference on Monday and would
not
> > > like to miss it ..
> > >
> > >
> > >
> >
> >
>|||I called the number out there and also called Alaska airlines and they have
cancelled all flights up until Monday
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uMy$fbqoEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Where did you hear this? I just looked at Orlando Airports web site and
> they state they have no plans to close MCO (Orlando International) due to
> Jeanne. They have closed other airports in the area but not Orlando
> according to their web site.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O83EWupoEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > Well all flights have been cancelled into Orlando beginning
> tomorrow...This
> > is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday after
> > 5pm...
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> > > It's hard to say if it will be closed or not but I changed my ticket
> from
> > > Sunday to Saturday and will have to leave a day earlier than planned.
I
> > > figured if it wasn't closed it was going to be a bumpy ride<g>.
> > >
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > Any one of you guys flying into Orlando on Sunday especially with
> Jeanne
> > > in
> > > > town that day ? Do you think the MCO airport might be shut down ? Or
> are
> > > you
> > > > folks trying to leave early ? Im planning to arrive on Sunday and
not
> > too
> > > > sure if the airlines might even take off if the airport is closed
down
> > > > there.. Any news ?
> > > > Ive got myself registered for the pre-conference on Monday and would
> not
> > > > like to miss it ..
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Just to clarify as of 10 PM PST, the airport has mentioned of a cutoff time
of 5pm ET on Saturday..Not exactly certain at this point. But infact the
airlines are beginning to cancel.. Atleast Alaska airlines which is the
airline i was supposed to be travelling has cancelled all flights from
Saturday up until Monday. So there goes day 1 of the pre-conference. I hope
i get a refund from PASS.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:Odw0LwroEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I called the number out there and also called Alaska airlines and they
have
> cancelled all flights up until Monday
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uMy$fbqoEHA.3728@.TK2MSFTNGP09.phx.gbl...
> > Where did you hear this? I just looked at Orlando Airports web site and
> > they state they have no plans to close MCO (Orlando International) due
to
> > Jeanne. They have closed other airports in the area but not Orlando
> > according to their web site.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:O83EWupoEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > > Well all flights have been cancelled into Orlando beginning
> > tomorrow...This
> > > is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday
after
> > > 5pm...
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> > > > It's hard to say if it will be closed or not but I changed my ticket
> > from
> > > > Sunday to Saturday and will have to leave a day earlier than
planned.
> I
> > > > figured if it wasn't closed it was going to be a bumpy ride<g>.
> > > >
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > > Any one of you guys flying into Orlando on Sunday especially with
> > Jeanne
> > > > in
> > > > > town that day ? Do you think the MCO airport might be shut down ?
Or
> > are
> > > > you
> > > > > folks trying to leave early ? Im planning to arrive on Sunday and
> not
> > > too
> > > > > sure if the airlines might even take off if the airport is closed
> down
> > > > > there.. Any news ?
> > > > > Ive got myself registered for the pre-conference on Monday and
would
> > not
> > > > > like to miss it ..
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
town that day ? Do you think the MCO airport might be shut down ? Or are you
folks trying to leave early ? Im planning to arrive on Sunday and not too
sure if the airlines might even take off if the airport is closed down
there.. Any news ?
Ive got myself registered for the pre-conference on Monday and would not
like to miss it ..It's hard to say if it will be closed or not but I changed my ticket from
Sunday to Saturday and will have to leave a day earlier than planned. I
figured if it wasn't closed it was going to be a bumpy ride<g>.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> Any one of you guys flying into Orlando on Sunday especially with Jeanne
in
> town that day ? Do you think the MCO airport might be shut down ? Or are
you
> folks trying to leave early ? Im planning to arrive on Sunday and not too
> sure if the airlines might even take off if the airport is closed down
> there.. Any news ?
> Ive got myself registered for the pre-conference on Monday and would not
> like to miss it ..
>
>|||Well all flights have been cancelled into Orlando beginning tomorrow...This
is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday after
5pm...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> It's hard to say if it will be closed or not but I changed my ticket from
> Sunday to Saturday and will have to leave a day earlier than planned. I
> figured if it wasn't closed it was going to be a bumpy ride<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > Any one of you guys flying into Orlando on Sunday especially with Jeanne
> in
> > town that day ? Do you think the MCO airport might be shut down ? Or are
> you
> > folks trying to leave early ? Im planning to arrive on Sunday and not
too
> > sure if the airlines might even take off if the airport is closed down
> > there.. Any news ?
> > Ive got myself registered for the pre-conference on Monday and would not
> > like to miss it ..
> >
> >
> >
>|||Where did you hear this? I just looked at Orlando Airports web site and
they state they have no plans to close MCO (Orlando International) due to
Jeanne. They have closed other airports in the area but not Orlando
according to their web site.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O83EWupoEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Well all flights have been cancelled into Orlando beginning
tomorrow...This
> is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday after
> 5pm...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> > It's hard to say if it will be closed or not but I changed my ticket
from
> > Sunday to Saturday and will have to leave a day earlier than planned. I
> > figured if it wasn't closed it was going to be a bumpy ride<g>.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > Any one of you guys flying into Orlando on Sunday especially with
Jeanne
> > in
> > > town that day ? Do you think the MCO airport might be shut down ? Or
are
> > you
> > > folks trying to leave early ? Im planning to arrive on Sunday and not
> too
> > > sure if the airlines might even take off if the airport is closed down
> > > there.. Any news ?
> > > Ive got myself registered for the pre-conference on Monday and would
not
> > > like to miss it ..
> > >
> > >
> > >
> >
> >
>|||I called the number out there and also called Alaska airlines and they have
cancelled all flights up until Monday
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uMy$fbqoEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Where did you hear this? I just looked at Orlando Airports web site and
> they state they have no plans to close MCO (Orlando International) due to
> Jeanne. They have closed other airports in the area but not Orlando
> according to their web site.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O83EWupoEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > Well all flights have been cancelled into Orlando beginning
> tomorrow...This
> > is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday after
> > 5pm...
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> > > It's hard to say if it will be closed or not but I changed my ticket
> from
> > > Sunday to Saturday and will have to leave a day earlier than planned.
I
> > > figured if it wasn't closed it was going to be a bumpy ride<g>.
> > >
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > Any one of you guys flying into Orlando on Sunday especially with
> Jeanne
> > > in
> > > > town that day ? Do you think the MCO airport might be shut down ? Or
> are
> > > you
> > > > folks trying to leave early ? Im planning to arrive on Sunday and
not
> > too
> > > > sure if the airlines might even take off if the airport is closed
down
> > > > there.. Any news ?
> > > > Ive got myself registered for the pre-conference on Monday and would
> not
> > > > like to miss it ..
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Just to clarify as of 10 PM PST, the airport has mentioned of a cutoff time
of 5pm ET on Saturday..Not exactly certain at this point. But infact the
airlines are beginning to cancel.. Atleast Alaska airlines which is the
airline i was supposed to be travelling has cancelled all flights from
Saturday up until Monday. So there goes day 1 of the pre-conference. I hope
i get a refund from PASS.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:Odw0LwroEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I called the number out there and also called Alaska airlines and they
have
> cancelled all flights up until Monday
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uMy$fbqoEHA.3728@.TK2MSFTNGP09.phx.gbl...
> > Where did you hear this? I just looked at Orlando Airports web site and
> > they state they have no plans to close MCO (Orlando International) due
to
> > Jeanne. They have closed other airports in the area but not Orlando
> > according to their web site.
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:O83EWupoEHA.3324@.TK2MSFTNGP15.phx.gbl...
> > > Well all flights have been cancelled into Orlando beginning
> > tomorrow...This
> > > is sad.. Is PASS still on ? MCO is closed from tomorrow Saturday
after
> > > 5pm...
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OBBSMRpoEHA.648@.tk2msftngp13.phx.gbl...
> > > > It's hard to say if it will be closed or not but I changed my ticket
> > from
> > > > Sunday to Saturday and will have to leave a day earlier than
planned.
> I
> > > > figured if it wasn't closed it was going to be a bumpy ride<g>.
> > > >
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:e1NVTVooEHA.516@.TK2MSFTNGP09.phx.gbl...
> > > > > Any one of you guys flying into Orlando on Sunday especially with
> > Jeanne
> > > > in
> > > > > town that day ? Do you think the MCO airport might be shut down ?
Or
> > are
> > > > you
> > > > > folks trying to leave early ? Im planning to arrive on Sunday and
> not
> > > too
> > > > > sure if the airlines might even take off if the airport is closed
> down
> > > > > there.. Any news ?
> > > > > Ive got myself registered for the pre-conference on Monday and
would
> > not
> > > > > like to miss it ..
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)