creating an existing db schema baseline

What is the best method of creating schema creation scripts that can be
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.

mike

--
Posted via http://dbforums.comwukie <member30544@.dbforums.com> wrote in message news:<3242331.1060980041@.dbforums.com>...
> What is the best method of creating schema creation scripts that can be
> stored into a version control system. The process of using em to
> generate a script is not an appealing option. I am still learning the
> MS Sql sys tables and have not found a useful list of all the codes &
> types to join the tables etc.
>
> mike

I don't like the fact that all source code versioning systems are
using proprietary files instead of proven relational databases
(SourceSafe is not exception from this). The reasons for this are
probably RDBMS licensing costs in the past.

Database schema can be exported also as XML file, which can be further
manipulated. If you and your team have serious schema versioning needs
I suggest you to evaluate Meta Data Services in SQL Server 2000 and
XML. One article about this has been published in the MSDN Magazine:
http://msdn.microsoft.com/msdnmag/i...es/default.aspx

Metadata Repository can be created not only through Enterprise Manager
but also programmatically using Meta Data API. Further information
with examples can be found in Meta Data Services SDK 3.0, which can be
downloaded for free.

Sinisa Catic|||found what I was looking for...

in EM > Tools > Generate SQL Scripts. THis will create the total schema
of the existing database.

mike

any known issues with this tool??

--
Posted via http://dbforums.com

CreateSubscription parameter array problem

I'm using the CreateSubscription method and having a problem with the
parameter values getting rejected. I am wondering if I am experiencing a
type conflict because my report parameters are all integers but from the
samples it looks like I may need to define them as strings. Is there a
limitation on using this web service method that your report parameters must
all be strings?
The error I am getting is not that helpful:
The value of parameter 'Parameters' is not valid. Check the documentation
for information about valid values. --> The value of parameter 'Parameters'
is not valid. Check the documentation for information about valid values.
I am building the parameter array like this:
Dim parameters(4) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "PriorMonthEndAsOfDateDimensionId"
parameters(0).Value = "353"
parameters(1) = New ParameterValue()
parameters(1).Name = "MonthEndAsOfDateDimensionId"
parameters(1).Value = "419"
parameters(2) = New ParameterValue()
parameters(2).Name = "StrategyDimensionId"
parameters(2).Value = "98"
parameters(3) = New ParameterValue()
parameters(3).Name = "FundDimensionId"
parameters(3).Value = "847"
I get the same error if I assign the values without the quotes.
Thanks,
SimonI'm an idiot.
I was defining the array incorrectly. When I change it to: Dim
parameters(3) As ParameterValue
it works.
Sorry.
"Simon Schmidt" wrote:
> I'm using the CreateSubscription method and having a problem with the
> parameter values getting rejected. I am wondering if I am experiencing a
> type conflict because my report parameters are all integers but from the
> samples it looks like I may need to define them as strings. Is there a
> limitation on using this web service method that your report parameters must
> all be strings?
> The error I am getting is not that helpful:
> The value of parameter 'Parameters' is not valid. Check the documentation
> for information about valid values. --> The value of parameter 'Parameters'
> is not valid. Check the documentation for information about valid values.
> I am building the parameter array like this:
> Dim parameters(4) As ParameterValue
> parameters(0) = New ParameterValue()
> parameters(0).Name = "PriorMonthEndAsOfDateDimensionId"
> parameters(0).Value = "353"
> parameters(1) = New ParameterValue()
> parameters(1).Name = "MonthEndAsOfDateDimensionId"
> parameters(1).Value = "419"
> parameters(2) = New ParameterValue()
> parameters(2).Name = "StrategyDimensionId"
> parameters(2).Value = "98"
> parameters(3) = New ParameterValue()
> parameters(3).Name = "FundDimensionId"
> parameters(3).Value = "847"
> I get the same error if I assign the values without the quotes.
> Thanks,
> Simon

CreateSubscription - Specify the Job Name or Get the GUID Job Name

We're using the CreateSubscription method to schedule a report for automatic delivery, using SQL Server Agent. Everything is working great. But, we had a question (or two).

Is there anyway to specify the name of the SQL Server Agent job that gets created when the CreateSubscription method is called? If so, how?
If not, is there anyway to get the GUID job name back after calling CreateSubscription?

TIA

There is no way to set the name of the SQL Agent job via the CreateSubscription method.

You can use the ListSubscriptions to get the guid and GetSubscriptionProperties to get further information.

ReportingService2005 rs = new ReportingService2005();
Subscription[] subscription = rs.ListSubscriptions(ReportAndPath, UserName);

rs.GetSubscriptionProperties(

subscription[0].SubscriptionID,

out actualExtensionSettings,

out actualDescription,

out actualActive,

out actualStatus,

out actualEventType,

out actualMatchData,

out actualParameters);

|||

Brad,

Thanks for the info. However, does using the ListSubscriptions and GetSubscriptionProperties give me the GUID name of the SQL Server Agent job?

Since there is no way to set the SQL Server Agent job to something more meaningful to end-users, the next best option for us is to provide the GUID name of the SQL Server Agent job to the end-user after is has been created. Thus, our ASP.NET app. will display to the end-user something like, "Your job has been created. The job name is xxxx." (where xxxx is the GUID job name as shown in the SQL Server list of jobs). In our situation, our end-users are 'knowledgeable' enough about our product to open SQL Server, navigate to SQL Server Agent, and find the job they just tried to create. So, we need to be able to give them some help with which job name is theirs.

Thanks.

|||

No. The guid for the SQL Agent job is not exposed through the SOAP Api's.

Why not just give them the subscription information? Instead of trying to tell them the SQL Agent Job, tell them the subscription name. "Your subscription has been created. The subscription is on "ReportX" for user "Foo" and is scheduled to send at "Time Selected". In Management Studio or Report Manager, more information is stored for the Subcription in RS then for the Job in the Agent. They can get parameter values, security info, and other information.

Just a thought.

CreateReports and Delphi 7

Hello,
I have question regarding the method CreateReports. I develope in Delphi 7
and did the following to call the method (other methods before, like
CreateDatasource work successfully):
procedure TRepServWrapper.PublishRDLFile (const rdlname, rdlfile, parentPath
: String);
var
fs : TFileStream;
byteArray : TByteDynArray;
warnings : ArrayOfWarning;
i : Integer;
begin
fs := TFileStream.Create(rdlfile,fmOpenRead);
SetLength(byteArray,fs.Size + 1);
Try
fs.ReadBuffer(byteArray,fs.Size);
except on e:Exception do
raise ECouldNotOpenRDLFile.Create('TRepServWrapper.PublishRDLFile: ' +
errCouldNotOpenRDLFile + ': '+e.Message);
end;
fs.Free;
Try
warnings := FRSInt.CreateReport(rdlname,parentPath,True,byteArray,nil);
If Not (warnings = nil) Then
begin
for i := 0 to High(warnings)-1 do
SendDebug('Warnung beim Upload der RDL: '+ warnings[i].Message);
end
else
SendDebug('RDL erfolgreich geuploadet!');
except on e : Exception do
raise ECouldNotUploadRDL.Create('TRepServWrapper.PublishRDLFile: ' +
errCouldNotUploadRDL + ': ' + e.Message);
end;
end;
When I compile and run the application nothing happens after the line
warnings := FRSInt.CreateReport(...)..no error message, just nothing!
What could be the reason? Do I have to format the byte array in a different
way? I have seen that it has to be Base64 encoded...is this done
automatically? I took an Visual Basic example and adapted it to Delphi.Me again,
I have solved it: I created the byte array in a wrong way. It would be
correct in the following way:
fs := TFileStream.Create(rdlfile,fmOpenRead);
Try
Try
if fs.Size > 0 Then
begin
SetLength(byteArray, fs.Size);
fs.ReadBuffer(byteArray[0],fs.Size);
end;
except on e:Exception do
raise ECouldNotOpenRDLFile.Create('TRepServWrapper.PublishRDLFile: '
+ errCouldNotOpenRDLFile + ': '+e.Message);
end;
finally
fs.Free;
end;

CreateReport() method under ASP.NET

CreateReport throws an error saying that user IUSR_XXXXX doesn't have
permisssions to run this method.
So,
1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
user
2) Assigned ReportserverSysadmin role for the new group.
3) Also set db_owner permissions for the IUSR_XXXXX user in
ReportService
related databases on
my local instance of MS SQL.
No results. Still can't call method CreateReport().
Any ideas ?
Thanks
Paul
PS. BTW this method works fine from *.rss script that is running under
local Admin account.There can be a few reasons that most likely cause this:
1. You have anonymous authentication enabled for "Reports" and
"ReportSerever" virtual folders. Go to IIS manager and make sure anonymous
access is disabled.
2. You are calling this method from within an ASP.NET application that
doesn't impersonate its user. It calls CreateReport using its own identity.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"pTsy" <ptsygura@.bna.com> wrote in message
news:#pDOs2veEHA.2916@.TK2MSFTNGP12.phx.gbl...
> CreateReport throws an error saying that user IUSR_XXXXX doesn't have
> permisssions to run this method.
> So,
> 1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
> user
> 2) Assigned ReportserverSysadmin role for the new group.
> 3) Also set db_owner permissions for the IUSR_XXXXX user in
> ReportService
> related databases on
> my local instance of MS SQL.
> No results. Still can't call method CreateReport().
> Any ideas ?
> Thanks
> Paul
> PS. BTW this method works fine from *.rss script that is running under
> local Admin account.
>

CreateReport method delivers empty response

Hello,
I use the web service for the reporting services with Delphi 7.
I call the CreateReport method and it works fine (the RDL is uploaded to the
server), but the SOAP response raises in exception because the answer is
empty:
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Header>
<ServerInfoHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices">
<ReportServerVersionNumber>Microsoft SQL Server Reporting Services, Version
8.00.1038.00</ReportServerVersionNumber>
<ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader>
</soap:Header>
<soap:Body>
<CreateReportResponse
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices" />
</soap:Body></soap:Envelope>'
What can I do to circumvent this? I mean, CreateReport returns an array of
warnings if there are some. But when there are no warnings this error occurs.
What can I do?Hello,
I solved the problem. I wrote a function which edits the SOAP-Packet after
receiving it in Delphi.
I killed the empty response node and now it is working fine, because the
returned value is nil.
"Sandra Geisler" <SandraGeisler@.discussions.microsoft.com> schrieb im
Newsbeitrag news:2BBFAC0E-4DA3-4A3B-9E68-9A73D76D47E5@.microsoft.com...
> Hello,
> I use the web service for the reporting services with Delphi 7.
> I call the CreateReport method and it works fine (the RDL is uploaded to
> the
> server), but the SOAP response raises in exception because the answer is
> empty:
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="">http://www.w3.org/2001/XMLSchema">
> <soap:Header>
> <ServerInfoHeader
> xmlns="">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices">
> <ReportServerVersionNumber>Microsoft SQL Server Reporting Services,
> Version
> 8.00.1038.00</ReportServerVersionNumber>
> <ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader>
> </soap:Header>
> <soap:Body>
> <CreateReportResponse
> xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"
> />
> </soap:Body></soap:Envelope>'
> What can I do to circumvent this? I mean, CreateReport returns an array of
> warnings if there are some. But when there are no warnings this error
> occurs.
> What can I do?
>

CreateMHTMLBody retuns "Invalid syntax" error

Hi,

I want to add html as body of my mail message. I added CreateMHTMLBody method in my SQL code.

Syntax I used is

Exec @.hr=sp_oamethod @.imsg,'CreateMHTMLBody',NULL,@.MsgFile,0,'',''

This syntax is working fine on my system but whem I implement this on production it gives error.

Needed ASAP as I have to implmenet this on my production server today.

Thanks

GAurav

What is the error message you are getting|||are you running the same version of cdosys.dll on dev and prod?|||

Why use CDO when you have sql database mail.

CreateMHTMLBody expects 4 parameters (url, cdomhtmlflags, user, pwd) so check your @.MsgFile.

|||Check the required COM DLLs are registered properly on your server.|||

Using SQL database mail you can't send the HTML mails where message body is created from the HTML file

And my syntax is correct as it is working perfectly fine on my development env. but it is not working on production

|||

Yes COM dll is properly registerde and it is sending Textbody mail. But when I use this CreateMHTMLBoby it gives me above error. And my code and syntax is also correct as it is working fine on development env.

Thanks