Invoking SSIS Packages from DotNet

Hi,

How can we invoke SSIS Packages from an Windows or ASP.NET Application (using C# ).

Thanks

Jegan

First you have a look at the documentation -

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.htm

or

Managing Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.aspx)

Then you ask some specific questions if you get stuck.

|||

Darren ,

Thanks for the links. now i am able to create sample application.but when i am using Fuzzylookup and FlatFileDestination in my package and i am executing the package from a windows application (C#).i am getting the following errors

1.The product level is insufficient for component Fuzzy Lookup
2.The product level is insufficient for component Conditional Split
3.The product level is insufficient for component Flat File

what could be the reason .

Thanks

Jegan

|||

Fuzzy Lookup requires installation of SSIS from Enterprise Edition of SQL Server, for other components SSIS installation from Standard Edition is OK. You've probably only installed the Client/Workstation Componentss, not SSIS - so you can't run the packages.

For more details see http://msdn2.microsoft.com/en-us/library/aa337371.aspx

|||

Michael,

my machine already have SSIS installed and i am using VS 2005 Team Edition for Software Developers.so what could be the problem

Thanks

Jegan

|||Check if you have a service called MsDtsServer (display name SQL Server Integration Services).

If you do not have such service, you don't have SSIS installed. Installing just Workstation Components gives you access to some SSIS aspects, e.g. designing the packages in BIDS. But it is not enough to execute the packages outside of designer.

Invoking SSIS Packages from DotNet

Hi,

How can we invoke SSIS Packages from an Windows or ASP.NET Application (using C# ).

Thanks

Jegan

First you have a look at the documentation -

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.htm

or

Managing Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/1a08c75e-ce8c-45ee-81bd-32248bbdb2b2.aspx)

Then you ask some specific questions if you get stuck.

|||

Darren ,

Thanks for the links. now i am able to create sample application.but when i am using Fuzzylookup and FlatFileDestination in my package and i am executing the package from a windows application (C#).i am getting the following errors

1.The product level is insufficient for component Fuzzy Lookup
2.The product level is insufficient for component Conditional Split
3.The product level is insufficient for component Flat File

what could be the reason .

Thanks

Jegan

|||

Fuzzy Lookup requires installation of SSIS from Enterprise Edition of SQL Server, for other components SSIS installation from Standard Edition is OK. You've probably only installed the Client/Workstation Componentss, not SSIS - so you can't run the packages.

For more details see http://msdn2.microsoft.com/en-us/library/aa337371.aspx

|||

Michael,

my machine already have SSIS installed and i am using VS 2005 Team Edition for Software Developers.so what could be the problem

Thanks

Jegan

|||Check if you have a service called MsDtsServer (display name SQL Server Integration Services).

If you do not have such service, you don't have SSIS installed. Installing just Workstation Components gives you access to some SSIS aspects, e.g. designing the packages in BIDS. But it is not enough to execute the packages outside of designer.sql

Invoking SQL Server JOB created for a report schedule

Hi,
Instead of runing a report through the schedule that I have created, I am invoking the SQL Agent Job that has been created for the schedule of the report, using the system stored procedure sp_startjob. Is this a recommended approach? Are there drawbacks for this approach?
Subash

This is a perfectly reasonable solution. If you are just trying to fire a subscription you could also use the FireEvent method to invoke the subscription.

The only drawback is that this is not a supported scenario, which means the job could change in a SP or new release, however this seems unlikely.

Invoking ms access from a scheduled job in sql server 2000

Is it possible to run a mdb from a scheduled job in sql server? I simply want to run a mdb with a autoexec in it. Don't want to use microsoft scheduler....

Thanks!

To 'run a mdb...with a autoexec in it' requires a client application.

SQL Server is NOT a client application.

Invoking MigrationWizardConsole

I have a couple of questions concerning invoking the application MigrationWizardConsole.exe. Can this application be invoked directly or is invoked from the GUI app, MigrationWizard.exe? My other question is if the MigrationWizardConsole can be invoked directly what are the parameters?

Thanks

Ron Cotton

MigrationWizardConsole is used only by setup during upgrades. It should not be invoked by users.|||

Sasha,

Thanks for the information...Do you have any suggestions concerning automating the migration of SQL 2000 databases to SQL 2005?

Thanks

Ron C

Invoking an assembly before a report is rendered

Hi All,
I want to implement a sort of licensing policy for the reports
developed by me. For this I have created my own custom assembly. I
want to invoke the assembly just before the report is rendered so that
I can check if the license is valid and then allow the report to be
rendered, else a default page.
Thanxs in anticipation.You might want to check out this section in BOL (OnInit):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_7gma.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"CoolGuyFromPune" <raviful@.hotmail.com> wrote in message
news:eed4d072.0407050640.7cc929cd@.posting.google.com...
> Hi All,
> I want to implement a sort of licensing policy for the reports
> developed by me. For this I have created my own custom assembly. I
> want to invoke the assembly just before the report is rendered so that
> I can check if the license is valid and then allow the report to be
> rendered, else a default page.
> Thanxs in anticipation.sql

Invoking a SQL Server user-defined function via SQL passed to Informix

==========
Background
==========
My colleague and I are using MS Reporting Services to create reports based upon data extracted from an Informix database. Inseveral reports, we have the need to parse the contents of a variable length field in order to extract a variable lengthforeign key value.

Note that the key value is of variable length and embedded within a string that's also of variable length.

==========
Question
==========
Given the fact that we aren't allowed to add anything within the back-end Informix environment, we wrote a SQL Server userdefined function that can extract a variable length "key" value from a variable length string. My question is as follows:

Is it even possible to invoke a SQL Server user defined function in our SQL statement (passed to Informix) within the context of Reporting Services?

==========
Environment Notes
==========
- We are NOT allowed to add anything within the back-end Informix db environment.

- We are connecting via an Informix ODBC driver with a linked Informix server defined in our SQL Server environment.

- We've tried defining the user-defined function within the "master", "ReportServer" and "ReportServerTempDB" databases with the appropriate permissions set.

==========
Error Messages
==========
We've received the following error message after trying to invoke the SQL Server user defined function:

SELECT field1, field2, field3, dbo.udf_GetStringElement(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Identifier length exceeds the maximum allowed by this version of the server.

We receive the following error message after creating a new SQL Server user defined function using a shorter name:

SELECT field1, field2, field3, dbo.gse(field3,'/',1,2) AS extrctd

[Informix][Informix ODBC Driver][Informix]Routine (dbo.gse) can not be resolved.

Any feedback would be appreciated,

ndm

have you tried using a custom code function for it ?|||

Hey, thanks for the reply.

I'm not sure I understand what you mean by "custom code function". The user defined functions I mentioned, "udf_GetStringElement", "gse", are user defined functions that I wrote. It's not one of the "system" functions (e.g. "fn_isreplmergeagent") found in master.

I merely created the function WITHIN the context of the "master" db and eventually within "Report Server" and "ReportServerTempDB" in an attempt to invoke the function within a Report Services SQL statement.

|||By Custom Code function I mean the custom code vb.net functions you canwrite under Report -> Properties -> Code tab. You canreplicate the logic of your user defined function you have in sql intovb.net.
what xactly foes your user defined function do ?
|||

Ah, I understand what you're saying about simply replicating/writing the string parsing/extraction logic within the Report Services environment, but our issue isn't really a (post-data aggregation) formatting problem.

Our SQL Server user defined function parses a variable length string in order to extract a variable length "foreign key" value that we'll need to use for joining data from several Informix tables.

Unless there's another approach of which I'm unaware, I'm assuming that we need to parse this string DURING the SQL execution in order to perform the requisite join(s).

Appreciate the feedback,

ndm

|||I didnt understand the situation completely..you have the SELECT stmt from Informix db but the UDF is in SQL Server right ? does the udf do anything other than string parsing ? looks like its a little complicated and we could be hitting the limitations ( one of the many) of RS.|||

Correct; the SELECT statement (defined within shared Report Services dataset) is passed to Informix and our custom UDF is in SQL Server.

More specifically, I've tried placing our custom SQL Server UDF within several of the SQL Server databases ("master", "ReportServer" and "ReportServerTempDB"). When I attempt to invoke the UDF within the SQL statement passed to Informix, I receive the aforementioned error messages.

The key point is that we need to be able to parse DURING the SQL execution in order to perform some requisite join(s).

Within a pure SQL Server environment, I can access the UDF from master (or, for that matter, any other SQL Server db so long as the appropriate permissions are set). I'm not even sure if what we're trying to accomplish is possible since the SQL statement is being passed to Informix. I was wondering if there was some "Reporting Services" method for invoking our custom UDF defined WITHIN the SQL Server realm.

Again, I appreciate the reply.

ndm

|||

The SQL statement you have is executed on Informix DB engine and it wouldnt understand the SQL UDF..unless you make a specific connection to SQL for which it may not be possible in 1 SELECT stmt..you prbly might need to use a set of stmts..If the UDF is only doing some string parsing I would recommend moving it into RS custom code and rewriting it in vb.net. Cant think of anything else..

|||

I would create a DTS package that is scheduled to get the data from Informix and populate the Reporting Service database. Try the urls below for more info. Hope this helps.

http://www.sqldts.com

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/infmxsql.mspx#EDAA

Kind regards,

Gift Peddie

|||

Yes, I'm thinking that we may have to take a less directextract+dump+cleanse route (Informix -> MS SQL Server -> MS Reporting Services) for this scenario. We were trying to avoid taking snapshots of the data and having to maintain a secondary (albeit temporary) data store.

Your recommendation merits further consideration.

Thanks for the feedback,

ndm

Invoking a service on firing a trigger

Hi

Im trying to invoke a .Net service when a trigger is fired on a table in SQL Server 2000.

The service is working fine but I need to invoke it when the trigger is fired. How can I achieve this?

Hi!

At first, why do you think your problem is concern with SQL-NS? The second: does you question relate to SQL Server 2005? You have wrote, that the trigger on SQL Server 2000 invokes the service and the services works well.

Then, what do you mean by saying "invokes"? Does the trigger start the service or it just send a message to him?

Invoking a service on firing a trigger

Hi

Im trying to invoke a .Net service when a trigger is fired on a table in SQL Server 2000.

The service is working fine but I need to invoke it when the trigger is fired. How can I achieve this?

Hi!

At first, why do you think your problem is concern with SQL-NS? The second: does you question relate to SQL Server 2005? You have wrote, that the trigger on SQL Server 2000 invokes the service and the services works well.

Then, what do you mean by saying "invokes"? Does the trigger start the service or it just send a message to him?

Invoking a batch file from Report

Hi !

I have to invoke a batch file (.bat) when an ad-hoc report is executed. I've currently written a .Net code for invoking (using System.Diagnostics.Process) the batch file. The rdl file calls the method.

When I run the report from Report Designer the batch file is getting executed. But if I deploy it in the Reporting Server and run the report through Web Interface the batch file is not getting executed.

Looks like some security issue. Can someone provide a solution for this?

Thanks.

Where did you place the batch file ? All commands executed either in Script or custom code or executed on the server not on the client.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just a suggestion on how to execute the .bat file. Could′t you use a SQL Agent job instead and call sp_start_job from a dataset in the report?|||

Hi Jens,

Thanks for the reply.

I placed the batch file in the server. I also moved the code (to invoke a batch file using System.Diagnostics.Process class) to a VB.Net dll and tried invoking the function from the rdl. The function is not getting invoked. I also added SecurityPermissionAttribute to the class. Is there any other Security permission that needs to be added for the code to be accessed.

Please find below the sample:

Public Shared Function fnRunSPX(ByVal x As String, ByVal y As String, ByVal z As String, ByVal w As String)

Dim Pro As New System.Diagnostics.Process

Dim Elog As New System.Diagnostics.EventLog

If Not Elog.SourceExists("BillingDateBatch") Then

Elog.CreateEventSource("BillingDateBatch", "Application")

End If

Dim EventLog1 As New System.Diagnostics.EventLog

EventLog1.Source = "BillingDateBatch"

EventLog1.Log = "Application"

EventLog1.WriteEntry("Success", System.Diagnostics.EventLogEntryType.SuccessAudit)

Pro.StartInfo.FileName = "C:\\batch.bat"

Pro.StartInfo.Arguments = x + " " + y + " " + z + " " + w

Pro.Start()

Pro.WaitForExit()

End Function

Even the evenlogging (present in teh above code) does not happen. Please let me know if you have any questions.

Thanks,

Aravind

Invoking or sending SQL queries using dOS

Hello Folks,
I want to create a simple batch DOS script to query mi SQL Server 2000 database. How I could do this?
I just want to run a select for a table but I dont want people to interct with sql query analyzer or enterprise manager to avoid any issues.
Regards,Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.|||Originally posted by Paul Young
Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.

UID stands for? PWD I assume is the pasword, isnt.

How I can redirect the results to a txt file,

OSQL -S OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b >> query.txt

Am I right?

Thanks for your prompt reply and help|||UID = User ID

You could pipe the output to a text file but the -o parm would be more usefull.|||How I can get the instance name. I have tested in my environment and I got the next error:

[DBNETLIB] Sql Server does not exist or access denied
[DBNETLIB] ConnectionOpen <Connect(())

As per this result I checked the server name I used typing osql -L. I got the names and used them but with the same result.

When I open the Sql query analyzer I connect using windows authentication option to connect to my databases.

Please suggest..|||For Windows Security I think you need to use -E option instead of -U and -P options.

Tim Ssql

Invoke 'Refresh fields' programmatically

Hi all
I've implemented our custom data processing extension for Reporting Services
and the 'Refresh Fields' button available in the Generic Query Designer is
very useful to us to make sure the fields in the RDL files are updated.
Unfortunately, I can't seem to find a way to programmatically invoke this
'Refresh Fields' command. I want to write my own utility, but I'm not an
expert in .NET nor XML. Any pointers or guidance on how to achieve my goal
is greatly appreciated!
Thanks!!Hi,
I'll see if I can find the answer. I'll update you once I have more
information.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you and your
"William Wang[MSFT]" wrote:
> Hi,
> I'll see if I can find the answer. I'll update you once I have more
> information.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi,
You may want to implement the refresh logic externally by calling
IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this thread
for more information:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
ae765089b645dc3a
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the info. It's helpful to know what exactly happens for 'Refresh
Fields' behind the scene.
Unfortunately my main goal is to update the given RDL file(s). Everytime I
clicked the 'Refresh Fields' button the corresponding RDL file gets updated,
which is what I'm looking for.
In a Reporting Project, I want to be able to programmatically refresh all
its RDL files. Maybe I should ask how to get access to an IDbCommand object
for each report?
Your help is appreciated!! Thanks!
Jenny
"William Wang[MSFT]" wrote:
> Hi,
> You may want to implement the refresh logic externally by calling
> IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this thread
> for more information:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
> se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
> s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
> ae765089b645dc3a
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||There's not a direct way so far.
<yinjennytam@.newsgroup.nospam> wrote in message
news:28AD35DA-113D-43C3-B95A-50C910D01CD8@.microsoft.com...
> Thanks for the info. It's helpful to know what exactly happens for
> 'Refresh
> Fields' behind the scene.
> Unfortunately my main goal is to update the given RDL file(s). Everytime
> I
> clicked the 'Refresh Fields' button the corresponding RDL file gets
> updated,
> which is what I'm looking for.
> In a Reporting Project, I want to be able to programmatically refresh all
> its RDL files. Maybe I should ask how to get access to an IDbCommand
> object
> for each report?
> Your help is appreciated!! Thanks!
> Jenny
>
> "William Wang[MSFT]" wrote:
>> Hi,
>> You may want to implement the refresh logic externally by calling
>> IDbCommand.ExecuteReader(SchemaOnly). I suggest that you review this
>> thread
>> for more information:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
>> se_thread/thread/d4a878f340785d77/ae765089b645dc3a?lnk=st&q=%22refresh+field
>> s%22+SchemaOnly+group:microsoft.public.sqlserver.reportingsvcs&rnum=5&hl=en#
>> ae765089b645dc3a
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>

Invoke or detect Print Report event

Hi
I'm not sure if this is possible, but hoping that someone out there can
give some guidance.
I'm using a reporting services ReportViewer object to view reports via
an ASP.NET application. I've just upgraded to SP2 to make use of the
print functionality.
However a requirement i have is that a particular report can be printed
only once. Is there a way that i can tap into the Print Report button
event so that i can detect if the print button has been clicked?
Or alternatively is there a way i can invoke the print method from
within ASP.NET aplication?
Or maybe there is some other way to do this....all suggestions
welcome!!!!!!!
Many many thanks in advance
Cheers!!!!There is no way to do this that I know of. And what counts as once? What if
they open up IE again and view the report again? How would you know that it
is the first or second time?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"muirboy" <gmuir@.statestreet.com> wrote in message
news:1157040604.891636.111600@.m73g2000cwd.googlegroups.com...
> Hi
> I'm not sure if this is possible, but hoping that someone out there can
> give some guidance.
> I'm using a reporting services ReportViewer object to view reports via
> an ASP.NET application. I've just upgraded to SP2 to make use of the
> print functionality.
> However a requirement i have is that a particular report can be printed
> only once. Is there a way that i can tap into the Print Report button
> event so that i can detect if the print button has been clicked?
> Or alternatively is there a way i can invoke the print method from
> within ASP.NET aplication?
> Or maybe there is some other way to do this....all suggestions
> welcome!!!!!!!
> Many many thanks in advance
> Cheers!!!!
>|||Bruce
They should be able to view it as many times as they want, therefore
coming out of IE and back in again would be fine. Essentially once
someone had clicked on the print button it would go back to the DB to
say that the report has been printed, thus locking it for anyone else.
If this sounds bizarre, its due a requirement from an external company
that only one print out should exist. There is a 'duplicate' function
which gives the user access to print the same report again, however the
word 'duplicate' would be splashed over to signify it is not the
original
So are you saying there's no way for me to detect the print function
has been invoked?
Thanks
Bruce L-C [MVP] wrote:
> There is no way to do this that I know of. And what counts as once? What if
> they open up IE again and view the report again? How would you know that it
> is the first or second time?
>|||It is a client side active X control. If you were to detect it, it would
have to be via script and I just don't know. From RS perspective it is just
another rendering request for the report. What about exporting to PDF?
Unless you turn off exporting to PDF (which cannot be done by report, only
for the complete server) then all the user has to do is export to PDF and
print (which is what my users used to do before they adding printing).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"muirboy" <gmuir@.statestreet.com> wrote in message
news:1157044757.246385.151290@.74g2000cwt.googlegroups.com...
> Bruce
> They should be able to view it as many times as they want, therefore
> coming out of IE and back in again would be fine. Essentially once
> someone had clicked on the print button it would go back to the DB to
> say that the report has been printed, thus locking it for anyone else.
> If this sounds bizarre, its due a requirement from an external company
> that only one print out should exist. There is a 'duplicate' function
> which gives the user access to print the same report again, however the
> word 'duplicate' would be splashed over to signify it is not the
> original
> So are you saying there's no way for me to detect the print function
> has been invoked?
> Thanks
> Bruce L-C [MVP] wrote:
>> There is no way to do this that I know of. And what counts as once? What
>> if
>> they open up IE again and view the report again? How would you know that
>> it
>> is the first or second time?
>>
>

Invoke a call for a report in VB2003

We just updated to SQL2005 and all it entails. I've made a report,
deployed it and published it on the web reportserver. My question is,
how do I call it in a VB2003 application? I know how to migrate the
apps in the new "DTS". I can find all the info I need on VB2005, but
we didn't upgrade to that. I need to be able to print this report
during our down time. So, if calling it in 2003 isn't an option, is
there anyway I can schedule the report to print. I see scheduling, but
that's just to compile the report, not print it(unless I'm missing
something). Any help is greatly appreciated. Thanks!Printing is client oriented in both RS 2000 and RS 2005. No difference
between these two. What you could do is schedule a job to render to a file,
pdf format and then have an app monitoring the directory, picking up the
file and printing it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MBE" <nada@.knology.net> wrote in message
news:1157724090.595634.194360@.i3g2000cwc.googlegroups.com...
> We just updated to SQL2005 and all it entails. I've made a report,
> deployed it and published it on the web reportserver. My question is,
> how do I call it in a VB2003 application? I know how to migrate the
> apps in the new "DTS". I can find all the info I need on VB2005, but
> we didn't upgrade to that. I need to be able to print this report
> during our down time. So, if calling it in 2003 isn't an option, is
> there anyway I can schedule the report to print. I see scheduling, but
> that's just to compile the report, not print it(unless I'm missing
> something). Any help is greatly appreciated. Thanks!
>|||That makes sense. I'll work on it and see what happens. Thanks! I
guess I've been struggling learning Reporting Services that I made it
harder than it was. Thanks again.
Bruce L-C [MVP] wrote:
> Printing is client oriented in both RS 2000 and RS 2005. No difference
> between these two. What you could do is schedule a job to render to a file,
> pdf format and then have an app monitoring the directory, picking up the
> file and printing it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "MBE" <nada@.knology.net> wrote in message
> news:1157724090.595634.194360@.i3g2000cwc.googlegroups.com...
> > We just updated to SQL2005 and all it entails. I've made a report,
> > deployed it and published it on the web reportserver. My question is,
> > how do I call it in a VB2003 application? I know how to migrate the
> > apps in the new "DTS". I can find all the info I need on VB2005, but
> > we didn't upgrade to that. I need to be able to print this report
> > during our down time. So, if calling it in 2003 isn't an option, is
> > there anyway I can schedule the report to print. I see scheduling, but
> > that's just to compile the report, not print it(unless I'm missing
> > something). Any help is greatly appreciated. Thanks!
> >

Invokation of a stored procedure from an Integration Services package

Is it possible to execute a stored procedure from an Integration Services package? I see that its possible to enter sql commands that can be run but when a command to execute a stored procedure is entered the system cannot find the stored procedure (eventhough 'use mydbname' preceded it.

thx,

Marilyn

You certainly can execute a stored procedure, but we'll need some more information to help.

Are you using the Execute SQL task in SSIS? Is the database SQL Server? Does the account your are developing under have permissions to see the stored procedure? What error messages are you seeing?

Donald

InvoiceNumber as a degenerated dimension in AS 2000?

Hello,

We have a couple of cubes in AS 2000 where the users would like to be able to search/view by #Invoicenumber and #POnumber. Instead of creating a separate Invoicedimension I would like to implement it as a 'degerated dimension' - just as a textfield in the facttable.

Some questions: Does AS2000 support this? Are there any special demands on the client tool to be able to support degenerated dimensions? At the moment InvoiceNumber is defined as varchar(16), is that OK?

Looking forward to your responses and advices ....

As far as I can recall this shouldnt be any problem.

Degenerate dimensions are handled just like any other dimension by ssas so there are no problems with client tools using that dimension.

BUT (there is a big but): Unless you are developing your own custom front end for this, the functionality you are looking for will be hard to implement in the front-ends i know of (proclarity, pivottables in excel) because there will typically be a humongous number of invoices. Im guessing that you want users to be able to punch in an invoice number and display invoice lines for that invoice, not show every invoice in a dropdown. My advice would be to look at reporting services to create this kind of report.

|||

I agree with Peter K that Reporting Services is a good platform for detailed low level analysis. On the other hand, if you are required to build this is in AS2000 you simply build a dimension from the invoice number in the fact table. The way you do it is by building fake levels in this dimension like the two first positions for the invoice number, followed by three and four and so on. You can use the TSQL Left-function to do this in the dimension editor for the key and name column.

Regards

Thomas

|||

Thanks Peter and Thomas,

Since we need to stick to AS2000 for the moment I have created a Invoicedimension. This dimension will be huge an flat but hopefully we could migrate to AS2005 within a year!

sql

Invoice with multi pages

Hi,

I want know if is possible create a report, with this caracteristics.

Page 1:

Header:

Nome of Company Invoice no 1

Original

Detail:

line 1

line 2

line 3

Page 2:

Header:

Nome of Company Invoice no 1

Copy

Detail:

line 1

line 2

line 3

.

.

.

Many pages of parameter in my code in Visual Studio.

Tanks,

Camsoft77

what about with two table controls that are identical except the "Original"/"Copy" difference?|||

urbancenturion,

My question is how i can create two pages or more, with same information but change for example 1 parameter.

I need build a report to create Invoice. with 3 copies of invoice.

Att,

Camsoft77

|||

>>I need build a report to create Invoice. with 3 copies of invoice.

The best way to do this is with a cartesian join added to your "real" query.

For example, the following will create three rows for each real table row, with an extra column called "invoice_info", properly ordered so the "original" shows up first, in case that is important to you <s>:

Code Snippet

select sales_no, invoice_info from orderheader join

(select 'ORIGINAL' as invoice_info, 1 AS invoice_order

union

select 'COPY1',2

union

select 'COPY2',3

) xx

on 1 = 1

order by sales_no, invoice_order

.. now put a group on your invoice # (or sales_no, in my example) or whatever you want in your report...

HTH,

>L<

Invoice Sample

Anyone know where I can find a sample RDL that is styled like an invoice
form?
Header - contains Sold To/Ship To
Detail - Invoice Line Items
Footer - contains invoice totals
TIA
TinaTina,
Reporting Services installs sample reports. There is one particular report
you could probably use as template.
The report is called Sales Order Detail report. It resembles an invoice. You
should be able to access this report in http://<server>/ReportServer in the
SampleReports folder. You can access the design of this report in Visual
Studio .Net.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Anyone know where I can find a sample RDL that is styled like an invoice
> form?
> Header - contains Sold To/Ship To
> Detail - Invoice Line Items
> Footer - contains invoice totals
> TIA
> Tina
>|||Thanks David, I'll have a look at the sample.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Tina,
> Reporting Services installs sample reports. There is one particular report
> you could probably use as template.
> The report is called Sales Order Detail report. It resembles an invoice.
You
> should be able to access this report in http://<server>/ReportServer in
the
> SampleReports folder. You can access the design of this report in Visual
> Studio .Net.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Anyone know where I can find a sample RDL that is styled like an invoice
> > form?
> >
> > Header - contains Sold To/Ship To
> > Detail - Invoice Line Items
> > Footer - contains invoice totals
> >
> > TIA
> > Tina
> >
> >
>|||That's a start but I need to repeat the BillTo & ShipTo on every page.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Tina,
> Reporting Services installs sample reports. There is one particular report
> you could probably use as template.
> The report is called Sales Order Detail report. It resembles an invoice.
You
> should be able to access this report in http://<server>/ReportServer in
the
> SampleReports folder. You can access the design of this report in Visual
> Studio .Net.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Anyone know where I can find a sample RDL that is styled like an invoice
> > form?
> >
> > Header - contains Sold To/Ship To
> > Detail - Invoice Line Items
> > Footer - contains invoice totals
> >
> > TIA
> > Tina
> >
> >
>|||Tina,
You can put the Bill To and Ship To information in the Table Header and have
it repeat.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
> That's a start but I need to repeat the BillTo & ShipTo on every page.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> Tina,
>> Reporting Services installs sample reports. There is one particular
>> report
>> you could probably use as template.
>> The report is called Sales Order Detail report. It resembles an invoice.
> You
>> should be able to access this report in http://<server>/ReportServer in
> the
>> SampleReports folder. You can access the design of this report in Visual
>> Studio .Net.
>> Dave
>> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Anyone know where I can find a sample RDL that is styled like an
>> > invoice
>> > form?
>> >
>> > Header - contains Sold To/Ship To
>> > Detail - Invoice Line Items
>> > Footer - contains invoice totals
>> >
>> > TIA
>> > Tina
>> >
>> >
>>
>|||That's what I'm working on now but I loose the free-form ability when
working in a table.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:uw4KOPP9EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Tina,
> You can put the Bill To and Ship To information in the Table Header and
have
> it repeat.
> Dave
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
> > That's a start but I need to repeat the BillTo & ShipTo on every page.
> >
> > "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> > news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
> >> Tina,
> >>
> >> Reporting Services installs sample reports. There is one particular
> >> report
> >> you could probably use as template.
> >>
> >> The report is called Sales Order Detail report. It resembles an
invoice.
> > You
> >> should be able to access this report in http://<server>/ReportServer in
> > the
> >> SampleReports folder. You can access the design of this report in
Visual
> >> Studio .Net.
> >>
> >> Dave
> >>
> >> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> >> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Anyone know where I can find a sample RDL that is styled like an
> >> > invoice
> >> > form?
> >> >
> >> > Header - contains Sold To/Ship To
> >> > Detail - Invoice Line Items
> >> > Footer - contains invoice totals
> >> >
> >> > TIA
> >> > Tina
> >> >
> >> >
> >>
> >>
> >
> >
>|||Welcome to Reporting Services. I am a Crystal Reports developer myself but
starting to understand Reporting Services.
Tip: You can select multiple cells and merge them. That is a huge help.
Dave
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:exmn0TP9EHA.3588@.TK2MSFTNGP10.phx.gbl...
> That's what I'm working on now but I loose the free-form ability when
> working in a table.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:uw4KOPP9EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> Tina,
>> You can put the Bill To and Ship To information in the Table Header and
> have
>> it repeat.
>> Dave
>> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> news:uPYifJP9EHA.2680@.TK2MSFTNGP09.phx.gbl...
>> > That's a start but I need to repeat the BillTo & ShipTo on every page.
>> >
>> > "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
>> > news:OMPQ7sM9EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> >> Tina,
>> >>
>> >> Reporting Services installs sample reports. There is one particular
>> >> report
>> >> you could probably use as template.
>> >>
>> >> The report is called Sales Order Detail report. It resembles an
> invoice.
>> > You
>> >> should be able to access this report in http://<server>/ReportServer
>> >> in
>> > the
>> >> SampleReports folder. You can access the design of this report in
> Visual
>> >> Studio .Net.
>> >>
>> >> Dave
>> >>
>> >> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
>> >> news:%23OhIxkM9EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> >> > Anyone know where I can find a sample RDL that is styled like an
>> >> > invoice
>> >> > form?
>> >> >
>> >> > Header - contains Sold To/Ship To
>> >> > Detail - Invoice Line Items
>> >> > Footer - contains invoice totals
>> >> >
>> >> > TIA
>> >> > Tina
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Invoice Pagination - How do I start next costomer on new sheet when needed?

Someone has to have asked this question before but I could not find the answer. I did find some places, not on this site, with the question.

Basically like the title says, I'm just doing for a different purpose but figured invoicing would be the most popular reason. If the last page of a group prints on the front of the sheet then I need to advance past the back of the sheet and onto a new sheet to start the next group. Just like invoicing would be. If the invoice finishes on page 3 then you need to skip page 4 and go to page 5.

My particular application is that I need to insert colored separators between groups so I can't have a new group start on the back page of a previous group. Hope that gets the point/need across.

Thanks

Carl

EDIT Another application for this same concept is printing a report for multiple departments or employees so the report will need to be printed so each department or employee can be given their part.

Lots of lookers but no ideas?

Do I need to make something more clear? Hopefully I gave enough info.

|||

Conceptually the way i would do this is to put the data inside a list control (group it by customer), and then set both PageBreakAtEnd and PageBreakAtStart to true, that way with two page breaks no invoice should start on the back of the previous one.

sluggy

|||

Would that not leave the potential for the first page to be blank? If so that solution will not work for me. I need one blank page if and only if the last printed page was an odd numbered page. I've tried setting up some conditions etc but page numbers can't be accessed in the detail and detail can't be accessed in the page header.

This seems like a very basic function that every aspiring reporting program would need.

EDIT I have already grouped this and have a page break at the end of group but wont set it for start of group for the reason stated above.

|||One more trip to the top before I come up with a work around. There has to be a simple answer or it can't be done in the current version at all.

Invoice Pagination - How do I start next costomer on new sheet when needed?

Someone has to have asked this question before but I could not find the answer. I did find some places, not on this site, with the question.

Basically like the title says, but this would apply to any report that needs to be broken up for different destinations such as invoices, splitting off to different departments, or different employees. I'm just doing for a different purpose but figured invoicing would be the most popular reason. If the last page of a group prints on the front of the sheet (ie the odd page) then I need to advance past the back of the sheet (the even numbered page) and onto a new sheet to start the next group. Just like invoicing would be. If the invoice finishes on page 3 then you need to skip page 4 and go to page 5.

My particular application is that I need to insert colored separators between groups so I can't have a new group start on the back page of a previous group. Hope that gets the point/need across.

I've posted in other forums and have yet to get an answer to this seemingly simple report requirement.

Thanks a bunch

Carl

So is this just not doable in RS?

Invoice Pagination - How do I start next costomer on new sheet when needed?

Someone has to have asked this question before but I could not find the answer. I did find some places, not on this site, with the question.

Basically like the title says, I'm just doing for a different purpose but figured invoicing would be the most popular reason. If the last page of a group prints on the front of the sheet then I need to advance past the back of the sheet and onto a new sheet to start the next group. Just like invoicing would be. If the invoice finishes on page 3 then you need to skip page 4 and go to page 5.

My particular application is that I need to insert colored separators between groups so I can't have a new group start on the back page of a previous group. Hope that gets the point/need across.

Thanks

Carl

EDIT Another application for this same concept is printing a report for multiple departments or employees so the report will need to be printed so each department or employee can be given their part.

Lots of lookers but no ideas?

Do I need to make something more clear? Hopefully I gave enough info.

|||

Conceptually the way i would do this is to put the data inside a list control (group it by customer), and then set both PageBreakAtEnd and PageBreakAtStart to true, that way with two page breaks no invoice should start on the back of the previous one.

sluggy

|||

Would that not leave the potential for the first page to be blank? If so that solution will not work for me. I need one blank page if and only if the last printed page was an odd numbered page. I've tried setting up some conditions etc but page numbers can't be accessed in the detail and detail can't be accessed in the page header.

This seems like a very basic function that every aspiring reporting program would need.

EDIT I have already grouped this and have a page break at the end of group but wont set it for start of group for the reason stated above.

|||One more trip to the top before I come up with a work around. There has to be a simple answer or it can't be done in the current version at all.sql

Invoice numbering system

I am creating an invoicing structure in our database. Our system allows
multiple companies to be setup within the one database. Each company should
have seperate invoice number sequences. E.g.
Company One's last invoice number was 10000
Company Two generates a new invoice. They should be given either 10000 or a
number in a completely different range. But they should not be given 10001
(in company one's sequence).
Has anyone any suggestions on how to create a system for generating invoice
numbers. Two users should never be given the same invoice number (within
the same company). There should never be any gaps in invoice numbers (e.g.
Company One next invoice number after 10000 must be 10001 and then 10002
etc).
I need a guaranteed system that will generate an invoice number using the
rules above.
Thanks,
ChrisIf I was to generate my next number using something such as this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
BY InvoiceNo DESC
This approach seems to work, except for when I generate my first invoice
because the select clause returns nothing. So I tried this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo FROM (
SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialNo
= 5
UNION
SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
This seems to work. Does this sound a good approach for generating invoice
numbers that are unique to a company? And does this guarantee two users
cannot be given the same number?
Thanks,
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I am creating an invoicing structure in our database. Our system allows
>multiple companies to be setup within the one database. Each company
>should have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
> a number in a completely different range. But they should not be given
> 10001 (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating
> invoice numbers. Two users should never be given the same invoice number
> (within the same company). There should never be any gaps in invoice
> numbers (e.g. Company One next invoice number after 10000 must be 10001
> and then 10002 etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>|||put it on separate tables
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Chris" wrote:

> I am creating an invoicing structure in our database. Our system allows
> multiple companies to be setup within the one database. Each company shou
ld
> have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
a
> number in a completely different range. But they should not be given 1000
1
> (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating invoic
e
> numbers. Two users should never be given the same invoice number (within
> the same company). There should never be any gaps in invoice numbers (e.g
.
> Company One next invoice number after 10000 must be 10001 and then 10002
> etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>
>|||Chris ,
why dont u try describing the column as identity using newid function to
give to unque number everytime
"Chris" wrote:

> If I was to generate my next number using something such as this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDE
R
> BY InvoiceNo DESC
>
> This approach seems to work, except for when I generate my first invoice
> because the select clause returns nothing. So I tried this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerial
No
> = 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
> This seems to work. Does this sound a good approach for generating invoic
e
> numbers that are unique to a company? And does this guarantee two users
> cannot be given the same number?
> Thanks,
> Chris
>
> "Chris" <cw@.community.nospam> wrote in message
> news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
>|||On Wed, 21 Sep 2005 13:29:44 +0100, Chris wrote:

>If I was to generate my next number using something such as this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
>BY InvoiceNo DESC
>
>This approach seems to work, except for when I generate my first invoice
>because the select clause returns nothing. So I tried this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialN
o
>= 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
>This seems to work. Does this sound a good approach for generating invoice
>numbers that are unique to a company? And does this guarantee two users
>cannot be given the same number?
Hi Chris,
This one is easier, and probably quicker as well:
INSERT INTO Invoices (InvoiceNo)
SELECT COALESCE(MAX(InvoiceNo),0) + 1
FROM Invoices
WHERE CompanySerialNo = 5
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
That's more readable!
Chris

> Hi Chris,
> This one is easier, and probably quicker as well:
> INSERT INTO Invoices (InvoiceNo)
> SELECT COALESCE(MAX(InvoiceNo),0) + 1
> FROM Invoices
> WHERE CompanySerialNo = 5
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Invoice form layout

Is there some way to simulate a fixed form layout for an invoice? I want to
have a fixed set of columns with dividing lines and totals boxes at the
bottom, regardless of how many actual rows of data are displayed. I've
tried 2 approaches (using PDF rendering only):
1. overlay a table with a set of manually drawn lines. This sort of works
for a single page, but if a 2nd page is required by the data it goes
completely haywire.
2. Use a table with conditional formatting. This is my current solution, BUT
a) the totals are always immediately after the last row, instead of at the
page bottom,
b) when the data requires just a little more than a single page the whole
table is moved to start on the 2nd page (where it fits in total due to less
header space) instead of starting on page 1 and continuing on page2 (hope
that makes sense).
Is there any published advice on designing this sort of page layout, or is
RS not really suited to the task?
brian smithHi Brian,
have you had any luck with this?
regards
Matt
"Brian Smith" <bsmith@.no_spam.schemiotics.co.uk> wrote in message
news:OElbad7nEHA.2300@.TK2MSFTNGP10.phx.gbl...
> Is there some way to simulate a fixed form layout for an invoice? I want
to
> have a fixed set of columns with dividing lines and totals boxes at the
> bottom, regardless of how many actual rows of data are displayed. I've
> tried 2 approaches (using PDF rendering only):
> 1. overlay a table with a set of manually drawn lines. This sort of works
> for a single page, but if a 2nd page is required by the data it goes
> completely haywire.
> 2. Use a table with conditional formatting. This is my current solution,
BUT
> a) the totals are always immediately after the last row, instead of at the
> page bottom,
> b) when the data requires just a little more than a single page the whole
> table is moved to start on the 2nd page (where it fits in total due to
less
> header space) instead of starting on page 1 and continuing on page2 (hope
> that makes sense).
> Is there any published advice on designing this sort of page layout, or is
> RS not really suited to the task?
> brian smith
>|||Nope, nothing :-(
My feeling is the RS cannot really do this sort of thing - it's really meant
for screen-based reporting.
brian
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ugVGLjSpEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Hi Brian,
> have you had any luck with this?
> regards
> Matt
> "Brian Smith" <bsmith@.no_spam.schemiotics.co.uk> wrote in message
> news:OElbad7nEHA.2300@.TK2MSFTNGP10.phx.gbl...
> > Is there some way to simulate a fixed form layout for an invoice? I want
> to
> > have a fixed set of columns with dividing lines and totals boxes at the
> > bottom, regardless of how many actual rows of data are displayed. I've
> > tried 2 approaches (using PDF rendering only):
> > 1. overlay a table with a set of manually drawn lines. This sort of
works
> > for a single page, but if a 2nd page is required by the data it goes
> > completely haywire.
> > 2. Use a table with conditional formatting. This is my current solution,
> BUT
> > a) the totals are always immediately after the last row, instead of at
the
> > page bottom,
> > b) when the data requires just a little more than a single page the
whole
> > table is moved to start on the 2nd page (where it fits in total due to
> less
> > header space) instead of starting on page 1 and continuing on page2
(hope
> > that makes sense).
> >
> > Is there any published advice on designing this sort of page layout, or
is
> > RS not really suited to the task?
> >
> > brian smith
> >
> >
>

Invoice dimension - best way to model it

We have a system that will have ~15 million invoices per year. We would like to track lots of very unique attributes of each invoice (invoice number, etc). Currently we are using a junk dimension ([Dim Invoice Attribute]) that contains 4 or 5 of the attributes, but our changes will require going to the grain of the invoice table. I have read having a physical dimension table that has a 1:1 cardinality to the fact table isn't a good idea (strictly because of the size of the table and the operation of joining fact to dimension). Currently we have 2 or 3 attributes of the invoice in the fact table (degenerate dimensions) in order to fulfill relational queries for tying things back to the MDX results.

My question is what is the best way to model this? I have read both sides of the story - create an invoice dimension table, and store everything there with keys relating back to the fact table, and also store all of the attributes in the fact table and create a named query in the .dsv that selects out only the attributes for the [Dim Invoice] dimension, thus saving space and join energies during processing.

Can anyone shed some light on this?

Thank you in advance,

John Hennesey

Typically in this situation, you have a fact table that represents the invoicing process. One of the dimensions is the invoice which will most likely have a one-to-one relationship with the fact. Does this describe your model?

Kimball would describe this as a degenerate dimension and would recommend you move the dimension into the fact table. SSAS provides support for this through a fact dimension. (See Books Online topic "Dimension Relationships" for a more detailed description.)

So this works if we're talking about a fact dimension with just a few attributes. After that, the model starts to look a little sloppy. I would look at the cardinality of some of these other attributes. If the cardinality is relatively low, you may want to move these into their own dimensions or if the cardinality is very low lump them into a junk dimension.

The other thing I would consider is the user's experience. How often will users be accessing these other dimensions created to hold those attributes? If these are presented separate from the Invoice dimension, would these seem weird or be cumbersome for your user? Do you plan on providing user-hierarchies that role your invoice number up to these attributes? If not, then I'd recommend going ahead with moving them out.

From a performance standpoint, I don't think your design choice has too big an impact on SSAS. If you figure that every attribute hierarchy is given its own storage and the only thing that would be different internally would be the relationship maps. The impact of having or not having these depends on how you intend to use your attributes.

So, I guess my general recommendation would be keep to standard, Kimball principles and put an emphasis on end-user experience. If you run into a performance problems down the road, you should be able to easily tweak the model.

If others folks have thoughts on this, esp regarding how the SSAS engine handles this stuff, please, please chime in.

Thanks,
Bryan

|||

I agree with the approach of keeping the invoice attributes in the fact table.

One issue is the number of invoices(metadata) that will be downloaded to the client when they use the invoice dimension. This will be a really slow process if you show to many invoice members and the solution is to create artificial levels above the invoice number. I build the first level with the first character in the invoice number using the TSQL-left function in the data source view.. A guide line is to have no more than 10-15 groups on each level. On the leaf level you have have larger groups.

I have asked the SSAS2005 development team about any performance issues with building a fact table dimension and they have no negative impact.

HTH

Thomas Ivarsson

|||

Interesting - I have heard arguments on both sides of the argument. To implement a fact table dimension, it would be using a named query that goes against the fact table, right? Is there a special way to set up a fact table dimension (any attributes of the dimension itself?) I have also read in just about every article it is necessary to set up a hierarchy to limit the number of members returned to the client - indeed a very good idea.

Thank you all for your input,

John Hennesey

|||

Hello John! Or use named queries in the fact table for each artifical level in the invoice hierarchy/dimension.

Good luck!

/Thomas

Invoice Detail

Hi

I have a invoice that displays the company name in a rectangle on top then it has a list in the list it has details tables my problem is that the invoice detail goes on the next page I need to have the company name as well on the second page I cant put it in the same table as the detail because I have a few tables in the detail so it is actually no header is it possible to have Repeated the Company name information on the next page?

Thanks

Try this:

http://blogs.msdn.com/ChrisHays/

You may be able to add a conditional text box that displays whenever RowNumber > a certain amount, which would be equal to the amount of records that could fit on a page.

cheers,

Andrew

|||<<

You may be able to add a conditional text box that displays whenever RowNumber > a certain amount, which would be equal to the amount of records that could fit on a page.>>

Hi Thanks Andrew

can you please explain a little bit more how you do this?

Thanks

|||A bit of a hack, but how about just creating another rectangle with visibility property =IIF(Globals!PageNumber.Value > 2)

Not sure if that will work though since the body does not know about page numbers.

Did you try Chris Hays's fix?

http://blogs.msdn.com/ChrisHays/

By passing in the RowNumber to a function, you should be able to toggle hidden property using something like =IIF(Code.RowCount(RowNumber(Nothing)) = 25, True, False)

Then just place the title in a new row in your table, or create a table within a table?

Apologies, hope you find an easier way.

cheers,
Andrew

Invoice calcualtion

hi!

i actually have more than question related to the SQL Server 2000

1) On my systemI charge users for using an online service (SMS ) and I have a monthly fees charge and monthly allowance usage so if they exceed the limit I can also charge them for over usage this is an example for a rate that I have in my Rate Table

Rate Id = 1

MonthlyFees ￡10.0

MothlyAllowence = 100 sms/ month

Extra usage = 0.12 p for each extra SMS

The system should calculate the monthly invoice from the registration date so if you register in 14 April the invoice will be generated every month at the same day (15) each month.

Now! I need to generate and calculate the invoice …..Where shall I do the calculation? In the Business object Layer or in somewhere else? And for invoicing the client every month I might have 20 clients and each one might have different day so the billing should be auto generated also what properties or methods that can calculate or check monthly related to the day so it will run in the way like the phone bill!! any advice is appreciate it!

Did you mean you want to caculate monthly invoice for each client? If so, we can accomplish in SQL server, using such T-SQL script to get the monthly statistics:

select sum(MonthlyFees),max(RecDate),max(clientID) from [Rate Table]
group by clientID,year(RecDate),month(RecDate)

I wish I didn't understand you.

sql

Invitation - MrBool.com

Hi friends,

First I would like present me. My name is Eduardo, I am publisher of
the Mr.Bool Portal (that can be accessed through the address www.mrbool.com).

There is short time was given beginning to this new challenge, and now
I am finding columnists that have interest in write articles for
publication.

The articles need to be related to the Java, SQL. Net, or to some
technology related to the mobile devices.

Below possible subjects for articles are listed:

Small articles that discuss aspects related to the Java. For
example, articles about: Eclipse, NetBeans, TomCat, persistence with
Java, JDBC, JME, JSE, JEE, EJB ,JUnit, Ant, Design Patterns,UML, RUP,
JExcelAPI, Prefuse, API's, Frameworks, among others.
Small articles that discuss aspects related to the .NET. For
example, articles about: ASP.NET, C#, J#, persistence with .NET,
Frameworks, content management, UML, XML, Design Patterns, among
others.
Small articles that discuss aspects related to the DataBase. For
example, articles about: Oracle, SQL Server, MySQL, Postgre SQL,
Hibernate, modeling tools, Entity-relationship, Reporting Services,
SGBD's, among others.
Small articles that discuss aspects related to the technologies for
mobile devices. For example, articles about: PDA, SmartPhone, Windows
CE, Symbian, Windows Mobile, PocketPC, JME, SuperWaba, N-Gage,
Blackberry, API's, Frameworks, programming for mobile devices
(languages, frameworks), among others.

The ideal is that we worked with short articles and with a regularity
of constant sending (for example, one or two articles to each month).

I think that this Portal is an excellent place to present your
knowledge. You already can find several articles and videos
about .Net. Soon we will have also articles and videos about Java and
SQL. All free!

What do you think about this? We would like of count with your
availability and contribution.

Who will be interested can enter in contact with me through the e-
mail:
eduspinola@.gmail.com
Thanks,
Eduardo SpnolaOn Mar 20, 8:51 am, "Eduardo Spnola" <eduardoospin...@.gmail.com>
wrote:

Quote:

Originally Posted by

Hi friends,
>
First I would like present me. My name is Eduardo, I am publisher of
the Mr.Bool Portal (that can be accessed through the addresswww.mrbool.com).
>
There is short time was given beginning to this new challenge, and now
I am finding columnists that have interest in write articles for
publication.
>
The articles need to be related to the Java, SQL. Net, or to some
technology related to the mobile devices.
>
Below possible subjects for articles are listed:
>
Small articles that discuss aspects related to the Java. For
example, articles about: Eclipse, NetBeans, TomCat, persistence with
Java, JDBC, JME, JSE, JEE, EJB ,JUnit, Ant, Design Patterns,UML, RUP,
JExcelAPI, Prefuse, API's, Frameworks, among others.
Small articles that discuss aspects related to the .NET. For
example, articles about: ASP.NET, C#, J#, persistence with .NET,
Frameworks, content management, UML, XML, Design Patterns, among
others.
Small articles that discuss aspects related to the DataBase. For
example, articles about: Oracle, SQL Server, MySQL, Postgre SQL,
Hibernate, modeling tools, Entity-relationship, Reporting Services,
SGBD's, among others.
Small articles that discuss aspects related to the technologies for
mobile devices. For example, articles about: PDA, SmartPhone, Windows
CE, Symbian, Windows Mobile, PocketPC, JME, SuperWaba, N-Gage,
Blackberry, API's, Frameworks, programming for mobile devices
(languages, frameworks), among others.
>
The ideal is that we worked with short articles and with a regularity
of constant sending (for example, one or two articles to each month).
>
I think that this Portal is an excellent place to present your
knowledge. You already can find several articles and videos
about .Net. Soon we will have also articles and videos about Java and
SQL. All free!
>
What do you think about this? We would like of count with your
availability and contribution.
>
Who will be interested can enter in contact with me through the e-
mail:
eduspin...@.gmail.com
>
Thanks,
Eduardo Spnola

Good Social Service . Keep it up !
Best Wishes

Invisible rows after failed syncronization

Publisher: SQL Server 2000 SP4
Subscriber: SQL Server 2005 Mobile Edition

Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.

I've reproduced a case:

[START Short Version]
All rows inserted between a comm failed sync and a fine sync became invisible.
This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore.
[END Short Version]

[START Detailed case]
STEP 0: INITIAL STATE
Publisher
Orders Table
EMPTY

Subscriber
NO DATABASE

STEP 1: After FIRST SYNC and INSERTING 2 Orders

Publisher

Orders Table

EMPTY

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 81
000002 4 4 81

STEP 2: After SECOND SYNC

Publisher

Orders Table

OrderId

000001

000002

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

STEP 3: INSERT ANOTHER ORDER

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

000003 6 6 81

STEP 4: After THIRD SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table

OrderId

000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81
Note: Publisher has the row but subscriber keeps the state in 81.

STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81
Note: Orders 000003 and 000004 have the same system info

STEP 6: After FOURTH SYNC
Publisher
Orders Table

OrderId
000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81

Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC

STEP 7: INSERT ANOTHER ORDER

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 81

000004 6 6 81

000005 8 8 81

STEP 8: After FIFTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81

000004 6 6 81

000002 8 8 80

Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation.
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80

Note: sysCG get its value updated correctly.

STEP 10: After SIXTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000004

000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 80

000004 6 6 80

000002 8 8 80

Note: Data merges correctly.
[END Detailed case]

I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.

I don't know if I'm missing something or if this is a bug or a known issue.

This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.

Any comment will be appreciated.Finally, I've modified my replication process solve this manually.

On a normal case (no previous error):
1. I get the current generation (__sysMergeSubscriptions.LastUploadedGen+1)
2. Launch a normal sync.
3. If it fails I save the failed generation for using it on next sync.

On the previous error case:
1. Do a normal sync (to get a valid __sysMergeSubscriptions.LastUploadedGen)
2. Do a dummy update on all rows (from all published tables (from __sysMergeArticles)) that __sysIG or __sysCG equals the wrong generation number.
3. Do a normal sync to send all the invisible rows to the publisher.

I don't like my own solution, but that's the only way I have to keep the system working as I haven't find any reference to this problem anywhere.|||

Hey MeZKaL,

I just have few questions to narrow downt he problem point in the whole path of sync components.

In the same above scenario, can you please also let me know the contents of __sysMergeSubscription and
__sysMergeSubscriptionProperties table for that subscription in each of the above STEPs (esp. Steps 4 to 6)?

Basically, I just want to know which of the following is true (after a failed sync)

1) Client did not enumerate the changes and hence did not send the changes

2) Client has enumerated the changes, sent the changes but metadata does not say its new change

3) Client has enumerated, sent the changes, metadata also says its a new change, but publisher ignores it for some internal
reasons like publisher metadata says its a false change ...etc

Thanks,

Laxmi

|||I repeated the case saving a copy of the mobile db for each step.

There are no changes on __sysMergeSubscriptionProperties so I will put it just one time.

__sysMergeSubscriptionProperties
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
Distributor
DistributorAddress
DistributorNetwork: 0
DistributorSecurityMode: 1
DistributorLogin
DistributorPassword
ExchangeType: 3
InternetLogin: domain\user
InternetURL: URL
InternetPassword: *******
LoginTimeout: 15
ProfileName: DEFAULT
Publisher: SERVER\INSTANCE
PublisherAddress
PublisherNetwork: 0
PublisherDatabase: DBName
PublisherSecurityMode: 1
PublisherLogin: domain\user
PublisherPassword: *******
Publication: PubName
QueryTimeout: 300
Validate: 0
HostName
Subscriber: domain\user
InternetProxyServer
InternetProxyLogin
InternetProxyPassword
ConnectionRetryTimeout: 120
CompressionLevel: 1
ConnectionManager: 0
SnapshotTransferType: 99

Many of the field value from __sysMergeSubscriptions didn't change during the process.

__sysMergeSubscriptions
Publisher: server\instance
PublisherDatabase: DBName
Publication: PubName
PublicationId: 03e497f6-83b6-4de3-87e3-e97835880c94
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
SyncPartnerId: 751a4dfe-2eba-7a4a-b11a-35beec9a8bf3
SyncAnchorId: 00000000-0000-0000-0000-000000000000
SchemaVersion: 481
SchemaVersionId: 00000000-0000-0000-0000-000000000000
ReplNick (casted to int): -312916250
Retention: 0
MachineId: 374677b5-b002-d9f5-d4e4-4628538f2ccd
PathName: Path\DB.sdf
ReinitRequested: 0
ReplProp: NULL
HostName:
ValidationHostName: 0
ReplicaState: 00000000-0000-0000-0000-000000000000
LastCleanupDate: 15/11/2006 16:30
ReconcilerVersion: 8
Subscriber: domain\user

[START Detailed case]

STEP 1: After FIRST SYNC
Publisher
Orders Table
EMPTY

Subscriber
Orders Table
EMPTY

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 2: After INSERTING 2 Orders
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 3: After SECOND SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 4: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 5: After THIRD SYNC
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 6: DUMMY UPDATE
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 81
57001 4 6 81
57002 4 6 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 7: After FOURTH SYNC
Publisher
Orders Table
OrderId
57000
57001
57002

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 80
57001 4 6 80
57002 4 6 80

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5213 7 15/11/2006 17:35 72
SentGen80Id
SentGen80Id: 4171a971-b963-a541-300a-0b1602902e39

[__sysMergeSubscriptions DETAIL]
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
step1 4977 0 15/11/2006 16:30 70
step2 4977 0 15/11/2006 16:30 70
step3 4977 0 15/11/2006 16:30 70
step4 4977 0 15/11/2006 16:30 70
step5 5095 5 15/11/2006 17:22 71
step6 5095 5 15/11/2006 17:22 71
step7 5213 7 15/11/2006 17:35 72

SentGen80Id
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
e5ff3df6-195c-3145-273e-86784ace7e16
e5ff3df6-195c-3145-273e-86784ace7e16
4171a971-b963-a541-300a-0b1602902e39|||Can you open a case with CSS please for this? That way we can get a better handle on this in a timely manner.|||My workaround runs pretty well and I don't have time now for spending hours and hours crossing mails and test results with a support service.

If anyone can help with the previous data, it'll be welcomed but I won't spent more time testing and logging for this issue. I've lots of work and not many time.

Invisible Replications

Derek,
can you run sp_removedbreplication in the previously
published databases.
If this doesn't remove the rogue red x's in replication
monitor, try restarting the sql server service - when I
have investigated this before, there is a reference to a
temp table in tempdb, so restarting removed both the
table and the red icon.
Apparently sp_MSload_replication_status may clear this
error as well.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
removedbreplication didn't work. It appears the "new" "Database A" has no
knowledge of the replications. I'm not sure where replication monitor is
getting it's information from, but that is what needs clearing out.
sp_MSload_replication_status also didn't seem to help.
I can get the database rebooted over the weekend, but is there anything else
I can try or any reading I can do to help investigate.
Thanks
Derek
"Paul Ibison" wrote:

> Derek,
> can you run sp_removedbreplication in the previously
> published databases.
> If this doesn't remove the rogue red x's in replication
> monitor, try restarting the sql server service - when I
> have investigated this before, there is a reference to a
> temp table in tempdb, so restarting removed both the
> table and the red icon.
> Apparently sp_MSload_replication_status may clear this
> error as well.
> HTH,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Invisible Replications

We have
Database A.
Database A Training
Database B
"Database A Training" was a copy of "Database A" 6 months ago.
Since then "Database A Training" has had replications set up to "Database B".
Last night a collegue decided that "Database A Training" needed updating and
so backed up "Database A" and then restored that backup over the top of
"Database A Training".
When I open the new "Database A Training" all the replications are gone.
This is not a problem as we were thinking of dispensing with most of them
anyway and the one I wanted is easy to recreate.
However when I now go to Replication Monitor all the old replications are
still showing and one of them is showing the big red X (OK, white X in red
circle) that shows something has failed.
When I right click - Delete I get the following message.
"SQL Server Enterprise Manager could not retrieve information about
publication 'XYZ'.
Error 21776: [SQL-DMO]The name 'XYZ' was not found in the TransPublications
collection. If the name is a qualified anem, use [] to separate various
parts of the name, and try again.
All I want to do is delete them and learn why this happened. I don't care
about recovering any information about them.
Your help is, as always, appreciated.
Derek
is this merge or transactional?
The best thing to do here is to restore the database again, this time using
the keep_replication switch. Then drop the publication using the GUI.
I'd also check the metadata tables to see if there is anything lingering
there.
You'll probably find some rows in syssubscriptions and sysmergesubscriptions
which you can delete.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Derek" <Derek@.discussions.microsoft.com> wrote in message
news:4FBE6B8B-8083-45DB-BD3F-6D9ACC1B8FE1@.microsoft.com...
> We have
> Database A.
> Database A Training
> Database B
> "Database A Training" was a copy of "Database A" 6 months ago.
> Since then "Database A Training" has had replications set up to "Database
B".
> Last night a collegue decided that "Database A Training" needed updating
and
> so backed up "Database A" and then restored that backup over the top of
> "Database A Training".
> When I open the new "Database A Training" all the replications are gone.
> This is not a problem as we were thinking of dispensing with most of them
> anyway and the one I wanted is easy to recreate.
> However when I now go to Replication Monitor all the old replications are
> still showing and one of them is showing the big red X (OK, white X in red
> circle) that shows something has failed.
> When I right click - Delete I get the following message.
> "SQL Server Enterprise Manager could not retrieve information about
> publication 'XYZ'.
> Error 21776: [SQL-DMO]The name 'XYZ' was not found in the
TransPublications
> collection. If the name is a qualified anem, use [] to separate various
> parts of the name, and try again.
> All I want to do is delete them and learn why this happened. I don't care
> about recovering any information about them.
> Your help is, as always, appreciated.
> Derek
|||Transactional. I'll let you know how this goes.
"Hilary Cotter" wrote:

> is this merge or transactional?
> The best thing to do here is to restore the database again, this time using
> the keep_replication switch. Then drop the publication using the GUI.
> I'd also check the metadata tables to see if there is anything lingering
> there.
> You'll probably find some rows in syssubscriptions and sysmergesubscriptions
> which you can delete.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Derek" <Derek@.discussions.microsoft.com> wrote in message
> news:4FBE6B8B-8083-45DB-BD3F-6D9ACC1B8FE1@.microsoft.com...
> B".
> and
> TransPublications
>
>
|||syssubscriptions and sysmergesubscriptions didn't show anything.
However when I restored an old copy of the training database (that had the
replications), I could then delete them all.
Thanks for all your help.
Derek
"Derek" wrote:
[vbcol=seagreen]
> Transactional. I'll let you know how this goes.
> "Hilary Cotter" wrote:

Invisible Parameters

Hi,
How do i make parameters invisible.
Scenario:
I am doing the dynamic sorting. I put 2 textboxes next to the table header
asc/desc
and am doing osrting sending values by parameters and reloading the report
with sorted options.
But how do i make them invisible parameters ?i.e, the parameters shouldnt be
shown on the report.
When i try making the prompt blank - it gives me an error that the parameter
is only read only.
Please help
Thanks
RamDid you put a Default value before making clearing the prompt text?
--
Thanks
Ravi
"Ramani" wrote:
> Hi,
> How do i make parameters invisible.
> Scenario:
> I am doing the dynamic sorting. I put 2 textboxes next to the table header
> asc/desc
> and am doing osrting sending values by parameters and reloading the report
> with sorted options.
> But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> shown on the report.
> When i try making the prompt blank - it gives me an error that the parameter
> is only read only.
> Please help
> Thanks
> Ram|||Yes, I did. I set the default values.
"Ravi" wrote:
> Did you put a Default value before making clearing the prompt text?
> --
> Thanks
> Ravi
>
> "Ramani" wrote:
> > Hi,
> > How do i make parameters invisible.
> > Scenario:
> > I am doing the dynamic sorting. I put 2 textboxes next to the table header
> > asc/desc
> > and am doing osrting sending values by parameters and reloading the report
> > with sorted options.
> > But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> > shown on the report.
> > When i try making the prompt blank - it gives me an error that the parameter
> > is only read only.
> > Please help
> > Thanks
> > Ram|||I solved this problem by upgrading the server to at least the service pack 1
of reporting services. Then the "readonly" error no longer appears. It is to
my knowledge the only way to get rid of that ?&%$&* message.
To make sure you have the right version of Reporting Services, go to the
base URL for the report server (typically http://servername/reportserver/. At
the bottom of the browser page will be the version number:
Microsoft SQL Server Reporting Services Version 8.00.743.00 <- this is
old
Microsoft SQL Server Reporting Services Version 8.00.878.00 <- this is
SP1
Hope this helps.
"Ramani" wrote:
> Yes, I did. I set the default values.
> "Ravi" wrote:
> > Did you put a Default value before making clearing the prompt text?
> >
> > --
> > Thanks
> > Ravi
> >
> >
> > "Ramani" wrote:
> >
> > > Hi,
> > > How do i make parameters invisible.
> > > Scenario:
> > > I am doing the dynamic sorting. I put 2 textboxes next to the table header
> > > asc/desc
> > > and am doing osrting sending values by parameters and reloading the report
> > > with sorted options.
> > > But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> > > shown on the report.
> > > When i try making the prompt blank - it gives me an error that the parameter
> > > is only read only.
> > > Please help
> > > Thanks
> > > Ram|||I have sp2 installed .. will that be fine ?
"Eric" wrote:
> I solved this problem by upgrading the server to at least the service pack 1
> of reporting services. Then the "readonly" error no longer appears. It is to
> my knowledge the only way to get rid of that ?&%$&* message.
> To make sure you have the right version of Reporting Services, go to the
> base URL for the report server (typically http://servername/reportserver/. At
> the bottom of the browser page will be the version number:
> Microsoft SQL Server Reporting Services Version 8.00.743.00 <- this is
> old
> Microsoft SQL Server Reporting Services Version 8.00.878.00 <- this is
> SP1
> Hope this helps.
> "Ramani" wrote:
> > Yes, I did. I set the default values.
> >
> > "Ravi" wrote:
> >
> > > Did you put a Default value before making clearing the prompt text?
> > >
> > > --
> > > Thanks
> > > Ravi
> > >
> > >
> > > "Ramani" wrote:
> > >
> > > > Hi,
> > > > How do i make parameters invisible.
> > > > Scenario:
> > > > I am doing the dynamic sorting. I put 2 textboxes next to the table header
> > > > asc/desc
> > > > and am doing osrting sending values by parameters and reloading the report
> > > > with sorted options.
> > > > But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> > > > shown on the report.
> > > > When i try making the prompt blank - it gives me an error that the parameter
> > > > is only read only.
> > > > Please help
> > > > Thanks
> > > > Ramsql

Invisible controls durig runtime

Hi,
Do you know why when I run my ssis packages in the dev machine, the diagrams are not visible during run time.
I can design the package but not sure why when I start the package, the diagrams in the control flow can not be viewed.
Please note that if I do this on the server, I can see the diagrams during run time.

Thanks

The controls or the diagram indicating the control/data flows? You change nouns between the subject and the body of this post. ("controls" versus "diagrams")

If the latter, are you sure you don't just need to scroll to the left, up, down, or to the right to see them?|||

Hi,

I mean the controls such as flat file source, ...

Thanks

invisible components

I am not sure why when the packages are run, the components dissapear. The only thing I see is the result in the output.

So there is no visual on the tabs.

Thanks

Could you provide more information on this. How do they disappear? Are you sure they are not just scrolled away due to your window layout in the debug mode?

Thanks,

Bob

|||

Simply, the components (flat file source, ole db source, etc...) disaapear as the package is run. I can only see what is happening in the output window.

Thanks

|||

arkiboys wrote:

Simply, the components (flat file source, ole db source, etc...) disaapear as the package is run. I can only see what is happening in the output window.

Thanks

Are you sure they just aren't to the side and that you need to scroll to see them? Look at the scroll bars in the design window and see if they are way off to the side.|||

Yes, I am sure.
There are no scroll bars during the run time.

Thanks

|||

arkiboys wrote:

Yes, I am sure.
There are no scroll bars during the run time.

Thanks

Any way you can capture a screen shot and send it to me via my e-mail listed in my profile?|||

Just sent email,

Thanks

|||

The email address phil_dot_brammer@.gmail.com bounced back.

Are you sure it works please?

|||You'll have to remove "_dot_" and replace it with "."|||That looks like the debug "Call Stack" window... Heck, I guess it even says so in the window title.

What happens when you go to "View -> Designer"?|||

You are right.
I did not notice the call stack window title.

Many thanks