Hi,
I want to read the database server name from config file at runtime and then pass on that to the datasource. I got an article on the MSDN stating the way it should be approached. But it doesn't seem to work.
Following is what I've done:
a.) Create a static datasource and dataset.
b.) Build a report based on the fields fetched from the stored procedure.
c.) Save the report.
d.) Change the datasource value to an expression: ="data source=" & Parameters!ServerName.Value & ";initial catalog=ReportTest"
e.) Deploy the report.
But when invoking the report from my aspx page, I receive an error
An error has occurred during report processing.
Query execution failed for data set 'TestDataSet'.
Incorrect syntax near 'usp_TestProcedure'.
Anybody who has implemented this or any idea what to do next?
TIA.
I have to create a script for changing the datasource at runtime.
Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".
I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination. I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
My boss claims that there is a free SQL server runtime. Is thistrue? I was only aware of MSDE. But I believe the connections arecapped at 5 in MSDE. If there is a free SQL server runtime, are thereany limitations to using it (performance, connection count)? Thanks.
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
Bob writes "Does SQL Server 2005 Express have a runtime version? If it does, how do I get it? And if I can get it, Will this waive the 180 day trial period I have? Thanks."
I upgraded my primary production server this morning to SQL 2005. Everything went fairly smoothly, but a couple of hours after my installation was complete, I found the following error in my event log:
Source: .NET Runtime Optimization Service EventID: 1101 .NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: Microsoft.ReportingServices.QueryDesigners, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002
I am a little stumped since we did not install Reporting Services. We only installed Database Services, Integration Services and Workstation Components. I'm open to any suggestions on this. This does not seem to be negatively affecting our server, but I do want to resolve it as soon as possible.
I downloaded orcas and created an application which also has sql server synchronisation, now if i wish to run it on another m/c, where can i get the runtime for 3.5, I searched but could not find it.
Does SQL Server 2005 Express have a runtime version? if so, How do I get It. I already have downloaded the Free Express version. If there is a runtime version, will it waive the 180 day trial period, and give me full permenant use?
I've been using the new features with ASP.Net 2 for some months now. I use the gridview control a lot with the sqldatasource. I recently started a new job and am having problems connecting a sqldatasource control to a sql server 7 database. At my old job we used sql server 2000.
I get the error: "SQL server version needs be 2000 or higher" when I try to set up a new connection.. I read some documentation that said you can use asp.net 2 datasource controls with sql server 7. Does anyone know how to get round this issue. I don't want to have to go back to 1.x methods of connecting data controls to databases using code. Thanks
I am trying to use the new Common Language Runtime (CLR) hosting feature to write stored procedures in C# i have addedMicrosfot.sqlserevr.server name spaceand ia m trying to use sqlContext Object as below using (SqlConnection connection = new SqlConnection(dbConn)) { connection.Open(); SqlCommand sqlcmd = new SqlCommand("select @@ version", connection); SqlContext.Pipe.ExecuteAndSend(sqlcmd); } i get the below error when i execute (SqlContext.Pipe.ExecuteAndSend(sqlcmd);) System.InvalidOperationException was unhandled by user code Message="The requested operation requires a SqlClr context, which is only available when running in the Sql Server process." Source="System.Data" i checked if (SqlContext.IsAvailable) and it returns false as well. Please le me know how to make it work. Thanks THNQDigital
I am trying to generate dates for a roster dynamically in a stored procedure. If my start date is '01/01/2015', end date is '12/31/2015', I should generate dates from '01/01/2015' adding 7 days.
The output comes like this:
01/01/2015 01/08/2015 01/15/2015 . . . until end date
I do it through loop but just posting it here to know, if there is any other efficient way using CTEs or some sort of queries which can avoid loop in SP.
I posted this on the .NET Framework inside Sql Server forum as well. Sorry if the cross-post offends anybody.
I upgraded my primary production server this morning to SQL 2005. Everything went fairly smoothly, but a couple of hours after my installation was complete, I found the following error in my event log:
Source: .NET Runtime Optimization Service EventID: 1101 .NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Failed to compile: Microsoft.ReportingServices.QueryDesigners, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002
I am a little stumped since we did not install Reporting Services. We only installed Database Services, Integration Services and Workstation Components. I'm open to any suggestions on this. This does not seem to be negatively affecting our server, but I do want to resolve it as soon as possible.
Hello All, How to assign the DataSource to Server Report which is stored in the remote machine. Now i need to work on it. If you have any solution please pass on it to me.
Please advise which kind of connection should be establish from excel 95 to MS SQL Server.
From my thought, I understand that the connection depends on Windows version rather than excel version. Thus, I just think that there will not be any issue connecting to SQL Server from Excel 5.0a.
Kindly advise if there is any addtional point to concern/consider further. What could be issue connecting to SQL Server 2005 from Excel 5.0a.
The package works fine in development site. It was compiled with "Enable package configurations" and sql server as configuration type. But after installed in production database using deployment manifest, the package does not load the configuration values at run time from the table, instead it uses the variables' value in design time.
If the configuration is a file in windows folder, I may be able to load it from the sql job agent's step configuration tab. When configuration values are stored in a table, how can I make it load the configuration from there at run time?
I moved all my databases to sql server 2005 & when appliction is trying to connect to 2005 databases i get the following error In the connection string i am using Password=test;Persist Security Info=True;User ID=test;Initial Catalog=test;Data Source=externalip/instancename
this is the only change on the web server
Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration> <system.web> <customErrors mode="Off"/> </system.web> </configuration> Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
Hi Friends, I want to have solution for one of the problem. The requirement is like this : I want to write stored procedure or function which will take parameter as SQL Server name, DB name, UserName and passwod. This Stored proc will connect to Remote server using these parameters and will get the count of the rows in one of the table. I created the connection using the linked server
Now I am trying to get count using following query : set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
But the question is that the execution goes this way :
exec(@SQLQuery)
Now how to assign this count value to some variable so that I can use it later ...?
Going forword I want to use cursor and get the rows in these table using cursor ...? How can I assign values returned from any runtime query to temporary variable or table ...?
I tried another approach also: I put remote connection and query execution in inner stored proc called usp_GetTableRowCount set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
exec(@SQLQuery)
and in outer stored proc : referenced the inner stored proc like this
exec @AFSDataRowCount = dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> The execution of dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> gives me exact no of rows but when I see value of AFSDataRowCount, I get 0.
Kindly help me out whereever I am making mistake or else pls tell me any other approach to follow. Thanks in advance.
I am new to both Reporting Services and Webservices implementation so I am just trying to use a simple example. I found a Webservice on the internet that returns movie information:
created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection) System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)
We are in the process of configuring a j2ee application with SQL Server 2005. We have downloaded the new version of SQL Server 2005 Java Database Connectivity (JDBC) Driver 1.0.
We are having some issues in getting it to work using the XA datasource. Description of the error is as follows:
javax.transaction.xa.XAException: java.sql.SQLException: Failed to create the XA control connection. Error: Could not find stored procedure 'master..xp_sqljdbc_xa_init'.. at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(Unknown Source)
Has anyone else has encountered this and possibly found a solution.
I'm just starting to use SQL Server 2005 Reporting services, and need to use an XMLdocument as the datasource. I will get the xml data from a webservice and then need to bind it to the report. Here is what I am doing:
1. I created a webservice at the following location: http://localhost/myWebDirectory/myService.asmx. this webservice has a method called "GetXMLDocument" that has 1 parameter (sql). This method uses the sql statement to fill a dataset from a SQL Server database, converts it to an xmlDocument, and returns the xmlDocument. Here is a sample of the results:
<?xml version="1.0" encoding="utf-8" ?> - <NewDataSet> - <Table> <ReportName>GirlsNamedMary</ReportName> <ReportFriendlyName>All Girls Named Mary</ReportFriendlyName> <ReportDescription>This is a test SQL Server Reporting Services report. It has no parameters.</ReportDescription> </Table> </NewDataSet>
2. I created a datasource in my reports project as follows: - Name: TestDataSource - Type: XML - Connection string: http://localhost/myWebDirectory/myService.asmx
3. I created the following dataset: - Name: TestDataSet - Data source: TestDataSource - Query string: <Query xmlns="http://tempuri.org/Service/GetXMLDocument"> <Method Namespace="http://tempuri.org/Service" Name="GetXMLDocument"/> <ElementPath /> </Query>
I am getting this error: ------------------------------------------------------------------------------------ An error occurred while executing the query. Failed to execute web request for the specified URL.
Additional Information: ----> Failed to execute web request for the specified URL. (Microsoft.ReportingServices.DataExtensions). ------------------------------------------------------------------------------------
I cannot figure out why I have this error. The service runs fine from the browser, but I just can't get it to work in Reporting Services. What am I doing wrong?
I have developed an SSIS Package which is the Data Source for a Reporting Services report (using the OLE DB Destination DataReader component - very cool and powerfull!!!).
I have one Connection Manager defined in the package to access a SQL Server 2000 Database. The authentication is set to SQL Server Authentication (per the documentation in BOL) where I provide the UserID and Password.
Here is what works and what doesn't work:
In BIDS on my local machine the report executes beautifully.
When deploying the SSIS Package to my local machine's Integration Services File System and the report to my local machine's Report Server database the report executes beautifully (Yes, I'm using the developer edition).
When deploying the package to a W2003 Server Integration Services installation as well as deploying the Report and Data Source to the same W2003 Server's Reporting Services installation - the report fails.
I think it is because the passsword to the SQL Account is not "set". I am able to run the package through BIDS via dtexecui on this W2003 server successfully. However, when I run the report on the server it fails.
I am guessing it is because the password for the SQL Server Authenitcation is not "there". I am encrypting via "Save SensitiveDataWithPasssword".
What am I doing wrong? Any suggestions would be welcome.
I'm trying to configure JBoss application server to use SQLServer 2005 as database with XA transactions. I can start a new transaction, access SQLServer from within the transaction, but when committing, there is the following XAException from SQLServer: 16:04:20,926 INFO [STDOUT] javax.transaction.xa.XAException: java.sql.SQLException: COMMIT: Status:0 msg:*** SQLJDBC_XA DTC_ERROR Context: xa_commit, state=1, StatusCode:-4 (0xFFFFFFFC) ***16:04:20,926 INFO [STDOUT] at com.microsoft.sqlserver.jdbc.SQLServerXAResource.commit(Unknown Source)16:04:20,941 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.commit(XAManagedConnection.java:176)16:04:20,941 INFO [STDOUT] at com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord.topLevelOnePhaseCommit(XAResourceRecord.java:641)16:04:20,941 INFO [STDOUT] at com.arjuna.ats.arjuna.coordinator.BasicAction.onePhaseCommit(BasicAction.java:2619)16:04:20,941 INFO [STDOUT] at com.arjuna.ats.arjuna.coordinator.BasicAction.End(BasicAction.java:1779) [...]
The system is running on Windows Server 2003 64bit with SQLServer 2005 64bit, and JBoss running on JDK 1.5.0 (32bit) Can anyone tell me what this message indicates?
the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?
In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:
'Document Date (most recent date first)' 'Document Number (highest number first)' 'Document Date (most recent first) and Number' 'Document Number (lowest number first)'
The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.
So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.
The code is:
DECLARE @ls_OrderBy varchar(80) --'Document Number (highest number first)' --'Customer and Document Date (most recent date first)' --'Customer and Document Number (highest number first)' --'Document Date (most recent first) and Number'
I have a VB.NET program which creates a Job (Microsoft.SqlServer.Management.Smo.Agent.Job) and schedules it, to load and run a existing SSIS package, which is on a different server.
I'm using the following assemblies for this purpose.
To make this application run in my application server, do i have to install any SQL server components or SSIS rumtime components? Or Is it enough if i copy only the above mentioned dlls to my applications bin directory, in my application?
Note: I have the enterprise edition of SQL Server 2005 installed on my database server.
We've deployed an assembly to a new production sql server 2005 instance, and have been having periodic restarts of sql server. The sql server service appears to be stopping and restarting on it's own since the assembly was deployed.
We've narrowed it down to a decryption method. We haven't been able to produce the issue on a consistant basis. Even if we put a ton of load on the stored procedure calls.
The error is below we receive (I cut out some, so it would fit in the thread), we also have mdmp files, that I've attempted to debug with Visual Studio 2005 and Windbg with no success. I have all the symbols (I'm pretty sure), but I don't have much experience with WinDbg, so it's been slow going.
Here's a little about the environment.
Windows Server 2003 Enterprise x64 SP2 Microsoft SQL Server version 9.00.3042.00 SP2 16 MB physical memory Quad 3.2 GHz Xeon 3 sets of raid drives. c: OS e: sql data e: sql logs All Sql and windows patches are up to date.
Only thing I can figure is this is something to do with the .NET libraries. When the assembly was compiled it was set to compile for any CPU. We even tried compiling on the 64 bit OS with no luck still.
If anyone has had a similar issue, it would be most helpful, thanks, Byron
I have created many shared datsources and reprots at http://localhost/resportserver.
I am trying to access these reports from ReportViewer in the web application. Actually I want to use these reports for different customers by just changing the datsource programitically.
Hello All,I am getting the following error when attemping to open a table inSQL2kSP3a.________________________________________SQL Server Enterprise ManagerDatabase Server: Microsoft SQL ServerVersion: 08.00.0760Runtime Error: [Microsoft][ODBC SQL Server Driver]Invalid time format_________________________________________I cannot find it in sysmessages, or on the web.Any ideas about how to resolve this? And how it occured...Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello, I got an exception when I try to load/run a SSIS Package programmatically on the server using remoting. The client machine has no SQL Server 2005 component installed..
This is the error I get from my exe application:
Microsoft.SqlServer.Dts.Runtime.DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. at Microsoft.SqlServer.Dts.Runtime.Application..ctor()
And this the Remote component:
<Serializable()> Public Class SSISComponent
Inherits MarshalByRefObject
Public Function LaunchPackage(ByVal sourceLocation As String, ByVal serverName As String, ByVal packageName As String, ByVal packageVariables As SortedList) As Microsoft.SqlServer.Dts.Runtime.DTSExecResult
Dim packagePath As String Dim myPackage As Package Dim integrationServices As New Application ' Combine path and filename. packagePath = Path.Combine(sourceLocation, packageName) Try If integrationServices.ExistsOnDtsServer(packagePath, serverName) Then myPackage = integrationServices.LoadFromDtsServer(packagePath, serverName, Nothing) Else Throw New ApplicationException( "Invalid package name or location: " & packagePath) End If If Not packageVariables Is Nothing Then For Each de As DictionaryEntry In packageVariables myPackage.Variables(de.Key).Value = de.Value Next End If LaunchPackage = myPackage.Execute() Catch ex As Exception Throw End Try End Function End Class
This is the code that call the remote component to run the package on the Server: Public Class Test Private Function LoadAndRunPackageRemotly(ByVal PackageName As String, ByVal PackageVariables As SortedList) As Boolean
Dim launchPackageService As New SSISComponent Dim packageResult As Microsoft.SqlServer.Dts.Runtime.DTSExecResult Try packageResult = launchPackageService.LaunchPackage(Me.SSISPackageLocation, Me.SSISServerName, PackageName, PackageVariables) If packageResult = DTSExecResult.Success Then LoadAndRunPackageRemotly = True Else LoadAndRunPackageRemotly = False End If Catch ex As Exception Throw End Try End Function End Class
I would like to inform that everything run inside a thread because the Package do a lot of things... When I build up the setup for the exe inside the Detected Dependencies there are several DLL related to SQL Server (Microsoft.SQLServer.DTSRuntimeWrap.dll etc..etc..) that I don't register .. Tis could be the problem? If yes, somebody can tell me which one I have to register ? as COM or COMRelativePath? Some other hints?
This error could be also caused by wrong permissions on the ssis? Thank you very much for any help... p.s. To use sql server agent will be my last option!! Marina b.
