Iterating Data Movement

Hi all

I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.

I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:

1) It runs in a tight loop and consequently degrades system performance enormously.

2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.

Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.

All help is appreciated.

Jays :-)

I think you want a trigger on the table so you know when the data in that table is changed, and copy it accordingly.

|||

Hi Christian

I would like to avoid that if I can.

My package has custom logging that writes a set of audit records each time it is run. This would result in a LOT of these records being written throughout the day when one such set would do.

Thanks

Jays :-)

ist of Databases: do not use master.dbo.sysdatabases...

Is there a SP or view to list databases for a server (2000 & 2005)? SQL 2005
has a view for sysdatabases, but they say it will not be available after thi
s
version.
We have a SP that lists the security for Logins and Databases. It uses
master.dbo.sysdatabases to loop through the databases. We need to modify thi
s
so it is not using the master database tables.
Thanks and God Bless,
ThomBeauxThomBeaux,
The SQL 2005 sys.databases view does not exists on SQL 2000. However,
sp_helpdb works on both releases of SQL Server and is not on the deprecation
list.
If you need the results of sp_helpdb in a table for your processing, do:
CREATE TABLE #db
(name sysname,
db_size nvarchar(13),
owner sysname,
dbid smallint,
created datetime,
status nvarchar (600),
compatibility_level tinyint)
INSERT INTO #db EXEC sp_helpdb
SELECT * FROM #db
Then do what you need with the results.
RLF
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:7B6B13C5-C01B-43C6-A370-14B191A939D2@.microsoft.com...
> Is there a SP or view to list databases for a server (2000 & 2005)? SQL
> 2005
> has a view for sysdatabases, but they say it will not be available after
> this
> version.
> We have a SP that lists the security for Logins and Databases. It uses
> master.dbo.sysdatabases to loop through the databases. We need to modify
> this
> so it is not using the master database tables.
> --
> Thanks and God Bless,
> ThomBeaux|||Thanks...
--
Thanks and God Bless,
ThomBeaux
"Russell Fields" wrote:

> ThomBeaux,
> The SQL 2005 sys.databases view does not exists on SQL 2000. However,
> sp_helpdb works on both releases of SQL Server and is not on the deprecati
on
> list.
> If you need the results of sp_helpdb in a table for your processing, do:
> CREATE TABLE #db
> (name sysname,
> db_size nvarchar(13),
> owner sysname,
> dbid smallint,
> created datetime,
> status nvarchar (600),
> compatibility_level tinyint)
> INSERT INTO #db EXEC sp_helpdb
> SELECT * FROM #db
> Then do what you need with the results.
> RLF
> "ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
> news:7B6B13C5-C01B-43C6-A370-14B191A939D2@.microsoft.com...
>
>

issues writing a deployment script

In this post, i asked if there were any tutorials available on using the RS command-line utility. I have since found some that steered me in the right direction.

This guy demonstrates how to render a report by passing a scripty-VB file with extension .rss into the utility :

http://www.sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

And this guy explains some of the underlying principles behind this marvel of modern software engineering :

http://odetocode.com/Blogs/scott/archive/2005/01/30/975.aspx

What I'm trying to do is build a script that deploys my whole suite of reports to the report server. I've built a test class that mirrors the functionality of my RSS script, because error handling in the RS utility distills every error message to this beauty :
error BC30456: 'Main' is not a member of '_ScriptClass'.

When I run my VB class, I'm consistently getting "http state 401 : Access Denied" when I make an rs call. For instance my create statement

rs.CreateReport(report, serverPath, True, definition, Nothing)

and K. Scott Allen's simple

rs.GetSystemPermissions()

Both return 401. I'm assuming there are a dozen layers of security I have yet to uncover in my quest. My question : why do I have permissions to do this through my browser but not in code? And then, which specific hoops must i jump through in order to give myself access to do this?

I've discovered i can dodge this problem by running the command prompt window as a user who has Administrator privileges on the server. This is what my shortcut looks like :

%windir%\system32\runas.exe /profile /user:DOMAIN\AdminUser /savecred %windir%\system32\cmd.exe

Of course the preferred way to accomplish this would be to give myself access without giving myself Admin privileges. Any thoughts?

|||What is the command line you are using to execute rs.exe?|||rs -i DeployAllFinancialReports.rss -s http://ServerName/ReportServer

Issue installing SQL Server Express on Visual Studio Beta 2

Hi,

I have installed VS 2005 Beta 2 (Team System) using a DVD available from MS TEch Ed. This also installs SQL Server 2005 Express edition along with other tools. This installation is done on a vpc.

However when I try to install SQL Server Express Manager over this installation, I get "The installer package could not be opened. Contact the vendor to make sure to verify that the Windows installer package is valid".

Is there a solution to get this resolved ?

Thanks in advance !
It likely that you need to download the file again. Also not that you may need to uninstall the .Net Framework and re-install the version that SQL Server shipped in its June CTP as the NetFx version between Vs Beta 2 and SQL CTP June is different.

-Euan