Hello,
I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.
John
I have a question about how Query Notification works and the objects and data created when a new subscription is created.
Here is what we are doing:
- When the application starts, we call the SqlDependency start method:
SqlDependency.Start( <connection string> )
- For each query we want to monitor, we run the following:
SqlConnection oConnection = new SqlConnection( <connection string> );
oConnection.Open();
SqlCommand oCommand = new SqlCommand( <sql statement> , oConnection);
SqlDependency oDependency = new SqlDependency(oCommand);
oDependency.OnChange += new OnChangeEventHandler( <handler delegate> );
SqlDataReader objReader = oCommand.ExecuteReader();
objReader.Close();
- When the application shuts down, we call the SqlDependency stop method:
SqlDependency.Stop( <connection string> )
We have observed that when we create a new subscription, the following things happen:
1. A row is added to sys.DM_QN_Subscriptions.
2. query_notification_% internal table is created.
3. SQLQueryNotificationStoredProcedure% stored procedure is created.
4. SQLQueryNotificationService% Service Queue is created.
5. queue_messages% internal table is created.
When the application ends, the stored procedure, service queue and queue messages internal table are dropped. The row in sys.DM_QN_Subscriptions and the query_notification_% persist.
When the application is restarted, a new row is added to sys.DM_QN_Subscriptions, but the existing query_notification_% internal table is used.
Is this normal behavior? Or, is there something we should be doing to clean up?
What I am wondering is if there is a threshold where these persistent items become a problem with locking, performance, other?
Hi
View 1 Replies View RelatedHello,
i am trying to create a pull subscription to my publication using T-SQL. The pull subcription is created successfully but the problem is that data is not merged correctly. I mean when i run the agent ( despite of having data in subscription / publication ) the data is not merged and i get "No Data Needed To Be Merged" message.
i run following sp in this sequence:
At Subscrber:
1 - sp_addmergepullsubscription
2 - sp_addmergepullsubscription_agent
At Publisher:
1 - sp_addmergesubscription
Again at subscriber :
Transaction for adding merge agent job.
Regards,
I have a client who is getting an error when they try to create a report subscription. The error is as follows:
An unexpected error occurred: A subscription delivery error has occurred. ---> A subscription delivery error has occurred. ---> One of the extension parameters is not valid for the following reason: The account you are using does not have administrator priviledges. A subscription cannot be created for [email address].
The thing I find strange is that the client was able to create a shared schedule with no problems, so I don't see how they don't have administrator privileges. The shared schedules and subscriptions are created programmatically through a web application if that is of any importance.
Any ideas on how to fix this error?
I am trying to publish / subscribe a database onto SQL Server Mobile and when I run the following code that was, for the most part, generated by the SQL Server Management Studio Subscription Wizard,
repl.InternetUrl=@"file://chiefjoseph/walklisttransfersite/sqlcesa30.dll";
repl.InternetLogin = @"rr";
repl.InternetPassword = @"88";
repl.Publisher = @"CHIEFJOSEPHSQLSERVER2005";
repl.PublisherDatabase = @"WalkList";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sx";
repl.PublisherPassword = @"xxx";
repl.Publication = @"WalkList";
repl.Subscriber = @"WalkListSubscription";
repl.SubscriberConnectionString = @"Data Source=""My DocumentsSQLServerNewWalkList.sdf"";Password=""pwd"";Max Database Size=32;Default Lock Escalation =100;";
try
{
// repl.AddSubscription(AddOption.ExistingDatabase);
repl.AddSubscription(AddOption.ExistingDatabase);
repl.Synchronize();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
I get the following error on the Pocket PC emulator
€œA native exception has occurred in WalkLister.exe. Select Quit and then restart this program, or select Details for more information.€?
When I try to use the subscription wizard to create the subscription, I get the following error:
Synchronizing Data (Error)
Messages
· Connection broken.
HRESULT 0x80004005 (0)
· The operation could not be completed.
Any insight to what the problem might be?
-Rob
Hi
View 10 Replies View RelatedI am trying to create a merge publication in sql server 2005 and a merge subscription in sql server 2005 mobile through transact sql using sql server query analyzer.
I am succeeded in creating the publication but not subscription.
I am using sp_addmergepublication and sp_addmergesubscription.
sp_addmergepublication is working fine.
But the problem is in creating sp_addmergesubscription.
Can anyone please guide me how to create subscription to sql server 2005 mobile database using transact sql.
Hi all,
I am having a HUGE issue with SSRS, I am running SQL 2005 on Win 2003 server, the SQL uses SQL and windows permissions.
I have a datasource which stores it credentials securely on the server but for some reason when i try to subscribe to the report it errors saying
"Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked server the link is no longer valid."
Beats me but the credentials are stored, I am connecting using a user account which can run the report fine (This is proven).
I have SP1 installed, and have literally tried every last option in the book.
N.B i have read all the existing post about this and i seem to have a different issue, I am not using any paraeters in the report i am trying to test and i dont use anything like USR!USERID
Please help!
Andy
Hi there
When I try and create a subscription in SQL Server Mobile to a published SQL Server 2005 database, I get the following problem error during the data synchronization process:
Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.
HRESULT 0x80004005 (29061)
Any help would be gratefully appreciated.
Regards
JB
Using SS2005, I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:
Procedure dbo.sp_ReportMaster @myid int=null
When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. Where it says specify a command, I put in exec sp_ReportMaster. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.
I don't know why it shows parameters in Reporting Services when I run the report from there, but when I try to create the subscription, it shows that I don't have any parameters.
Thanks, Iris
We have this sporadic problem where when we go in to save an edit on a SSRS report subscription, it gives us a duplicate job number error. When I look at the Jobs in the Agent, there are duplicates, so I need to manually delete both duplicate jobs, then the subscription save will work. If we delete just 1 of the jobs, the issue seems to clear for a while, but then reoccurs. This has happened to us on multiple reports, but not consistently. The subscriptions look fine.
View 7 Replies View RelatedI'm trying to create a user from a powerbuilder application through powerscript.
I'm trying to create first the login using sp_addlogin
but I'm getting the following error
[microsoft][odbc for sql server]the procedure sp_addlogin cannot be executed in transaction.
Plz does someone know what should I do?
I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!
View 1 Replies View RelatedHi,
I'm developing a WinForms app that will be deployed via ClickOnce deployment and that uses SQLExpress as a local data store. In order to avoid the upgrade DB problem, I'm having my application check for a database and check its version on startup. I am able to successfully create the database if it does not exist, but when I try to connect to the new database, I get a login failed error. However, if I restart the app, it is able to connect to the newly created instance. Here's what I'm doing:
If the db does not exist, I connect using this connect string:
Data Source=.SQLEXPRESS;Integrated Security=True;User Instance=True;Connect Timeout=60
I then issue a CREATE DATABASE command and run my table scripts. I then try to reconnect as
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ImportToolData.mdf;Integrated Security=True;User Instance=True;Connect Timeout=60
When i attempt to connect to the newly created database, I get a login failed error message. However, if I restart the application and try to connect using that same connection string, it succeeds. Any ideas why I would have to restart the app to successfully connect to the new database?Thanks, Scott
Hello!!
I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.
Can anyone give me a hand?
thanks
hi all,
i want to create a service broker application between two different instances.
can i achieve the dialog security and transport security with out using certificate?
like we can achieve the transport security using windows authentication (both the instances are in same domain).
so how to achieve the dialog security with out using certificates.
if any body has any link plz provide me.
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!
Geekz,
My problem is that , I had setup blog on server say, blog.xyz.com [I am using blogengine.net, which is in ASP.Net]. It is using sql server2005 as a backend for storing its blog's posts. After settingup blog, i tried to access its database through my windows application. But while development I was getting error that
"An error has occured while establishing a onnection to the server. When connecting to SQL server 2005, this failure may be caused by the fact that under the defaukt settings SQL server doesnot allow remote connections.(Provider Named Pipes, error :40 - Could not open a connection to SQL Server)"
I am using [String ConnectionString = "Data Source=sql-v123.mesa11.1Server.net; Initial Catalog=abc; User ID=xyz; Password=xyz123";]
Then I decided to created a webservice that will access the data from databse blog and store it somewhere in datatable and using windows application i will retrieve those data for end user. So I want to to know that whether i can access the datatable of webservice holding the blog data from windows application or not. if answer is Yes, then please provide me with source or some great links. Or if There is any other option so that I can access my database directly from windows application, then it would be better. Looking for your answers.
Thanks and regards
Hi guys, need help again.
I'm running packages from C# code and thus I need to create an instance of SqlServer.DTS.Runtime.Application.
In my maching, creating the application instance doesn't even take 1 sec but when the application was deployed to our QA server creating the instance took 30 secs (always ... every single time with no exception).
this single code is taking the 30 secs:
Application integrationService = New Application();
Does anyone have any idea what I need to check or configure to make this faster?
I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.
1) How do I create a new schema?
2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button?
3) Are scripts that work for Express going to have any problem executing on SQL Server?
It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).
Is there a way to "reset" a failed subscription without have to recreate the entire subscription?
Thank you
Devon Kyle
28638
SSCE_M_DUPLICATETABLE
Existing subscription already contains table included in the new subscription.
What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?
Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?
This is a great tutorial and it's a shame one of the more important steps was missed.
In the €œCreate the snapshot user€? section you you find the steps to create the snapshot_agent account. Then in the €œCreate the snapshot folder€? section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section €œCreate a new subscription".
To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.
Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.
I am studying the tutorial in SQL Server 2005 Mobile Edition Books Online, and the topic is Creating a Mobile Application with SQL Server Mobile. I have got a problem when creating a new subscription after created a new SQL Server Mobile database. And the problem is shown below:
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
* Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.
HRESULT 0x80004005 (29061)
* 無法完æˆ?作æ¥ã€‚
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Before I have met this problem, I have finished all the task. And I can browse the localhost web site by using anonymous account even I use internet explorer or browse the directly in IIS.
Does anyone can solve it?? Thank you very much~~~
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep
Hey,
don't know if it's the right place for this question but i hope you help me.
I made an application with VS 2005 that connecting to sql server file db.mdf
Now i want this application work on another user computer, and of course i don't want to install vs 2005 there.
I did install .net framework, but what i need to do to make the database work? do i have to install sql server on his computer? or is there something more simple?
I know that if i was using access file than i need to install nothing else.
please help!
thanx.
max
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
Hi,
We are migrating our reporting services database from one server to another.
What needs to be done to subscriptions to ensure they work properly. Will they automatically update as part of the migration, or is there a manual process.
We are using Reporting Services 2000 and will be using Reporting services 2000 as well on the new server.
Thanks
Hi all,
I'm creating data driven subscription for parameterized reports. I'm supposed to deliver one report to six different customers. But each customer can see only his own data in the report. This is not a problem. But the problem is I need another report in which all the data can be seen. When I create DD subsription, I chosed Auto increment option for the write mode under "
Specify delivery extension settings for Report Server FileShare" .
But then it creates seperate report for each parameter value. But it does not create the report which contains all the data for all parameter values. How can I make it without creating another report.
Also when I use overwrite option instead of Auto Increment, It creates only one reports. Does any one know why it happens?
Thanks
Hello,
is it possible to make a new subscription remote over a website and NOT in the Reportmanager?
Has anybody done something like this and are there any examples?
Thx
Jo-Jo
Hi,
I am doing a repl.in SQL7/NT.
At time of 'Push Subscription wizard' - I used the option "Yes,Initialize the schema and data......" whereas I already had data and schema at the subscriber.
Now my question is what will happen now and how long will this process take(for approx5GB of DAta).
OR should I stop the process - it's already an hour when I started the process.
Any suggestion appreciated.
I have created a subscription pull from one SQL server to another so that the database from one SQL server is replicated to another. But when the subscription runs, no data appears in the subscriber SQL server. When I check the subscription, I see that the database is connecting to the publisher but that is it. I have the login correct but what am I missing? Do the tables in the subscriber database have to be already created and matching the ones in the publisher database before the subscription runs or are they automaticaly created after the subscription runs?
Thank you in advance
Hello all,
I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help.
The information in the database for the report is as follows
FileName : Test1
FileExtn : True
Path = \ram\C$Reports
Render_Format = PDF
Username = Administrator
Password = password
Writemode = Overwrite
All the fields are varchar(50) in size.