How to insert or update info into table Subscriptions that inside Report Server DB

[ROY.BLOOM]

Hi all
I crated a Report in SQL Server Reporting Services.
The Report uses 1 parameter.

When I exe the Report in my IE it asks me for the parameter and then run fine.
When I use Subscriptions' I can put there my email. And sent it to me. (Work fine to)

I want to send this Report to 600 deferent emails. Each and every one get unique parameter.
I have a table with the emails and the parameter.

1. How can I update or insert Subscriptions using a query?
Or
2. Where can I find a script that will help me to do so?

I tried to insert row into table Subscriptions but the server did not let me do so because OF The key.
I try to update row Parameters, ExtensionSettings and the query failed. It’s an ‘ntext’ column. (Xml)

Can u help me?

10x all
Roy

[sknake]

I'm afraid I don't have much experience with the reporting services but this sounds like you're having issues with the TSQL statements? Can you right click on the table you're trying to insert to in the SQL Server Management Studio, click "Script table as -- Create to -- Clipboard" then post the structure here inside of code tags?

[code=sql] ...query here...

[/code]

[ROY.BLOOM]

hear

USE [ReportServer]
GO
/****** Object:  Table [dbo].[Subscriptions]    Script Date: 08/09/2009 14:53:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Subscriptions](
	[SubscriptionID] [uniqueidentifier] NOT NULL,
	[OwnerID] [uniqueidentifier] NOT NULL,
	[Report_OID] [uniqueidentifier] NOT NULL,
	[Locale] [nvarchar](128) NOT NULL,
	[InactiveFlags] [int] NOT NULL,
	[ExtensionSettings] [ntext] NULL,
	[ModifiedByID] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[Description] [nvarchar](512) NULL,
	[LastStatus] [nvarchar](260) NULL,
	[EventType] [nvarchar](260) NOT NULL,
	[MatchData] [ntext] NULL,
	[LastRunTime] [datetime] NULL,
	[Parameters] [ntext] NULL,
	[DataSettings] [ntext] NULL,
	[DeliveryExtension] [nvarchar](260) NULL,
	[Version] [int] NOT NULL,
 CONSTRAINT [PK_Subscriptions] PRIMARY KEY CLUSTERED 
(
	[SubscriptionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[Subscriptions]  WITH NOCHECK ADD  CONSTRAINT [FK_Subscriptions_Catalog] FOREIGN KEY([Report_OID])
REFERENCES [dbo].[Catalog] ([ItemID])
ON DELETE CASCADE
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[Subscriptions] CHECK CONSTRAINT [FK_Subscriptions_Catalog]
GO
ALTER TABLE [dbo].[Subscriptions]  WITH NOCHECK ADD  CONSTRAINT [FK_Subscriptions_ModifiedBy] FOREIGN KEY([ModifiedByID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[Subscriptions] CHECK CONSTRAINT [FK_Subscriptions_ModifiedBy]
GO
ALTER TABLE [dbo].[Subscriptions]  WITH NOCHECK ADD  CONSTRAINT [FK_Subscriptions_Owner] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[Subscriptions] CHECK CONSTRAINT [FK_Subscriptions_Owner]

[sknake]

While I take a look at this hit "Edit Post" under your last post. It should look like:

[code=sql] CREATE TABLE [dbo].[Subscriptions]( [SubscriptionID] [uniqueidentifier] NOT NULL, ...etc...

[/code]

If you hit reply please take out the "" tag. That lets me show how code tags are using without the server actually parsing them :)

Give me a few minutes[noparse ]" tag. That lets me show how code tags are using without the server actually parsing them :)

Give me a few minutes

[ROY.BLOOM]

select distinct CallType from cdr where Customer_Code=734
order by date_c



select convert (char,getdate(),101)


convert(char,DocDate,101)

DateAdd("d", -1, DateAdd("m", 1, DateValue(1 & "/" & m & "/" & y)))





     convert (char,LastDayofmonth ,101) AS DocDate

like this?:)

[sknake]

You got it!

I have looked at your table setup and I'm not sure how you can insert data in this case. I tend to avoid FK so i'm not sure how that plays in when inserting data. How are you currently inserting data? You can use the SQL Server Profiler and trap the insert queries to see what is required to populate the table.

[ROY.BLOOM]

10x
i need SQL pro and not standard

this is the problem