i want to modify my DTS to don't have any maintenance to do on it if a Username or password change.
Each DTS will have an ActiveX script to read connections properties in a table that i created and will automatically set each connection in the DTS each time that the DTS Run. So, we will have only to update the table and the DTS will continue working fine.
To update the Connections properties i have no problem, the problem is how can i retrieve the data from my table to get the connection properties, without hardcoding a connection inside my ActiveX script? Because i don't want to have to change each ActiveX script in each DTS if the SQL Server paswword change.
I have a SSRS report that I'm passing a dynamic parameter to another SSRS report. The datasourse is a SSAS cube. Can anyone tell me the if following syntax is correct for receiveing report. I'm having trouble creating the datasource for the second report. I'm new at SSRS so any help would be appreciated.
When I parse this code in management Studio I get the following An MDX Statement was expected. An MDX expression was specified.
'= [SELECT NON EMPTY { [Measures]].[Qty Paid]], [Measures]].[US Cost]], [Measures]].[Qty Recd]] } ON COLUMNS, NON EMPTY
When I run the query below on my DB, it gives the following error: Server: Msg 8114, Level 16, State 5, Procedure partstlist ...Error converting data type varchar to float. Only after I replace the " + @d + " section with a float numnber such as 39020.5 does it work fineIt's reported that my temporary table named #tbl doesn't exist within the scope of the environment that the EXEC(UTE) command operates.Someone said that I couldn't combine dynamic SQL and temporary tables or table variables...and that I would have to use a global temp table "##tbl" or a permanent table. So here I have appearantly a "DYNAMiC SQL & TEMPORARY TABLE" problem. That's sure. But I don't have much knowledge and experience with SQL tables and have difficulty forming a global temp table "##tbl" or a permanent table.and I have to employ the datetime variable dynamically @d inside the Dynamic SQL string. So what kind of a code should I use here to properly combine Dynamic SQL string with my dynamic datetime variable? I'd be grateful if you could post your code suggestions so that I can try them on my DB .. alter PROCEDURE partslist@no INT,@dt DATETiMEASDECLARE @s VARCHAR(5000)DECLARE @d floatSET @d = CONVERT (float, @dt2)create TABLE #tbl(pageindex int IDENTITY(1,1) PRIMARY KEY , prt_name varchar(50), prt_country varchar(50) , prt_date smalldatetime, partID int , prt_cat varchar(50),prt_product_type nvarchar(10), producer_company nvarchar(50), producer_city nvarchar(50), prt_released DATETIME, dvalue float, datevalue varchar(26) )BEGiNSET ANSI_WARNINGS OFFSET ROWCOUNT @noSELECT @s = 'INSERT INTO #tbl SELECT prt_name, Countries.Val AS ''prt_country'' , prt_date, partID , partsCategories.Val AS ''prt_cat'' , partsProductTypes.Val AS ''prt_product_type'' , producers.producer_company AS ''producer_company'' , producers.producer_city AS ''producer_city'' , prt_released , CONVERT (float, prt_released) , CONVERT (varchar(26) , prt_released, 109) FROM producer_parts JOIN partsProductTypes ON (producer_parts.prt_product_type = partsProductTypes.ID) JOIN partsCategories ON (producer_parts.prt_cat = partsCategories.ID)JOIN Countries ON (producer_parts.prt_country = Countries.ID) JOIN producers ON (producer_parts.producerID = producers.producerID) WHERE prt_visible = 1 AND CONVERT (float, prt_released) > ' + @d + ' AND CONVERT (float, prt_released) < 39025.5ORDER BY prt_released DESC SELECT * FROM #tbl'EXEC (@s)SET ROWCOUNT 0ENDGO partslist 10 , '10.10.2006 10:10:10' -- This is just to test the SP. This code may not be syntatically correct, it's a re-edited version of my code which works fine in my DB.
I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.
Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.
I'm working with Schedule Subcription with parameter @startdate in SQL 2005 Standard version. The @startdate is depend on user needs, I've tried to use DayAdd() function in the parameter but don't work. How can I set the parameter in subcription to do it? Thanks!
I have been trying to add in date functions for parameters in a report subscription and they are not accepted. I have a report with 2 parameters, @start and @end. I have default parameters set wtihin the report of Now - month for @start and Now for @end. Can my users create a subscriotion using something like "=DateAdd("D", -1, Now)" for the parameters rather than the default?
I use a DataGrid to show the data, and I want it has a sorting and Paging function, so I use dataset to collect the data from middle tier function and stored procedure. I have code in aspx page like BindData(ViewState("SortExpr")).
In the stored procedure I pass SortExpr as parameter as following:
DECLARE @RowCount int SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0 BEGIN SELECT z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance /* The above functions requires the Distance Assistant. */ FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a WHERE z.Latitude <= r.MaxLat AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND z.CityType = 'D' AND z.ZIPCodeType <> 'M' AND z.ZIPCode = a.Zipcode AND a.AdActive = '1' AND a.AdExpiredate >= getdate() AND a.Make = @Make AND a.Model = IsNull(@Model,a.Model) AND a.Condition = IsNull(@Condition, a.Condition) AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles ORDER BY @SortExpr END ELSE SELECT -1 As ZIPCode --ZIP Code not found... GO
but I got the error as "variables are only allowed when ordering by an expression referenceing a column name". How I fix this error? Please help.
I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer. But when I try to get values in asp.net page it does not return anything. No execeptions or errors also. Below is the code snippet HDConn = CommonMethods.BuildConnection();SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);cmd1.CommandType = CommandType.StoredProcedure;// add parameters for Proccmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;try{cmd1.ExecuteNonQuery();//the below line does not print the valueResponse.Write(cmd1.Parameters["@rowidval"].Value.ToString());}My stored proc looks like :CREATE PROCEDURE dbo.GenerateRowids@TableName varchar(50),@ColumnName varchar(50),@rowidval varchar(30) outputASBegindeclare @sql nvarchar(4000), @Idval varchar(50) set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableNameexec sp_executesql @query = @sql,@params = N'@rowidval varchar OUTPUT', @rowidval = @rowidval OUTPUT ENDGO
So far so good. I want to send this report by email everyday, so I need to set these parameters as a default value. This is where I run into problems.
When I add any of these parameters I get the following error "The restrictions by the CONSTRAINED flag in the STRTOSET function were violated". This problem occur when I choose NON-queried under Report->Report Parameters, and set a default value for THISYEAR under DEFAULT VALUES
I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.
I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.
I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.
I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply: The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)
I have 2 tables (table1KKK, table2KKK), and want to run the same query on them by using parameter with the value "1" or "2". Is it possible to use that parameter as a part of the queried table name? Something like (only for demonstration €“ doesn€™t work): Select * From table +myParameter + kkk Thanks in advance!
I am trying to write a query for the default values for month, and I am stuck in bringing out only the parameter values, the query I have , brings back the month on row and value on column, and I am trying to get the value out. and will ssrs accept it as a valid expression for a default value
WITHÂ MEMBER [MEASURES].[PARAMETERVALUE] AS [Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME
[Code] .....
I tried using the exp
=Parameters!MONTH.Value(0)
so as to return the first parameter in the parameter dataset , but i have the error the expression that ref the parameter MONTH does not exists in the parameter collection.
I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck. Error: Unterminated String Constant. What is the problem? Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
Hi, SSIS packages are now created and tested against dev sql server. Now I would like them all to point to the live sql server. What is the best way to have the connection dynamic? Obviously I prefer not to go through each package and change the connection manager. Thanks
A how to question...I have to run a querys on 19 as400 which means i have to supply 19 server,library and file names then run query and update destination table.
Idea was to create a table with server,library and file names and query iam not able to figure out how iam going to make the connection manager to dynamically take this parameters.
Any help appreciated and thanks for taking time to read this issue.
Is there any possibility to have the  Defaultvalue of  a Parameter in Bold fonts and rest of it as Default parametervalue=Defaultvalue ,"Bold","Default".So for example in drop down of parameter if the C is the current value it would look like this:
Hi, I am looking to allow a user to select which database they need to connect to, and then for them to be able to use that connection string until they choose a different database. I have a separate database specifically to hold the list of databases with the respective connection strings, and various forms that are currently looking at the web.config for the connection string. Unfortunately, I can't just put all the connection strings into the web.config as the number of databases available to the user will increase on a weekly basis which will be handled by a database administrator, so I want a user to select from a list of databases (for which they have permissions), and for that connection string to remain for that user. Potentially there will be 20 or more users each connected to a different database (all SQL Express). If anyone can provide any help on this I will be truly thankful as I've hunted on the internet for hours and have been unable to find anything that has helped. Thanks in advance. Paul
Dynamic connection strings I have found lots of people asking for something similar to this but cannot find any solutions - can you help? I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database. Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection string is held in the web.config file. My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind to change the connection string for a SQLDatatSource: I thought I had it cracked with the following: Public objClientConnection As New ConnectionStringSettings Public Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)If Session("ClientDBConnectionString") <> "" ThenobjClientConnection.ConnectionString = Session("ClientDBConnectionString")objClientConnection.Name = "ClientDBConnection"objClientConnection.ProviderName = "System.Data.SqlClient"ElseobjClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")End IfEnd Sub The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure there is always a design time connection string available. The trouble is, when I try and declare the SQL data souce like this: <asp:SqlDataSource ID="dsTest" runat="server" ConnectionString="<%# objClientConnection.ConnectionString %>" SelectCommand="usp_SEL_DocumentTypes" SelectCommandType="StoredProcedure" ></asp:SqlDataSource> I get an error: The ConnectionString property has not been initialized. I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way? Many thanks, Cliff
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.
I work on a system that is mirrored. there is production, and UAT(user acceptance testing). We have to do repetitive tasks on the systems. I want to write an application to make those tasks easier, using integration services. I would rather have one app and be able to specify which database server the dtsx packages would use each time. is this possible in dtsx packages, to specify a particular sql server? the tables and databases would not change between production and uat
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
I 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.
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.
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?
