Estimate Log Space for Alter Table Statement

My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.

What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.

Any suggestions? Thanks in advance.

You can change the truncate log setting in a database if you don't need it to recover in time of emergency it is a very complicated subject so run a search for truncate log on checkpoint option for your database setting in the BOL(books online). The links below are basic guidelines from Microsoft. Hope this helps.

http://support.microsoft.com/kb/873235

http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx

|||

If I remember correctly when you alter column in table, SQL server renames old one, created new table with new structure and next moves data into new table. So in your case you need at least amount of space your data table takes for log because all process has to be done at one shot.
But maybe you can do it yourself: create table with new structure and move data from old table to new table with quantity like 100 or 1000 records in one shot? Log file will be small and you should succeed. You only have to take care about identity insert if you have one in your table.

Thanks

|||

Thanks jpazgier, that's a great suggestion! I usually do the alter using Enterprise Manager directly for this client but I have scripted this type of change for other clients. I never thought about taking the generated script and breaking up the insert part of it into multiple inserts and performing the truncate log after each insert. That would reduce the log space required.

If I don't delete the old records as I go, I still need twice the data just for the old and new copies but I wouldn't need so much log space. If I trust my alterations, then I could delete the old records as they get moved into the new copy and really reduce the free space required. Not as safe but probably sufficient.

Thanks

|||

(1) You could keep the DB in simple mode while you do the data transfer.

(2) You can also have a job running to truncate the log during the transfer and let the job run every 1 minute, so you dont have to do it yourself manually.

(3) You can use bcp/Bulk Insert to transfer the data.

Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a nor
mal string value. When I remove the comma everything works fine. Is there a
way to escape the comma? I've spent a great deal of time looking for the ans
wer, apparently I am not lo
oking in the right places. Any help would be appreciated.Can you be a bit more specific, or show us a repro? Below statement work jus
t like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal st
ring value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've sp
ent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help wo
uld be appreciated.|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work j
ust like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've
spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help
would be appreciated.
>
>|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrec
t syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm sorry but that is not something I can execute as you didn't post the CRE
ATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the stri
ng definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a normal string value. When I remove the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking for the answer, apparently I am not lo
oking in the right places. Any help would be appreciated.
Can you be a bit more specific, or show us a repro? Below statement work just like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal string value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help would be appreciated.
|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work just like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help would be appreciated.
>
>
|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
> "Tibor Karaszi" wrote:
|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I'm sorry but that is not something I can execute as you didn't post the CREATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the string definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
> "Tibor Karaszi" wrote:
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]
|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

escape single qoute

Is there a way to escape single quotes ' in a sql statement that uses a sql data source that is automated? I know you can do it with a string manip and replacing them with double single quotes. I am just looking for a simple way.

Thanks

Adam

If you are using parameters, you don't have to worry about single quotes in strings because SQL Server preserves the input data as it is.

If you aren't using parameters, you'll have to use two single quotes.

|||

You need to use two single quotes.

escape clause causes error in lookup modified sql statement

I need to use a modified SQL statement for a lookup component. It has an escape clause in it and this causes error:

select * from dbo.typecustomer where ? like '%'+type_subtype +'%'
ESCAPE '_'

Is this is a bug? Any help will be greatly appreciated.

Thanks

Akin
The escape clause shouldn't be affecting the parameter usage if the SQL works without it. The lookup doesn't parse the SQL, so I suspect the problem is with the provider. Essentially, the lookup asks the provider to prepare the command and then to derive parameter information. The provider is failing in one of the above steps (probably the prepare step). I would first try this with the latest provider (i.e. use snac instead of sqloledb in the connection manager for the lookup) and if the problem persists, ask on the snac forum if command preparation followed by parameter derivation is problematic with your specific command.

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
--
%_*
%_*
John
"Sathyaish" wrote:
> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegroups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.

Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do?

Use paramitrimized queries. Then you never have to worry about format's or

SQL Injection.
It's olso better for the preformance, because you don't need

to have to concatenate a string for example:

string query

= "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" +

"txtName.Text + "\"";

No escape characters needed, you doesn't

have to think about using a " or not etc.

Parameters are like

placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date

variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1

WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate",

SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new

SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query,

dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add(

pMyDate );

// Execute the query and get results.
SqlDataReader reader =

dbCommand.ExecuteReader();

try
{
// Walkthrough

results.
while(reader.Read())
{
// TODO: Do something with

the data.
}
}
finally
{
// Close

reader.
reader.Close();
}
}
}
finally
{
// Close

connection.
dbConn.Close();
}

Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do?

Use paramitrimized queries. Then you never have to worry about format's or

SQL Injection.
It's olso better for the preformance, because you don't need

to have to concatenate a string for example:

string query

= "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" +

"txtName.Text + "\"";

No escape characters needed, you doesn't

have to think about using a " or not etc.

Parameters are like

placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date

variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1

WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate",

SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new

SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query,

dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add(

pMyDate );

// Execute the query and get results.
SqlDataReader reader =

dbCommand.ExecuteReader();

try
{
// Walkthrough

results.
while(reader.Read())
{
// TODO: Do something with

the data.
}
}
finally
{
// Close

reader.
reader.Close();
}
}
}
finally
{
// Close

connection.
dbConn.Close();
}

Errors using multiple parameters in a SQL Statement

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||

I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.

Here is how:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756434&SiteID=1

|||

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

|||

Rafael Salas wrote:

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.

Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".

The issue is likely as Rafael pointed out if you are using a complex query.|||

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

|||

Mike Batton wrote:

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

Can you post your query?|||

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

|||

Mike Batton wrote:

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

Use question marks - always, and don't number them.

select * from tew_d_dt where dt_key = ? and cal_yr_id = ?

Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

|||

Mike Batton wrote:

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||

What about in the case part of a select statement... should that work or not?

Below is part of the actual query that we're trying to get to work.

Mike

select 'ent' AS level_desc,

sku.new_concept_cd AS code_1,

sku.color AS color_name,

case when sls.date_key = ?

then

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) as sales_life,

from edw.jjwhse.tew_l_stock_status_sku sku

left outer join

edw_jj.sales.vew_r_sls_sku_loc_wk sls

on sls.sku_key = sku.sku_key

where sls.date_key <= ?

and sku.sku_type_desc = 'sales'

and sls.loc_key not in (6,9,10,11,12,13,14,106)

and sku.new_concept_cd is not null

group by sku.new_concept_cd, sku.color, sls.date_key

|||

Mike Batton wrote:

What about in the case part of a select statement... should that work or not?

Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||

Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.

Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.

error_number() won't return error code

hi all,

i have sql statement in ExecuteSQLTask, connecting to AdventureWorks

BEGIN TRY
insert into person.contacts(contactid) values (1);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

i purposely put a table that does not exist in the database to catch
the error. however, the error number that is 208 won't appear, instead
the whole error message will be displayed.

if i change the query in try block to
insert into person.contact(contactid) values (1);

it'll return the error number 544, which is correct.

what did i do wrong?
thanks!

You did nothing wrong.

Try/Catch doesn't catch all errors, specifically compile time errors (there are a few others). Try running the following, "more obvious" compile time error. This compile time error won't be caught either, since the execution of the statements never happens.

BEGIN TRY

PRINT 'Inside Try-Block'

'This is not sql'

END TRY

BEGIN CATCH

SELECT error_number() as 'Error In Try Block'

END CATCH

|||

thanks jaegd for your reply.. it didn't say in BOL that try/catch doesn't catch all errors or maybe i left that out.

anyhow, my problem is not solved. is there an alternative where i can catch any errors that occur?

|||See the following BOL reference for the constructs which Try/Catch does not trap.
http://msdn2.microsoft.com/en-us/library/ms175976.aspx

However, To catch almost every error (including compile time and deferred name resolution errors), you can use dynamic SQL in combination with sp_executesql.

BEGIN TRY
DECLARE @.not_sql NVARCHAR(100)
SET @.not_sql = 'This is not sql'
EXEC sp_executesql @.not_sql

END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
END CATCH

Note that almost every error is caught with this method, because broken client connections are not

trapped, hopefully for relatively obvious reasons (you have no

connection).|||thanks a lot jaegd

error: when insert into table

Violation of PRIMARY KEY
constraint 'PK__tablename__182C9B23'.
Cannot insert duplicate key in object 'tablename'.
The statement has been terminated.
Thanks reply
The error that you are getting is related to the violation of "PRIMARY KEY"
constraint. To know what primary key is defined on the table run the
following statment.
sp_pkeys <table name>
Make sure that you are inserting non duplicate values in the primary key.
ex:
create table t(idd int primary key)
insert into t values (1)
insert into t values (2)
insert into t values (3)
insert into t values (3) --error
in above case 4th insert into will fail because of duplication of primary
key value.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com