How to tracking trigger

Hi guys,
I'm using trigger to write multiple files (5-6 file on multiple directories)
using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
I've found that sometime it does not output all the files it should be
written (almost the last file). So I'd like to check what happen on my
trigger. Could you please suggest me?
Thanks in advance,
Thana N.Thana
Run SQL Server Profiler to capture SP:StmtCompleted event.
"Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I'm using trigger to write multiple files (5-6 file on multiple
directories)
> using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> I've found that sometime it does not output all the files it should be
> written (almost the last file). So I'd like to check what happen on my
> trigger. Could you please suggest me?
> Thanks in advance,
> Thana N.
>|||Uri,
Is there anyway to track the historical for this? Because it is running on
the production and I cannot have a right to run Profiler for this (my sql
user is not in a group of sysadmin).
Thanks for reply,
Thana N.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ONJrz0S9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thana
> Run SQL Server Profiler to capture SP:StmtCompleted event.
>
> "Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
> news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > Hi guys,
> >
> > I'm using trigger to write multiple files (5-6 file on multiple
> directories)
> > using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> >
> > I've found that sometime it does not output all the files it should be
> > written (almost the last file). So I'd like to check what happen on my
> > trigger. Could you please suggest me?
> >
> > Thanks in advance,
> > Thana N.
> >
> >
>

How to tracking trigger

Hi guys,
I'm using trigger to write multiple files (5-6 file on multiple directories)
using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
I've found that sometime it does not output all the files it should be
written (almost the last file). So I'd like to check what happen on my
trigger. Could you please suggest me?
Thanks in advance,
Thana N.Thana
Run SQL Server Profiler to capture SP:StmtCompleted event.
"Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I'm using trigger to write multiple files (5-6 file on multiple
directories)
> using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> I've found that sometime it does not output all the files it should be
> written (almost the last file). So I'd like to check what happen on my
> trigger. Could you please suggest me?
> Thanks in advance,
> Thana N.
>|||Uri,
Is there anyway to track the historical for this? Because it is running on
the production and I cannot have a right to run Profiler for this (my sql
user is not in a group of sysadmin).
Thanks for reply,
Thana N.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ONJrz0S9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thana
> Run SQL Server Profiler to capture SP:StmtCompleted event.
>
> "Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
> news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> directories)
>

How to tell if I can convert a varchar to int

Hi,
I am attempting to write a procedure whereby I can get the maximum numeric
value in a varchar column (which may contain a mix of words and number
strings).
I'm trying to use:
SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
end ) FROM table
However, given that entries in the value column can be up to 255 characters,
I obviously have a problem when the number is too long to be cast as an int
(or indeed a bigint or whatever).
In the case of this happening, I'd like to ignore these values, so what I
need is a means of telling whether a value can be cast as an int before
attempting to do it. Using isnumeric(..) isn't enough because it can't
confirm that I can actually convert it.
Any help would be very welcome,
Thanks in advance,
Chris.Maybe try datalength(column) which gives you the number of characters...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>|||To add to Wayne's response, consider using LIKE instead of ISNUMERIC. The
ISNUMERIC function will return 1 for some obscure numeric expressions.
SELECT
MAX(CAST(Value as decimal(38)))
FROM MyTable
WHERE
Value NOT LIKE '%[^0-9]%' AND
DATALENGTH(Value) < 39 AND
Value <> ''
Also, the need to do this sort of thing may be symptomatic of a database
design issue. It's usually not a good idea to store different types of data
in the same column in a relational database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>

how to subquery with 2 or multiple columns

This is a small problem .. hope I could find quick answer
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solution
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
David Portas
|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help..

how to subquery with 2 or multiple columns

This is a small problem .. hope I could find quick answer :)
I am trying to write a query which uses a subquery in its where
condition. there are 2 columns (both PK) for which checking is
required.
I can run this query in DB2 with no problems. what is the correct
approach to do that in SQL Server 2005 ?
SELECT EMP_ID,DEPT_ID
FROM
STAGE_RMRS_PPLSFT
WHERE
(EMP_ID,DEPT_ID)
NOT IN
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM
STAGE_RMRS_PPLSFT
)
Note : I did managed to do the same in SQL with combining the 2
columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
as index scan doesn't apply here.
pls suggest me a better solutionAs you've noticed, SQL Server doesn't allow multiple columns in IN (row-valued constructors). I
suggest you use EXISTS instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution|||"Ashish Prasad" <ashishbitm@.gmail.com> wrote in message
news:97e9525a-73c6-4b5d-a39e-e0d872779f48@.d70g2000hsb.googlegroups.com...
> This is a small problem .. hope I could find quick answer :)
> I am trying to write a query which uses a subquery in its where
> condition. there are 2 columns (both PK) for which checking is
> required.
> I can run this query in DB2 with no problems. what is the correct
> approach to do that in SQL Server 2005 ?
> SELECT EMP_ID,DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> WHERE
> (EMP_ID,DEPT_ID)
> NOT IN
> (SELECT MANAGER_ID,MGR_DEPT_ID
> FROM
> STAGE_RMRS_PPLSFT
> )
> Note : I did managed to do the same in SQL with combining the 2
> columns into 1 as EMP_ID+''+DEPT_ID, but this has performance issues
> as index scan doesn't apply here.
> pls suggest me a better solution
Try one of the following. There is a difference however. Your query would
return no rows at all if either of MANAGER_ID or MGR_DEPT_ID contain any
nulls. Very often that is not a desirable result so the two alternatives
I've given here might be suitable.
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT
EXCEPT
SELECT MANAGER_ID, MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT;
SELECT EMP_ID,DEPT_ID
FROM STAGE_RMRS_PPLSFT S
WHERE NOT EXISTS
(SELECT MANAGER_ID,MGR_DEPT_ID
FROM STAGE_RMRS_PPLSFT
WHERE MANAGER_ID = S.EMP_ID
AND MGR_DEPT_ID = S.DEPT_ID);
Going by the column names alone I would guess this table is not well
normalized (MANAGER_ID->MGR_DEPT_ID?), which is something you should
probably consider fixing.
HTH
--
David Portas|||will use the correlated query with EXIST clause ..
Thanks a lot for your Help.. :)

How to Store System Variables in DB?

Hi,

I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @.IntDate and also changed the parameter name but the error was the same.

Please help how to write this System Variable value in to that table.

Regards,
Samy

In this situation I recommend using expressions rather than parameters. This article talks about expressions to store a SQL statement for an OLE DB Source component but this principle can apply to Execute SQL Task as well: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

I know Jamie is a fan of using expressions over parameters, but I disagree, and there are situations where parameters are still better. Security being the big issue with expressions.

Anyway, to get your parameter to work, make sure you have set mappend the parameter on the "Parameter Mapping" page of the Execute SQL Task. Select variable StartTime, direction is Input, Data Type is DATE, and Parameter name is 0.

For OLE-DB connections the parameter name is a zero based incrementing count of the parameters.

The Data Type is DATE, which seems rather confusing, as normally DBTIMESTAMP is the SSIS data type to use, however these are clearly not normal SSIS data types. We just do not have enough different types within SSIS!

|||

Dear Darren,

I did what you mentioned, but the package is not functioning:

SSIS package "HRM_tblParty.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)" 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.

Task failed: Execute SQL Task

Warning: 0x80019002 at HRM_tblParty: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "HRM_tblParty.dtsx" finished: Failure.

This is my Query:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)

and I mapped the System::StartTime to Parameter0 (Type: Date and Direction: Input)

Pleae help,
Sassan

|||

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

|||

SamyLahur wrote:

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

The OLE DB Source component can be set by selecting a table, entering a SQL statement, or taking a SQL statement that is stored in a variable. You need to select the 3rd of these options.

-Jamie

|||

The parameter name should be 0, nothing else just the character for zero.

For the expression option you need to specify the variable name correctly-

@.[System::StartTime]

You will also need to convert to date time valeu to a string to do the concatenation-

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= '" + (DT_WSTR, 20 )@.[System::StartTime] + "'"

Note the quick and dirty conversion I have used is open to date format misinterpretation issues DMY, MDY etc. You should break it down and convert to component parts into a string as an unambiguous format. For SQL Server yyyymmdd is unambiguous. Of course using parameter support avoids this issue as it is treated as a date type all the way through.

|||

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

|||Have you read my last post? I appreciate that you may well have missed it due to the exceptionally well designed interface and threading capabilities of these forums.|||

SamyLahur wrote:

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

My first guess would be that you need to cast the date as a string.

There is no expression editor on variable expressions which is (and I'm being polite here) very very BAD. It will appear in SP1 but in the meantime here's a tip for you. Build your expression against the "Description" property of the package. This will enable you to use the expression editor to build your expression and validate it within there. When you have it working, copy and paste the expression into the appropriate place for the variable.

Hope that makes sense.

-Jamie