Creating a Report Model - any doc other than Books Online?

Is there any documentation available on building a report model other than
the SQL Books Online? The subject is usually given about 3-5 pages max in
any Reporting Services book. I'd like to see a whole book on the subject,
with lots of examples.
--
LehrSJOn Sep 12, 6:18 pm, LehrSJ <lehr...@.noemail.nospam> wrote:
> Is there any documentation available on building a report model other than
> the SQL Books Online? The subject is usually given about 3-5 pages max in
> any Reporting Services book. I'd like to see a whole book on the subject,
> with lots of examples.
> --
> LehrSJ
This link might be helpful.
http://www.databasejournal.com/features/mssql/article.php/10894_3598931_1
Regards,
Enrique Martinez
Sr. Software Consultant

Creating a new Subscription with RMO objects

Hi Gang,
Does anyone know how to createa a new subscription using RMO on SQL Server
2005. I'm using the beta version (Sep CTP) but SQL books online has bad
links on the doc's for doing this.
Any help will be greately appreciated,
Thanks,
--Ader.
The following link should hoperfully work better...
http://msdn2.microsoft.com/en-us/library/ms146863
HTH
-Raymond
"gonzalez" wrote:

> Hi Gang,
> Does anyone know how to createa a new subscription using RMO on SQL Server
> 2005. I'm using the beta version (Sep CTP) but SQL books online has bad
> links on the doc's for doing this.
> Any help will be greately appreciated,
> Thanks,
> --Ader.
>
>

Creating a local SQL Server Compact Edition DB

Hi All ...

I'm setting up replication for the 1st time following the steps in the Books Online to get it up and running:

ms-help://MS.SSCE.v31.EN/ssmmain3/html/5a82aa7a-41a3-4246-a01a-2b1e4b2fdfe9.htm.

Anyhow, I get down to the section labeled - Create a new SQL Server Compact Edition database and am having a problem. I open SQL Server Management Studio as instructed. I click Connect and then am supposed to select SQL Server Compact Edition - yet can't find that option.

And, yes, I've installed Compact Edition on my development laptop. So, how do I get this option so that I can create the local DB for Compact Edition and continue development?

UPDATE:

Ok, duh moment, I have SQL Server Management Studio Express loaded on my laptop, so did not have the option. I've attempted to uninstall SQL Server Management Studio Express and install the full version from the SQL Server CDs. However, when I go to install the full version it tells me that the management tools are already installed and won't let me install anything. So, how do I get around this so that I can install full version of SQL Server Management Studio and then get the option to connect to the SQL Server Compact Edition?

Any help will be appreciated.

UPDATE 2:

Ok, figured it out. You can't just uninstall SQL Server Management Studio express, you must go in to Add/Remove Programs, SQL Server 2005, Change. Then select to uninstall the Workstation components. Then you can go use the SQL Server install discs to install the full blown SQL Server Management Studio along with the other Workstation components.

Thanks ...

David L. Collison

Any day above ground is a good day!

As a script-kiddie wannabe, I will suggest that you can use the following vbscript as well:

<job id="SqlServerCompactEditionDatabaseCreator">
<script language="VBScript">

Option Explicit

Dim strConnectionString
Dim oCatalog

strConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" & WScript.Arguments(0) & ";"

Set oCatalog = WScript.CreateObject("ADOX.Catalog")
oCatalog.Create strConnectionString

</script>
</job>

Save the script to something like SqlServerCompactEditionDatabaseCreator.wsf, and you can create a SQL Server Compact Edition database with something like cscript SqlServerCompactEditionDatabaseCreator.wsf foo.sdf from the command prompt.

Then again, that is probably just my 2 cents

-Raymond

CreateDataDrivenSubscription

I try to use .NET to subscribe report which email address is retrived from Access databse. I follow the online example of CreateDataDrivenSubscription and the main difference is the mail list come from Access DB.
The code is as follow:
Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
But when I run the report, the following error is shown:
rsMissingElement
400
The required field DataSourceDefinitionOrReference is missing from the input structure.
What's wrong with my code ?Can you post more of your code? From the looks of it perhaps you are not
setting the Item property on your DataRetrievalPlan to the
DataSourceDefinition that you defined below.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> I try to use .NET to subscribe report which email address is retrived from
Access databse. I follow the online example of CreateDataDrivenSubscription
and the main difference is the mail list come from Access DB.
> The code is as follow:
> Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> But when I run the report, the following error is shown:
> rsMissingElement
> 400
> The required field DataSourceDefinitionOrReference is missing from
the input structure.
> What's wrong with my code ?|||The code are as follows:
Dim delivery As New RptSvc.DataSource
delivery.Name = ""
Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
dataSrcDefinition.CredentialRetrieval = RptSvc.CredentialRetrievalEnum.Store
dataSrcDefinition.Enabled = True
dataSrcDefinition.EnabledSpecified = True
dataSrcDefinition.Extension = "OLEDB"
dataSrcDefinition.ImpersonateUser = False
dataSrcDefinition.ImpersonateUserSpecified = False
dataSrcDefinition.UserName = "admin"
dataSrcDefinition.Password = ""
dataSrcDefinition.Prompt = Nothing
dataSrcDefinition.WindowsCredentials = False
delivery.Item = dataSrcDefinition
' Create the fields list.
Dim fieldsList(1) As RptSvc.Field
fieldsList(0) = New RptSvc.Field
fieldsList(0).Name = "EmailAddress"
fieldsList(0).Alias = "EmailAddress"
fieldsList(1) = New RptSvc.Field
fieldsList(1).Name = "EmplID"
fieldsList(1).Alias = "EmplID"
' Create the data set for the delivery query.
Dim dataSetDefinition As New RptSvc.DataSetDefinition
dataSetDefinition.AccentSensitivitySpecified = False
dataSetDefinition.CaseSensitivitySpecified = False
dataSetDefinition.KanatypeSensitivitySpecified = False
dataSetDefinition.WidthSensitivitySpecified = False
dataSetDefinition.Fields = fieldsList
Dim queryDefinition As New RptSvc.QueryDefinition
queryDefinition.CommandText = "Select * from MailList"
queryDefinition.CommandType = "Text"
queryDefinition.Timeout = 45
queryDefinition.TimeoutSpecified = True
dataSetDefinition.Query = queryDefinition
Dim results As New RptSvc.DataSetDefinition
Dim changed As Boolean
Try
results = rs.PrepareQuery(delivery, dataSetDefinition, changed)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerText.ToString())
End Try
' Set the event type and match data for the delivery.
Dim dataRetrieval As New RptSvc.DataRetrievalPlan
dataRetrieval.DataSet = results
' Set the event type and match data for the delivery.
Dim eventType As String = "TimedSubscription"
Dim matchData As String = "<ScheduleDefinition>" & _
"<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" & _
"<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
"<DaysOfWeek>" & _
"<Thursday>True</Thursday>" & _
"</DaysOfWeek></WeeklyRecurrence>" & _
"</ScheduleDefinition>"
' Set the report parameter values.
Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
empID.ParameterName = "EmplID"
empID.FieldAlias = "EmplID"
parameters(0) = empID
Dim VVD As New RptSvc.ParameterValue
VVD.Name = "VVD"
VVD.Value = "CB1345"
parameters(1) = VVD
Dim OPR As New RptSvc.ParameterValue
OPR.Name = "OPR"
OPR.Value = "*"
parameters(2) = OPR
Dim BillOpr As New RptSvc.ParameterValue
BillOpr.Name = "BILL_COMP"
BillOpr.Value = "*"
parameters(3) = BillOpr
Try
Dim subscriptionID As String = rs.CreateDataDrivenSubscription( _
reportPath, settings, dataRetrieval, sEmaiDesc, eventType, matchData, parameters)
Catch e As SoapException
Console.WriteLine(e.Detail.InnerText.ToString())
End Try
Actually, the code is simliar as the example
Thanks a lot !!!
"Daniel Reib [MSFT]" wrote:
> Can you post more of your code? From the looks of it perhaps you are not
> setting the Item property on your DataRetrievalPlan to the
> DataSourceDefinition that you defined below.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > I try to use .NET to subscribe report which email address is retrived from
> Access databse. I follow the online example of CreateDataDrivenSubscription
> and the main difference is the mail list come from Access DB.
> >
> > The code is as follow:
> > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> >
> > But when I run the report, the following error is shown:
> > rsMissingElement
> > 400
> > The required field DataSourceDefinitionOrReference is missing from
> the input structure.
> >
> > What's wrong with my code ?
>
>|||I find a Microsoft document about the errors of sample code. As you say, I forget to set item property of dataRetrievel plan to dataSurceDefinition.
Thanks for your help !!!
"May Liu" wrote:
> The code are as follows:
> Dim delivery As New RptSvc.DataSource
> delivery.Name = ""
> Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> dataSrcDefinition.CredentialRetrieval = RptSvc.CredentialRetrievalEnum.Store
> dataSrcDefinition.Enabled = True
> dataSrcDefinition.EnabledSpecified = True
> dataSrcDefinition.Extension = "OLEDB"
> dataSrcDefinition.ImpersonateUser = False
> dataSrcDefinition.ImpersonateUserSpecified = False
> dataSrcDefinition.UserName = "admin"
> dataSrcDefinition.Password = ""
> dataSrcDefinition.Prompt = Nothing
> dataSrcDefinition.WindowsCredentials = False
> delivery.Item = dataSrcDefinition
> ' Create the fields list.
> Dim fieldsList(1) As RptSvc.Field
> fieldsList(0) = New RptSvc.Field
> fieldsList(0).Name = "EmailAddress"
> fieldsList(0).Alias = "EmailAddress"
> fieldsList(1) = New RptSvc.Field
> fieldsList(1).Name = "EmplID"
> fieldsList(1).Alias = "EmplID"
> ' Create the data set for the delivery query.
> Dim dataSetDefinition As New RptSvc.DataSetDefinition
> dataSetDefinition.AccentSensitivitySpecified = False
> dataSetDefinition.CaseSensitivitySpecified = False
> dataSetDefinition.KanatypeSensitivitySpecified = False
> dataSetDefinition.WidthSensitivitySpecified = False
> dataSetDefinition.Fields = fieldsList
> Dim queryDefinition As New RptSvc.QueryDefinition
> queryDefinition.CommandText = "Select * from MailList"
> queryDefinition.CommandType = "Text"
> queryDefinition.Timeout = 45
> queryDefinition.TimeoutSpecified = True
> dataSetDefinition.Query = queryDefinition
> Dim results As New RptSvc.DataSetDefinition
> Dim changed As Boolean
> Try
> results = rs.PrepareQuery(delivery, dataSetDefinition, changed)
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerText.ToString())
> End Try
> ' Set the event type and match data for the delivery.
> Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> dataRetrieval.DataSet = results
> ' Set the event type and match data for the delivery.
> Dim eventType As String = "TimedSubscription"
> Dim matchData As String = "<ScheduleDefinition>" & _
> "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" & _
> "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> "<DaysOfWeek>" & _
> "<Thursday>True</Thursday>" & _
> "</DaysOfWeek></WeeklyRecurrence>" & _
> "</ScheduleDefinition>"
> ' Set the report parameter values.
> Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> empID.ParameterName = "EmplID"
> empID.FieldAlias = "EmplID"
> parameters(0) = empID
> Dim VVD As New RptSvc.ParameterValue
> VVD.Name = "VVD"
> VVD.Value = "CB1345"
> parameters(1) = VVD
> Dim OPR As New RptSvc.ParameterValue
> OPR.Name = "OPR"
> OPR.Value = "*"
> parameters(2) = OPR
> Dim BillOpr As New RptSvc.ParameterValue
> BillOpr.Name = "BILL_COMP"
> BillOpr.Value = "*"
> parameters(3) = BillOpr
> Try
> Dim subscriptionID As String = rs.CreateDataDrivenSubscription( _
> reportPath, settings, dataRetrieval, sEmaiDesc, eventType, matchData, parameters)
> Catch e As SoapException
> Console.WriteLine(e.Detail.InnerText.ToString())
> End Try
> Actually, the code is simliar as the example
> Thanks a lot !!!
> "Daniel Reib [MSFT]" wrote:
> > Can you post more of your code? From the looks of it perhaps you are not
> > setting the Item property on your DataRetrievalPlan to the
> > DataSourceDefinition that you defined below.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > I try to use .NET to subscribe report which email address is retrived from
> > Access databse. I follow the online example of CreateDataDrivenSubscription
> > and the main difference is the mail list come from Access DB.
> > >
> > > The code is as follow:
> > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > dataSrcDefinition.ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > >
> > > But when I run the report, the following error is shown:
> > > rsMissingElement
> > > 400
> > > The required field DataSourceDefinitionOrReference is missing from
> > the input structure.
> > >
> > > What's wrong with my code ?
> >
> >
> >|||In case anyone is interested:
http://support.microsoft.com/?kbid=842854
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:5D614D3D-B132-48C0-9388-C2ED19F0ABFE@.microsoft.com...
> I find a Microsoft document about the errors of sample code. As you say,
I forget to set item property of dataRetrievel plan to dataSurceDefinition.
> Thanks for your help !!!
> "May Liu" wrote:
> > The code are as follows:
> >
> > Dim delivery As New RptSvc.DataSource
> > delivery.Name = ""
> > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> >
> > dataSrcDefinition.ConnectString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist
Security Info=False"
> > dataSrcDefinition.CredentialRetrieval =RptSvc.CredentialRetrievalEnum.Store
> > dataSrcDefinition.Enabled = True
> > dataSrcDefinition.EnabledSpecified = True
> > dataSrcDefinition.Extension = "OLEDB"
> > dataSrcDefinition.ImpersonateUser = False
> > dataSrcDefinition.ImpersonateUserSpecified = False
> > dataSrcDefinition.UserName = "admin"
> > dataSrcDefinition.Password = ""
> > dataSrcDefinition.Prompt = Nothing
> > dataSrcDefinition.WindowsCredentials = False
> >
> > delivery.Item = dataSrcDefinition
> >
> > ' Create the fields list.
> > Dim fieldsList(1) As RptSvc.Field
> > fieldsList(0) = New RptSvc.Field
> > fieldsList(0).Name = "EmailAddress"
> > fieldsList(0).Alias = "EmailAddress"
> > fieldsList(1) = New RptSvc.Field
> > fieldsList(1).Name = "EmplID"
> > fieldsList(1).Alias = "EmplID"
> >
> > ' Create the data set for the delivery query.
> > Dim dataSetDefinition As New RptSvc.DataSetDefinition
> > dataSetDefinition.AccentSensitivitySpecified = False
> > dataSetDefinition.CaseSensitivitySpecified = False
> > dataSetDefinition.KanatypeSensitivitySpecified = False
> > dataSetDefinition.WidthSensitivitySpecified = False
> > dataSetDefinition.Fields = fieldsList
> >
> > Dim queryDefinition As New RptSvc.QueryDefinition
> > queryDefinition.CommandText = "Select * from MailList"
> > queryDefinition.CommandType = "Text"
> > queryDefinition.Timeout = 45
> > queryDefinition.TimeoutSpecified = True
> > dataSetDefinition.Query = queryDefinition
> > Dim results As New RptSvc.DataSetDefinition
> > Dim changed As Boolean
> >
> > Try
> > results = rs.PrepareQuery(delivery, dataSetDefinition,
changed)
> > Catch e As SoapException
> > Console.WriteLine(e.Detail.InnerText.ToString())
> > End Try
> >
> > ' Set the event type and match data for the delivery.
> > Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> > dataRetrieval.DataSet = results
> >
> > ' Set the event type and match data for the delivery.
> > Dim eventType As String = "TimedSubscription"
> > Dim matchData As String = "<ScheduleDefinition>" & _
> > "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" &
_
> > "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> > "<DaysOfWeek>" & _
> > "<Thursday>True</Thursday>" & _
> > "</DaysOfWeek></WeeklyRecurrence>" & _
> > "</ScheduleDefinition>"
> >
> > ' Set the report parameter values.
> > Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> >
> > Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> > empID.ParameterName = "EmplID"
> > empID.FieldAlias = "EmplID"
> > parameters(0) = empID
> >
> > Dim VVD As New RptSvc.ParameterValue
> > VVD.Name = "VVD"
> > VVD.Value = "CB1345"
> > parameters(1) = VVD
> >
> > Dim OPR As New RptSvc.ParameterValue
> > OPR.Name = "OPR"
> > OPR.Value = "*"
> > parameters(2) = OPR
> >
> > Dim BillOpr As New RptSvc.ParameterValue
> > BillOpr.Name = "BILL_COMP"
> > BillOpr.Value = "*"
> > parameters(3) = BillOpr
> >
> > Try
> > Dim subscriptionID As String =rs.CreateDataDrivenSubscription( _
> > reportPath, settings, dataRetrieval, sEmaiDesc,
eventType, matchData, parameters)
> > Catch e As SoapException
> > Console.WriteLine(e.Detail.InnerText.ToString())
> > End Try
> >
> > Actually, the code is simliar as the example
> >
> > Thanks a lot !!!
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > Can you post more of your code? From the looks of it perhaps you are
not
> > > setting the Item property on your DataRetrievalPlan to the
> > > DataSourceDefinition that you defined below.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > >
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > > I try to use .NET to subscribe report which email address is
retrived from
> > > Access databse. I follow the online example of
CreateDataDrivenSubscription
> > > and the main difference is the mail list come from Access DB.
> > > >
> > > > The code is as follow:
> > > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > > dataSrcDefinition.ConnectString ="Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > > >
> > > > But when I run the report, the following error is shown:
> > > > rsMissingElement
> > > > 400
> > > > The required field DataSourceDefinitionOrReference is
missing from
> > > the input structure.
> > > >
> > > > What's wrong with my code ?
> > >
> > >
> > >|||I have two more question. Where is the physical location of generated file after the report is generated and email to user ? Does reporting service provide UI to let me to open this report ?
"Bryan Keller [MSFT]" wrote:
> In case anyone is interested:
> http://support.microsoft.com/?kbid=842854
> --
> Bryan Keller
> Developer Documentation
> SQL Server Reporting Services
> A friendly reminder that this posting is provided "AS IS" with no
> warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:5D614D3D-B132-48C0-9388-C2ED19F0ABFE@.microsoft.com...
> > I find a Microsoft document about the errors of sample code. As you say,
> I forget to set item property of dataRetrievel plan to dataSurceDefinition.
> >
> > Thanks for your help !!!
> >
> > "May Liu" wrote:
> >
> > > The code are as follows:
> > >
> > > Dim delivery As New RptSvc.DataSource
> > > delivery.Name = ""
> > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > >
> > > dataSrcDefinition.ConnectString => "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\RptSvc.mdb;Persist
> Security Info=False"
> > > dataSrcDefinition.CredentialRetrieval => RptSvc.CredentialRetrievalEnum.Store
> > > dataSrcDefinition.Enabled = True
> > > dataSrcDefinition.EnabledSpecified = True
> > > dataSrcDefinition.Extension = "OLEDB"
> > > dataSrcDefinition.ImpersonateUser = False
> > > dataSrcDefinition.ImpersonateUserSpecified = False
> > > dataSrcDefinition.UserName = "admin"
> > > dataSrcDefinition.Password = ""
> > > dataSrcDefinition.Prompt = Nothing
> > > dataSrcDefinition.WindowsCredentials = False
> > >
> > > delivery.Item = dataSrcDefinition
> > >
> > > ' Create the fields list.
> > > Dim fieldsList(1) As RptSvc.Field
> > > fieldsList(0) = New RptSvc.Field
> > > fieldsList(0).Name = "EmailAddress"
> > > fieldsList(0).Alias = "EmailAddress"
> > > fieldsList(1) = New RptSvc.Field
> > > fieldsList(1).Name = "EmplID"
> > > fieldsList(1).Alias = "EmplID"
> > >
> > > ' Create the data set for the delivery query.
> > > Dim dataSetDefinition As New RptSvc.DataSetDefinition
> > > dataSetDefinition.AccentSensitivitySpecified = False
> > > dataSetDefinition.CaseSensitivitySpecified = False
> > > dataSetDefinition.KanatypeSensitivitySpecified = False
> > > dataSetDefinition.WidthSensitivitySpecified = False
> > > dataSetDefinition.Fields = fieldsList
> > >
> > > Dim queryDefinition As New RptSvc.QueryDefinition
> > > queryDefinition.CommandText = "Select * from MailList"
> > > queryDefinition.CommandType = "Text"
> > > queryDefinition.Timeout = 45
> > > queryDefinition.TimeoutSpecified = True
> > > dataSetDefinition.Query = queryDefinition
> > > Dim results As New RptSvc.DataSetDefinition
> > > Dim changed As Boolean
> > >
> > > Try
> > > results = rs.PrepareQuery(delivery, dataSetDefinition,
> changed)
> > > Catch e As SoapException
> > > Console.WriteLine(e.Detail.InnerText.ToString())
> > > End Try
> > >
> > > ' Set the event type and match data for the delivery.
> > > Dim dataRetrieval As New RptSvc.DataRetrievalPlan
> > > dataRetrieval.DataSet = results
> > >
> > > ' Set the event type and match data for the delivery.
> > > Dim eventType As String = "TimedSubscription"
> > > Dim matchData As String = "<ScheduleDefinition>" & _
> > > "<StartDateTime>2007-07-22T17:00:00-18:00</StartDateTime>" &
> _
> > > "<WeeklyRecurrence><WeeksInterval>1</WeeksInterval>" & _
> > > "<DaysOfWeek>" & _
> > > "<Thursday>True</Thursday>" & _
> > > "</DaysOfWeek></WeeklyRecurrence>" & _
> > > "</ScheduleDefinition>"
> > >
> > > ' Set the report parameter values.
> > > Dim parameters(3) As RptSvc.ParameterValueOrFieldReference
> > >
> > > Dim empID As New RptSvc.ParameterFieldReference ' Data-driven.
> > > empID.ParameterName = "EmplID"
> > > empID.FieldAlias = "EmplID"
> > > parameters(0) = empID
> > >
> > > Dim VVD As New RptSvc.ParameterValue
> > > VVD.Name = "VVD"
> > > VVD.Value = "CB1345"
> > > parameters(1) = VVD
> > >
> > > Dim OPR As New RptSvc.ParameterValue
> > > OPR.Name = "OPR"
> > > OPR.Value = "*"
> > > parameters(2) = OPR
> > >
> > > Dim BillOpr As New RptSvc.ParameterValue
> > > BillOpr.Name = "BILL_COMP"
> > > BillOpr.Value = "*"
> > > parameters(3) = BillOpr
> > >
> > > Try
> > > Dim subscriptionID As String => rs.CreateDataDrivenSubscription( _
> > > reportPath, settings, dataRetrieval, sEmaiDesc,
> eventType, matchData, parameters)
> > > Catch e As SoapException
> > > Console.WriteLine(e.Detail.InnerText.ToString())
> > > End Try
> > >
> > > Actually, the code is simliar as the example
> > >
> > > Thanks a lot !!!
> > >
> > > "Daniel Reib [MSFT]" wrote:
> > >
> > > > Can you post more of your code? From the looks of it perhaps you are
> not
> > > > setting the Item property on your DataRetrievalPlan to the
> > > > DataSourceDefinition that you defined below.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > >
> > > >
> > > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > > news:3A9D364D-C5C4-4204-B6E8-D88570C437E7@.microsoft.com...
> > > > > I try to use .NET to subscribe report which email address is
> retrived from
> > > > Access databse. I follow the online example of
> CreateDataDrivenSubscription
> > > > and the main difference is the mail list come from Access DB.
> > > > >
> > > > > The code is as follow:
> > > > > Dim dataSrcDefinition As New RptSvc.DataSourceDefinition
> > > > > dataSrcDefinition.ConnectString => "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=D:\Temp\RptSvc.mdb;Persist Security Info=False"
> > > > >
> > > > > But when I run the report, the following error is shown:
> > > > > rsMissingElement
> > > > > 400
> > > > > The required field DataSourceDefinitionOrReference is
> missing from
> > > > the input structure.
> > > > >
> > > > > What's wrong with my code ?
> > > >
> > > >
> > > >
>
>

Create view "disconnected"

I occasionally need to create or alter a view that references a linked
server, where the linked server is not currently online (or my Internet
connection is not currently connected, or whatever).
An "Alter View" statement that references a linked server name will fail
with "Could not find server", "Login Failed" or other errors, if the server
is not accessible at the moment.
I sometimes don't care about this problem at the time that I *define* the
view; when I *run* the view, the server will be there, and things will
work.
I can't find how to get around this, which seems like a limitation of the
"Create View" and "Alter View" commands.
Thanks for any suggestions.
David Walker
are you runng sql 2000 or 2005?
|||David,
Although it is always good to mention your version of SQL Server, I don't
think this behavior has changed between 2000 and 2005. What you are looking
for is 'deferred name resolution' which is only available in certain
circumstances for stored procedures.
http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For 2000
FWIW, creating a stored procedure that refers to a table on an unavailable
linked server also fails.
RLF
"DWalker" <none@.none.com> wrote in message
news:%23ZPNF7g1HHA.4584@.TK2MSFTNGP03.phx.gbl...
>I occasionally need to create or alter a view that references a linked
> server, where the linked server is not currently online (or my Internet
> connection is not currently connected, or whatever).
> An "Alter View" statement that references a linked server name will fail
> with "Could not find server", "Login Failed" or other errors, if the
> server
> is not accessible at the moment.
> I sometimes don't care about this problem at the time that I *define* the
> view; when I *run* the view, the server will be there, and things will
> work.
> I can't find how to get around this, which seems like a limitation of the
> "Create View" and "Alter View" commands.
> Thanks for any suggestions.
> David Walker
|||Hi David,
I think that the problem is when a view is being created or modified, SQL
Server will check the objects names, relations etc. Assuming that the
linked server is offline, if we type an error column name for a remote
database table and if offline linked server is allowed in this situation,
how can SQL Server check if the view is valid?
Also, as Russell mentioned that deffered name resolution (DNR) is only
available certain circumstances (table objects) for stored procedures. Even
if SQL Server allows DNR, it still need to store the information in SQL
Server. In this case, if the linked server is offline, the operation will
fail.
Hope that these two points could explain the limitation. If you have any
other questions or concerns, please feel free to post back.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:#5CRGkq1HHA.3916@.TK2MSFTNGP02.phx.gbl:

> David,
> Although it is always good to mention your version of SQL Server, I
> don't think this behavior has changed between 2000 and 2005. What you
> are looking for is 'deferred name resolution' which is only available
> in certain circumstances for stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
> http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For
> 2000
> FWIW, creating a stored procedure that refers to a table on an
> unavailable linked server also fails.
> RLF
>
Thanks for the references. There are cases where I would like to have SQL
defer the name resolution of all objects until I actually run the query.
Apparently, I can't do that. Oh well.
Thanks.
David Walker
|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:Plq66h91HHA.360@.TK2MSFTNGHUB02.phx.gbl:

> Hi David,
> I think that the problem is when a view is being created or modified,
> SQL Server will check the objects names, relations etc. Assuming that
> the linked server is offline, if we type an error column name for a
> remote database table and if offline linked server is allowed in this
> situation, how can SQL Server check if the view is valid?
In a few rare cases, I want to tell SQL server NOT to check if the view is
valid until it is run. (For example, if I am working on views or stored
procedures while not connected to the remote server that has the data that
the views or procs refer to.) Apparently I can't do that.
I meant to say earlier that this applies to both SQL 2000 and SQL 2005,
since someone asked me that earlier.
It does explain the limitation, and what I'm asking for is probably an
enhancement. I'll post a suggestion somewhere.
Thanks.
David Walker
|||Ken <kshapley@.sbcglobal.net> wrote in news:1186174524.738575.238770
@.r34g2000hsd.googlegroups.com:

> are you runng sql 2000 or 2005?
>
I am running both.
David
|||Hi David,
Since this is a product limitaion, I recommend that you give Microsoft
feedback via https://connect.microsoft.com/sql, your feedback will be
routed to Microsoft product team and hope that this feature will be
included in the next release of SQL Server.
Also, I got an idea of using a stored procedure with dynamic T-SQL
statements to perform the query, since dynamic T-SQL can avoid object and
data type check. For example:
CREATE PROCEDURE proc_getRemoteTable()
AS
BEGIN
DECLARE @.strSQL nvarchar(500)
SET @.strSQL = N'SELECT * FROM linked_server_name.dbo.remote_table_name'
EXECUTE sp_executeSQL @.strSQL
END
Could you please let us know whether or not this workaround is acceptable
for your scenario?
We appreciate your understanding that for such product limitaion issues, we
will try our best to work with you for getting a workaround, however
sometimes it may not be able to meet your real expectations. In this case,
your feedback will be greatly appreciated and we really value having you as
a Microsoft customer.
If you have any other questions or concerns, please feel free to let us
know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Create view "disconnected"

I occasionally need to create or alter a view that references a linked
server, where the linked server is not currently online (or my Internet
connection is not currently connected, or whatever).
An "Alter View" statement that references a linked server name will fail
with "Could not find server", "Login Failed" or other errors, if the server
is not accessible at the moment.
I sometimes don't care about this problem at the time that I *define* the
view; when I *run* the view, the server will be there, and things will
work.
I can't find how to get around this, which seems like a limitation of the
"Create View" and "Alter View" commands.
Thanks for any suggestions.
David Walkerare you runng sql 2000 or 2005?|||David,
Although it is always good to mention your version of SQL Server, I don't
think this behavior has changed between 2000 and 2005. What you are looking
for is 'deferred name resolution' which is only available in certain
circumstances for stored procedures.
http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For 2000
FWIW, creating a stored procedure that refers to a table on an unavailable
linked server also fails.
RLF
"DWalker" <none@.none.com> wrote in message
news:%23ZPNF7g1HHA.4584@.TK2MSFTNGP03.phx.gbl...
>I occasionally need to create or alter a view that references a linked
> server, where the linked server is not currently online (or my Internet
> connection is not currently connected, or whatever).
> An "Alter View" statement that references a linked server name will fail
> with "Could not find server", "Login Failed" or other errors, if the
> server
> is not accessible at the moment.
> I sometimes don't care about this problem at the time that I *define* the
> view; when I *run* the view, the server will be there, and things will
> work.
> I can't find how to get around this, which seems like a limitation of the
> "Create View" and "Alter View" commands.
> Thanks for any suggestions.
> David Walker|||Hi David,
I think that the problem is when a view is being created or modified, SQL
Server will check the objects names, relations etc. Assuming that the
linked server is offline, if we type an error column name for a remote
database table and if offline linked server is allowed in this situation,
how can SQL Server check if the view is valid?
Also, as Russell mentioned that deffered name resolution (DNR) is only
available certain circumstances (table objects) for stored procedures. Even
if SQL Server allows DNR, it still need to store the information in SQL
Server. In this case, if the linked server is offline, the operation will
fail.
Hope that these two points could explain the limitation. If you have any
other questions or concerns, please feel free to post back.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi David,
I am interested in this issue. Could you please let us know if you need
further assistance on this issue?
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:#5CRGkq1HHA.3916@.TK2MSFTNGP02.phx.gbl:

> David,
> Although it is always good to mention your version of SQL Server, I
> don't think this behavior has changed between 2000 and 2005. What you
> are looking for is 'deferred name resolution' which is only available
> in certain circumstances for stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
> http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For
> 2000
> FWIW, creating a stored procedure that refers to a table on an
> unavailable linked server also fails.
> RLF
>
Thanks for the references. There are cases where I would like to have SQL
defer the name resolution of all objects until I actually run the query.
Apparently, I can't do that. Oh well.
Thanks.
David Walker|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:Plq66h91HHA.360@.TK2MSFTNGHUB02.phx.gbl:

> Hi David,
> I think that the problem is when a view is being created or modified,
> SQL Server will check the objects names, relations etc. Assuming that
> the linked server is offline, if we type an error column name for a
> remote database table and if offline linked server is allowed in this
> situation, how can SQL Server check if the view is valid?
In a few rare cases, I want to tell SQL server NOT to check if the view is
valid until it is run. (For example, if I am working on views or stored
procedures while not connected to the remote server that has the data that
the views or procs refer to.) Apparently I can't do that.
I meant to say earlier that this applies to both SQL 2000 and SQL 2005,
since someone asked me that earlier.
It does explain the limitation, and what I'm asking for is probably an
enhancement. I'll post a suggestion somewhere.
Thanks.
David Walker|||Ken <kshapley@.sbcglobal.net> wrote in news:1186174524.738575.238770
@.r34g2000hsd.googlegroups.com:

> are you runng sql 2000 or 2005?
>
I am running both.
David|||Hi David,
Since this is a product limitaion, I recommend that you give Microsoft
feedback via https://connect.microsoft.com/sql, your feedback will be
routed to Microsoft product team and hope that this feature will be
included in the next release of SQL Server.
Also, I got an idea of using a stored procedure with dynamic T-SQL
statements to perform the query, since dynamic T-SQL can avoid object and
data type check. For example:
CREATE PROCEDURE proc_getRemoteTable()
AS
BEGIN
DECLARE @.strSQL nvarchar(500)
SET @.strSQL = N'SELECT * FROM linked_server_name.dbo.remote_table_name'
EXECUTE sp_executeSQL @.strSQL
END
Could you please let us know whether or not this workaround is acceptable
for your scenario?
We appreciate your understanding that for such product limitaion issues, we
will try our best to work with you for getting a workaround, however
sometimes it may not be able to meet your real expectations. In this case,
your feedback will be greatly appreciated and we really value having you as
a Microsoft customer.
If you have any other questions or concerns, please feel free to let us
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi David,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Create view "disconnected"

I occasionally need to create or alter a view that references a linked
server, where the linked server is not currently online (or my Internet
connection is not currently connected, or whatever).
An "Alter View" statement that references a linked server name will fail
with "Could not find server", "Login Failed" or other errors, if the server
is not accessible at the moment.
I sometimes don't care about this problem at the time that I *define* the
view; when I *run* the view, the server will be there, and things will
work.
I can't find how to get around this, which seems like a limitation of the
"Create View" and "Alter View" commands.
Thanks for any suggestions.
David Walkerare you runng sql 2000 or 2005?|||David,
Although it is always good to mention your version of SQL Server, I don't
think this behavior has changed between 2000 and 2005. What you are looking
for is 'deferred name resolution' which is only available in certain
circumstances for stored procedures.
http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For 2000
FWIW, creating a stored procedure that refers to a table on an unavailable
linked server also fails.
RLF
"DWalker" <none@.none.com> wrote in message
news:%23ZPNF7g1HHA.4584@.TK2MSFTNGP03.phx.gbl...
>I occasionally need to create or alter a view that references a linked
> server, where the linked server is not currently online (or my Internet
> connection is not currently connected, or whatever).
> An "Alter View" statement that references a linked server name will fail
> with "Could not find server", "Login Failed" or other errors, if the
> server
> is not accessible at the moment.
> I sometimes don't care about this problem at the time that I *define* the
> view; when I *run* the view, the server will be there, and things will
> work.
> I can't find how to get around this, which seems like a limitation of the
> "Create View" and "Alter View" commands.
> Thanks for any suggestions.
> David Walker|||Hi David,
I think that the problem is when a view is being created or modified, SQL
Server will check the objects names, relations etc. Assuming that the
linked server is offline, if we type an error column name for a remote
database table and if offline linked server is allowed in this situation,
how can SQL Server check if the view is valid?
Also, as Russell mentioned that deffered name resolution (DNR) is only
available certain circumstances (table objects) for stored procedures. Even
if SQL Server allows DNR, it still need to store the information in SQL
Server. In this case, if the linked server is offline, the operation will
fail.
Hope that these two points could explain the limitation. If you have any
other questions or concerns, please feel free to post back.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi David,
I am interested in this issue. Could you please let us know if you need
further assistance on this issue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"Russell Fields" <russellfields@.nomail.com> wrote in
news:#5CRGkq1HHA.3916@.TK2MSFTNGP02.phx.gbl:
> David,
> Although it is always good to mention your version of SQL Server, I
> don't think this behavior has changed between 2000 and 2005. What you
> are looking for is 'deferred name resolution' which is only available
> in certain circumstances for stored procedures.
> http://msdn2.microsoft.com/en-us/library/ms190686.aspx For 2005
> http://msdn2.microsoft.com/en-us/library/Aa214346(SQL.80).aspx For
> 2000
> FWIW, creating a stored procedure that refers to a table on an
> unavailable linked server also fails.
> RLF
>
Thanks for the references. There are cases where I would like to have SQL
defer the name resolution of all objects until I actually run the query.
Apparently, I can't do that. Oh well.
Thanks.
David Walker|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:Plq66h91HHA.360@.TK2MSFTNGHUB02.phx.gbl:
> Hi David,
> I think that the problem is when a view is being created or modified,
> SQL Server will check the objects names, relations etc. Assuming that
> the linked server is offline, if we type an error column name for a
> remote database table and if offline linked server is allowed in this
> situation, how can SQL Server check if the view is valid?
In a few rare cases, I want to tell SQL server NOT to check if the view is
valid until it is run. (For example, if I am working on views or stored
procedures while not connected to the remote server that has the data that
the views or procs refer to.) Apparently I can't do that.
I meant to say earlier that this applies to both SQL 2000 and SQL 2005,
since someone asked me that earlier.
It does explain the limitation, and what I'm asking for is probably an
enhancement. I'll post a suggestion somewhere.
Thanks.
David Walker|||Ken <kshapley@.sbcglobal.net> wrote in news:1186174524.738575.238770
@.r34g2000hsd.googlegroups.com:
> are you runng sql 2000 or 2005?
>
I am running both.
David|||Hi David,
Since this is a product limitaion, I recommend that you give Microsoft
feedback via https://connect.microsoft.com/sql, your feedback will be
routed to Microsoft product team and hope that this feature will be
included in the next release of SQL Server.
Also, I got an idea of using a stored procedure with dynamic T-SQL
statements to perform the query, since dynamic T-SQL can avoid object and
data type check. For example:
CREATE PROCEDURE proc_getRemoteTable()
AS
BEGIN
DECLARE @.strSQL nvarchar(500)
SET @.strSQL = N'SELECT * FROM linked_server_name.dbo.remote_table_name'
EXECUTE sp_executeSQL @.strSQL
END
Could you please let us know whether or not this workaround is acceptable
for your scenario?
We appreciate your understanding that for such product limitaion issues, we
will try our best to work with you for getting a workaround, however
sometimes it may not be able to meet your real expectations. In this case,
your feedback will be greatly appreciated and we really value having you as
a Microsoft customer.
If you have any other questions or concerns, please feel free to let us
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi David,
Just check with you to see if you need further assistance on this issue.
Please feel free to let us know if you have any other questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================