How to make a File Share Subscription running on Vista?

I recently migrate to Vista. I had a bunch of reports with file share subscriptions that were running fine on XP.

After installing reporting services on vista (that part only was a challenge), I re-created my subscriptions using the report manager. As expected, when a subscription is executed, the ‘last run’ column shows the last time the report output has been delivered to the file share and the ‘status’ column shows ‘New Subscription’. I thought that this was the signature of a successfully configured subscription.

But surprisingly, there is nothing in the file share. The directory is empty. Anybody has an idea why? Anybody knows how I could possibly find information on my system to find the cause of the problem?

Also, in report manager – after spending hours to figure that I had to run IE in admin mode to see the content of the web app - I cannot delete subscriptions. After selecting the subscription and pressing the delete button I get a JavaScript error and nothing happens. Any trick I could use to get rid of the unused subscriptions (other than using the Reporting Services API).

Thanks,

Dom.

Hi Dom,

There are a few things here.

1. You mention that the subscription executed successfully as the "Last Run" column is updated. In that event the "Status" column should also be updated to something like "The file was successfully saved...." or "Failure writing file....", etc and not remain as "New Subscritpion".

2. Can you check if the fileshare has adequate permissions for the owner of the sunscription?

3. I'm not aware of the JavaScript error and using SOAP API(DeleteSubscription) would be the best way to go about it.

Thanks,
Sharmila

|||

Hi Sharmila,

Thanks for your reply. You are absolutely right; the status column shouldn't say 'new subscription' but 'the file was successfully ...'. Good call!

I checked the file share permissions and unless I am missing something I think the permissions are ok. Everything is running on the same machine (reporting services, sql server 2005 enterprise edition trial and the shared folder).

Let’s say my machine name is ‘ka’ and the user name is ‘boom’.Boom is an admin on the machine (remember I am running Vista).The owner of the subscription is ‘ka\boom’ and the file share delivery extension is set with ‘ka\boom’ and his password.

On the same machine, I have a shared folder and the owner permissions are given to ‘ka\boom’.

I have another machine running XP on my local network with reporting services.I tried to configure a subscription on the XP machine with a file share delivery pointing to the shared folder on my machine running Vista.The result is the same, the status column keep saying ‘new subscription’ while the last run column gets updated correctly.

Finally, yes I did use the soap api to get rid of the undesired subscriptions.

Thanks again,

Dom.

|||

Can you include the logs specifically around the time the subscription fired?

Thanks,
Sharmila

|||

I re-configured the whole thing today in order to get good log files for you and this time I got a different error message in the ‘status’ column of the report manager. I got: “Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

I zipped all the log files I could think might help to solve the problem. You can get the package at: http://dchoquette.aaafly.com/logs/log_files.zip

Let me know if you need more logs to diagnose the problem.

I created the subscription at 11:03 am today (February 22^nd, 2007) and the subscription fired at 11:10 am.

I am going to start looking on my side for answers related to that new error message that I got.

Thanks for you help.

|||

I was puzzled before that the "Status" column was not updated even though the subscription executed successfully. It makes sense now that both the "Last Run" and "Status" columns are updated. It looks like a permission issue to me. I do not have enough info from the logs. You need to send the report server service log of this format(Eg: ReportServerService__02_13_2007_14_38_32.log).

Thanks,

Sharmila

|||

I just updated the zip file, you can get it at the same location. It now contains the log file you requested and I briefly saw permission problems around 11:10 am. Not sure what is the cause though. I didn’t know there was a ReportServerService AND a ReportServerServiceMain in the logs directory :)

Let me know if you find out why I have that permission issue.

|||

Talking to the developer here, it looks like it could be two things:

1. RS Windows service account does not have enough rights.

2. Are you seeing this only on your Vista machine? Have you tried on a different machine?

Thanks,
Sharmila

|||

The RS Windows service is running under the ‘Local System account’ and the option ‘Allow service to interact with desktop’ is unchecked.This must be the default configuration because I do not remember changing the log on settings.

Yes I only experience this problem on Vista.The exact same subscription configuration is working fine on another machine running XP.

Thanks for helping me, I really appreciate it.

|||

To narrow down and track the real issue, can you try using different accounts for the RS Windows service to see if that works?

Thanks,
Sharmila

|||

I couldn't repro this in house and so was wondering if you have the latest released SP2 installed(not CTP3)? See if that makes a difference.

Thanks,

Sharmila

|||

I tried to change the log on account of the reporting services window service. I configured it with an administrator account on my machine. When I tried to create a file share subscription to test I got the following error message:

A subscription delivery error has occurred. (rsDeliveryError) Get Online Help

	The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
	Bad Data. (Exception from HRESULT: 0x80090005)

Not sure what to do to get that other problem out of the way. I changed the log on account of reporting services back to ‘local system account’ and now I can create the file share subscription but it still gives me the same error:

“Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

Concerning which service pack I have installed, I checked the digital signature of the executable I installed on my system and the Singing time is ‘Saturday, December 09, 2006 4:12:36 AM’. Hopefully you can tell if it is the latest release of SP2 and not CTP3.

Thanks,

Dom.

|||

It looks like you have CTP3. Check out the post about the release of SP2.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247133&SiteID=1

Thanks,

Sharmila

|||

I installed the new release of SP2 and I still get the same error. That's it, I give up :) ... I wrote a custom delivery extension that is writing the report output in a directory and it is working fine for my needs.

I had a question concerning the delivery extensions if you don't mind. Do you know if it is possible to reuse the fonctionality of the built-in email delivery extension from the custom delivery extension I wrote? If so, do you have a link to a page explaining how to do so?

Thanks a lot for your help, you have been very helpful.

Dom

How to make a File Share Subscription running on Vista?

I recently migrate to Vista. I had a bunch of reports with file share subscriptions that were running fine on XP.

After installing reporting services on vista (that part only was a challenge), I re-created my subscriptions using the report manager. As expected, when a subscription is executed, the ‘last run’ column shows the last time the report output has been delivered to the file share and the ‘status’ column shows ‘New Subscription’. I thought that this was the signature of a successfully configured subscription.

But surprisingly, there is nothing in the file share. The directory is empty. Anybody has an idea why? Anybody knows how I could possibly find information on my system to find the cause of the problem?

Also, in report manager – after spending hours to figure that I had to run IE in admin mode to see the content of the web app - I cannot delete subscriptions. After selecting the subscription and pressing the delete button I get a JavaScript error and nothing happens. Any trick I could use to get rid of the unused subscriptions (other than using the Reporting Services API).

Thanks,

Dom.

Hi Dom,

There are a few things here.

1. You mention that the subscription executed successfully as the "Last Run" column is updated. In that event the "Status" column should also be updated to something like "The file was successfully saved...." or "Failure writing file....", etc and not remain as "New Subscritpion".

2. Can you check if the fileshare has adequate permissions for the owner of the sunscription?

3. I'm not aware of the JavaScript error and using SOAP API(DeleteSubscription) would be the best way to go about it.

Thanks,
Sharmila

|||

Hi Sharmila,

Thanks for your reply. You are absolutely right; the status column shouldn't say 'new subscription' but 'the file was successfully ...'. Good call!

I checked the file share permissions and unless I am missing something I think the permissions are ok. Everything is running on the same machine (reporting services, sql server 2005 enterprise edition trial and the shared folder).

Let’s say my machine name is ‘ka’ and the user name is ‘boom’. Boom is an admin on the machine (remember I am running Vista).The owner of the subscription is ‘ka\boom’ and the file share delivery extension is set with ‘ka\boom’ and his password.

On the same machine, I have a shared folder and the owner permissions are given to ‘ka\boom’.

I have another machine running XP on my local network with reporting services.I tried to configure a subscription on the XP machine with a file share delivery pointing to the shared folder on my machine running Vista.The result is the same, the status column keep saying ‘new subscription’ while the last run column gets updated correctly.

Finally, yes I did use the soap api to get rid of the undesired subscriptions.

Thanks again,

Dom.

|||

Can you include the logs specifically around the time the subscription fired?

Thanks,
Sharmila

|||

I re-configured the whole thing today in order to get good log files for you and this time I got a different error message in the ‘status’ column of the report manager. I got: “Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

I zipped all the log files I could think might help to solve the problem. You can get the package at: http://dchoquette.aaafly.com/logs/log_files.zip

Let me know if you need more logs to diagnose the problem.

I created the subscription at 11:03 am today (February 22^nd, 2007) and the subscription fired at 11:10 am.

I am going to start looking on my side for answers related to that new error message that I got.

Thanks for you help.

|||

I was puzzled before that the "Status" column was not updated even though the subscription executed successfully. It makes sense now that both the "Last Run" and "Status" columns are updated. It looks like a permission issue to me. I do not have enough info from the logs. You need to send the report server service log of this format(Eg: ReportServerService__02_13_2007_14_38_32.log).

Thanks,

Sharmila

|||

I just updated the zip file, you can get it at the same location. It now contains the log file you requested and I briefly saw permission problems around 11:10 am. Not sure what is the cause though. I didn’t know there was a ReportServerService AND a ReportServerServiceMain in the logs directory :)

Let me know if you find out why I have that permission issue.

|||

Talking to the developer here, it looks like it could be two things:

1. RS Windows service account does not have enough rights.

2. Are you seeing this only on your Vista machine? Have you tried on a different machine?

Thanks,
Sharmila

|||

The RS Windows service is running under the ‘Local System account’ and the option ‘Allow service to interact with desktop’ is unchecked. This must be the default configuration because I do not remember changing the log on settings.

Yes I only experience this problem on Vista.The exact same subscription configuration is working fine on another machine running XP.

Thanks for helping me, I really appreciate it.

|||

To narrow down and track the real issue, can you try using different accounts for the RS Windows service to see if that works?

Thanks,
Sharmila

|||

I couldn't repro this in house and so was wondering if you have the latest released SP2 installed(not CTP3)? See if that makes a difference.

Thanks,

Sharmila

|||

I tried to change the log on account of the reporting services window service. I configured it with an administrator account on my machine. When I tried to create a file share subscription to test I got the following error message:

A subscription delivery error has occurred. (rsDeliveryError) Get Online Help

	The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
	Bad Data. (Exception from HRESULT: 0x80090005)

Not sure what to do to get that other problem out of the way. I changed the log on account of reporting services back to ‘local system account’ and now I can create the file share subscription but it still gives me the same error:

“Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

Concerning which service pack I have installed, I checked the digital signature of the executable I installed on my system and the Singing time is ‘Saturday, December 09, 2006 4:12:36 AM’. Hopefully you can tell if it is the latest release of SP2 and not CTP3.

Thanks,

Dom.

|||

It looks like you have CTP3. Check out the post about the release of SP2.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247133&SiteID=1

Thanks,

Sharmila

|||

I installed the new release of SP2 and I still get the same error. That's it, I give up :) ... I wrote a custom delivery extension that is writing the report output in a directory and it is working fine for my needs.

I had a question concerning the delivery extensions if you don't mind. Do you know if it is possible to reuse the fonctionality of the built-in email delivery extension from the custom delivery extension I wrote? If so, do you have a link to a page explaining how to do so?

Thanks a lot for your help, you have been very helpful.

Dom

sql

How to list tables with Primary keys

Hello,

We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have them. Is there a
command that I can use (on the source db) to find out which tables
contain primary keys? The db has hundreds of tables and I'd rather not
go through each one to see which has a primary key.

Also, for future reference, is there a way to include the primary key
on an import?

Thanks,
PepsDanny (dlapitan@.gmail.com) writes:

Quote:

Originally Posted by

We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have them. Is there a
command that I can use (on the source db) to find out which tables
contain primary keys? The db has hundreds of tables and I'd rather not
go through each one to see which has a primary key.

Hopefully all tables have primary keys!

It would have helped if you had said which version of SQL Server you are
using. The query below will run on both SQL 2000 and SQL 2005, but the
old system tables are deprecated on SQL 2005, so had I known you were
using SQL 2005, I would have used the new catalog views instead.

If you did not bring over the primary keys, I suspect that no indexes at
all were copied. This query lists all indexes in a database, and the
column ispk indicates that the index is a primary key. The column
isuniqueconst indicates whether the index is a UNIQUE constraint.

Note that the query as I've written it, will only include the first
five columns in the index. Neither does include information about
ascending/descening, and other less commonly used index properties.

SELECT o.name, i.name,
isclustered = Indexproperty(o.id, i.name, 'IsClustered'),
isunique = Indexproperty(o.id, i.name, 'IsUnique'),
ispk = CASE WHEN o2.xtype = 'PK' THEN 1 ELSE 0 END,
isuniqueconst = CASE WHEN o2.xtype = 'UQ' THEN 1 ELSE 0 END,
cols = ik.col1 + coalesce(', ' + ik.col2, '') +
coalesce(', ' + ik.col3, '') + coalesce(', ' + ik.col4, '') +
coalesce(', ' + ik.col5, '')
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
LEFT JOIN sysobjects o2 ON o2.name = i.name
AND o2.parent_obj = o.id
JOIN (SELECT ik.id, ik.indid,
col1 = MIN(CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN(CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN(CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN(CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN(CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysindexkeys ik
JOIN syscolumns c ON ik.id = c.id
AND ik.colid = c.colid
GROUP BY ik.id, ik.indid) AS ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE Indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND Indexproperty(i.id, i.name, 'IsStatistics') = 0
AND Indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
ORDER BY o.name, i.indid

Quote:

Originally Posted by

Also, for future reference, is there a way to include the primary key
on an import?

There is. But I don't know which tool you used, which version of SQL Server
you have etc. Personally, I prefer to build databases from scripts. When
I need to copy a database, I prefer to use BACKUP/RESTORE.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 8, 7:59 pm, Danny <dlapi...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have them. Is there a
command that I can use (on the source db) to find out which tables
contain primary keys? The db has hundreds of tables and I'd rather not
go through each one to see which has a primary key.
>
Also, for future reference, is there a way to include the primary key
on an import?
>
Thanks,
Peps

What are all the keys used in a database

http://www.sqlhacks.com/faqs/list_all_keys
USE AdventureWorksLT;
go

SELECT schm.name AS 'Schema', tbl.name AS 'Table'
, KEYS.name AS 'Constraint', KEYS.type_desc AS 'Type'
, cols.name AS 'Column'
FROM sys.key_constraints AS KEYS
JOIN sys.TABLES AS tbl
ON tbl.object_id = KEYS.parent_object_id
JOIN sys.schemas AS schm
ON schm.schema_id = tbl.schema_id
JOIN sys.index_columns AS idxcols
ON idxcols.object_id = tbl.object_id
AND idxcols.index_id = KEYS.unique_index_id
JOIN sys.COLUMNS AS cols
ON cols.object_id = tbl.object_id
AND cols.column_id = idxcols.column_id
ORDER BY 1,2,3,4;
go

AND

What are all the tables without a primary key?

http://www.sqlhacks.com/faqs/no_primary_key
USE sql911;
go

SELECT SCHEMA_NAME(schema_id) AS "Schema", name AS "Table"
FROM sys.TABLES
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1,2;
go

This includes samples and explanations on how to do it.

Also new this week:

SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server?
What's the structure of a table with MS SQL Server?