uninstall SQL: February 2012

Saturday, February 25, 2012

Capturing XML

Ok, this is a broad question. Our system is going to receive xml files from vendors that contain background check results for individuals. We want to capture the xml results in the db for historical purposes and to have a copy of what the vendor sent before we translated the results and updated our system. I was going to store the xml in a table with a text column but wasn't sure if there was a better way to do this. Anyone have any suggestions?

ThanksI think there have a couple of posts recently on this very subject. I think the general consensus is to store the XML in an NText or Text column. Individual contributors have cautioned that these column types require additional "attention" in the form of more frequently scheduled DBCC CHECKDB statements. That has not been my experience, but others have suggested it.

Certainly in your case, since you want to keep a copy of the data received prior to manipulating it, storing it "as-is" is probably a good idea. An alternative might be to store the filename (as a pointer) in the database while saving the XML to a file on either the database server or some other location.

Regards,

hmscott|||Well, you're going to need to use the data too, right?

DECLARE @.xml varchar(1000)

SET @.xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
<InsertSessionData>
<Input>
<SessionID>6F9619FF-8B86-D011-B42D-0C04FC964FF</SessionID>
<Last_Accessed>20030101</Last_Accessed>
<State><somexml></somexml></State>
</Input>
</InsertSessionData>'

DECLARE @.idoc int

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml

SELECT * FROM OPENXML(@.idoc, '/InsertSessionData/Input', 2)
WITH(
SessionID varchar(8000),
Last_Accessed Datetime,
State Text
)|||Brett,

Your point is well taken. However, there are other ways of getting at the data in the documents. We use a VBScript and the MS DOM to strip out the necessary data and store it in the relational tables. We don't keep a copy of the XML docs (no requirement to do so), but if I did, I could simply add a line to insert the text into an archive table for historical purposes.

But I don't use OPENXML to parse the XML itself.

Regards,

hmscott|||Thanks for the ideas. We're going to extract the data we need through java code. The table is strictly for archiving. Kind of an insurance policy if we have problems with our interface application.

Capturing WARNINGS to write to error log?

Hey y'all...

Anyone know how to capture SQL Warnings so I can write them to an error log? I can't seem to find any info on it in Books Online...

I can capture the errors just fine by using @.@.ERROR after a select, but what about warnings such as "Warning: Null value is eliminated by an aggregate or other SET operation."

Thanks!IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'NULL_VALUE'))
EXECUTE msdb.dbo.sp_delete_alert @.name = N'NULL_VALUE'
EXECUTE msdb.dbo.sp_add_alert @.name = N'NULL_VALUE', @.message_id = 8153, @.severity = 0, @.enabled = 1, @.delay_between_responses = 60, @.include_event_description_in = 4, @.category_name = N'[Uncategorized]'|||Warnings get returned as messages in the TDS stream, much like PRINT statements do. I don't think that they generate an internal event or change any spid state information, so I don't think that there is any way for a script/stored procedure to capture them.

I'm pretty sure that you can modify the client to capture warning messages and the text (after substitution) of error messages, but I can't think of any way to capture it on the server side.

-PatP|||Pat, do me a favor and replicate on your TEST system the following screens. I saved the actual error message screens, but before posting this also experimented with warnings that you called "like PRINT statements." So that there won't be any further confusion, anything that is stored in sysmessages can be logged to the errorlog of both SQL Server and Windows Application log, as long as the check box in screen #3 is checked.|||Will do, but it will be a couple of days before I have the opportunity. I'm traveling now, and when I get back to the orifice I'll be working on other issues for a couple of days. It will probably be 2004-07-29 before I really have a chance to try it.

-PatP

Capturing value of identity column for use later?

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @.@.IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@.WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

-------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@.WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@.CurWeekReportedLineID = @.@.IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@.WeekReportedID1,
@.CurWeekReportedLineID,
@.BeginDate1,
Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM#EstimateLines;
--------

The #Temp table create via VB:
--------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
--------

--
PeteCresswell"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.4ax.com...
> This doesn't work because the first INSERT is creating multiple
> records for multiple projects. @.@.IDENTITY, then, contains the Identity
> column value for the last tblWeekReportedLine record inserted.
> Consequently, all the hours records are then associated with
> that last value.

<snip
Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@.BeginDate1,
e.Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @.WeekReportedID1;

Simon|||RE/
>this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet...

Thanks. I may make my Monday deadline yet....

--
PeteCresswell

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

Capturing the output from store procedure and use it

How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?

For example,

CREATE PROCEDURE dbo.sp_test AS
-- returns all words not in Mastery Level 0

EXEC sp_anothertest

-- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults

SELECT * tblFinalResults
GO

Thanks!I do not think you can do this exactly as you would like. You may need to resort to either a UDF which returns a table, using table variables within the stored procedure, or using temporary tables. Functions are the most flexiable and temporary tables are the slowest. You can also use table variables as output parameters of the stored procedures. Below is an example of using table variables.
begin
DECLARE @.Result1 table (key1 int, foo varchar(32) )
insert into @.Result1 select 1, 'This is Table 1'

DECLARE @.Result2 table ( key2 int, foo varchar(32) )
insert into @.Result2 select 1, 'This is Table 2'

select * from @.Result1 inner join @.Result2 on( key1 = key2 )
end

capturing the output from a stored procedure into a report

I have a stored procedure that takes some parameters. I can execute it
in the data tab in vb.net and get my results. How do i take these
results and form a report? Is there a way to capture the fields that
are returned in order to drop them into a report?
I'm doing all this in vb.net.
Thanks.Are you using the report designer and the data tab? Does the stored
procedure execute and return data from the data tab? If so, sometimes
executing the stored procedure does not fill the field list. Try clicking on
the refresh fields button (look to the right of the ... , it looks like the
fresh button for IE. Hover over it and it will tell you what the button is
for). If this doesn't cause the field list to fill in then you can put in
the fields manually in the list. Right mouse click in the field list, add
field and give it the name of the field name.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"muris" <rmuris@.hotmail.com> wrote in message
news:1112374203.464263.41490@.z14g2000cwz.googlegroups.com...
> I have a stored procedure that takes some parameters. I can execute it
> in the data tab in vb.net and get my results. How do i take these
> results and form a report? Is there a way to capture the fields that
> are returned in order to drop them into a report?
> I'm doing all this in vb.net.
> Thanks.
>|||hitting the refresh button worked!! Thank you.

Capturing the Error Description in a stored procedure

Hi,

I have a few stored procedure which will be executed one after another. If any error occurs, i need capture the Error number and ERROR DESCRIPTION and SAVE it into a table within stored procedure itself. Any idea how to do it?

I saw a similar problem from http://www.sqlservercentral.com/columnists/ajethva/capturingtheerrordescriptioninastoredprocedure.asp but i cannot download the sample code.

i want to CAPTURE the following msg :

e.g. Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

It would be great if you could send sample code.

thanks.

rama

If you use SQL server 2000, you can use the following batch. But you can’t suppers the error message.

Code Snippet

Create table ErrorLog

(

Source nvarchar(100),

CalledUser varchar(100),

ErrorNumber int,

ErrorDescription nvarchar(2000),

ErrorDatetime datetime

)

Create proc MyProc

@.I as int

Declare @.Error as int

Select 1/@.I

Set @.Error = @.@.ERROR

If @.Error <> 0

Begin

Insert Into ErrorLog

select

object_name(@.@.PROCID),

suser_sname(),

@.Error,

description,

getdate()

from

Sysmessages

Where

error=@.Error

and msglangid = (select msglangid from syslanguages where name='us_english') -- You can change to your local language

End

Exec MyProc 1

Exec MyProc 0

select * from ErrorLog

Capturing stored procedures parameters

Is it possible to capture, via trace or other means, the value of the
parameters passed to a stored procedure? There is a stored procedure in the
SharePoint database I want to monitor, but I don't want to change it. I want
to know when it's called and what parameters were passed to it.
Thank you in advance,
Daniel
This can easily be done using SQL Profiler. Just establish a new trace
using one of the SQLProfilerTSQL_xxxx templates. If you are only looking to
trace a single procedure, then you should probably play with the filter
criteria to eliminate some of the background noise otherwise you'll need to
wade through all of the TSQL commands being executed.
--Brian
(Please reply to the newsgroups only.)
"Daniel Corra" <daniel.correa@.e-component.com> wrote in message
news:esZKlWeoFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Is it possible to capture, via trace or other means, the value of the
> parameters passed to a stored procedure? There is a stored procedure in
> the SharePoint database I want to monitor, but I don't want to change it.
> I want to know when it's called and what parameters were passed to it.
> Thank you in advance,
> Daniel
>

Capturing stored proc names in profiler

Hello,
I was wondering if anyone had some insight into my problem.
I'm running a trace and I want to generate a report to show some simple
statistics about the duration, reads, and writes for the stored procedures
in my database. I'm having trouble parsing the stored procedure name from
the TextData field. The RPC:Completed event gives me everything I need for
the statistics, but it doesn't give me the stored procedure name or object
id. I does, however, give me full command, but I have to parse this with
string manipulation which is not quick. The SP:Completed event gives me the
object id of the stored procedure, but non of the statistics. My trace
files are very large and I'm looking for an easy way to generate some
statistics.
Thanks!Hi
I assume that you are loading these into a table for analysis, therefore if
you create a new column for the procedure name (and the procedures have a
recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
textdate, then you can use substring and charindex to update the new column.
When you select the data from the table you can restrict the events
returned.
John
"Oliwa" <abc> wrote in message
news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I was wondering if anyone had some insight into my problem.
> I'm running a trace and I want to generate a report to show some simple
> statistics about the duration, reads, and writes for the stored procedures
> in my database. I'm having trouble parsing the stored procedure name from
> the TextData field. The RPC:Completed event gives me everything I need
> for the statistics, but it doesn't give me the stored procedure name or
> object id. I does, however, give me full command, but I have to parse
> this with string manipulation which is not quick. The SP:Completed event
> gives me the object id of the stored procedure, but non of the statistics.
> My trace files are very large and I'm looking for an easy way to generate
> some statistics.
> Thanks!
>|||Hey John,
That's an idea, but I'm doing something very similar with string
manipulation. I was looking for methods that didn't require string
manipulation.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> I assume that you are loading these into a table for analysis, therefore
> if you create a new column for the procedure name (and the procedures have
> a recognisable prefix e.g 'prc_ ' in the first 8000 characters of the
> textdate, then you can use substring and charindex to update the new
> column.
> When you select the data from the table you can restrict the events
> returned.
> John
>
> "Oliwa" <abc> wrote in message
> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I was wondering if anyone had some insight into my problem.
>> I'm running a trace and I want to generate a report to show some simple
>> statistics about the duration, reads, and writes for the stored
>> procedures in my database. I'm having trouble parsing the stored
>> procedure name from the TextData field. The RPC:Completed event gives me
>> everything I need for the statistics, but it doesn't give me the stored
>> procedure name or object id. I does, however, give me full command, but
>> I have to parse this with string manipulation which is not quick. The
>> SP:Completed event gives me the object id of the stored procedure, but
>> non of the statistics. My trace files are very large and I'm looking for
>> an easy way to generate some statistics.
>> Thanks!
>|||Hi
If you are only profiling a single user then you can always check your
object ID from the previous SP:Completed event. Even if not a single user,
then you should be able to match SIDs.
When moving the profile into a table, Profiler will generate a RowNumber
Identity column for you.
John
"Oliwa" <abc> wrote in message news:OGJDUB0JFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hey John,
> That's an idea, but I'm doing something very similar with string
> manipulation. I was looking for methods that didn't require string
> manipulation.
> Thanks
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23rCtG6zJFHA.3336@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> I assume that you are loading these into a table for analysis, therefore
>> if you create a new column for the procedure name (and the procedures
>> have a recognisable prefix e.g 'prc_ ' in the first 8000 characters of
>> the textdate, then you can use substring and charindex to update the new
>> column.
>> When you select the data from the table you can restrict the events
>> returned.
>> John
>>
>> "Oliwa" <abc> wrote in message
>> news:%23qbnElyJFHA.2576@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I was wondering if anyone had some insight into my problem.
>> I'm running a trace and I want to generate a report to show some simple
>> statistics about the duration, reads, and writes for the stored
>> procedures in my database. I'm having trouble parsing the stored
>> procedure name from the TextData field. The RPC:Completed event gives
>> me everything I need for the statistics, but it doesn't give me the
>> stored procedure name or object id. I does, however, give me full
>> command, but I have to parse this with string manipulation which is not
>> quick. The SP:Completed event gives me the object id of the stored
>> procedure, but non of the statistics. My trace files are very large and
>> I'm looking for an easy way to generate some statistics.
>> Thanks!
>>
>

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
Susan
It can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith
|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the first
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
--
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the first
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL Statements?

Hi.
I need to be able to identify which SQL Statements are the poorest
performing so that the application developers can improve them. I've setup
Profiler to trace the login names that I want, but it only captures the firs
t
255 characters of the SQL Statement. Is there anyway to capture all of the
SQL that is being executed?
Thanks!
SusanIt can capture the entire text. My guess is that you are viewing the results
in Query Analyzer which has the default visible column length set to 255.
You should be able to change this via the options menu.
Anith|||On Mon, 5 Feb 2007 09:01:01 -0800, Susan Cooper
<SusanCooper@.discussions.microsoft.com> wrote:
>I need to be able to identify which SQL Statements are the poorest
>performing so that the application developers can improve them. I've setup
>Profiler to trace the login names that I want, but it only captures the fir
st
>255 characters of the SQL Statement. Is there anyway to capture all of the
>SQL that is being executed?
It's all there.
Save it to a database table and it's a text field, you can access it
with substring().
J.

Capturing SQL noise word exception...

Hi All,

I have a requirement where I need to find the list of noise words from a set of words in a SP.

PS: Too common words are said to be noise words and SQL maintains list of noise words on its own.

Say I have a sentence like "I am a software engineer. Here I need to get the list of noise words (I, am, a).I have written a logic where I will split the sentence into words and process word by word. I will first take one word and I have a select statement which will throw SQL noise word error exception if it is noise word.

Logic is
1. Take a word from the sentence.
2. Write a select statement like "select * from job where contains (jobdescription,' extracted word')"
3. If the word is noise word then SQL will throw a noise word exception.
4. I try to capture this error and based on that I have some logic.
5. If noise error thrown (I am using @.@.error)
do this;
do this;
else
do this;
do this;
6. Now my problem is, when the SQL throws noise exception, the execution of the SP stops immediately and the rest of the logic is not executed.
7. But some how I need to capture the exception and continue with the program flow.

I have different logic where I can achieve my requirements. (Instead of capturing SQL exception, maintain the noise words in a table and check with the table). but my question is there any way where I can capture the SQL exception and continue the program flow?

Please reply to my mail id.

TIA,
Varada.Can we do this 1 problem at a time?

Are you looking for a word in a string?

Look up CHARINDEX

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.
Hi
Run SQL Server Profiler on the target server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:

> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.Hi
Run SQL Server Profiler on the target server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:
> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL from Upsizing Wizard

Is there a way in which I could capture the SQL (create table, insert...)
used by the Upsizing Wizard when moving from Access to SQL Server? Ideally
I'd like to be able to provide this to my users in the event that once we
completely phase out Access, they have something to "guide" them in the
creation/alteration of tables. Thanks in advance.Hi
Run SQL Server Profiler on the target server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SABmore" wrote:

> Is there a way in which I could capture the SQL (create table, insert...)
> used by the Upsizing Wizard when moving from Access to SQL Server? Ideall
y
> I'd like to be able to provide this to my users in the event that once we
> completely phase out Access, they have something to "guide" them in the
> creation/alteration of tables. Thanks in advance.

Capturing SQL Data via Triggers

Good afternoon!

The code below only captures previous recorded profile information but not the profile information currently being saved.

How can I correct this issue?

TRIGGER INFORMATION:

USE heat601
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trk_profile' AND type = 'TR')
DROP TRIGGER trk_profile
GO
CREATE TRIGGER trk_profile
ON profile
FOR INSERT
AS
DECLARE @.vTracker varchar(10), @.vTrackername varchar (25), @.vAuditDate varchar(10), @.vCustId varchar(10)

SELECT @.vTracker = profile.Tracker, @.vTrackername = calllog.RbyFullName, @.vAuditDate = profile.DTLMod, @.vCustId = profile.CustId
From profile JOIN calllog On profile.custid = calllog.custid

Insert Into AuditTrail Values(@.vTracker,@.vTrackername,@.vAuditDate,@.vCusti d)

EXEC master..xp_sendmail 'tward@.caremark.com',
'HEAT CMS customer profile is currently being manipulated.'
GODo U use the Temporary Inserted & Deleted Tables ?

Capturing SQL code that generates error

I have an application that imports application data based on the Windows Installer .MSI schema into a SQL database. If it possible that the data in the tables exceeds the size allowed in our database. When that happens, an error message is generated. This
is fine as the user has the option to 'Ignore.'
The problem is that I cannot tell what application is being imported when the error message is produced. Is there a method to capturing the 'parent' SQL insert statements so that I can get the application name?
Thanks
Have you considered using Profiler? Let us know if you need help with using
Profiler to capture the statement and the error.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DanetteB" <DanetteB@.discussions.microsoft.com> wrote in message
news:E5E14CFC-8F1C-49F5-AEE5-40C14448FC86@.microsoft.com...
I have an application that imports application data based on the Windows
Installer .MSI schema into a SQL database. If it possible that the data in
the tables exceeds the size allowed in our database. When that happens, an
error message is generated. This is fine as the user has the option to
'Ignore.'
The problem is that I cannot tell what application is being imported when
the error message is produced. Is there a method to capturing the 'parent'
SQL insert statements so that I can get the application name?
Thanks
|||Vyas -
Thanks for the reply. I have tried the Profiler, but I am not getting the data I need. I'm probably not capturing the correct information. Any suggestions would be much appreciated.
"Narayana Vyas Kondreddi" wrote:

> Have you considered using Profiler? Let us know if you need help with using
> Profiler to capture the statement and the error.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
|||Vyas -
I got it - thank you for the Profiler suggestion. I went back into the tool and added the TSQL events.
DanetteB
"DanetteB" wrote:

> Vyas -
> Thanks for the reply. I have tried the Profiler, but I am not getting the data I need. I'm probably not capturing the correct information. Any suggestions would be much appreciated.
>
> "Narayana Vyas Kondreddi" wrote:
>

Capturing SQL code that generates error

I have an application that imports application data based on the Windows Ins
taller .MSI schema into a SQL database. If it possible that the data in the
tables exceeds the size allowed in our database. When that happens, an error
message is generated. This
is fine as the user has the option to 'Ignore.'
The problem is that I cannot tell what application is being imported when th
e error message is produced. Is there a method to capturing the 'parent' SQL
insert statements so that I can get the application name?
ThanksHave you considered using Profiler? Let us know if you need help with using
Profiler to capture the statement and the error.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DanetteB" <DanetteB@.discussions.microsoft.com> wrote in message
news:E5E14CFC-8F1C-49F5-AEE5-40C14448FC86@.microsoft.com...
I have an application that imports application data based on the Windows
Installer .MSI schema into a SQL database. If it possible that the data in
the tables exceeds the size allowed in our database. When that happens, an
error message is generated. This is fine as the user has the option to
'Ignore.'
The problem is that I cannot tell what application is being imported when
the error message is produced. Is there a method to capturing the 'parent'
SQL insert statements so that I can get the application name?
Thanks|||Vyas -
Thanks for the reply. I have tried the Profiler, but I am not getting the da
ta I need. I'm probably not capturing the correct information. Any suggestio
ns would be much appreciated.
"Narayana Vyas Kondreddi" wrote:

> Have you considered using Profiler? Let us know if you need help with usin
g
> Profiler to capture the statement and the error.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>|||Vyas -
I got it - thank you for the Profiler suggestion. I went back into the tool
and added the TSQL events.
DanetteB
"DanetteB" wrote:

> Vyas -
> Thanks for the reply. I have tried the Profiler, but I am not getting the
data I need. I'm probably not capturing the correct information. Any suggest
ions would be much appreciated.
>
> "Narayana Vyas Kondreddi" wrote:
>
>

Capturing Server Name?

Hi y'all...long time, I know...

I have what OUGHT to be a simple question...or so I think (which really hurts, mind you...)

I am writing a stored proc that will reside on several different databases and be used to write a row to a "wait table" that is used to control processing in the various databases...

Essentially, the stored proc already exists, and writes what is essentially a note (or process semaphore) that says "Hey, Process XYZ is waiting on the completion of process 123"

Problem is...process XYZ has the same name on a number of different servers, so I have to come up with a way to differentiate the process name that's waiting on a job on a single server (in other words, 5 or more XYZ's can be waiting on a single job on a single server in the network, and the wait table resides on that single server).

So...my thought (again, think pain) is that I will put a process name of "SERVER.XYZ" into the wait table.

The SP I will use to write the "waiting on" semaphore is a common one, so - long story short(er) I need a way to capture the name of the current server (like db_name(), only server_name() - or something like it).

Any suggestions? Thanks in advance...
Paulselect SERVERPROPERTY ('ServerName')

that'll do it...

Thanks for reading though!|||SELECT @.@.SERVERNAME may also work for you...|||Do you mean the name of the server that the database is on? If so, then you have your solution. If you wanted the client server's name, you could use:

select hostname
from master..sysprocesses
where spid = @.@.spid

If you go this route, you will have to make sure that all of your connection strings supply a hostname, though. Not all applications do. Also, the hostname can be spoofed, so be careful about hardcoding that. Good luck.|||...or simply HOST_NAME()|||I'll be darned. Microsoft actually came up with something that might be useful. I may have to try that with an application that spoofs a hostname, and see what happens. Thanks, rdjabarov.

Capturing rows inserted from bulk insert

Is there any way to capture the count of rows processed by a bulk insert task?
A property perhaps?
I can run queries post import but would prefer it if there was a way to capture that number directly. It was something we had in the old DTS as the package ran. Anything we can do to discover it in SSIS?

Paul PisarekOne option is to enable the out-of-box logging to sysdtslog90 table - the components displays number of rows processed. It's in the 'message' text colum though, so you need to add a little bit of parsing to get your specific data.
KDog|||That's right. We're working on a sample that parses the string and creates a report from it, hopefully should be available to you soon. But, yes, parsing the log is the right approach.

Capturing return status of EXEC(@String_Variable)

hello world how are thereWell that's certainly a lot of information to go on.

Care to elaborate?

Capturing results from SPROC

I need to execute a SPROC from another SPROC that returns 1 row with three fields. How do I capture the data out of those?

Have sproc #1 create a #Temp table,

then have sproc #2 populate that #Temp table.

The data will then be available in sproc #1.

|||If the stored procedure will always return 1 or less rows another good alternative is to have the stored procedure return three OUTPUT parameters instead of a row with three columns.|||

Here you can use either Insert Into ...Exec or OUTPUT param.

Code Snippet

--Option 1: If you are not autorized to change the Procedure code.

Create Proc #TestProc1

Select 1 Col1, 'Test data' Col2, Getdate() Col3

Create Table #TempData (

Col1 int,

Col2 Varchar(100),

Col3 Datetime

)

Insert Into #TempData

Exec #TestProc1

Select * from #TempData

Code Snippet

--Option 2: Best Fit

Create Proc #TestProc2(

@.Col1 as Int OUTPUT,

@.Col2 as Varchar(100) OUTPUT,

@.Col3 as DateTime OUTPUT

)

Select

@.Col1 = 1

,@.Col2 = 'Test data'

,@.Col3 = Getdate()

Declare @.Col1 as Int,

@.Col2 as Varchar(100) ,

@.Col3 as DateTime

Exec #TestProc2 @.Col1 OUTPUT, @.Col2 OUTPUT, @.Col3 OUTPUT

Select @.Col1, @.Col2, @.Col3

capturing reportviewer parameters

Hi Guys
I have an application which consists of a navigation bar and a frame.
When a user selects a hyperlink on the navigation bar that particular
report is displayed in the frame. Now these reports contains links
through which the user can jump to some other report which is not
present in the navigation bar. Now when the user clicks any other link
on the navigation bar the new report is starting up using the default
parameters set for that report. But i dont want this to happen. The
report viewer should take the latest parameters set in the report
viewer and pass them to the new report that will be generated when the
user clicks on the navigation bar. Can any body help with this aspect.
it is blowing up my brains. dont even know that whether this is
possible.
Also can we somehow get the url of the report being displayed in the
report viewer." i know that report viewer is an iframe which uses url
access beneath it to access the reports." Only the first report server
url is encoded in the application. But when the user navigated to some
other report through the links in reports how do i get access to that
particular report url .
any help will really be great. I am wondering is it really possible to
do the stuff i just mentioned above. Any tips on this will really save
me a lot of time.
Thanks in advance.../......
Passxunlimitedyou have to add some code to save the parameters and reuse them when you
open a new report.
you have to intercept some events (from the reportviewer control) to
retrieve the parameters when the first report is refreshed with new
parameters, save the values into a the session, in the page load event if
you open a new report change the values of the parameters of these reports
regarding what you have saved before.
its not complicated, the API is easy to use to do this.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> Hi Guys
> I have an application which consists of a navigation bar and a frame.
> When a user selects a hyperlink on the navigation bar that particular
> report is displayed in the frame. Now these reports contains links
> through which the user can jump to some other report which is not
> present in the navigation bar. Now when the user clicks any other link
> on the navigation bar the new report is starting up using the default
> parameters set for that report. But i dont want this to happen. The
> report viewer should take the latest parameters set in the report
> viewer and pass them to the new report that will be generated when the
> user clicks on the navigation bar. Can any body help with this aspect.
> it is blowing up my brains. dont even know that whether this is
> possible.
> Also can we somehow get the url of the report being displayed in the
> report viewer." i know that report viewer is an iframe which uses url
> access beneath it to access the reports." Only the first report server
> url is encoded in the application. But when the user navigated to some
> other report through the links in reports how do i get access to that
> particular report url .
> any help will really be great. I am wondering is it really possible to
> do the stuff i just mentioned above. Any tips on this will really save
> me a lot of time.
> Thanks in advance.../......
> Passxunlimited
>|||i was trying to do exactly the same thing . But could not find any
events associated with report viewer wherein i can catch the parameter
values or session state. Any idea or resources regarding this.
thanks
passx
Jeje wrote:
> you have to add some code to save the parameters and reuse them when you
> open a new report.
> you have to intercept some events (from the reportviewer control) to
> retrieve the parameters when the first report is refreshed with new
> parameters, save the values into a the session, in the page load event if
> you open a new report change the values of the parameters of these reports
> regarding what you have saved before.
> its not complicated, the API is easy to use to do this.
>
> "Passx" <passxunlimited@.gmail.com> wrote in message
> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> > Hi Guys
> >
> > I have an application which consists of a navigation bar and a frame.
> > When a user selects a hyperlink on the navigation bar that particular
> > report is displayed in the frame. Now these reports contains links
> > through which the user can jump to some other report which is not
> > present in the navigation bar. Now when the user clicks any other link
> > on the navigation bar the new report is starting up using the default
> > parameters set for that report. But i dont want this to happen. The
> > report viewer should take the latest parameters set in the report
> > viewer and pass them to the new report that will be generated when the
> > user clicks on the navigation bar. Can any body help with this aspect.
> > it is blowing up my brains. dont even know that whether this is
> > possible.
> >
> > Also can we somehow get the url of the report being displayed in the
> > report viewer." i know that report viewer is an iframe which uses url
> > access beneath it to access the reports." Only the first report server
> > url is encoded in the application. But when the user navigated to some
> > other report through the links in reports how do i get access to that
> > particular report url .
> >
> > any help will really be great. I am wondering is it really possible to
> > do the stuff i just mentioned above. Any tips on this will really save
> > me a lot of time.
> >
> > Thanks in advance.../......
> > Passxunlimited
> >|||try the onunload event or any event after the rendering step.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167942475.840285.10930@.51g2000cwl.googlegroups.com...
>i was trying to do exactly the same thing . But could not find any
> events associated with report viewer wherein i can catch the parameter
> values or session state. Any idea or resources regarding this.
>
> thanks
> passx
> Jeje wrote:
>> you have to add some code to save the parameters and reuse them when you
>> open a new report.
>> you have to intercept some events (from the reportviewer control) to
>> retrieve the parameters when the first report is refreshed with new
>> parameters, save the values into a the session, in the page load event if
>> you open a new report change the values of the parameters of these
>> reports
>> regarding what you have saved before.
>> its not complicated, the API is easy to use to do this.
>>
>> "Passx" <passxunlimited@.gmail.com> wrote in message
>> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
>> > Hi Guys
>> >
>> > I have an application which consists of a navigation bar and a frame.
>> > When a user selects a hyperlink on the navigation bar that particular
>> > report is displayed in the frame. Now these reports contains links
>> > through which the user can jump to some other report which is not
>> > present in the navigation bar. Now when the user clicks any other link
>> > on the navigation bar the new report is starting up using the default
>> > parameters set for that report. But i dont want this to happen. The
>> > report viewer should take the latest parameters set in the report
>> > viewer and pass them to the new report that will be generated when the
>> > user clicks on the navigation bar. Can any body help with this aspect.
>> > it is blowing up my brains. dont even know that whether this is
>> > possible.
>> >
>> > Also can we somehow get the url of the report being displayed in the
>> > report viewer." i know that report viewer is an iframe which uses url
>> > access beneath it to access the reports." Only the first report server
>> > url is encoded in the application. But when the user navigated to some
>> > other report through the links in reports how do i get access to that
>> > particular report url .
>> >
>> > any help will really be great. I am wondering is it really possible to
>> > do the stuff i just mentioned above. Any tips on this will really save
>> > me a lot of time.
>> >
>> > Thanks in advance.../......
>> > Passxunlimited
>> >
>

Capturing Report Parameters

I am trying to capture the selected report parameters for a generated
report prior to it being sent to the server. The intent is to use
these values to set as defaults for the saved linked report. I have
no problem retrieving the list of valid values but don't know if the
values selected in the ReportView can be captured. Any Ideas?
PaulOn Dec 18, 3:04 pm, Paul <blackwell_p...@.hotmail.com> wrote:
> I am trying to capture the selected report parameters for a generated
> report prior to it being sent to the server. The intent is to use
> these values to set as defaults for the saved linked report. I have
> no problem retrieving the list of valid values but don't know if the
> values selected in the ReportView can be captured. Any Ideas?
> Paul
It's not very likely that this is possible (in terms of capturing the
report parameter on the client-side). You could try using Javascript;
but, this is a long shot. If you are just trying to pass the parameter
value selected to another report (via Jump to Report, Jump to URL,
etc) You can just set the report to jump to (or the URL of the report)
and then select the parameter to pass to it (or in the case of using a
URL, append the Parameter's value). To access the parameter's value
you would set the Parameter to an expression similar to:
=Parameters!Param1Name.Value
If using a URL, a similar expression to this should work.
="http://ServerX/reportserver?/SomeReportsDirectory/
ReportName&rs:Command=Render&Param1=" + Parameters!Param1.Value
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Capturing record count for a table in Oracle and saving it in a table in SQL Server

I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.

I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:

Imports System.Data.SqlClient

Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.

any idea?

thanks

Why are you trying to use a Script Task? This can be achieved very easily using an Execute SQL Task.

-Jamie

|||

I'd concur with Jamie, use an Execute SQL task.

If you have to use the script task, then you need to reference the System.Data.OleDb namespace for OLEDB connections, or the System.Data.OracleClient for the Oracle client.

|||well how do I use sql task to accomplish this?|||select count(*) from table

Store the contents of that SQL statement into a single result set. Variable mapped to 0 in the Result tab.|||the result tab is frozen. how do I enable it? do I need to define an expression?|||

Set ResultSet='Single Row'

-Jamie

|||is that an expression?|||

No. ResultSet is a property of the Execute SQL Task.

-Jamie

|||gotcha.. i did set it single row. Now how do I get the value from that resultset and use it to update a column.|||On the Result Set tab, add a result, make the name 0 and specify the variable to put the count in. Then use another Execute SQL task, with an Insert statement, to write the variable to your table.|||

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

|||No, 0 refers to the first column of the resultset (it's a zero-based index).|||

Shahab03 wrote:

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?
also what would the query look like in the second sql task to get value from the variable?
is there any example I can follow?

INSERT INTO MyTable (RecordCountCol)

VALUES (?)

On the Parameter Mapping tab, add a parameter, set the variable to the same one you populated earlier, make the name 0, and set the data type appropriately.

See this for a really good overview of using the Execute SQL task. http://www.sqlis.com/58.aspx

|||

well I have a sqltask now with following properties:

query: select count(*) as EmpCompRC from empcomp

I have also defined a variable EmpCompRC. Result name in result set tab is set to 0 and variable is EmpCompRC.

Now as I understand I have setup another sql task after the defined above. Correct?

and what would that task look like? I dont see how would I pass the variable value from previous task to this one. I tried following statement but it failed:

update detail

set sourcerecordcount = @.EmpCompRC

does anyone know how to setup this sqltask? query above still leaves the variable name out.

Capturing queries sent to SQL Server

I use a software package that queries a Microsoft SQL Server 2000 database
from a client side Windows application. The client application has a built
in report generator that allows users to easily produce complex reports.
The client application was written in Delphi, and uses Borland's BDE to
connect to the SQL Server.
I would like to trap the SQL code that is being produced and sent to the
SQL Server from the report generator (so that I can get a get a better
understanding of how to directly query the database outside of the report
generator).
Any suggestions on how to do this? Are there any third party products that
can provide me with this capability?
Thanks,
DeanOne of my favourite tools will do this "Profiler". It is in your SQL Server
program group.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"dean" <deanbillings@.yahoo.com> wrote in message
news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> I use a software package that queries a Microsoft SQL Server 2000 database
> from a client side Windows application. The client application has a
built
> in report generator that allows users to easily produce complex reports.
> The client application was written in Delphi, and uses Borland's BDE to
> connect to the SQL Server.
> I would like to trap the SQL code that is being produced and sent to the
> SQL Server from the report generator (so that I can get a get a better
> understanding of how to directly query the database outside of the report
> generator).
> Any suggestions on how to do this? Are there any third party products
that
> can provide me with this capability?
> Thanks,
> Dean|||Dean,
You don't need a third party product. Take a look a SQL Profiler that comes
with SQL Server. The following article will help you get the most out of
Profiler http://www.sql-server-performance.com/sql_server_profiler_tips.asp.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"dean" <deanbillings@.yahoo.com> wrote in message
news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> I use a software package that queries a Microsoft SQL Server 2000 database
> from a client side Windows application. The client application has a
built
> in report generator that allows users to easily produce complex reports.
> The client application was written in Delphi, and uses Borland's BDE to
> connect to the SQL Server.
> I would like to trap the SQL code that is being produced and sent to the
> SQL Server from the report generator (so that I can get a get a better
> understanding of how to directly query the database outside of the report
> generator).
> Any suggestions on how to do this? Are there any third party products
that
> can provide me with this capability?
> Thanks,
> Dean|||And if you have already looked there and could not find anything but
parameter passing. Get the SPID and find the first calls that define the
procedure.
If it is totally obscure, Net Monitor will show you the text in
any packet sent to your machine.
I think that you should use SQL Profiler so that you have this skill
in your tool bag anyway.
"Largo SQL Tools" <nospam@.yahoo.com> wrote in message
news:unNOnjGSDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Dean,
> You don't need a third party product. Take a look a SQL Profiler that
comes
> with SQL Server. The following article will help you get the most out of
> Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp.
> J.R.
> Largo SQL Tools
> The Finest Collection of SQL Tools Available
> http://www.largosqltools.com
>
> "dean" <deanbillings@.yahoo.com> wrote in message
> news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> > I use a software package that queries a Microsoft SQL Server 2000
database
> > from a client side Windows application. The client application has a
> built
> > in report generator that allows users to easily produce complex reports.
> > The client application was written in Delphi, and uses Borland's BDE to
> > connect to the SQL Server.
> >
> > I would like to trap the SQL code that is being produced and sent to the
> > SQL Server from the report generator (so that I can get a get a better
> > understanding of how to directly query the database outside of the
report
> > generator).
> >
> > Any suggestions on how to do this? Are there any third party products
> that
> > can provide me with this capability?
> >
> > Thanks,
> > Dean
>|||Profiler is definitely your best option. One of the best tools available
and well worth the perceived 'non productive' time spent using/learning it.
"dean" <deanbillings@.yahoo.com> wrote in message
news:Xns93B61780A829Bdean918yahoocom@.24.168.128.78...
> I use a software package that queries a Microsoft SQL Server 2000 database
> from a client side Windows application. The client application has a
built
> in report generator that allows users to easily produce complex reports.
> The client application was written in Delphi, and uses Borland's BDE to
> connect to the SQL Server.
> I would like to trap the SQL code that is being produced and sent to the
> SQL Server from the report generator (so that I can get a get a better
> understanding of how to directly query the database outside of the report
> generator).
> Any suggestions on how to do this? Are there any third party products
that
> can provide me with this capability?
> Thanks,
> Dean

Capturing Processing Status and Progress Messages

When you use BI Dev Studio or SS Management Studio to process an Analysis Services database or object, there's a nice dialog box that shows very robust status and progress information.

Is there any way to capture this information if you are processing something programmatically? Specifically interested in both the ability to capture and display this information to a user when processing via a custom application. Also interested in whether or not there's a way to capture this information to a log (or table) for display and analysis after the fact (or if the custom application is running in a batch mode via a schedule).

Thanks,
Dave Fackler
I've been looking for the same thing. Were you able to find where this information is stored/logged?

Thanks!
|||One simple way to do this is using the SQL Server Profiler tool which has the option of logging trace information to files or tables as well as the UI. You can also do this programmatically using the AMO using Server.SessionTrace or by adding custom traces to Server.Traces.|||

Using the AMO, you can listen Session Trace. Sample code is following:

this.sessionTrace = server.SessionTrace;

this.sessionTrace.OnEvent += new TraceEventHandler(sessionTrace_TraceEventHandler);

this.sessionTrace.Stopped += new TraceStoppedEventHandler(sessionTrace_Stopped);

if (!this.sessionTrace.IsStarted)

{

this.sessionTrace.Start();

}

|||

You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies") to subscribe to trace events. Sample code below. The code formatting might be lost when posting, but copy paste in VS, and then "Edit -> Advanced -> Format Document" should fix it.

//=====================================================================
//
// File: Program.cs
// Summary: Sample code for using traces with AMO.
// Date: 2006-05-23
//
//
//
// Copyright (C) Microsoft Corporation. All rights reserved.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
//
//=====================================================================
using System;
using Microsoft.AnalysisServices;

namespace Microsoft.AnalysisServices.CodeSamples
{
/// <summary>
/// Demonstrates use of traces with AMO.
/// </summary>
/// <remarks>
/// This code requires the "Adventure Works" database to be present on the Analysis Services server
/// and also the "AdventureWorksDW" relational database on SQL Server (for processing objects to
/// generate trace events).
/// </remarks>
class AmoTrace
{
static int Main(string[] args)
{
//--
// There are 2 types of traces that AMO programmer can use:
// - the session trace: provides the events from the current session (established
// on Connect)
// - the custom traces: they allow to choose the particular event classes and
// columns to be traced. Unlike the session trace, a custom trace needs to be
// created and saved to server explicitly (as any other AMO object, like a
// Dimension or a Cube).
//
// This is the plan of the code:
// 1. we'll connect to an Analysis Services server
// 2. we'll locate the "Product" dimension from the "Adventure Works DW" database
// (we'll process it to generate trace events)
// 3. we'll use the session trace
// 4. we'll create, use and then delete a custom trace
//--

string connectionString = "Data Source=localhost";
string databaseName = "Adventure Works DW"; // use "Adventure Works DW Standard Edition" if you have the standard edition installed

try
{
//--
// STEP 1: connect to Analysis Services server.
//--
Server server = new Server();
server.Connect(connectionString);

try
{
//--
// STEP 2: locate the "Product" dimension.
//--
Database database = server.Databases.FindByName(databaseName);
if (database == null)
{
Console.Error.WriteLine("The [{0}] database is missing.", databaseName);
return 1;
}

Dimension productDimension = database.Dimensions.FindByName("Product");
if (productDimension == null)
{
Console.Error.WriteLine("The [Product] dimension is missing.");
return 1;
}

//--
// STEP 3: use the session trace.
//--
UseSessionTrace(server, productDimension);

//--
// STEP 4. create, use and then delete a custom trace.
//--
UseCustomTrace(server, productDimension);

return 0;
}
finally
{
server.Disconnect();
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.ToString());
return 1;
}
}

/// <summary>
/// Demonstrates the use of session trace.
/// </summary>
private static void UseSessionTrace(Server server, Dimension dimensionToProcess)
{
//--
// 1. Subscribe to the session trace events.
//--
SessionTrace sessionTrace = server.SessionTrace;
TraceEventHandler onTraceEvent = new TraceEventHandler(OnTraceEvent);
TraceStoppedEventHandler onTraceStopped = new TraceStoppedEventHandler(OnTraceStopped);

sessionTrace.OnEvent += new TraceEventHandler(OnTraceEvent);
sessionTrace.Stopped += new TraceStoppedEventHandler(OnTraceStopped);
sessionTrace.Start(); // this method is not blocking, it starts a separate thread to listen for events from server

//--
// 2. Process the dimension; this will generate events that we'll display to
// the Console.
//--
try
{
dimensionToProcess.Process(ProcessType.ProcessFull); // this method blocks; while processing, events are received
}
finally
{
//--
// 3. Un-subscribe from the trace.
//--
sessionTrace.Stop();
sessionTrace.OnEvent -= onTraceEvent;
sessionTrace.Stopped -= onTraceStopped;
}
}

/// <summary>
/// Demonstrates the use of a custom trace.
/// </summary>
private static void UseCustomTrace(Server server, Dimension dimensionToProcess)
{
//--
// 1. Create and save to server a custom trace with only the
// ProgressReportBegin, ProgressReportCurrent and ProgressReportEnd events
// and the EventClass and EventSubclass columns.
//--
Trace trace = server.Traces.Add(); // a Name and ID will be generated for the new Trace
TraceEvent event1 = trace.Events.Add(TraceEventClass.ProgressReportBegin);
TraceEvent event2 = trace.Events.Add(TraceEventClass.ProgressReportCurrent);
TraceEvent event3 = trace.Events.Add(TraceEventClass.ProgressReportEnd);

event1.Columns.Add(TraceColumn.EventClass);
event1.Columns.Add(TraceColumn.EventSubclass);

event2.Columns.Add(TraceColumn.EventClass);
event2.Columns.Add(TraceColumn.EventSubclass);

event3.Columns.Add(TraceColumn.EventClass);
event3.Columns.Add(TraceColumn.EventSubclass);

// Save the newly created Trace to the server; others could use it (unlike the session
// trace which is specific to a particular session).
trace.Update();

//--
// 2. Subscribe to the newly create trace.
//--
TraceEventHandler onTraceEvent = new TraceEventHandler(OnTraceEvent);
TraceStoppedEventHandler onTraceStopped = new TraceStoppedEventHandler(OnTraceStopped);

trace.OnEvent += new TraceEventHandler(OnTraceEvent);
trace.Stopped += new TraceStoppedEventHandler(OnTraceStopped);
trace.Start(); // this method is not blocking, it starts a separate thread to listen for events from server

//--
// 3. Process the dimension; this will generate events that we'll display to
// the Console.
//--
try
{
dimensionToProcess.Process(ProcessType.ProcessFull); // this method blocks; while processing, events are received
}
finally
{
//--
// 3. Un-subscribe from the trace.
//--
trace.Stop();
trace.OnEvent -= onTraceEvent;
trace.Stopped -= onTraceStopped;
}

//--
// 4. Cleanup: delete the trace from the server. We could leave it (to be used
// in the future or by other users), but we'll delete it since it was only a
// sample.
//--
trace.Drop();
}

/// <summary>
/// Event handler for trace events, called on a separate thread by the AMO trace.
/// </summary>
/// <remarks>
/// When the Start() method is called on a Trace, AMO creates a separate thread
/// that listens for events from the server; when an event is read, this method is
/// called on that thread. There should be no heavy calculations in this method
/// because the trace thread will be blocked and events might overflow the connection's
/// buffer(s), resulting in loss of events.
/// </remarks>
private static void OnTraceEvent(object sender, TraceEventArgs e)
{
Console.WriteLine("Event ({0}, {1})", e.EventClass, e.EventSubclass);
}

/// <summary>
/// Event handler for trace stop events, called on a separate thread by the AMO trace when
/// a trace is stopped (by the user, by the server or by an exception).
/// </summary>
/// <remarks>
/// There are 3 main reasons for a trace to be stopped:
/// - the user called the Stop() method
/// - the server ended the trace (because somebody deleted it meanwhile for example)
/// - an exception occured during parsing of events (because of a network disconnect for example)
/// </remarks>
private static void OnTraceStopped( ITrace sender, TraceStoppedEventArgs e )
{
Console.WriteLine("Trace Stopped: Cause = {0}, Exception = {1}", e.StopCause, e.Exception);
}
}
}

Adrian Dumitrascu.

|||Does anyone know if there is anyway of figuring out which events support which columns from the object model? At the moment I have had to resort to hard coding the columns supported by a given event.|||Thanks for all the responses and the sample code!

I would like to capture this information using SSIS. Of course, the script tasks only supports VB as well. Is there an easy way to capture trace information from the cube processing within SSIS?

Thanks again for all the help!
|||

AMO doesn't provide the list of supported columns for a particular trace event. But, there are 2 other places containing the associations:

- the "tracedefinition90.xml" file (search for it in "%ProgramFiles%\Microsoft SQL Server"). You will need to parse it to generate a user-friendlier map. But please note that this file might change in the future (probably not the xml schema, but columns/events might be added/removed/changed).

- SQL Profiler; when you create a new AS2005 trace, in the 'Trace Properties' window, see the 'Events Selection' tab (generated from the trace definition file)

Adrian Dumitrascu

|||

I don't have yet a sample VB.NET code for using AMO traces, but a work-around is to have a separate assembly containing the trace code (written in C# from the sample posted previously) and use this assembly from the VB.NET script task in SSIS.

Adrian Dumitrascu

|||

You could try running the sample code through one of the online C# to VB.NET converters (eg. http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx). They are not perfect, but they can get you pretty close.

I don't know if the event handling code will work inside an SSIS script task. In this case one option might be to configure the trace to log to a file.

|||

Thanks Adrian, the tracedefinitions90.xml looks like it could be very useful.

The other thing I just found was that by going File -> Export -> Script Trace Definition. I can get an xmla script that will create the server side trace.

This might suit my needs as I can configure the trace using the profiler GUI, script it out and then just get my app to execute the xmla and then connect to the server side trace.

|||

Hi,

I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.

Any help would be highly appreciated.

Thanks in advance.

|||

Take a look at the following link on Mosha's website. It contains a set of links to web-based browsers and applications. You can probably use them as a guide to developing your own custom web-based app for doing this (or perhaps use one of the listed applications directly).

http://www.mosha.com/msolap/util.htm#ThinClients

HTH,

Dave Fackler