Launch SSIS package with SQL Event

Is it possible to launch an SSIS package after a SQL event takes place? I need to run a package after a customer order is placed. Can a trigger in SQL launch the package?

You can use xp_cmdshell to call DTEXEC. You could also set up a job for the package and call sp_start_job.

See this link for some more detail and some other options: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

Last straw

Upgraded SQL 2005 to Dec CTP sp2. Had previously run Nov CTP and installed the Sharepoint add-in. Had that all working.

Now trying to add the Dec CTP sharepoint add-in. But it won't complete. Keeps rolling back and then ending with dialog '.... setup was interrupted'

Anyone else seeing problems with this Dec CTP install?

I was geting the same error when I was trying to install under an user with not enough rights. I loged in with the local Administrator and the installation works well.

I’m not sure it is the same scenario for you, but may be can help you …

Dan

|||

Thanks for the advice, but not a matter or permissions. Hmm, you said local admin, I have only tried domain accounts thus far. I'll try a local computer account and see what that does. Nope, no luck with that either. Very frustrating since I had the Nov CTP working and have my sharepoint 3.0 server running in integrated mode with reporting services. Since I put in the Dec CTP sp2 for SQL server, that is broken too.

I'd hate to reinstall since significant sharepoint structure was already in place.

|||

When you run the SharePointRS.msi, it creates a log file in the Temp folder - RS_SP_<number>.log.

Can you send us the latest RS_SP_<number>.log?

thanx,
Prash

|||

I don't get a log in the C:\temp folder. And I'm running this from C:\temp in fact. It always 'rollsback' near the end of the install. I have tried using win installer cleanup to remove the previous CTP of this addin. No help. The CTP sp2 for SQL 2005 seemed to work fine.

the only newly created log file is located in C:\Program Files\Common Files\Microsoft Shared\web server extensions\LOGS and it's called WISMDCN6-20070108-0738.log. It's about 775 kb and so I don't want to paste it here. I also see several other files generated in the same folder right at the time of the aborted install. They are: datalast.dat; data024.dat; watsonrc.wat; data023.dat. Are any of these files of concern? Is there some way to attach these files after zipping them. I cannot see pasting the contents of these large files into a message here.

I think that the install of December CTP sp2 did the same thing that the Nov CTP did to my WSS 3 install. Namely, made the website inaccessible. That is still true. I've not revisited the troubleshooting notes on Brian Welckers blog about this. Perhaps until I get this resolved I will have no hope of installing SharepointRS.msi?

If I knew how to do attachments to forum messages I could also send along a couple of screenshots. Or should I just use your email address?

tia

|||

The location of the file should generally be in the <Drive>:\Documents and Settings\_AWTEST\Local Settings\Temp folder.

You should be able to cut-paste the contents of the latest RS_SP_<number>.log file into the reply.

Let me know if this does not help and I will contact you offline.

thanx,
Prash

|||

Ah, now that directory has what we are looking for. Okay, here is the text of the log file:

User: Administrator
Installing Report Server feature.
Calling copyappbincontents command.

copyappbincontents command completed successfully.
Adding Report Server feature to farm.
Installed Report Server feature.
Activating Report Server feature to root level site collections.
Activating feature in web app 'SharePoint - 80'
Activating feature to root site collection: http://wismdcn6
Install error: A duplicate name "Report Builder Report" was found.
Uninstalling Report Server feature.
Uninstalling Report Server feature.
Report Server feature was uninstalled.
Stopping W3SVC service.
Removing Report Server binary files
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories8\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories4\_app_bin.
Removing web.config updates
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories8\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories8\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories4\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories4\web.config saved with entries removed.
Removing DocIcon.xml file updates
Starting W3SVC service.
SSRSCustomActionError: A duplicate name "Report Builder Report" was found.

I reviewed a half dozen previous logs from other failed attempts and they all have the same exact entries right up to the failure point.

|||

Can you delete the "Report Builder Report" and "Report Data Source" content types from the Document Libraries to which you added them and then re-try the CTP3 install?

Hope this helps.

thanx,
Prash

|||Well, that will depend on whether or not I get the Sharepoint server working again. Have not had the time to go back and start working on that. I seen to have more than one sharepoint server now after installing the Dec sp2 ctp. Any tips on getting my WSS3 server working again?|||

Are you now getting the following error when browsing to any SharePoint site :
Server Error in '/' Application.
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed.
OR
CS0101: The namespace 'Resources' already contains a definition for 'ReportServer'

If yes, then check the following in Internet Information Services Manager for each SharePoint web site:
- Explore into <Web site>\App_GlobalResources
- Do you see any Reportserver.<culture>.resx files (where culture is a string like EN / FR/ CS etc.)?
- If yes, then delete all Reportserver.<culture>.resx except for ReportServer.resx file.

If not, can you give me more details on the error you are seeing when you imply your SharePoint server is not working?

Hope this helps.

thanx,
Prash

|||

Removing the <culture>.resx files did the trick. I was able to open my WSS 3.0 site again. And I drilled down into every document library I could find that had report builder or report data source document types. Could be I missed one. Here is the same error log I am still getting when running SharePointRS.msi. Apparently this is what leaves those <culture>.resx files in its wake. They were back and prevented the WSS 3 site from opening again.

Is there any way I can test to be absolutely sure I removed all the old reporting services document types?

I tried once again after a very thorough removal of report types in document libraries. Still no go. Same error log as below and same problem with .resx files left behind.

thanks, and please stick with me to get this resolved.

--

User: dotwcd
Installing Report Server feature.
Calling copyappbincontents command.

copyappbincontents command completed successfully.
Adding Report Server feature to farm.
Installed Report Server feature.
Activating Report Server feature to root level site collections.
Activating feature in web app 'SharePoint - 80'
Activating feature to root site collection: http://wismdcn6
Install error: A duplicate name "Report Builder Report" was found.
Uninstalling Report Server feature.
Uninstalling Report Server feature.
Report Server feature was uninstalled.
Stopping W3SVC service.
Removing Report Server binary files
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\80\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\29408\_app_bin.
Removed binary files from C:\Inetpub\wwwroot\wss\VirtualDirectories\21144\_app_bin.
Removing web.config updates
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\29408\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\29408\web.config saved with entries removed.
Removing entries from C:\Inetpub\wwwroot\wss\VirtualDirectories\21144\web.config.
C:\Inetpub\wwwroot\wss\VirtualDirectories\21144\web.config saved with entries removed.
Removing DocIcon.xml file updates
Starting W3SVC service.
SSRSCustomActionError: A duplicate name "Report Builder Report" was found.

|||Did I mention that Microsoft Office Forms server is installed on this server too? seems to be difficult to remove though after the sp2 ctp is installed.|||

Great! The forms server failed uninstall completely removed the sharepoint sites. I'd like to know how to recover the site now. I'm about ready to toss the December sp2 CTP out the window.

|||

I setup a repro machine and here are the steps I took to get CTP3 working:
- Uninstall Reporting Services Add-in for SharePoint CTP2.
- Install Reporting Services Add-in for SharePoint CTP3. This should now run to complet
- You may still run into the Server error due to the ReportServer.<culture>.resx files (This is an Add-In setup bug we are working on fixing in the RTM release.). If yes, then as before a remove the appropriate ReportServer.<culture>.resx files. Do not remove the ReportServer.resx file.
- Repeat the configuration steps from the Central Administration site to re-integrate the Report Server and SharePoint.

About restoring - not sure how effective this would be in your case, however you may want to check out the restore command provided by SharePoint's stsadm.exe tool ( in the \Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN).

thanx,
Prash

|||Hi Prash, I have downloaded the December CTP which I gather is the CTP2 (33.8MB) which is giving me the same error in the log file "Report Builder Report" duplicate. As this is a new install (Fresh install of WSS 3.0, SQL2005 with SP2, just configured report server to SharePoint Integration mode) On the first attempt installing the Report Viewer Add-in, it rolled back right at the end. I have no content on the SharePoint site, so I am at a loss as to what "Report Builder Report" and "Report Data Source" I can delete. Any Pointers would be fantastic.

You keep mentioning the CTP3. Is this available for download, if so where do I find this (URL Please Please Please). Or does the same CTP download page continue to update with the latest CTP version.

I have setup a system running what must have been the November CTP release (3MB) and have had no problems with it. But I really need some help in getting either the CTP3 or December version past this "Duplicate Report Builder Report" Error.

Thanks heaps,

Sean

Last SQL Statement run on SQL Server

Hi All,

Im just wondering if there is any way to get the last SQL Stmt or SP that was run on the server without using the SQL Profiler or third party software.

Thanks and have a nice day to all.No. There is really no easy way to determine this information. And do you want last SQL statement or SP executed by any connection? Or is it the last on the server? In some cases, you could use DBCC INPUTBUFFER with the currently executing or inactive SPIDs but this is not entirely accurate. And if the connections have disconnected from the server then there is really no record. You could look at the cached plan entries but there is no way to relate them in order of time and you will miss statements that were not cached for example. What are you trying to do with this information though? What do you want to determine from knowing the last command that was sent to the server?|||

Thanks for the reply.

Just only wondering if there is any alternate way using sql stmt on SQL Query Analyzer by not using the SQL profiler to get the SQL stmts.

Last Run Time History

Is there a way to store the history of every subscription to a report?
Currently, it only stores the last run time.Presently no. You could write a custom delivery extension that logged this
information for each delivery but that is an involved process.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jvmiller" <jvmiller@.discussions.microsoft.com> wrote in message
news:CFD19093-C5EE-4D9E-BC4F-0C4CBDE84D4A@.microsoft.com...
> Is there a way to store the history of every subscription to a report?
> Currently, it only stores the last run time.

last is not a recognised function name

Hi all,
ive tried to run a query in a view and am getting this error message:
'last' is not a recognised function name
i was sure that last was used in sql, how can i get the same result if it wont accept 'last'?
greguse MAX

LAST is a microsoft access function|||oh yeah!! forgot the syntax changed-

cheers

greg

Last DBCC checkdb date

Is there a way to find out when was DBCC checkdb run on a database in SQL
2000 and SQL 2005
Thanks,
Ranga
The last successful CHECKDB is found in the errorlog on 2005. SQL Server reports this at startup as
a type of "Last known Good".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:2D08F154-B9D1-4D76-982E-51B175A9C57A@.microsoft.com...
> Is there a way to find out when was DBCC checkdb run on a database in SQL
> 2000 and SQL 2005
> Thanks,
> Ranga
|||Hello,
All the DBCC CHECKDB executions will be having an entry in SQL Server error
log. AFAIK, there is no system views/catalogues stores this information.
Thanks
Hari
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:2D08F154-B9D1-4D76-982E-51B175A9C57A@.microsoft.com...
> Is there a way to find out when was DBCC checkdb run on a database in SQL
> 2000 and SQL 2005
> Thanks,
> Ranga

Last DBCC checkdb date

Is there a way to find out when was DBCC checkdb run on a database in SQL
2000 and SQL 2005
Thanks,
RangaThe last successful CHECKDB is found in the errorlog on 2005. SQL Server rep
orts this at startup as
a type of "Last known Good".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:2D08F154-B9D1-4D76-982E-51B175A9C57A@.microsoft.com...
> Is there a way to find out when was DBCC checkdb run on a database in SQL
> 2000 and SQL 2005
> Thanks,
> Ranga|||Hello,
All the DBCC CHECKDB executions will be having an entry in SQL Server error
log. AFAIK, there is no system views/catalogues stores this information.
Thanks
Hari
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:2D08F154-B9D1-4D76-982E-51B175A9C57A@.microsoft.com...
> Is there a way to find out when was DBCC checkdb run on a database in SQL
> 2000 and SQL 2005
> Thanks,
> Ranga

Last DBCC checkdb date

Is there a way to find out when was DBCC checkdb run on a database in SQL
2000 and SQL 2005
Thanks,
RangaThe last successful CHECKDB is found in the errorlog on 2005. SQL Server reports this at startup as
a type of "Last known Good".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:2D08F154-B9D1-4D76-982E-51B175A9C57A@.microsoft.com...
> Is there a way to find out when was DBCC checkdb run on a database in SQL
> 2000 and SQL 2005
> Thanks,
> Ranga|||Hello,
All the DBCC CHECKDB executions will be having an entry in SQL Server error
log. AFAIK, there is no system views/catalogues stores this information.
Thanks
Hari
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:2D08F154-B9D1-4D76-982E-51B175A9C57A@.microsoft.com...
> Is there a way to find out when was DBCC checkdb run on a database in SQL
> 2000 and SQL 2005
> Thanks,
> Ranga

Last Date of previous Month

ive been trying very unsucessfully to try and get the last date of the
previous month,
ie if i run the query today i want the result to be 31/01/2006
declare @.d datetime
set @.d='20060201'
select DATEADD(day,-DATEPART(day,@.d),@.d)|||print dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:168DF5B1-BCE5-4601-802A-8FF10CAD3F2E@.microsoft.com...
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006
>|||Have you looked up how to use a Calendar table? The table could
include a code for last (business?) day of each month, so you can pull
out a set of such dates for reports. I
Your other options are computational. There is the "COBOL method" and
the "UNIX method". The Cobol guys think of dates as character strngs,
and they will build a date with concatendation and extraction
functions. The bad news is that they will probably use a local date
format instead of ISO-8601 style.
The UNIX method uses extraction functions and temporal math -- go to
the first of this month and subtract 1 day. Since Sybase/SQL Server
use a floating point number for date time, it can be pretty fast for
one-at-time processing.|||SELECT DATEADD(d,- 1,CAST(CONVERT(char(6),CURRENT_TIMESTAMP
,112) + '01'
as smalldatetime))
Here are a few other little tricks too:
http://sqlnerd.blogspot.com/2005/06...sql-tricks.html
*mike hodgson*
http://sqlnerd.blogspot.com
Peter Newman wrote:

>ive been trying very unsucessfully to try and get the last date of the
>previous month,
>ie if i run the query today i want the result to be 31/01/2006
>
>|||Why go to all the effort and extra storage of building a calendar table when
you can simply use one of SQL Servers built in functions...
dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138838149.462000.53340@.o13g2000cwo.googlegroups.com...
> Have you looked up how to use a Calendar table? The table could
> include a code for last (business?) day of each month, so you can pull
> out a set of such dates for reports. I
> Your other options are computational. There is the "COBOL method" and
> the "UNIX method". The Cobol guys think of dates as character strngs,
> and they will build a date with concatendation and extraction
> functions. The bad news is that they will probably use a local date
> format instead of ISO-8601 style.
> The UNIX method uses extraction functions and temporal math -- go to
> the first of this month and subtract 1 day. Since Sybase/SQL Server
> use a floating point number for date time, it can be pretty fast for
> one-at-time processing.
>|||>>Why go to all the effort and extra storage of building a calendar table wh
en you can simply use one of SQL Servers built in functions...<<
1) Portability. Temporal functions vary quite a bit from product. Did
you mean to avoid using the Standard CURRENT_TIMESTAMP with the
proprietary getdate()?
2) Standards conformance. Did you notice that you assumed the date
format is "dd-mm-yyyy" because you are British? An American would
assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
'yyyy-mm-dd" is the only format allowed in the ISO standards
3) Your code will have problems with the time part of a DATETIME. the
last day of a month should end at the last possible fraction of a
second to be sure to catch everything. The standard SQL version would
be:
CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
|| EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
|| '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
4) The Calendar table is a more general tool for other temporal
problems. For a small extra effort now, he can solve a lot of problems
in the future.
Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
/ <<insert cowboy coding fad here>>. the current batch programmers
think only in terms of the immediate problem. The results are bad.
It is not just duplicated code that cannot be found to be refactored.
It is a lot of code that does the same task in many different ways.
Arrrgh!|||I'm with Joe on this one. Solve the problem with data rather than
code. Then you can get the last day of last month, the last business
day of last month, the first day of this month, the first business
day... and so on.
Best
Payson
Peter Newman wrote:
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006|||> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
Oh dear, not checked the product specs lately?
I use the format DD MMMMMMMM YYYY, example 1 February 2006; so there is NO
confusion at all. Also, I remove the time component entirely and reset back
to midnight on casting to smalldatetime
print '1 ' + datename( month, getdate() ) + ' ' + datename( year,
getdate() )
Even a beginner would **know** to look at the manual and not guess how
things work, how come you make such fundemental mistakes?

> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
That was not the posters requirement, they required a date without the time
component, which we don't have in SQL Server, so within SQL Server we use
00:00:00.000 for when no time component is required - again, even a beginner
would **know** that.
The syntax you give doesn't even work in SQL Server and as this is a
MICROSOFT SQL SERVER forum I would appreciate it if you at least point out
the differences when you post your unusable rubbish.

> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
A very big overhead for a simple problem, again, stop bloating up the design
to make more money. Keep things Simple Sweet and the design will be more
supportable and less likely to be buggy.

> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
Seriously, you need to get some real programming experience because you
don't seem to have any, either that or just stick to logical design and live
the programming to people who have been professionally trained and not
picked their knowledge up from reading books.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138908162.308682.73220@.f14g2000cwb.googlegroups.com...
> 1) Portability. Temporal functions vary quite a bit from product. Did
> you mean to avoid using the Standard CURRENT_TIMESTAMP with the
> proprietary getdate()?
> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
>|||I frequently use calendar table for calculations like '5 business days
later' or
'now many business daya are there between 15-jan-2006 and 25-jan-2006'.
Ignoring any theories, with calendar table my queries sometimes perform
better.
I don't take that for granted and like to re-check that I'm using
efficient queries from time to time.
In this particular case, I took 500K rows from a production table. I
ran this query:
set statistics time on
go
select min(dateadd( day, -1, cast( '1 ' + datename( month, order_dt ) +
' ' +
datename( year, order_dt ) as smalldatetime ) )) mmin, max(dateadd(
day, -1, cast( '1 ' + datename( month, order_dt ) + ' ' +
datename( year, order_dt ) as smalldatetime ) ) ) mmx
from orders
...
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 17423 ms.
Then I built a calendar table:
create table month_calendar(num int, date_from datetime, date_to
datetime)
declare @.d1 datetime
declare @.d2 datetime
declare @.n int
set @.d1='19700101'
set @.d2='19700201'
set @.n=1
while @.d1 < '20060505'
begin
insert into month_calendar values(@.n, @.d1,@.d2)
set @.n=@.n+1
set @.d1 = @.d2
set @.d2 = dateadd(month, 1, @.d2)
end
go
select min(date_from), max(date_to) from month_calendar
select count(*) from month_calendar
create unique clustered index uci on month_calendar(date_from)
select * from month_calendar
then I ran another query:
select min(m.num), max(m.num)
--select count(*)
from month_calendar m join orders on date_from<=order_dt and
order_dt<date_to
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 1010 ms.
(both queries were rerun several times to account for warm/cold cache,
random fluctuations,
whatever else)
So for this particular scenario in this particular environment I'd
definitely go for a calendar table, if and only if the query is run
frequently.
I'm not saying calendar tables are the best choice in all the cases.
Any comments?

Large Volumes of Varchar Data - Design Advice

Hello all,

I have recently been task with rewriting a database that holds large volumes of data, whilst ensuring that query can be run in optimal time. Having never really delved into this sort of thing before, I hoped you guys might be able to offer some advice and guidance.

The design I have inherited is based around 2 main tables:

[captured_traps]
[id] [int] IDENTITY (1, 1) NOT NULL
[snmp_version] [int] NULL
[community_name] [varchar] (255)
[packet_type] [varchar] (50)
[oid] [varchar] (500)
[source_ip] [varchar] (15)
[generic] [int] NULL
[specific] [int] NULL
[time_stamp] [varchar] (15)
[trap_entered] [datetime] NULL
[status] [int] NULL

[captured_varbinds]
[id] [int] IDENTITY (1, 1) NOT NULL
[captured_trap_id] [int] NOT NULL
[varbind_oid] [varchar] (500)
[varbind_text] [varchar (500)

The relationship between the two tables is on the "captured_traps (id)" to "captured_varbinds (captured_trap_id)". Currently the "captured_traps" table contains around 350 million rows, the "captured_varbinds" table contains around 900 million rows.

Now as you can probably gather this model runs like a...well it sort of hobbles more than runs hence the need to redesign.

My current thoughts on this are:

- Normalising all varchars - there is alot of duplicate values in most of the varchar fields.
- Full Text Indexing

However beyond that I am not sure which route to go down. After googling for most of today I have come across a number of "solutions" however I do not want to go steaming down the track of one of these to discover that it is fatally flawed somewhere.what fields do you currently search and join on? are there indices there? if so have they been maintained? are they in seperate filegroups and on seperate disks than the data? Any thought given to partititioning?|||As far as searches are concerned users may search any field or combination of fields. A typical search example might be:

snmp_version = 2
oid = 1.2.5.67345.12.34
trap_entered > a UTC date time
trap_entered < a UTC date time

This would be expected to return all data from captured_traps and all associated captured_varbinds with the relationship on "captured_traps (id)" to "captured_varbinds (captured_trap_id)".

or a user might search on the varbind table instead, and expect to see all traps with a specific varbind_oid value, for example

varbind_oid = 2.3.4.123.6.4.5.8766
trap_entered > a UTC date time
trap_entered < a UTC date time

This would be expected to return all traps within the given date range that have a varbind with of the given type

The previous owner of this DB only put indexes on the two id columns

As far as filegroups and partioning are concerned, I have encountered these whilst searching for solutions - and if viable may be implemented.

Basically I have a clean slate here, so i am open to any and all suggestions|||what version of SQL Server are you running?

We will also need to see the indexing methodology...do you know how to script those?

Also, how is all the data accessed? Stored Procedures only?

What do you get when you do sp_depends on those 2 tables?|||As mentioned earlier I have a clean slate to work from, so version can be anything from 2000sp4 to 2005. Which ever is better suited to the task.

Data will only ever be accessed via stored procedures, however due to the fact that searches can be on any fields or combination of fields the main search stored procedure will build and execute the SQL dynamically (unless anybody has any better ideas)

As far as indexes are concerned, this are the full scripts for the current model

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_snmp_captured_varbinds_snmp_captured_traps]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[snmp_captured_varbinds] DROP CONSTRAINT FK_snmp_captured_varbinds_snmp_captured_traps
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snmp_captured_traps]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[snmp_captured_traps]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snmp_captured_varbinds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[snmp_captured_varbinds]
GO

CREATE TABLE [dbo].[snmp_captured_traps] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[snmp_version] [int] NULL ,
[community_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[packet_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oid] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[source_ip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[generic] [int] NULL ,
[specific] [int] NULL ,
[time_stamp] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[packet_data] [image] NULL ,
[trap_entered] [datetime] NULL ,
[status] [int] NULL ,
[support_reference] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[listener_event_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[snmp_captured_varbinds] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[trap_id] [int] NULL ,
[varbind_oid] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varbind_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_traps] WITH NOCHECK ADD
CONSTRAINT [PK_snmp_captured_traps] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_varbinds] WITH NOCHECK ADD
CONSTRAINT [PK_snmp_captured_varbinds] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_traps] WITH NOCHECK ADD
CONSTRAINT [DF_snmp_captured_traps_status] DEFAULT (1) FOR [status]
GO

CREATE INDEX [IDX_snmp_captured_varbinds_trap_id] ON [dbo].[snmp_captured_varbinds]([trap_id]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[snmp_captured_varbinds] ADD
CONSTRAINT [FK_snmp_captured_varbinds_snmp_captured_traps] FOREIGN KEY
(
[trap_id]
) REFERENCES [dbo].[snmp_captured_traps] (
[id]
) ON DELETE CASCADE
GO

Edit : in the new model the packet_data column of the snmp_captured_traps table is not required|||If you are using sprocs, why would you need dynamic sql?

In any case, you only have 1 index...

How do the rows get added to these tables, and what is the rate of inserts

You will need more indexes, and probably need to utilize index intersection instead of of coverering indexes

With the additional indexes you are probably scanning the tables...|||Well the dynamic SQL is an assumption I am carrying across from the current design. Currently there is no stored proc for doing searches, the application that sits ontop of this database builds an SQL string based on user input and then executes it. Something along the lines of:

SELECT x,y,z FROM snmp_captured_traps WHERE a=b AND c=d AND e>f AND g<h

I dont like the fact that that the SQL string is controlled by the app, it also means that remote users have to have select priveledges on the necessary tables. Hence the idea of moving that code logic into a stored procedure. I am open to any ideas/suggestions on how to do this using an alternative system

As far as getting data into the tables at the moment there is simply a series of simple insert stored procedures, e.g.

CREATE PROCEDURE add_captured_trap
(
@.snmp_version int,
@.community_name varchar(255),
@.packet_type varchar(50),
@.oid varchar(500),
@.source_ip varchar(15),
@.generic int,
@.specific int,
@.trap_entered datetime,
@.packet_data image,
@.listener_event_id int
)
AS BEGIN TRANSACTION

INSERT INTO snmp_captured_traps (
snmp_version,
community_name,
packet_type,
oid,
source_ip,
generic,
specific,
trap_entered,
packet_data,
listener_event_id
)
VALUES (
@.snmp_version,
@.community_name,
@.packet_type,
@.oid,
@.source_ip,
@.generic,
@.specific,
@.trap_entered,
@.packet_data,
@.listener_event_id
)

select scope_identity();

COMMIT TRANSACTION
GO

CREATE PROCEDURE add_captured_varbind
(
@.trap_id int,
@.varbind_oid varchar(500),
@.varbind_text text
)
AS BEGIN TRANSACTION

INSERT INTO snmp_captured_varbinds (
trap_id,
varbind_oid,
varbind_text
)
VALUES (
@.trap_id,
@.varbind_oid,
@.varbind_text
);

COMMIT TRANSACTION
GO

As you can see nothing to clever, and it worked ok until the load became to much.

As far as volumes of inserts, on average the system receives 50 traps a second, each trap has an average of 2 varbinds. So thats 150 inserts

One other thing, everything I have said so far seems to indicate that I want to redesign from scratch. Thats not entirely true, if there is something that I can apply to the current model in order to get things running quicker then that is my preferred short term option

Large Text

I need to run a usp (user defined stored procedure) with a very large input text variable

probably 10,000-20,000 characters.

what variable can I use?

thanks in advanceUse varchar(max)|||

Hi,

If you are using sql server 2000 then use 'text' datatype, if you are using sql server 2005 then you can use 'varchar(max)' as Arnie Rowland suggested.

Ravi

large table update

hello, i have a following problem: I have got 130 million row TABLEA
and I need to run this update:
UPDATE TABLEA
SET COLUMNA=(SELECT COLUMNB FROM TABLEB WHERE condition)

I suppose that for every row from TABLEA (80 million times) select statement must be executed, which is terribly slow. Isn't there a better way to write this update? Thank youCould you provide the WHERE clause in detail, pls?|||Originally posted by Coolberg
Could you provide the WHERE clause in detail, pls?

WHERE column from TABLEA=column FROM TABLEB

Large Sample Database required for testing

We are creating a search engine in MS SQL using FullText etc.

we need to run some tests against a database that is near to realistic data. We have been able to generate a database of thousands of usernames, first names etc. but we can't figure out where to obtain a database full of data that contains product descriptions, product titles etc.

Does anyone have any idea where we could obtain (or even buy if need be) a large database (over 10,000 records) of sample data that we could run test queries against, so that we can optimise the queries, provide execution time estimates etc.

We have downloaded the adventureworks sample database, but there are not nearly enough records in it.

Any help would be much appreciated.

Create one on your won, there are many data generation tools outside there generating either random data or data based on your own data while obfuscating it. The MS product for this is Visual Studio for Database professionals which has a great data generator.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Large report killing server

We have a report that needs to run 2-3 times day that is fairly large.
The need is to have it in Excel format and it is about 20 columns by
7-8,000 rows. The data is returned by the db server fairly readily, but
when Reporting Services tried to make it an Excel file, the render
object on the web front end timesout. I checked the server during this
time and the CPU usage is 100% and memory usage climbs. Is it normal
for RS to choke on a large report like this?Do you have SP1 installed?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"no one" <noone@.yahoo.com> wrote in message
news:420E4351.E3D5A5CA@.yahoo.com...
> We have a report that needs to run 2-3 times day that is fairly large.
> The need is to have it in Excel format and it is about 20 columns by
> 7-8,000 rows. The data is returned by the db server fairly readily, but
> when Reporting Services tried to make it an Excel file, the render
> object on the web front end timesout. I checked the server during this
> time and the CPU usage is 100% and memory usage climbs. Is it normal
> for RS to choke on a large report like this?
>|||SP1 for what? Reporting Services? SQL Server? Win2k?
"Bruce L-C [MVP]" wrote:
> Do you have SP1 installed?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "no one" <noone@.yahoo.com> wrote in message
> news:420E4351.E3D5A5CA@.yahoo.com...
> > We have a report that needs to run 2-3 times day that is fairly large.
> > The need is to have it in Excel format and it is about 20 columns by
> > 7-8,000 rows. The data is returned by the db server fairly readily, but
> > when Reporting Services tried to make it an Excel file, the render
> > object on the web front end timesout. I checked the server during this
> > time and the CPU usage is 100% and memory usage climbs. Is it normal
> > for RS to choke on a large report like this?
> >|||SP 1 for Reporting Services. Before digging too much more into this I would
suggest installing SP1. Note that you need to install it at the server and
wherever you develop your reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"no one" <noone@.yahoo.com> wrote in message
news:420FB123.1423F27A@.yahoo.com...
> SP1 for what? Reporting Services? SQL Server? Win2k?
> "Bruce L-C [MVP]" wrote:
>> Do you have SP1 installed?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "no one" <noone@.yahoo.com> wrote in message
>> news:420E4351.E3D5A5CA@.yahoo.com...
>> > We have a report that needs to run 2-3 times day that is fairly large.
>> > The need is to have it in Excel format and it is about 20 columns by
>> > 7-8,000 rows. The data is returned by the db server fairly readily,
>> > but
>> > when Reporting Services tried to make it an Excel file, the render
>> > object on the web front end timesout. I checked the server during this
>> > time and the CPU usage is 100% and memory usage climbs. Is it normal
>> > for RS to choke on a large report like this?
>> >
>|||The server guys tell me they installed it at most 6 weeks ago. Did SP1 come out
after that?
"Bruce L-C [MVP]" wrote:
> SP 1 for Reporting Services. Before digging too much more into this I would
> suggest installing SP1. Note that you need to install it at the server and
> wherever you develop your reports.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "no one" <noone@.yahoo.com> wrote in message
> news:420FB123.1423F27A@.yahoo.com...
> > SP1 for what? Reporting Services? SQL Server? Win2k?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Do you have SP1 installed?
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "no one" <noone@.yahoo.com> wrote in message
> >> news:420E4351.E3D5A5CA@.yahoo.com...
> >> > We have a report that needs to run 2-3 times day that is fairly large.
> >> > The need is to have it in Excel format and it is about 20 columns by
> >> > 7-8,000 rows. The data is returned by the db server fairly readily,
> >> > but
> >> > when Reporting Services tried to make it an Excel file, the render
> >> > object on the web front end timesout. I checked the server during this
> >> > time and the CPU usage is 100% and memory usage climbs. Is it normal
> >> > for RS to choke on a large report like this?
> >> >
> >|||No, SP1 is older than 6 weeks, but it included some fixes for rendering in
Excel that sound like what your problem is.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"no one" <noone@.yahoo.com> wrote in message
news:42100F1E.8F4DD308@.yahoo.com...
> The server guys tell me they installed it at most 6 weeks ago. Did SP1
> come out
> after that?
> "Bruce L-C [MVP]" wrote:
>> SP 1 for Reporting Services. Before digging too much more into this I
>> would
>> suggest installing SP1. Note that you need to install it at the server
>> and
>> wherever you develop your reports.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "no one" <noone@.yahoo.com> wrote in message
>> news:420FB123.1423F27A@.yahoo.com...
>> > SP1 for what? Reporting Services? SQL Server? Win2k?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Do you have SP1 installed?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "no one" <noone@.yahoo.com> wrote in message
>> >> news:420E4351.E3D5A5CA@.yahoo.com...
>> >> > We have a report that needs to run 2-3 times day that is fairly
>> >> > large.
>> >> > The need is to have it in Excel format and it is about 20 columns by
>> >> > 7-8,000 rows. The data is returned by the db server fairly readily,
>> >> > but
>> >> > when Reporting Services tried to make it an Excel file, the render
>> >> > object on the web front end timesout. I checked the server during
>> >> > this
>> >> > time and the CPU usage is 100% and memory usage climbs. Is it
>> >> > normal
>> >> > for RS to choke on a large report like this?
>> >> >
>> >
>|||Go to http://yourserver/Reportserver
It should show version 8.00.878.00 if you have SP1 installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"no one" <noone@.yahoo.com> wrote in message
news:42100F1E.8F4DD308@.yahoo.com...
> The server guys tell me they installed it at most 6 weeks ago. Did SP1
come out
> after that?
> "Bruce L-C [MVP]" wrote:
> > SP 1 for Reporting Services. Before digging too much more into this I
would
> > suggest installing SP1. Note that you need to install it at the server
and
> > wherever you develop your reports.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "no one" <noone@.yahoo.com> wrote in message
> > news:420FB123.1423F27A@.yahoo.com...
> > > SP1 for what? Reporting Services? SQL Server? Win2k?
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Do you have SP1 installed?
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "no one" <noone@.yahoo.com> wrote in message
> > >> news:420E4351.E3D5A5CA@.yahoo.com...
> > >> > We have a report that needs to run 2-3 times day that is fairly
large.
> > >> > The need is to have it in Excel format and it is about 20 columns
by
> > >> > 7-8,000 rows. The data is returned by the db server fairly
readily,
> > >> > but
> > >> > when Reporting Services tried to make it an Excel file, the render
> > >> > object on the web front end timesout. I checked the server during
this
> > >> > time and the CPU usage is 100% and memory usage climbs. Is it
normal
> > >> > for RS to choke on a large report like this?
> > >> >
> > >
>|||Bruce L-C [MVP] wrote:
> Go to http://yourserver/Reportserver
> It should show version 8.00.878.00 if you have SP1 installed.
>
Since I have control of our Dev box, I checked it as well. It did not
have SP1. I downloaded and installed. The report still chokes the
server while trying to render with 100% CPU and memory usage climbing
about actual RAM (on this box it is only 512 MB). Looks like the SP
didn't help.|||As an experiment / workaround try rendering as CSV. If that works then that
can be loaded into Excel. It is possible that SP2 might make a difference
but I don't know.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bryon" <blape@.whittmanhart.com> wrote in message
news:O3VThA4EFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Bruce L-C [MVP] wrote:
> > Go to http://yourserver/Reportserver
> >
> > It should show version 8.00.878.00 if you have SP1 installed.
> >
> >
> Since I have control of our Dev box, I checked it as well. It did not
> have SP1. I downloaded and installed. The report still chokes the
> server while trying to render with 100% CPU and memory usage climbing
> about actual RAM (on this box it is only 512 MB). Looks like the SP
> didn't help.|||Bruce L-C [MVP] wrote:
> As an experiment / workaround try rendering as CSV. If that works then that
> can be loaded into Excel. It is possible that SP2 might make a difference
> but I don't know.
>
sp2?|||SP2 is supposed to be release before too long (Feb/March). It is always
possible that they have put in somemore Excel rendering improvements.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bryon" <blape@.whittmanhart.com> wrote in message
news:eOTLcE5EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Bruce L-C [MVP] wrote:
> > As an experiment / workaround try rendering as CSV. If that works then
that
> > can be loaded into Excel. It is possible that SP2 might make a
difference
> > but I don't know.
> >
> sp2?