how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increas
e
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find th
e
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

How to detect if column data changed and know prev. and new value

I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

BillHi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/d...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <billtepe@.mssonline.net> wrote in message
news:7364847c.0309060600.7023b89a@.posting.google.c om...
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.
> Is there a "simple" way to do this? I know I could query the table
> before the update and compare to what the new value is and react
> accordingly.
> Just wondering if there is something nifty in Sql Server that I am
> missing that could help me with this.
> Thanks in advance for your help.
> Bill|||[posted and mailed, please reply in news]

Bill Tepe (billtepe@.mssonline.net) writes:
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.

In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.

Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.

You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:

select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted

Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel. www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How to derive a calculated from a measure and a dimension attributes?

Hi,Does anyone know how to write an MDX queries that can enable calculation of a measure value and a dimension attributes? Eg: Measure Revenue * Conversion Rate (from dimension attribute).Reason that this conversion rate is not in the fact layer is that the conversion rate is dependant to the Currency the user want to convert to during run-time.Thanks.JLim

Hello

Here you have the mdx query performed on the Food Mart 2000 Sales cube.

with member [Measures].[Modified Unit Sales] as 'iif([Promotions].CurrentMember.Name="Dollar Days",[Measures].[Unit Sales]*0.9,[Measures].[Unit Sales])'
select {[Measures].[Modified Unit Sales],[Measures].[Unit Sales]} on axis(0) , {[Promotions].DefaultMember.Children} on axis(1)

from [sales]

Due to possible performance problems please consider the option to create one measure per each currency.

Regards

Maciej Kiewra

How to define the periodicity_Hint value

Hi, all experts here,

I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?

Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The preiodicity hint should be specified as a string that looks as follows:

{a,b,c}

where a,b and c are the hints you specify. In your example, if you have a periodicity in the data of 12, the hint string would look like {12}.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

But my another question on it is: assume we did not know the periodicity of the data, so it is always better to leave this propertity blank in which case the system willl automatically detect the periodicity of the data?

Thank you very much and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||Exactly: the system tries to detect the periodicity of the data.
If you leave this blank, the results of the detection are used.

You should provide a value for this parameter when:
- you know the periodicity (and want to hint the system, before it runs the detection step) OR
- you know the periodicity and want to correct the algorithm's auto-detection results

In both cases, your periodicity hints will be analyzed together with the periods automatically detected by the algorithm|||

Hi, all friends,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

How to define the periodicity_Hint value

Hi, all experts here,

I am having a question on defining the value of Periodicity_hint. e.g, I wanna predict the monthly sales amont of a product, and assume the data follows monthly patterns, then in this case, what value should we set for the periodicity_hint parametre?

Thanks a lot for your kind attention and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The preiodicity hint should be specified as a string that looks as follows:

{a,b,c}

where a,b and c are the hints you specify. In your example, if you have a periodicity in the data of 12, the hint string would look like {12}.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

But my another question on it is: assume we did not know the periodicity of the data, so it is always better to leave this propertity blank in which case the system willl automatically detect the periodicity of the data?

Thank you very much and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||Exactly: the system tries to detect the periodicity of the data.
If you leave this blank, the results of the detection are used.

You should provide a value for this parameter when:
- you know the periodicity (and want to hint the system, before it runs the detection step) OR
- you know the periodicity and want to correct the algorithm's auto-detection results

In both cases, your periodicity hints will be analyzed together with the periods automatically detected by the algorithm|||

Hi, all friends,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

How to define multi-valued 'default value' parameters for a report on the ReportServer

Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:

ReportParm1 String [Deal Dim].[Shelf].&[AAM]

But, how would i define it with multiple values as a 'default value' ?, for example:

ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]

NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.

Any help would be greatly appreciated.

thank you.

Once you publish the report, you cannot edit the expressions that define the defaut or valid values. If you have a set of expressions in the RDL, you'll be able to choose from the evaluated values of the expressions, but you won't be able to modify the expressions themselves.

If you just want to choose from a list of values derived from expressions, put the expressions in the RDL and mark the parameter as MultiValue:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=(1+1)</Value>
<Value>123456</Value>
<Value>-123456</Value>
<Value>=CInt(123456/123456)</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

If you're meaning to emit the strings representing the expressions instead of the evaluated expressions, then just make the parameter a multivalued string parameter:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>="(1+1)"</Value>
<Value>="123456"</Value>
<Value>="-123456"</Value>
<Value>="CInt(123456/123456)"</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

Now you can use the string value as an expression.

How to define a user variable on Execute Sql Task?

Hi everyone,

How to define a Input variable in a Execute Sql Task?

I've defined a User::Inicio variable which contains 4 as value.

In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:

UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]

or

UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]

Anyway, I'm stuck, both did not work

Thanks in advance for your comments

Enric,

Use an expression in SQlStatementSource property of your Execute SQL task to build your SQL statement:

"UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = " @.[User::Inicio]

Rafael Salas

|||

Hi Rafael,

Thanks for your quick answer but it doesn't work.

[Execute SQL Task] Error: Executing the query "UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [@.User::Inicio]" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Let me know, I can imagine that's a silly thing..

|||

Well, the error talks abour the ResulSet porperty; what is your value for that? what is you set that to None. Also i think you do not need anything in your parameter tab since the SQL statement is being created by the expression

RAfael Salas

|||Rafael is telling you to set an Expression for the SQLStatementSource property and not set the property value directly. Looks like you set the SQLStatementSource directly to "UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = " + @.[User::Inicio]. To set an expression for the SQLStatementSource property click on the Expressions node on the left hand side of the Execute SQL Task Editor dialog.|||

Hi,

You mean you want to use the user variable in your query right?

Refer this:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

|||Thanks to all of you