Default Params In E-Mail Delivered Reports
Oct 26, 2006
Hi all,
[Reporting Services 2005]
I have reports delivered by email subscription. My "date from - to" parameters are set to "default" as "=Today()" and "=Now()". Report is delivered fine but I have problems with "link" to that report (attached to email). RS don't add my default parameter values to this link so every time user click this link, report is generated with new "date from" and "date to" and I want to generate exact the same report (same parameters) as in the email.
Is there a way to send default parameter values as static values in email delivered report link ?
Thanks
Maciej
View 4 Replies
ADVERTISEMENT
Mar 25, 2008
I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.
Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.
Anybody run into something like this?
Rob
View 5 Replies
View Related
Nov 2, 2007
Hi
I tried to deliver a report using data driven subscriptions option. But It does not process. In report manager, under status bar, it indicates 0 processing of 12 total; 0 errors.
Although there is no error I can not see any reports on the shared folder.
But when I go to Mngt Studio and select the specific report and try to refresh that reports subscription I get the following error.
==================================
invalid node. (Microsoft.SqlServer.SmoEnum)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.XPathExpression.Load(AstNode ast)
at Microsoft.SqlServer.Management.Smo.XPathExpression.Compile(String strXPathExpression)
at Microsoft.SqlServer.Management.Smo.Urn.get_XPathExpression()
at Microsoft.SqlServer.Management.Smo.Urn.Compare(Urn u1, Urn u2, CompareOptions[] compInfoList, CultureInfo cultureInfo)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.RebuildItem(INavigableItem item, Boolean applyItemParentFilter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequeryProperties()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.Refresh(Boolean autoRefresh)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.Refresh()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.Refresh()
Does any body know how to fix this ?
Thanks
View 3 Replies
View Related
Nov 5, 2007
Hi All,
I'm delivering some reports through data driven to a shared folder in another computer in my network. In report manager, under status, it shows the following message.
Processing: 10 processed of 12 total; 0 errors.
When I chek in the folder, I can see sometimes only 6 reports. I rescheduled and run it again. Then I could see only 10 out of 12 reports.
I opened the Management Studio and tried to refreshed the report subscription under subscription. Then I got the following error message.
"
nvalid node. (Microsoft.SqlServer.SmoEnum)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.XPathExpression.Load(AstNode ast)
at Microsoft.SqlServer.Management.Smo.XPathExpression.Compile(String strXPathExpression)
at Microsoft.SqlServer.Management.Smo.Urn.get_XPathExpression()
at Microsoft.SqlServer.Management.Smo.Urn.Compare(Urn u1, Urn u2, CompareOptions[] compInfoList, CultureInfo cultureInfo)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.RebuildItem(INavigableItem item, Boolean applyItemParentFilter)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequeryProperties()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.Refresh(Boolean autoRefresh)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.Refresh()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.Refresh()
Even If see error log file, I can not see any file there. This is actually a file of the previous run.
<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.2047.00</Product>
<Locale>en-US</Locale>
<TimeZone>Pacific Standard Time</TimeZone>
<Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServerService__11_05_2007_11_29_11.log</Path>
<SystemName>DEV02</SystemName>
<OSName>Microsoft Windows NT 5.1.2600 Service Pack 2</OSName>
<OSVersion>5.1.2600.131072</OSVersion>
</Header>
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
ReportingServicesService!configmanager!4!11/5/2007-11:29:12:: w WARN: WebServiceAccount is not specified in the config file. Using default: DEV02ASPNET
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
ReportingServicesService!library!4!11/5/2007-11:29:12:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
ReportingServicesService!library!8!11/5/2007-11:29:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Cannot open database "ReportServer" requested by the login. The login failed.
Login failed for user 'ZOPAratnayake'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection()
--- End of inner exception stack trace ---
ReportingServicesService!library!8!11/5/2007-11:29:21:: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Cannot open database "ReportServer" requested by the login. The login failed.
Login failed for user 'ZOPAratnayake'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
ReportingServicesService!library!4!11/05/2007-11:43:03:: Status: The file "Members - Awaiting Approval.xls" has been saved to the "\derbyReports" file share.
ReportingServicesService!notification!4!11/05/2007-11:43:03:: Notification 77fc9843-80f6-4392-9c26-3a84b087dafb completed. Success: True, Status: The file "Members - Awaiting Approval.xls" has been saved to the "\derbyReports" file share., DeliveryExtension: Report Server FileShare, Report: Members - Awaiting Approval, Attempt 0
ReportingServicesService!dbpolling!4!11/05/2007-11:43:03:: NotificationPolling finished processing item 77fc9843-80f6-4392-9c26-3a84b087dafb
ReportingServicesService!library!4!11/5/2007-11:43:03:: i INFO: Initializing SqlStreamingBufferSize to default value of '64640' Bytes because it was not specified in Server system properties.
ReportingServicesService!library!4!11/5/2007-11:43:03:: i INFO: Initializing SnapshotCompression to 'SQL' as specified in Server system properties.
ReportingServicesService!library!d!11/05/2007-11:43:03:: Data Driven Notification for activation id 668b2d9c-d5ea-46e9-8efd-e2824e9a8c64 was saved.
ReportingServicesService!library!d!11/05/2007-11:43:03:: Status: The file "Members - Awaiting Approval.xls" has been saved to the "\derbyReports" file share.
ReportingServicesService!notification!d!11/05/2007-11:43:03:: Notification 5fa50ecb-1b0f-4e40-86c8-4c4700c4b820 completed. Success: True, Status: The file "Members - Awaiting Approval.xls" has been saved to the "\derbyReports" file share., DeliveryExtension: Report Server FileShare, Report: Members - Awaiting Approval, Attempt 0
ReportingServicesService!dbpolling!d!11/05/2007-11:43:03:: NotificationPolling finished processing item 5fa50ecb-1b0f-
ReportingServicesService!library!d!11/05/2007-11:43:03:: Data Driven Notification for activation id 668b2d9c-d5ea-46e9-8efd-e2824e9a8c64 was saved.
ReportingServicesService!library!d!11/05/2007-11:43:03:: Status: The file "Members - Awaiting Approval_1.xls" has been saved to the "\derbyReports" file share.
ReportingServicesService!notification!d!11/05/2007-11:43:03:: Notification 3a282979-9cab-44fd-b4e8-a083558c494e completed. Success: True, Status: The file "Members - Awaiting Approval_1.xls" has been saved to the "\derbyReports" file share., DeliveryExtension: Report Server FileShare, Report: Members - Awaiting Approval, Attempt 0
ReportingServicesService!dbpolling!d!11/05/2007-11:43:03:: NotificationPolling finished processing item 3a282979-9cab-44fd-b4e8-a083558c494e
ReportingServicesService!subscription!4!11/05/2007-11:43:03:: System.IO.IOException: The process cannot access the file '\derbyReportsMembers - Awaiting Approval_1.xls' because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.SaveReport(Notification notification, SubscriptionData data)
ReportingServicesService!subscription!4!11/05/2007-11:43:03:: Error writing file Members - Awaiting Approval_1.xls to path \derbyReports
ReportingServicesService!library!4!11/05/2007-11:43:03:: Data Driven Notification for activation id 668b2d9c-d5ea-46e9-8efd-e2824e9a8c64 was saved.
ReportingServicesService!library!4!11/05/2007-11:43:03:: Status: Failure writing file Members - Awaiting Approval_1.xls : The process cannot access the file '\derbyReportsMembers - Awaiting Approval_1.xls' because it is being used by another process.
ReportingServicesService!dbpolling!4!11/05/2007-11:43:03:: NotificationPolling finished processing item 09e28175-ce44-41cd-ab55-56199a58885f
ReportingServicesService!dbpolling!d!11/5/2007-11:43:03:: NotificationPolling processing item 41cfd72e-bda0-4e8f-87dd-a22789eb340f
ReportingServicesService!dbpolling!c!11/5/2007-11:43:03:: NotificationPolling processing 2 more items. 2 Total items in internal queue.
ReportingServicesService!dbpolling!4!11/5/2007-11:43:03:: NotificationPolling processing item 34e19ce1-b298-48e6-a2fc-b7637765968c
ReportingServicesService!subscription!11!11/05/2007-13:18:10:: Auto increment filename = Members - Awaiting Approval_5.xls
ReportingServicesService!subscription!11!11/05/2007-13:18:10:: System.IO.IOException: The process cannot access the file '\derbyReportsMembers - Awaiting Approval_5.xls' because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.SaveReport(Notification notification, SubscriptionData data)
ReportingServicesService!subscription!11!11/05/2007-13:26:15:: Auto increment filename = Members - Awaiting Approval_11.xls
ReportingServicesService!library!11!11/05/2007-13:26:15:: Data Driven Notification for activation id 7bd68b9c-855f-4f2a-9482-43ac5f7c9589 was saved.
ReportingServicesService!library!11!11/05/2007-13:26:15:: Status: The file "Members - Awaiting Approval_11.xls" has been saved to the "\derbyReports" file share.
ReportingServicesService!notification!11!11/05/2007-13:26:15:: Notification 76aedee6-e860-4c60-aebc-2c3aeb854bed completed. Success: True, Status: The file "Members - Awaiting Approval_11.xls" has been saved to the "\derbyReports" file share., DeliveryExtension: Report Server FileShare, Report: Members - Awaiting Approval, Attempt 1
ReportingServicesService!dbpolling!11!11/05/2007-13:26:15:: NotificationPolling finished processing item 76aedee6-e860-4c60-aebc-2c3aeb854bed
ReportingServicesService!library!11!11/5/2007-13:29:12:: i INFO: Cleaned 0 batch records, 0 policies, 12 sessions, 0 cache entries, 12 snapshots, 36 chunks, 0 running jobs, 0 persisted streams
Here I did not post the whole error log, because its too long.
Does any one know why I can not deliver all the reports?
View 2 Replies
View Related
Mar 20, 2006
All,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]
@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
And inside of aaJonTemp, I have the following :
NULL
here2
NULL
NULL
here2
NULL
How is this possible? If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
Thx
jonpfl
View 1 Replies
View Related
Mar 10, 2008
Hi,
I am new to SQL Reporting Services and was wondering how to set it up so that the reports are automatically e-mailed to recipients if that is possible.
Thanks,
MKB
View 3 Replies
View Related
Mar 13, 2007
Hi All,
In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report
i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.
Thanks in advance for help
Warm Regards,
Chanduu.
View 1 Replies
View Related
Jan 21, 2008
Hey guys, I need a little help for my group project(Using ASP.NET 2 connected to SQL Server 2005). Here's the scenario:
Our system is basically a sort of login/logout system, so we have a table that has (Name, Date, Subject, TimeIn, TimeOut).
1. User registers on website, so information like e-mail is recorded into the database (and the user in the website is "linked/checked" to his data in the SQL Server for verification).
2. Users can choose to subscribe to a weekly report e-mail(maybe using a simple "IsSubscribed" boolean value in the database).
3. Every week, our system scans the database for those users who subscribed, then send different e-mail reports depending on the particular user (a compiled weekly report for Mon-Fri).
Is there such a feature in Database Mail? If not, how do I execute these types queries and just set them as a Scheduled Task?
Any help would be appreciated.
View 3 Replies
View Related
Jul 17, 2007
Hi all,
how can standard subscription or data driven subscription be used to deliver reports to specific email address.
the standard subscription or data driven subscription can be used to delever reports using Windows file share ,but how to make it work for delivering to emails..
can anyone help me out???
View 1 Replies
View Related
Nov 8, 2005
Hi,
i have installed SQL RS and have a directory listing in the Reports directory via my browser. CAn anyone tell me which file / setting needs tweaking please?
thanks
View 1 Replies
View Related
Mar 29, 2007
Cannot deploy reports to default website get error msg..
'A connection could not be made to the report server http://localhost/ReportServer'
additional information:
The request failed with http status 400: Bad Request. (Microsoft reporting Services Designer)
View 6 Replies
View Related
Jan 29, 2007
Ladies and Gents,
I have a question with regards to the default rendering extensions for reports. I am using RS 2005 with SP1.
I am accessing a report as follows:
http://<ReportServerName>/Reportserver?/<Folder>/<Report Name>
Sample: http://TestServer/Reportserver?/Special Reports/Dealer Report
This displays the parameter bar and the view report button. When pressing the view report button, the report is automatically rendered into HTML 4.0. Is there a way to use the existing parameter bar and automatically render the report to a different extension (such as PDF) when the parameters are entered and the View Report Button is pressed.
Thank You for any advice you can give,
Raph
View 9 Replies
View Related
Nov 10, 2006
If all the parameters for a report have default values, the report runs automatically as soon as you open it. Is there a way to prevent this?
View 9 Replies
View Related
Mar 27, 2008
I am trying to send a parameter to an uploaded report, It's default path is:
http://server/Reports/Pages/Report.aspx?ItemPath=%2fNavsion%2fNavision+PO+Report
I tried:
http://server/Reports/Pages/Report.aspx?ItemPath=%2fNavsion%2fNavision+PO+Report&rs:Command=Render&PONo=15818
It did not run and when I manually added the parameter it changed to:
http://server/Reports/Pages/Report.aspx?ItemPath=%2fNavsion%2fNavision+PO+Report&rs%3aCommand=Render&PONo=15818
I also tried to change the properties parameters settings has default unchecked and prompt unchecked
do I need to place the file in a folder on the server instead of uplading it?
View 1 Replies
View Related
Sep 30, 2015
I know that SSRS default rendering formats is HTML. is there anyway we can change this behaviour to render in XM format for specific reports.
View 5 Replies
View Related
Jan 26, 2007
Hi, I want to modify the layout and stuff of the main reporting page...but I'm not sure how to open it ... is there a project for it that gets installed on the server?
I've seen some video demos on the microsoft site and they had some pretty nice front ends set up. I'd like to put some actual metrics on that front page and set up some links and different layouts.
Can someone please at least point me in the right direction to do this? I'm hoping it doesn't require setting up an entirely new site from scratch.
Thanks,
Roxanne
View 3 Replies
View Related
Jun 23, 2015
during period of troubles connecting to Local Servers and Sites location i.e., to [URL], the place where all Reports are saved by default, I have continued to work on some Reports that have been saved to file system of my localhost/developing machine. But, upon recovery of http location, I can not see/open those files?
how to approach those files in order to open them in Report Builder 3.0 and thus, to "save as" them to the http location? Seems that Report Builder has chosen that location, by default, somehow.....
View 2 Replies
View Related
Sep 14, 2007
I've tried changing the default zoom setting by editing the RSReportServer.config file to include
<Configuration>
<DeviceInfo>
<Zoom>Page Width</Zoom>
</DeviceInfo>
</Configuration>
under the
<Render>
<Extension Name = "HTML4.0" etc.
However, all reports still show up with the zoom setting at 100% on the toolbar.
I stop and restart the Report Server Service and it comes up OK so I know my XML syntax is cool. What am I doing wrong?
This is on a 2005 Report Server. Reports are built in VS2005 Report Designer and deployed to the Report Server directly. Reports are accessed via IE7 browser.
View 2 Replies
View Related
Jul 28, 2005
When I send message from Server1 to Server2,I found the error "This message could not be delivered because it is duplicate" in SQLProfiler on Target, why message can be duplicate ? who can help me?
View 8 Replies
View Related
May 11, 2007
How to give authentication for Send Mail Task component?
View 4 Replies
View Related
Sep 7, 2004
Hi,
after i try to build the web application "Web Development - Data Entry Form" i get the following error message. I already change the variable username to system which is defined in Machine.Config.
The Error messega is like following:
SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Thanks,
View 1 Replies
View Related
Mar 20, 2007
Hello,
I'm hoping someone can help me with this. We use t-sql scripts to install merge replication and we have run into a bit of a problem. Sometimes the subscriber already has a database with data and we don't want to apply the snapshot. I thought that if you call sp_addmergereplication with the @sync_type set to 'none' then for that subscriber it will forgo the initial snapshot. It looks like it is still applying it and I'm getting foreign key constraint errors. This occurs on both 2k and 2k5. What's more we could swear that this used to work...
Anyway, any advice would be greatly appreciated. Thanks for reading!
Scott
View 4 Replies
View Related
Jun 14, 2007
I am running SQL Server 2005 Reporting Services. I created a subscription delivered by "Report Server File Share" and the UNC path points to a share on a Netware server. I get the following error when the schedule runs:
Failure writing file Completed.pdf : The network path was not found
The UNC path is \rmh5vol1deptimsoperhiscommon.
I can create the same subscription, but have it write to a Windows file share and everything works fine.
Does SQL Server 2005 Reporting Services support writing to a Netware share?
View 3 Replies
View Related
Apr 17, 2007
Hi,
I am using service broker in between two database servers. following is the way i am sending and receiving messages
Send
BEGIN TRAN
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @SendService
TO SERVICE @ReceiveService
ON CONTRACT @Contract
WITH LIFETIME = @lifetime;
SEND ON CONVERSATION @handle
MESSAGE TYPE @xmlMessageType(@xmlMessage);
COMMIT
Receive
BEGIN TRAN;
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
----------------------------------------------------------------------------------------------------
-- Check to make sure a message was returned to process. In theory this should never happen.
----------------------------------------------------------------------------------------------------
IF @@rowcount = 1
BEGIN
IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @handle;
COMMIT
RETURN 0
END
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @handle;
COMMIT
RETURN 0
END
SET @sql = 'EXEC '+@message_processor_name+' @xml'
BEGIN TRAN
EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage
COMMIT TRAN
END CONVERSATION @handle;
END
COMMIT
I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.
1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.
2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.
Let me know if you need any other information
View 4 Replies
View Related
Jul 31, 2007
I looked online and couldn't find anything to help me make this change. I want to change the default URL for reporting services to another url. Is this possible? Any assistance would be greatly appreciated.
View 3 Replies
View Related
Oct 12, 2007
Hi,
I already had a thread for same problem but didn't recieved any response in last 4-5 months so I created a new thread for this
I am using service broker in between two database servers. following is the way i am sending and receiving messages
Send
BEGIN TRAN
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @SendService
TO SERVICE @ReceiveService
ON CONTRACT @Contract
WITH LIFETIME = @lifetime;
SEND ON CONVERSATION @handle
MESSAGE TYPE @xmlMessageType(@xmlMessage);
COMMIT
Receive
BEGIN TRAN;
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
----------------------------------------------------------------------------------------------------
-- Check to make sure a message was returned to process. In theory this should never happen.
----------------------------------------------------------------------------------------------------
IF @@rowcount = 1
BEGIN
IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @handle;
COMMIT
RETURN 0
END
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @handle;
COMMIT
RETURN 0
END
SET @sql = 'EXEC '+@message_processor_name+' @xml'
BEGIN TRAN
EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage
COMMIT TRAN
END CONVERSATION @handle;
END
COMMIT
I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.
1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.
2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.
Let me know if you need any other information
View 2 Replies
View Related
Aug 7, 2007
Most developers running SharePoint in integrated mode have been experincing issues when displaying SRS reports in a web site other than the default zone.
There is a blog http://www.sharepointblogs.com/nrdev/archive/2007/06/21/ssrs-in-sharepoint-2007-site-using-forms-based-authentication-sharepoint-integrated-mode.aspx that explains that this is a bug and may be fixed when SP3 is released.
We are usi ng dual authentication for a particular site that hosts SRS reports and the default zone is windows auth and the internet zone is FBA. Since we cannot display reports using the RSViewerPage.aspx that comes with RS sharepoint addon, I decided to look for some way to display these reports in a non default zone. This method may help those experincing ths issue but the method assumes that you have one default zone or default zone [0] is the zone where reports are deployed from Visual Studio.
Here are the steps.
1. Go to your Layouts/reportseerver folder in your MOSS installation.
2. make a backup of RSViewerPage.aspx
3. Open RSViewerPage.aspx in visual studio and on the header declarations change the following line that looks like
<%@ Page language="C#" Codebehind="RSViewerPage.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.SharePoint.UI.RSViewerPage,Microsoft.ReportingServices.SharePoint.UI.ServerPages,Version=9.0.242.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91" %>
to
<%@ Page language="C#" AutoEventWireup="false" %>
4. Add the following script line to the page
<script runat="server">
protected override void OnLoad(EventArgs e)
{
SPSite site = SPContext.Current.Site;
if (!Page.IsPostBack)
{
String RelativeReportUrl = Request["RelativeReportUrl"];
if (site.Zone != SPUrlZone.Default)
{
foreach (SPAlternateUrl url in site.WebApplication.AlternateUrls)
{
if (url.UrlZone == SPUrlZone.Default && RelativeReportUrl != null && url == site.WebApplication.AlternateUrls[0])
{
String path = url.Uri.AbsoluteUri.Substring(0, url.Uri.AbsoluteUri.Length - 1) + RelativeReportUrl;
m_sqlRsWebPart.ReportPath = path;
}
}
}
else
{
m_sqlRsWebPart.ReportPath = site.Url + Request["RelativeReportUrl"];
}
}
//add nav
SiteMapPath map = new SiteMapPath();
map.SiteMapProvider = "SPSiteMapProvider";
map.RenderCurrentNodeAsLink = true;
m_breadCrumbs.Controls.Add(map);
}
</script>
5. This script section just points the web part in the RSviewerPage to the report file hosted in the MOSS default zone.
Thats it.
This should work in either of the MOSS zones.
Gatharia
View 11 Replies
View Related
Jan 27, 2006
HeyLet's say I have some stored procedure that takes as a param userId and it's simple SELECT statement. I want to assign this param and value in my code how to do it ???Jarod
View 2 Replies
View Related
Oct 13, 1999
Can I pass a parameter to a DTS package?
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Is it possible?
Thanks in advance,
Nishi
View 1 Replies
View Related
Oct 31, 2007
Hello,
Basically I am trying to do an order by statement using variables.
I have the SQL:
IF @SortOrder = 'ASC'
BEGIN
SELECT DISTINCT D.Code as 'T1', D.CompanyName as 'T2',
S.SpendAmount as 'T3', E.SpendAmount as 'T4',
E.SpendAmount - S.SpendAmount as 'T5',
Case When S.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/S.SpendAmount) * 100
When S.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
When E.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/E.SpendAmount) * 100
When E.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
END as 'T6'
FROM DivisionData D,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @StartYear and Month = @StartMonth and Division = @Division) S,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @EndYear and Month = @EndMonth and Division = @Division) E
WHERE D.Division = @Division
AND S.Code = D.Code
AND E.Code = D.Code
END
I have the params @Crit1 @Crit2 .... to @Crit6. These parameters come from a web form and can have the values 'T1' 'T2'... to 'T6' They correspond to the field names I have put in my select statement.
What I want to do is use these params in an Order By statement, enabling the user to select how the fields are ordered.
I have tried using something along the lines of:
ORDER BY
CASE WHEN @Crit1 = 'T1'
THEN 'T1' END
This works as long as I dont use the word DISTINCT in my select statement, otherwise I get the error:
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I do need the distinct... I was wondering if there is a simple way to get round this, or if anyone can point me in the right direction or a article/example of this?
Many thanks
Andrew Rayner
View 6 Replies
View Related
Feb 2, 2004
I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?
Dim strWhere
strWhere = " WHERE dbo.""User"".UID IS NOT NULL "
If Not request.querystring("EmployerID") = "" Then
strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'"
End If
If Not request.querystring("AccountNumber") = "" Then
strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'"
End If
If Not request.querystring("LastName") = "" Then
strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'"
End If
If Not request.querystring("FirstName") = "" Then
strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'"
End If
DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr"))
DBCommand = New OleDbDataAdapter _
("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)
Thanks in advance?
View 3 Replies
View Related
Feb 1, 2005
Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @@IDENTITY) as input for some of the other procedures.
I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...
CREATE PROCEDURE addTemplateDetail
@TemplateID int,
@GroupNameID int,
@SubGroupNameID int=null,
@MethodID int,
@AnalyteID int
AS
DECLARE @TemplateGroupNameID int
DECLARE @TemplateMethodID int
SET @TemplateGroupNameID=(EXEC addTemplateGroupName @TemplateID, @GroupNameID)
SET @TemplateMethodID=(EXEC addTemplateMethod @TemplateGroupNameID, @SubGroupNameID, @MethodID)
EXEC addTemplateAnalyte(@TemplateMethodID,@AnalyteID)
I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.
View 1 Replies
View Related
Feb 7, 2005
I have a long running trigger that makes calls to several tables in order to get values for a list of parameters before doing my final INSERT statement into a different table.
One of my parameters is for a local language translation of a particular word which is stored in a table. The problem is - I do not know the name of the table until runtime when I dynamically build the name as follows:
DECLARE @SQL nVarChar(200)
SET @SQL = 'SELECT @Translation = nvcLocalEventDescription FROM Monitoring.dbo.tblSignalTemplate'
+ CAST(@MonitoringCentreID AS nVarChar) + ' WHERE nvcEventDescription = "' + @EventDescription + '"'
EXECUTE Management.dbo.usp_parmsel_LocalEventDescription @SQL, @LocalEventDescription OUTPUT
If there is a MonitoringCentreID of 1234, then there will be a table named tblSignalTemplate1234 - which will contain a nvcLocalEventDescription field containing the value that I am after. Here is the code for the stored procedure...
CREATE PROCEDURE [dbo].[usp_parmsel_LocalEventDescription]
@strSQL nVarchar(150),
@Translation nVarChar(100) OUTPUT
AS
EXECUTE sp_executesql @strSQL
GO
The error I get is "Must declare the variable '@Translation'" - which has thrown me a little as it declared on the 3rd line of the stored proc.
Anyone got any ideas where I am going wrong, or as usual, is there a simpler way ?
Steve.
View 2 Replies
View Related
