Passing GUID into dtexec, invalid cast to a string variable. Solution?

I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.

Thanks for any help! Aaron B.

What does your SET look like, this works fine for me. I just set it, and check the value in a Script Task, just to ensure the value is being passed in OK.

/SET "\Package.Variables[StringVariable].Value";"{2B7045E0-F3D2-478a-BCC3-0E73858C59A8}"

|||

Thanks for your help i am using another field for the subquery which is not GUID and its working fine. Thanks for your help.

Passing dates to ServerFilter from Access

Hello.
I have a solution written in Access. In order to filter a
report a date is passed the the serverfilter property.
e.g. MyFilterDate='01/28/2004'
This works fine on one machine but on another machine I
get an overflow error due to the fact that date is passed
in American Format.
Both machines appear configured with the same regional
settings, the VBA references are the same and anything I
have thought of have been the same.
They are both connected to the same SQL server.
Can anyone help?
If you pass in the date as 'yyyymmdd' it will never get confused.
Andrew J. Kelly SQL MVP
"Scamps" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
> Hello.
> I have a solution written in Access. In order to filter a
> report a date is passed the the serverfilter property.
> e.g. MyFilterDate='01/28/2004'
> This works fine on one machine but on another machine I
> get an overflow error due to the fact that date is passed
> in American Format.
> Both machines appear configured with the same regional
> settings, the VBA references are the same and anything I
> have thought of have been the same.
> They are both connected to the same SQL server.
> Can anyone help?
|||Thank you very much.
Problem appears solved immediately.
>--Original Message--
>If you pass in the date as 'yyyymmdd' it will never get
confused.
>--
>Andrew J. Kelly SQL MVP
>
>"Scamps" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
filter a[vbcol=seagreen]
passed[vbcol=seagreen]
I
>
>.
>

Passing a variable to a job

Is it possible to pass a global variable to a SQL Server 2000 job? Or
is there another solution that I can use - I don't want to store what
I'm passing through as plain text.
Thanks,
SaulHi
Do you mean if you call a stored procedure you'd want to pass a paremeter to
this sp from the client?
As far as I know you cannot do that , however you may want to consider
using DTS Packages which do accepts parameters
<saulmarg@.gmail.com> wrote in message
news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> Is it possible to pass a global variable to a SQL Server 2000 job? Or
> is there another solution that I can use - I don't want to store what
> I'm passing through as plain text.
> Thanks,
> Saul
>|||Hi Uri,
I'm trying to get a VBA app to call a job to call a DTS package which
calls the BCP application to transfer data between two databases. The
BCP app needs a password to access the server.
Any suggestions?
Uri Dimant wrote:
> Hi
> Do you mean if you call a stored procedure you'd want to pass a paremeter to
> this sp from the client?
> As far as I know you cannot do that , however you may want to consider
> using DTS Packages which do accepts parameters
>
>
> <saulmarg@.gmail.com> wrote in message
> news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > is there another solution that I can use - I don't want to store what
> > I'm passing through as plain text.
> >
> > Thanks,
> > Saul
> >|||Why do you call a job that activates the DTS package? You can activate
the DTS package directly from the application. If you'll activate the
DTS, you'll be able to pass it parameters. If for some reason you have
to use job between the application and the DTS package, then you can
insert you parameter into a table before you run the job. In the
begining of the job you run a select statement on that table in get the
value that was inserted into the table.
Adi
saulmarg@.gmail.com wrote:
> Hi Uri,
> I'm trying to get a VBA app to call a job to call a DTS package which
> calls the BCP application to transfer data between two databases. The
> BCP app needs a password to access the server.
> Any suggestions?
>
> Uri Dimant wrote:
> > Hi
> >
> > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > this sp from the client?
> >
> > As far as I know you cannot do that , however you may want to consider
> > using DTS Packages which do accepts parameters
> >
> >
> >
> >
> > <saulmarg@.gmail.com> wrote in message
> > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > is there another solution that I can use - I don't want to store what
> > > I'm passing through as plain text.
> > >
> > > Thanks,
> > > Saul
> > >|||Thanks Adi. I use the job because the VBA app doesn't have features to
record history of jobs etc. The only problem with putting the
parameter into a table is that it's a password which I'd prefer not to
store anywhere. Is there any built in SQL functionality to encrypt and
decrypt?
Adi wrote:
> Why do you call a job that activates the DTS package? You can activate
> the DTS package directly from the application. If you'll activate the
> DTS, you'll be able to pass it parameters. If for some reason you have
> to use job between the application and the DTS package, then you can
> insert you parameter into a table before you run the job. In the
> begining of the job you run a select statement on that table in get the
> value that was inserted into the table.
> Adi
> saulmarg@.gmail.com wrote:
> > Hi Uri,
> >
> > I'm trying to get a VBA app to call a job to call a DTS package which
> > calls the BCP application to transfer data between two databases. The
> > BCP app needs a password to access the server.
> >
> > Any suggestions?
> >
> >
> > Uri Dimant wrote:
> > > Hi
> > >
> > > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > > this sp from the client?
> > >
> > > As far as I know you cannot do that , however you may want to consider
> > > using DTS Packages which do accepts parameters
> > >
> > >
> > >
> > >
> > > <saulmarg@.gmail.com> wrote in message
> > > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > > is there another solution that I can use - I don't want to store what
> > > > I'm passing through as plain text.
> > > >
> > > > Thanks,
> > > > Saul
> > > >|||Unfortunately there isn't a built in functionality to encrypt and
decrypt in SQL Server 2000. You can try and develop an extended stored
procedure that does it, but this seems to be a to big and to
complicated task to do:-). I'm sure that there are other companies
that developed such extended stored procedure, but since I didn't need
one I can't recommend a good product (and of course this will cost you
some money). Maybe you can still use the table. You can play around
with the table's permissions and not let anyone run select on this
table. You can let the user that you use in the connection string run
only insert on the table (and not even select) and in the job run the
select statement that will get the password (I assume that the SQL
agent is configured to run under the administrator's account, so the
job would be able to run the select statement).
Adi
saulmarg@.gmail.com wrote:
> Thanks Adi. I use the job because the VBA app doesn't have features to
> record history of jobs etc. The only problem with putting the
> parameter into a table is that it's a password which I'd prefer not to
> store anywhere. Is there any built in SQL functionality to encrypt and
> decrypt?
>
> Adi wrote:
> > Why do you call a job that activates the DTS package? You can activate
> > the DTS package directly from the application. If you'll activate the
> > DTS, you'll be able to pass it parameters. If for some reason you have
> > to use job between the application and the DTS package, then you can
> > insert you parameter into a table before you run the job. In the
> > begining of the job you run a select statement on that table in get the
> > value that was inserted into the table.
> >
> > Adi
> >
> > saulmarg@.gmail.com wrote:
> > > Hi Uri,
> > >
> > > I'm trying to get a VBA app to call a job to call a DTS package which
> > > calls the BCP application to transfer data between two databases. The
> > > BCP app needs a password to access the server.
> > >
> > > Any suggestions?
> > >
> > >
> > > Uri Dimant wrote:
> > > > Hi
> > > >
> > > > Do you mean if you call a stored procedure you'd want to pass a paremeter to
> > > > this sp from the client?
> > > >
> > > > As far as I know you cannot do that , however you may want to consider
> > > > using DTS Packages which do accepts parameters
> > > >
> > > >
> > > >
> > > >
> > > > <saulmarg@.gmail.com> wrote in message
> > > > news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> > > > > Is it possible to pass a global variable to a SQL Server 2000 job? Or
> > > > > is there another solution that I can use - I don't want to store what
> > > > > I'm passing through as plain text.
> > > > >
> > > > > Thanks,
> > > > > Saul
> > > > >

Passing a variable to a job

Is it possible to pass a global variable to a SQL Server 2000 job? Or
is there another solution that I can use - I don't want to store what
I'm passing through as plain text.
Thanks,
SaulHi
Do you mean if you call a stored procedure you'd want to pass a paremeter to
this sp from the client?
As far as I know you cannot do that , however you may want to consider
using DTS Packages which do accepts parameters
<saulmarg@.gmail.com> wrote in message
news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...
> Is it possible to pass a global variable to a SQL Server 2000 job? Or
> is there another solution that I can use - I don't want to store what
> I'm passing through as plain text.
> Thanks,
> Saul
>|||Hi Uri,
I'm trying to get a VBA app to call a job to call a DTS package which
calls the BCP application to transfer data between two databases. The
BCP app needs a password to access the server.
Any suggestions?
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> Do you mean if you call a stored procedure you'd want to pass a paremeter
to
> this sp from the client?
> As far as I know you cannot do that , however you may want to consider
> using DTS Packages which do accepts parameters
>
>
> <saulmarg@.gmail.com> wrote in message
> news:1152168729.543700.124180@.p79g2000cwp.googlegroups.com...|||Why do you call a job that activates the DTS package? You can activate
the DTS package directly from the application. If you'll activate the
DTS, you'll be able to pass it parameters. If for some reason you have
to use job between the application and the DTS package, then you can
insert you parameter into a table before you run the job. In the
begining of the job you run a select statement on that table in get the
value that was inserted into the table.
Adi
saulmarg@.gmail.com wrote:[vbcol=seagreen]
> Hi Uri,
> I'm trying to get a VBA app to call a job to call a DTS package which
> calls the BCP application to transfer data between two databases. The
> BCP app needs a password to access the server.
> Any suggestions?
>
> Uri Dimant wrote:|||Thanks Adi. I use the job because the VBA app doesn't have features to
record history of jobs etc. The only problem with putting the
parameter into a table is that it's a password which I'd prefer not to
store anywhere. Is there any built in SQL functionality to encrypt and
decrypt?
Adi wrote:[vbcol=seagreen]
> Why do you call a job that activates the DTS package? You can activate
> the DTS package directly from the application. If you'll activate the
> DTS, you'll be able to pass it parameters. If for some reason you have
> to use job between the application and the DTS package, then you can
> insert you parameter into a table before you run the job. In the
> begining of the job you run a select statement on that table in get the
> value that was inserted into the table.
> Adi
> saulmarg@.gmail.com wrote:|||Unfortunately there isn't a built in functionality to encrypt and
decrypt in SQL Server 2000. You can try and develop an extended stored
procedure that does it, but this seems to be a to big and to
complicated task to do:-). I'm sure that there are other companies
that developed such extended stored procedure, but since I didn't need
one I can't recommend a good product (and of course this will cost you
some money). Maybe you can still use the table. You can play around
with the table's permissions and not let anyone run select on this
table. You can let the user that you use in the connection string run
only insert on the table (and not even select) and in the job run the
select statement that will get the password (I assume that the SQL
agent is configured to run under the administrator's account, so the
job would be able to run the select statement).
Adi
saulmarg@.gmail.com wrote:[vbcol=seagreen]
> Thanks Adi. I use the job because the VBA app doesn't have features to
> record history of jobs etc. The only problem with putting the
> parameter into a table is that it's a password which I'd prefer not to
> store anywhere. Is there any built in SQL functionality to encrypt and
> decrypt?
>
> Adi wrote:

Passing a RS authentication ticket/cookie to browser?

Hello,
It looks like the best solution for deploying my reports is through the
hybrid solution of using both URL access (a url over the internet to
render the report) and SOAP API access (a web service call to
authenticate via Forms Authentication, and a custom security
extension).
I'm reasonably clear about how to implement my RS Web service proxy,
and to gain access to the authentication ticket/cookie passed back from
RS once I am authenticated. I'd like to pass this authentication
ticket to a browser so I can use URL Access for the rest of my RS
session. How do I go about doing this?
I want the browser to take advantage of the RS HTML Viewer tool for
report rendering.
My Web application that's using reporting services is not an ASP.NET
application.
Any ideas would be a great help.
Thanks,
ZeroVerticalI am not sure what exactly your application topology is. Where are you
making the LogonUser API call? In general, you have to do the same thing
that Forms Authentication sample does in order to move the cookie from the
server call to the browser. If you have two applications, they have to be on
the same domain.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"zerovertical" <zerovertical@.yahoo.com> wrote in message
news:1132773532.572637.207830@.g47g2000cwa.googlegroups.com...
> Hello,
> It looks like the best solution for deploying my reports is through the
> hybrid solution of using both URL access (a url over the internet to
> render the report) and SOAP API access (a web service call to
> authenticate via Forms Authentication, and a custom security
> extension).
> I'm reasonably clear about how to implement my RS Web service proxy,
> and to gain access to the authentication ticket/cookie passed back from
> RS once I am authenticated. I'd like to pass this authentication
> ticket to a browser so I can use URL Access for the rest of my RS
> session. How do I go about doing this?
> I want the browser to take advantage of the RS HTML Viewer tool for
> report rendering.
> My Web application that's using reporting services is not an ASP.NET
> application.
> Any ideas would be a great help.
> Thanks,
> ZeroVertical
>

pass sql parameter to report

greetingz,

i would really need some help with parameter passing, i spent my whole day searching for a solution but no dice. i never used these business intelligence reports before so i only know a fhew basic thigs.
i managed to make a report, but the sql querys require a parameter as input. now this would be unacceptable, i cant expect the user to fill it in every time he wants a report of a bill. so i have a gridview with some filterable data, and when the user selects a row i want the row's specific col entry to be passed to the report on a new page as a parameter

i saw some people say that i can pass this in the URL with ¶mname=x/y/z, yea life never went easy on me so of course it must requre some settings that i dont know how to do
i would prefer to pass things in code as some report parameter but i dont even know how or where i would begin with that

for example here's a query

1Select2case szf_fizmod3when 1then'átutalás'4when 0then'Készpénz'5end as'Fizetési mód',6 szf_teljdatas'Teljesítés ideje', szf_szkelteas'Sámla kelte', szf_fizhatidoas'Fizetési határid?', szf_szamas'Számlasorszám', szf_megjas Megjegyzés78from szamlafej9where szf_szamlike @.szam

now i would need the @.szam to be populated for it to give back anything

thanks

If you run it in an ASP.NET page you can use code like mine:

protected void Page_Init(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
// Set the processing mode for the ReportViewer to Remote
reportViewer.ProcessingMode = ProcessingMode.Remote; //ProcessingMode="Remote"
ServerReport serverReport = reportViewer.ServerReport;
// Set the report server URL and report path

NameValueCollection appSettings = WebConfigurationManager.AppSettings as NameValueCollection;

serverReport.ReportServerUrl = new Uri(appSettings["SSRSReportServer"]);
serverReport.ReportPath = "/OTDReports/OTDMain";

// Create the parameters
ReportParameter FromDate = new ReportParameter(); FromDate.Name = "FromDate";
FromDate.Values.Add(Request["TextBoxFromDate"]); FromDate.Visible = false;
ReportParameter ToDate = new ReportParameter(); ToDate.Name = "ToDate";
ToDate.Values.Add(Request["TextBoxToDate"]); ToDate.Visible = false;

ReportParameter HrefBase = new ReportParameter(); HrefBase.Name = "HrefBase";
HrefBase.Values.Add("http://" + Request.ServerVariables["SERVER_NAME"] + Request.ServerVariables["URL"].Remove(Request.ServerVariables["URL"].LastIndexOf("/")));
HrefBase.Visible = false;

reportViewer.ServerReport.SetParameters(new ReportParameter[] { FromDate, ToDate , HrefBase });

}
}

|||

ahh thankyou,

now my question is, when i try to load the page i get an error saying i'm not authorised to view the report server due to insufficient credentials. i searched around the forums and found which seems like a solution

ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

only problem is, it doesnt know what ReportingService is, but i searched in the help and it seems its under system.web.services(.soaphttpclient) which is in the references, but still no dice

|||

This a security issue!

You should stay with only windows authorisation in your website

I do nothing for credentials and every thing works fine.

|||

the site would work like at the login page the user logs in to a sql account on the sql server, and this account would determine what he can do on the page and i thought i'll set the report viewing like this also, so this is not supported, or just comes with too much fuss?