Hi,
is it possible to build a SQL SELECT COMMAND with a Cookie Request in between a IF ELSE Loop?
My idea is to change the SQL SELECT COMMAND depending to a cookie.
Is there any help or tutorial that somebody can suggest?
caspar.net
cologne, germany, EU
Hello, I have run into a strange problem with RS 2000. I have a base class that will call a reporting server web service, render a report to pdf, then write the report output to the stream. This has worked fine for me for reports.
However, when I render a graph to pdf, I get the following exception:
[ArgumentException: cookie.Domain]
System.Net.CookieContainer.Add(Cookie cookie) +583
Learning.Common.Reporting.ReportServerProxy.GetWebRequest(Uri uri)
...
I only get this exception in our staging environment, not locally.
Here is a code snippet from the ReportServerProxy class, but it is pretty textbook according to the MSDN samples..
public class ReportServerProxy : ReportingService {
protected override WebRequest GetWebRequest(Uri uri) {
HttpWebRequest request;
request = (HttpWebRequest)HttpWebRequest.Create(uri);
// Create a cookie jar to hold the request cookie
CookieContainer cookieJar = new CookieContainer();
request.CookieContainer = cookieJar;
Cookie authCookie = AuthCookie;
// if the client already has an auth cookie
// place it in the request's cookie container
if (authCookie != null){
request.CookieContainer.Add(authCookie);
}
...
}
As I said, I can run reports one after another without issue, graphs are causing me problems.
I put HttpWebRequest in a CLR so I can call website in my SQL. In same calls, I need pass cookies from the previous call to the next call. So I am thinking I can do this in two ways:
1, store the cookiecontainer somewhere , but not sure if CLR allow me to do that ,
2, return the cookiecontainer, and send it to the next call from SQL, this way I need use cookie string, is there a function to serialize a cookiecontainer?
thanks
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost
Servername: DED1774 (the machine name)
Servername: localhost/reportserver
Servername: DED1774/reportserver
Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Thanks
i created a cookie as follows
HttpCookie myCookie = new HttpCookie("Portal");
myCookie["EMail_ID"] = Email_ID;myCookie["Role"] = Role_ID.Value.ToString();myCookie.Expires = DateTime.Now.AddMinutes(1);
Response.Cookies.Add(myCookie);
Now i have to take the"EMail_ID" as input parameter in sqldatasourse.
<asp:SqlDataSource ID="SqlDS_DC_List" runat="server" ConnectionString="<%$ ConnectionStrings:EnR_Portal_ConnectionString %>"
SelectCommand="proc_DC_List" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:CookieParameter CookieName=" " Name="Email_ID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
how can i specify Email ID as cookie name?
does anyone know how i can make it so you can view all the users in the database on a page and there names are clickable with a link and then shows there profile page which is basicly a text box that all users can edit there own.
i have a "users" table and mysite stores there username in a cookie called user for the session.
any help will be very very grateful
I am trying to setup custom forms authentication in Reporting Services.
I have followed the UFAIRS sample. I have the site running, reporting services running, and I have a page where I can input my login information.
I can debug the code- and I get to this section of code:
Protected Overrides Function GetWebResponse(ByVal request As WebRequest) As WebResponse
Dim response As WebResponse = MyBase.GetWebResponse(request)
Dim cookieName As String = response.Headers("RSAuthenticationHeader")
' If the response contains an auth header, store the cookie
If Not (cookieName Is Nothing) Then
Utilities.CustomAuthCookieName = cookieName
Dim webResponse As HttpWebResponse = CType(response, HttpWebResponse)
Dim authCookie As Cookie = webResponse.Cookies(cookieName)
' If the auth cookie is null, throw an exception
If authCookie Is Nothing Then
Throw New Exception("Authorization ticket not received by LogonUser")
End If
' otherwise save it for this request
Me.AuthCookie = authCookie
' and send it to the client
Utilities.RelayCookieToClient(authCookie)
End If
Return response
Notice the line: Dim cookieName As String = response.Headers("RSAuthenticationHeader")
Basically the cookie name is null after that, and therefore it processes no further and I never get a cookie and I can never authenticate. Any thoughts as to what I am doing wrong?
Also, this is on a test box, so I don't have SSL capabilities. Unless there is a way to "emulate" SSL, I pretty much don't know if this works without SSL, or if this is causing the problem. We have environments that use SSL, but I do not have access to it at the moment- I will have to do additional work to get that up and running.
Thanks in advance,
Robert
I need to write a SQL String to retrieve a field value from a table. The problem is that I need to supply the table name as a parameter. If I was just updating a table, I could build a dynamic SQL String and use Exec()
This is what I would write if the name of the table was known:
Select @RecordNo = MAx([VehicleID]) from Alto
This is what my dynamic SQl string looks like:
Select @SqlStatement = 'Select Max([VehicleID]) from ' + @TableName
So how do I run this statement and get the value it would return? Is there an equivalent to exec() that returns a value?
Hello,
I'm trying to build a dynamic SQL string in the following
SP, but the ' and "" character gives me trouble regarding
the @strPosition. Position could be A,B,C etc. All kinds of
suggestions are very welcome!
Regards,
Abrazame
CREATE PROCEDURE spEstateList
@PositionID nvarchar(1)=NULL
AS
DECLARE @strPosition nvarchar(20),
@strSQL nvarchar(500)
/* Build WHERE-string */
--Position
IF @PositionID IS NULL
SELECT @strPosition = '1=1'
ELSE
SELECT @strPosition = 'tblEstate.Position=' +@PositionID
/* Build complete SQL-string */
SELECT @strSQL = 'SELECT *
FROM tblEstate
WHERE ' +@strPosition + '
ORDER BY EstateID'
/* Execute SQL string.*/
EXEC sp_executesql @strSQL
I'm building a select string on the fly based on criteria selected by the user. The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel. Everything is working fine, except when the Name has an ' in it. For example O'Kelly or St. John's. I know I can take all the 's out of the database, but I'd rather keep the data authentic. Is there a way to manipulate a select string built on the fly accounting for an embedded '?
For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up.
Any ideas?
Sample Code:<code> For Each SelectedIndex In rsc.SelectedIndexes Counter=Counter + 1 dgAssociates.SelectedIndex = SelectedIndex If Counter = 1 then SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'" Else SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'" End If Next If Counter > 0 then SelectString &= ")" End If</code>
I have the following data in a table;
Create Table #Table (Column1 Varchar(1000))
INSERT INTO #Table
Select 'Execute Procedure @param1 = 100, @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param1 = 10, @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param1 = 100000, @param2 = 1000, @param3 = ''longtext'' '
Select * from #Table
I want to get rid of @Param1 in every row of the table so my final results look like this.
Create Table #FinalTable (Column1 Varchar(1000))
INSERT INTO #FinalTable
Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '
UNION ALL
Select 'Execute Procedure @param2 = 1000, @param3 = ''longtext'' '
Select * from #FinalTable
The tricky part is that the @param1 value length varies so a straight forward substring or replace function won't work.
Hey guys, I 'm coding my very first stored procedure as accessed by a
.NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl
Help would be appreciated. Thank you.
We were able to use a dynamic connection string in the report designer, but once we deployed to the report server we are getting the following error:
Error during processing of the ConnectString expression of datasource €˜Dynam€™. Has anyone experienced this, and how did you fix it?
Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group€™s connection to Oracle. All subsequent connections to Oracle will use the group€™s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.
View 7 Replies View RelatedI need to be able to deploy my updated website to many customers on a monthly basis and dont want to be mucking around changing the connection strings each time. Some of my web servers have multiple copies of my site and DB so each website will need a different connection string.
The simplest method I could come up with is to use the Application Name field in IIS as it doesn't get overwritten by Visual Studio when I deploy the site.
I am trying to write some code to dynamically change the connection string in the web config but cannot find any way of reading the Application Name field in IIS to use in altering the connection string. I'm using the Global.asax file to change the connection string before the DB gets called.
I had tried embedding the DB in the website folder but it would overwrite the customers database.
I'm constructing a SQL string that needs single quotes in the WHERE clause. How do I encapsulate them in a string variable. I looked into ESCAPE and SET QUOTED_IDENTIFIER, but i don't really see any examples using string Concatenation. I'm trying to filter out the zls (0 length strings)
This doesn't work (all single quotes):
@sqlString = ' SELECT * FROM myTbl '
@sqlString = @sqlString + 'WHERE fld1 <>'' '
Thanks,
Carl
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View 5 Replies View RelatedHi,
In c# - how to pass uid,pwd,dbname and servername as input parameters from vs 2003 windows application (am calling the rdl file from reporting service 2005 web service) to sql server 2005 rdl files.
Thanks,
Shanthi
i have successfully implemented a dynamic connection string based on a dropdown list of environments (dev, test, prod). it works well during testing in the vs2005 ide; but once i deploy it to the rs server, it complains that the credentials are not stored in the rs server database and won't run the report.
as in most large organizations, the developers do not have control over the rs server, so i cannot manipulate rs config or web config files on the server side; so, how do i get past this obstacle?
thanks in advance
Hi,
Is there any way to Dynamically change(Based on User login) Connection String for a report data source from code behind. In My application each user may have different data base. I am using a single shared data source for all the report. Please give me a solution.
Thanks
Sonu
This method has worked beautifully for all my SSIS pkgs thus far.
Basically, I use a Script Task to derive the name of the newest file in a local directory. Then I save the name of the file to user a user variable, e.g. User::File.
Then, in my flat file properties > Expressions, I set "ConnectionString" to reference User::File.
However, when attempting to use this method with an Excel source, I get this error message:
Error at myPkg [Connection manager "Excel Connection Manager"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error at myPkg: The result of the expression "@[User::Folder]+ @[User::File]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
HELP......... I need this to work!
Thanks
I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)
That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.
I have a table with the following data;
CREATE TABLE #Tab (Data Varchar(100))
INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab
How do I replace every value before the '=' but leave the comma.
Here is what the final output should look like
CREATE TABLE #TabFinal (Data Varchar(100))
INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'
Select * from #TabFinal
HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)
View 2 Replies View RelatedI have a package where I need a dynamic connection string for an Analysis Services connection manager.
I have implemented this successfully for a Text data source, and a SQL data source, but the same approach does not seem to be working for an AS connection.
I set some expressions for the AS connection manager (ServerName, InitialCatalog, even the entire ConnectionString itself), but they don't take. I don't get any errors, but the task processes the cubes for the AS connection as it was established at run time. The design time connection string changes don't appear to get evaluated. This seems to be an issue only for AS connections.
Let me know if you have any ideas - thanks.
I am trying to write where clause after pivot but I could not know how?
DECLARE @PivotQuery AS NVARCHAR(MAX)
Has anyone been able to find a way to use a stored procedure with a dynamic connection string?
Hi, I'm a newbie to SSRS, and was wondering if anybody can shed light on a problem I have. I have a report which every client uses, but each client's data is held in its own database. Rather than create many reports, is it possible to create 1 report, which all can use, passing in the different datasource? I was thinking of a hidden parameter, passed by URL. Or maybe using the report viewer control in VS2005. Can anybody please help?
Thanks
Dan
Hi all,
I am able to set dynamic source for the text file(flat file) but i want to set the connection string (file name) to excel source dynamicaly. I have tryied lots of time by taking a variable in foreachloop container . Variable is itself able to pick the file name dynamicaly but whern i am tying to set connectionstring to excel source it gives error.
Steps that i have done: -
1) Drag foreachloop container
2) set directory,FileNameRetrieval,FileSpec
3) Made VariableMapping
4) Now drag a dataflow task in the foreachloop container
5) select excel source
6) When i am selecting varaible as connectin string from properties of excel connectin manager, i am getting this error : -
TITLE: Microsoft Visual Studio
------------------------------
Error at Package3 [Connection manager "Excel Connection Manager 2"]: An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Please help me, whats the problem? can i set connectionstring via variable to excel source
Hello,
Kindly give me the solution ASAP how to do Dyanmic Connection in ExcelConnection manager.
Thanks
Thiru
Hi,
View 3 Replies View RelatedI'm trying to setup a dynamic ole db connection using the SA user ID, it has to be dynamic because the server name will change and it has to be SA because we're pulling information from system databases that some users don't have access to.
If I setup a regular static connection using SA credentials it works like a charm of course. When I create an expression to use the User:erver variable it doesn't work, it throws an error message saying that "The login failed for user sa" among other things, I'm thiking that the sa's password is not being saved.
Where exactly do I place a password for dynamic connections using sql server users? On the connection string? On the password property of the source? Any ideas?
Hi All,
The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?
Thanks,
Manoj Kumar