Passing Job to a JobStep

Back in the day of COM and DMO. You could create a COM component, create an SQL job, and using an 'AcitveX Script' job step pass the Job to the component.

The component could then lookup the job schedule using DMO to figure out how long it should run.

Now in the days of SMO and CLR. I want to pass the Job to a CLR Stored Procedure as part of a Transact SQL step... (without hard coding the JobId in the script)

any help is appreciated...

rich

Well, I guess I'll have to do some extra work.

I will automate the job creation and pass put the JobId into the Trasact SQL.

The reason I need this is that the sproc is long running and manages it's own schedule. After it executes it determines the next runtime and updates the schedule on the job

rich

sql

Passing Impersonation through the ReportViewer IFrame?

I am trying to impersonate a user through the web.config of the webapp that
I'm using to view reports. Reports are pulled back through the ReportViewer
object, and so, pulled back through an IFrame.
I impersonate the user which we set up in the report server, so that we can
bypass windows authentication dialog in the webapp and have impersonation do
this for us, so we can go straight into the report. When a user tries to go
to the service directly they get the prompt, and so are restricted. This is
what I'm after.
But, when I hit the report gen button on the Report Viewer it still prompts
for user/pass in a windows dialog. Is impersonation lost through the
IFrame? Does anyone have a better solution?
Appreciated,
Matt SIt sounds like you're creating a custom report manager. The call to the
report URL is separate from the call to the report manager; they are
separate web apps on the same server. In the Microsoft forms authentication
sample code, they deal with this issue by sharing session information
between the report manager and report server via an auth cookie.
I'm not sure exactly how this will apply to your situation, but hopefully
that helps you at least start tracking down the issues. See this article
for more information on the security interaction between the report manager
and report server:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Matt Swift" <matthewswift@.deletethisbitplshotmail.com> wrote in message
news:OuzKQpd4EHA.1524@.TK2MSFTNGP09.phx.gbl...
>I am trying to impersonate a user through the web.config of the webapp that
> I'm using to view reports. Reports are pulled back through the
> ReportViewer
> object, and so, pulled back through an IFrame.
> I impersonate the user which we set up in the report server, so that we
> can
> bypass windows authentication dialog in the webapp and have impersonation
> do
> this for us, so we can go straight into the report. When a user tries to
> go
> to the service directly they get the prompt, and so are restricted. This
> is
> what I'm after.
> But, when I hit the report gen button on the Report Viewer it still
> prompts
> for user/pass in a windows dialog. Is impersonation lost through the
> IFrame? Does anyone have a better solution?
> Appreciated,
> Matt S
>sql

passing datetime variables into a bcp statement

Hi

I posted a question a while back about passing dates through a BCP SQL statement and received the answer that they should look as follows

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

Now I need to do it differently and I have declared date variables and set the values and now i want to place the varaible names into the statement but i am receiving errors such as cannot convert character to datetime and once again i am looking for the correct way to type the bcp statement

I have the following example

Declare @.EndDate Datetime

Declare @.StartDate DateTime

Declare @.FilePath varchar (250)

Declare @.ServerName varchar (250)

Declare @.sql varchar(8000)

SET @.EndDate = '05/01/2007'

SET @.StartDate = '06/01/2007'

SET @.FilePath = 'C:\test.txt'

SET @.ServerName = 'SQLEXPRESSSERVERPATH'

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor @.EndDate, @.StartDate " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

I have tried

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor '' + @.EndDate+ '', '' + @.StartDate + ''" queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

And many many other variations but am mystified as to the correct format.

Can anyone help?

Syvers

Try:

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor ' + @.EndDate + ', ' + @.StartDate + ' " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

|||

Code Snippet

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''' + convert(varchar(10), @.EndDate, 101) + ''', '''+ convert(varchar(10), @.StartDate, 101) + '''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

|||

Thanks Dale, my thinking was not on all cylinders this morning -had to rush out for a meeting.

|||

Team work!

|||Thank you for your help, works great now.

Passing back NULL

Is there any way in a query to pass back a NULL if no data is found?
I have this query that is looking at our Customer table and some customers
may not exist but in that case I want to pass back a NULL...
My SQL looks like so...
SELECT *
FROM _CUSTOMER_
WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
The first name, Susanna Alway does NOT exist...so in that case I'd like to
pass pack a NULL.
These groups are the best!
Please help me out!
Thanks!A null.....? A single null column? An empty resultset?
Thomas
"RTP" <RTP@.discussions.microsoft.com> wrote in message
news:8847FFB7-02DA-4357-A62C-A95BE3B18576@.microsoft.com...
> Is there any way in a query to pass back a NULL if no data is found?
> I have this query that is looking at our Customer table and some customers
> may not exist but in that case I want to pass back a NULL...
> My SQL looks like so...
> SELECT *
> FROM _CUSTOMER_
> WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
> OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
> OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
> OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
> OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
> OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
> The first name, Susanna Alway does NOT exist...so in that case I'd like to
> pass pack a NULL.
> These groups are the best!
> Please help me out!
> Thanks!|||Yes use If Exists
If Exists (Select * From ...
Where ... ) -- make sure From & Where clause are same as main
query
Select <Stuff> From ...
Where ...
Order By ...
Else
Select Null As Col1Name, Null as Col2Name,
Null as Col3Name, etc...
After your query
"RTP" wrote:

> Is there any way in a query to pass back a NULL if no data is found?
> I have this query that is looking at our Customer table and some customers
> may not exist but in that case I want to pass back a NULL...
> My SQL looks like so...
> SELECT *
> FROM _CUSTOMER_
> WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
> OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
> OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
> OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
> OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
> OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
> The first name, Susanna Alway does NOT exist...so in that case I'd like to
> pass pack a NULL.
> These groups are the best!
> Please help me out!
> Thanks!|||That won't work because the EXISTS will return a TRUE condition for the OR i
n
the query. I have to build this for 7,000 names so I can't query each name
individually. I'm thinking of using a JOIN which will pass back NULLS if the
row doesn't exist. My problem here is that a user messed up a whole
spreadsheet where there are e-mail addresses and I'm cutting and pasting
these 7,000 names out of an e-mail spreadsheet and then using Query Analyzer
to go up against the database to see if we have their e-mail address. The
7,000 names include those people which may NOT be in out _CUSTOMER_ table an
d
there is no way of determining whether they're in the database or not until
I
query.
Any help or insight would be GREATLY appreciated!!!
"CBretana" wrote:
> Yes use If Exists
> If Exists (Select * From ...
> Where ... ) -- make sure From & Where clause are same as main
> query
> Select <Stuff> From ...
> Where ...
> Order By ...
> Else
> Select Null As Col1Name, Null as Col2Name,
> Null as Col3Name, etc...
>
> After your query
> "RTP" wrote:
>|||Since this sounds like a one-time thing, then write a script that cycles thr
ough
each address from the spreadshet and does whatever sophisticated checking an
d
data cleasing is necesassry.
If you do that in T-SQL, then you'll use cursors or you could do it in some
other language like VBA from Excel.
Thomas
"RTP" <RTP@.discussions.microsoft.com> wrote in message
news:098909F0-4473-4145-94EC-056005EB917A@.microsoft.com...
> That won't work because the EXISTS will return a TRUE condition for the OR
in
> the query. I have to build this for 7,000 names so I can't query each name
> individually. I'm thinking of using a JOIN which will pass back NULLS if t
he
> row doesn't exist. My problem here is that a user messed up a whole
> spreadsheet where there are e-mail addresses and I'm cutting and pasting
> these 7,000 names out of an e-mail spreadsheet and then using Query Analyz
er
> to go up against the database to see if we have their e-mail address. The
> 7,000 names include those people which may NOT be in out _CUSTOMER_ table
and
> there is no way of determining whether they're in the database or not unti
l I
> query.
> Any help or insight would be GREATLY appreciated!!!
> "CBretana" wrote:
>

Passing back more than 1 output parameter to VBA code

I have a stored procedure which has 2 output parameters, namely @.RecCnt and
@.RetCode. In the stored procedure, I am using the SET statements to pass the
data back. I am calling the stored procedure from my VBA code. I use
objCmd.Execute options:=adExecuteNoRecords.
I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
Could any of you tell me what is wrong?
VBA Code:
--
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_addback_selectcount_CorpAcctCtr"
.NAME = "sp_addback_selectcount_CorpAcctCtr"
.CommandType = adCmdStoredProc
'Create parameter list for objCmd
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
3, vstrCorp)
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
5, vstrAcct)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
vstrCtr)
.Parameters.Append .CreateParameter("RecCnt", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
.Execute options:=adExecuteNoRecords
End With
If objCmd.Parameters("RetCode").Value Then
rlngRecCnt = objCmd.Parameters("RecCnt").Value
fnGetAddbackCnt = True
End If
--Store Procedure
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GOSoooorrryyy...Goofed up the code and stored procedure...
Here is the correct one:
VBA Code:
......
.....
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_lotusdata_load_from_recon"
.NAME = "sp_lotusdata_load_from_recon"
.CommandType = adCmdStoredProc
'Create parameter list for oCmd
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
Len(vstrAcct), vstrAcct)
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
Len(vstrCorp), vstrCorp)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
Len(vstrCtr), vstrCtr)
.Parameters.Append .CreateParameter("RecsAffected", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
'-- execute the proc
.Execute options:=adExecuteNoRecords
'-- return success if stored proc is successful
If .Parameters("RetCode").Value = True Then
rintRecsAffected = .Parameters("RecsAffected")
fnLoadLotusData = True
End If
....
...
Stored Procedure Code:
--
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GO
Sorry for the error.
Regards,
Paddy
"Paddy" wrote:

> I have a stored procedure which has 2 output parameters, namely @.RecCnt an
d
> @.RetCode. In the stored procedure, I am using the SET statements to pass t
he
> data back. I am calling the stored procedure from my VBA code. I use
> objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
> VBA Code:
> --
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
> .NAME = "sp_addback_selectcount_CorpAcctCtr"
> .CommandType = adCmdStoredProc
> 'Create parameter list for objCmd
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> 3, vstrCorp)
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> 5, vstrAcct)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
5,
> vstrCtr)
> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> .Execute options:=adExecuteNoRecords
> End With
>
> If objCmd.Parameters("RetCode").Value Then
> rlngRecCnt = objCmd.Parameters("RecCnt").Value
> fnGetAddbackCnt = True
> End If
> --Store Procedure
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO|||Paddy (Paddy@.discussions.microsoft.com) writes:
> I have a stored procedure which has 2 output parameters, namely @.RecCnt
> and @.RetCode. In the stored procedure, I am using the SET statements to
> pass the data back. I am calling the stored procedure from my VBA code.
> I use objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
How do you conclude that you can not retriev the RecCnt value?
I don't think it should really matter, but it is a good idea to align
the names in the parameters collection with the actual parameters names.
Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
@.RetCode. Furthermore there is one parameter missing. That is, each
stored procedure has a return value, which in ADO you declare as a the
first parameter and as adParamReturnValue. Then again, I think it's find
to not include that paraemeter.

> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
The sp_ prefix is reserved for system objects, and SQL Server first
looks in the master database for these. Don't use it, in your own code.

> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
I think adParamInputOutput are more appropriate as that is what they
are.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The above code always return RecsAffected value as 0. When I run the sp in
Query Analyser it shows the correct data.
Please help.
Regards,
Paddy
"Paddy" wrote:
> Soooorrryyy...Goofed up the code and stored procedure...
> Here is the correct one:
> VBA Code:
> ......
> .....
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_lotusdata_load_from_recon"
> .NAME = "sp_lotusdata_load_from_recon"
> .CommandType = adCmdStoredProc
> 'Create parameter list for oCmd
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> Len(vstrAcct), vstrAcct)
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> Len(vstrCorp), vstrCorp)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
> Len(vstrCtr), vstrCtr)
> .Parameters.Append .CreateParameter("RecsAffected", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> '-- execute the proc
> .Execute options:=adExecuteNoRecords
> '-- return success if stored proc is successful
> If .Parameters("RetCode").Value = True Then
> rintRecsAffected = .Parameters("RecsAffected")
> fnLoadLotusData = True
> End If
> ....
> ...
> Stored Procedure Code:
> --
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO
> Sorry for the error.
> Regards,
> Paddy
> "Paddy" wrote:
>|||Hi, Erland,
Please read my 2nd and 3rd message. I copied the wrong code in the message.
I posted the code which has problem in my second message.
I debugged the VBA code and know it is returning 0.
I knew about sp_ prefix, but used it for easily distinguish stored
procedures. I know there is some performance penalties.
Anyway, is there anything wrong in the way I am setting the output parameter
in the stored procedure?
Thanks.
Paddy
"Erland Sommarskog" wrote:

> Paddy (Paddy@.discussions.microsoft.com) writes:
> How do you conclude that you can not retriev the RecCnt value?
> I don't think it should really matter, but it is a good idea to align
> the names in the parameters collection with the actual parameters names.
> Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
> @.RetCode. Furthermore there is one parameter missing. That is, each
> stored procedure has a return value, which in ADO you declare as a the
> first parameter and as adParamReturnValue. Then again, I think it's find
> to not include that paraemeter.
>
> The sp_ prefix is reserved for system objects, and SQL Server first
> looks in the master database for these. Don't use it, in your own code.
>
> I think adParamInputOutput are more appropriate as that is what they
> are.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Paddy (Paddy@.discussions.microsoft.com) writes:
> The above code always return RecsAffected value as 0. When I run the sp in
> Query Analyser it shows the correct data.
But RetCode is still True then?
When you run from QA, I suspect that you run as as or dbo, but how
do run the application? Does that account have CREATE TABLE permissions?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

passing a timestamp datatype column to a variable and back

After several hours of trying, I trow the towel in the ring and come here to ask a question.

Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).

What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable

I try to do this in 2 sql execute tasks:

sqltask 1: "select @.@.DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?

DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

INT64 and UINT64 don't work error message that types for column and parameter are different

STRING works

Then I want to store this variable back in a table of a different data source

sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...

if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ?

if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.

Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?

How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?

tx for any help

Dirk

After some more hours (It just kept anoying me till late in the evening) I finaly found a way to make this work.

Make the variable in SSIS of type string

To store a timestamp as a variable

select Cast(timestampcolumn as bigint) as outputcolumn from controltable

create result set to map outputcolumn to variable

(SSIS wil do the conversion to string itself) Know that I tried to include this in the sql a convert to bigint an then to string but didn't work.

To use the timestamp in your queries or write it to another table

select * from table where timestampcolumn >= CAST(? AS BIGINT)

parameter mapping variable input type VARCHAR

(SSIS wil to the conversion from bigint to binary(8) itself

Got this working.

Point stays that datatypes should be more alligned between all the different places we use them, scripts, expressions, variables, parameters, sql data types...

Anyone got a better way, let me know.

tx

Dirk

|||

SSIS can genertae timestamps without an external data source. Is that not an option for you?

-Jamie

|||

Hi Jamie,

Don't think so. Let me explain what the purpose of all this was. The source ERP system uses timestamp columns on the tables. So I would like to use these columns as ModificationTime for my incremental load of new data in the DWH.

My problem was that I needed to get the @.@.DBTS from the source ERP system (hence the first SQL task) so that I would know the timestamp from the moment the upload started (my datapumps have a where clause like WHERE ModificationTimestamp >= TimestampOfStartLastSuccesfullUpload, so it will get all new and changed records since the start of the last upload).

Then I needed to store this Timestamp in my control table of the DWH and that's the 2d SQLtask.

I would have been extremly simple if the controltable was in the source ERP system, I could just insert the @.@.DBTS, but that was not an option. So I needed to pass the timestamp between the 2 SQL tasks. And that was my problem, getting the timestamp from the first select SQL task store it in a variable and pass that variable to the second insert sql task.

Perhaps this would have been easier in a script task, but I'm not that good at cooding. I haven't passed the level of copy, paste and modify some code ;-).

What do you think ? Is there a simpler solution ?

Dirk

|||

Ah I understand now. You need to persist the max timestamp between executions. That's a common requirement of course.

I may be mistaken but it seems the format of the timestamp is somewhat proprietary, hence the solution that you have come up with should be the most suitable - it sounds as though it will work fine though.

Question. What does the timestamp value look like? i.e. Can you paste it up here?

Regards

Jamie

|||

Dirk Van der Straeten wrote:

(BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

Yes, there is. Drag the variable into a Watch window within the BIDS environment. When you execute and break you will be able to look at the value of the variable.

-Jamie

|||

Jamie,

Following select on the database and resultset.

select @.@.dbts as timestamp, CAST(@.@.dbts as bigint) as ConvertedInteger, CAST(@.@.dbts as varchar) as ConvertedVarchar

0x00000000000F94C7 1021127 _

The Timestamp is a binary(8) that is non nullable. But you can also interpreted is a an 8 byte integer which is the same as a bigint. Where __ is actualy the blanc of the string. That was the problem that converting directly from timestamp into the string variable did not work. Strangly I also didn't get it to work when I made the Variable a UI64 or I64 and then user LARGE_INTEGER as parameter type. He gave the error that the types where not compatible.

Got the watch thing figured out now.Tx.

/Dirk

Passing a list/array to an SQL Server stored procedure 2005

Hi, I m using sql 2005 as a back end in my application...

I am useing Store procedure..for my data in grid..

ALTERPROCEDURE [dbo].[ProductZoneSearct]

(

@.Productidchar(8),
@.Pronamechar(8),
@.radiusint,
@.modevarchar(5)='M',
@.Zonenvarchar(1000),

)

AS
SETNOCOUNTON;
Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))

Insert #Product
Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment
,Miles, UserID, Daily, Phone
From [tblproduct]
Where city IN (@.Zone)

Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment
,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone
From #Product As L
Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID
Order By DateAvailable

if i pass value in"where city in (@.Zone)" and@.Zone ='CA','AD','MH' then it can not get any result..but if writewhere city in ('CA','AD','MH') then it give me perfact result..

I tried to below syntax also but in no any user
Where city IN ('+@.Zone+')

In short if i pass value through varibale (@.Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?

Please Hel[p me !!!

Thank you !!!

Check out this blog post:

Passing lists to SQL Server 2005 with XML Parameters

|||

Hmmm... you may be better off writing an object data source and filtering the data after the select. Otherwise, have a look at sp_executesql or somesuch...

|||

Problem is If i pass only one value into variable then it gives me result but if i pass more than one value then it wount give me result.
Example..If i pass @.Zone='KS' then it works fine but if i pass @.Zone='KS','MS' Then it wong give me data...coz "," (comma) .seperated..may be it count after comma seprateion its a new value...or i dont know why i m not getting result

Please help me

Thank you & Regards.

|||

Sorry I am not getting you..:-((

|||

Instead of passing the parameters to the stored procedure, you can just select everything, and filter the data after. Look at FilterParameters and FilterExpression for SQL DataSource. I think you can set the FilterExpression="WHERE Region IN (@.Region)", and add @.Region as a FilterParameter, or something like that. Otherwise, you can make an ObjectDataSource that executes the stored procedure, and you can filter the resultset inside there however you want.

EDIT

Well, the whole Filter parameter thing with an "IN" clause isn't working for me... however, I think you can handle the datasource's filtering event to build the filter expression.

Pass user credentials RS web service?

I'm using the RS web service to pull back customized reports via a C#
app, which is running under a service account.
I need to pass in the user's credentials to the Render method to make
sure the user has been given access to the report on the Security tab
of the front end.
If I pass the default credentials, the service account's credentials
are used.
Can anyone help? Many thanks.
BurtYou need to create an instance of System.Net.NetworkCredential.
Try:
rs.Credentials = new System.Net.NetworkCredential(UserName, Password);
instead of:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
"Burt" wrote:
> I'm using the RS web service to pull back customized reports via a C#
> app, which is running under a service account.
> I need to pass in the user's credentials to the Render method to make
> sure the user has been given access to the report on the Security tab
> of the front end.
> If I pass the default credentials, the service account's credentials
> are used.
> Can anyone help? Many thanks.
> Burt
>|||David,
Thanks, but how do I get the current user's password? I'm using windows
authentication on this intranet app.
Burt|||FYI, the solution was:
WindowsImpersonationContext windowsImpersonationContext = null;
WindowsIdentity currentIdentity =(WindowsIdentity)Thread.CurrentPrincipal.Identity;
windowsImpersonationContext = currentIdentity.Impersonate();
MyService.Credentials =System.Net.CredentialCache.DefaultCredentials;
windowsImpersonationContext.Undo();
windowsImpersonationContext = null;

Pass Scope_Identity() back to the calling application

I'm using an Access2K front end on a SQL Server2K backend.
I use Scope_Identity() in a lot of stored procedures to pass the
newwly inserted record's unique ID to the next select statement in the
same stored procedure.
What I'm wondering is how I can pass the Scope_Identity back to the
calling application.
I'm hoping someone can show me the SP code and the aceess code needed
to accomplish the following:

I insert a new record in a table with a stored procedure. The SP
passes the uniqueID (Scope_Identity) back to MS Access, then MS Access
uses the uniqueID when calling another function...

thanksHi

Check out the following that shows how to use stored procedures and output
parameters.

http://msdn.microsoft.com/library/d...html/sa00i8.asp

John

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0407170829.39971192@.posting.google.c om...
> I'm using an Access2K front end on a SQL Server2K backend.
> I use Scope_Identity() in a lot of stored procedures to pass the
> newwly inserted record's unique ID to the next select statement in the
> same stored procedure.
> What I'm wondering is how I can pass the Scope_Identity back to the
> calling application.
> I'm hoping someone can show me the SP code and the aceess code needed
> to accomplish the following:
> I insert a new record in a table with a stored procedure. The SP
> passes the uniqueID (Scope_Identity) back to MS Access, then MS Access
> uses the uniqueID when calling another function...
> thanks

pass in null/blank value in the date field or declare the field as string and co

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please helpThe stored procedure will accept null if you define the parameter that way:

create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go

exec TESTPROCEDURE '1/1/2003'
go

exec TESTPROCEDURE
go

blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan

Hello Dan,

What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?

Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan

Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have

Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))

mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.

Also there must be a better way to write the code that I have above.

Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?

Dan

Pass back one value from UDF having found it in a table

How do I pass back only one value, such as TotalJobPrice from the tblJobRecords when I know which JobID to Select. All I want is the TotalJobPrice returned from the UDF, not a record from the table. I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)
Hope that is clear.
Thanks in advance,you should write your UDF as a scalar function.

Post your fnTotalJobPrice here.|||I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @.TotalJobPrice = fnTotalJobPrice(@.JobID)I would like to write a function that would allow me to be able to get the TotalJobPrice out of the function. But I DO NOT KNOW HOW to get one field of data out of one record in a table using T-SQL. I am brand new at T-SQL and would like to know if there is a way to get one field of data out of one record so I can put that data into a local variable. I don't have fnTotalJobPrice to post. I want to learn how to do this one thing, then I feel I can write it. Thanks,|||declare @.i int
select @.i = myintcolumn from mytable where ...

Just make sure that the query returns only one row. if it returns multiple, which value you get is undefined.|||Thank you very much for your input on how to obtain a value from a table within a stored procedure or function. Here is my stored procedure, as I found out I did not need the function after all, but the same coding seems to hold true in both types of objects. I also noticed that I needed the SELECT in the SQL string. Any idea why? I thought is must be because I was not assigning the return of that SQL string to a local variable.ALTER PROC spCalculateTotalBillToDate
@.JobID as int
AS
BEGIN
DECLARE @.TotAmount AS Money
DECLARE @.BillOption AS varchar(3)
DECLARE @.BillCodeID AS int
DECLARE @.BillingStep as int
SET @.TotAmount = 0
SET @.BillCodeID = (SELECT Max(BillCodeID) FROM dbo.tblInvoice
WHERE JobID = @.JobID)
IF ISNULL(@.BillCodeID, ' ') = ' '
Begin
GOTO Return0
END
-- Bill Option
SET @.BillOption = (SELECT BillOption FROM dbo.tblBillOptions
WHERE BillOptionID =
(SELECT BillOptionID FROM dbo.tblProject
WHERE ProjectID =
(SELECT ProjectID FROM dbo.tblJob WHERE JobID = @.JobID)))
SET @.BillingStep = (SELECT max(BillingStep) FROM dbo.tblJobStatus
WHERE JobID = @.JobID AND JobStatusID <=
(SELECT JobStatusID FROM dbo.tblJob WHERE JobID = @.JobID))
--Calculate TotAmount
SET ANSI_WARNINGS OFF
SET @.TotAmount = (SELECT Sum(Amount) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob WHERE JobID = @.JobID)
AND BillCodeID <= @.BillCodeID)
SET ANSI_WARNINGS ON
IF @.BillingStep >= 89
BEGIN
--Print 'BillingStep is >= 89'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END
ELSE
IF @.BillOption = '_'
BEGIN
--Print 'BillOption = underscore'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID,
(SELECT Sum([Percent]) FROM dbo.tblPlanBillAmounts
WHERE PlanID = (SELECT PlanID FROM dbo.tblJob
WHERE JobID = @.JobID) AND BillCodeID <= @.BillCodeID))
END
ELSE
IF @.BillOption = 'D'
BEGIN
--Print 'BillOption = "D"'
SET @.TotAmount = @.TotAmount + dbo.fnAddTheOptionLines(@.JobID, 1)
END

Return0:
--print CAST(@.TotAmount AS VARCHAR)
SELECT @.TotAmount TotAmount
END|||not exactly sure what you are asking. There are several ways to return data from a proc. this article will educate you on all of them:

http://www.sommarskog.se/share_data.html|||What I was asking was about needing to use "SELECT" when obtaining the information from the table. The code you shared with me you did NOT use "SELECT", so I asked if this was different because I was using in within some other SQL, rather than just assigning the data to a local variable. If you notice within the stored procedure I posted, I was able to return what I wanted to the caller.

My original question was not how to return data FROM a function, but how to get data from a table to be used IN a function.

Again, thanks for your help.|||hmm.

in the code I posted for you, i *did* use "select" to get data from a table. I didn't type it in all caps though. sql keywords are not case sensitive. I don't know of any way to get data out of a table other than select...

in any case it sounds like you got the answer you were looking for. :)

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

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

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

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

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!

pasing a set of records to SP

hi
I need to send a set of records to a SP in order to make some comparisos and
calculations and return back from the SP the same records but modified
What is the best (faster,optimal) way to send the records from my client App
via internet to the SQLDB in order to the SP reading those records?
I just discover that a SP does not accept arrays as input parameters.
thks
kenhttp://www.sommarskog.se/arrays-in-sql.html
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:F69119F4-7020-4B87-9BCA-414F3A446AFC@.microsoft.com...
> hi
> I need to send a set of records to a SP in order to make some comparisos
and
> calculations and return back from the SP the same records but modified
> What is the best (faster,optimal) way to send the records from my client
App
> via internet to the SQLDB in order to the SP reading those records?
>
> I just discover that a SP does not accept arrays as input parameters.
> thks
> ken
>

Partitions and Common Practice

Hello All,
I posted a simillar question sometime back but I did not see any response. I
have modified my question quite a bit with more info and posting it hoping to
see some response this time :)
I have a Policy Master data file whose uniqueness is identified by the
following
columns (Business Key).
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction was entered into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
We loaded this file into the Staging table of SQLServer 2005 database --
STGPOLICY. The data in this staging table, STGPOLICY, will be processed and
populated into FACPOLICY table.
STGPOLICY Table has these 5 important columns apart from the other columns
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date]
Surrogate Key, UniqID, is an identity column that gets incremented as
records are inserted into the table.
For better performance and Maintainability, we decided to use Partitions on
FACPOLICY table with [Transaction Date] being the Partition Key.
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
For faster loading and processing, I have to Partition the STGPOLICY table
on [Transaction Date] column as this column is used for extensive
search/processing. So, I created the partition with [TransactionDate] as the
Partition column. However, when I try to create a unique index on the natural
keys the system gives an error as Transactiondate is not part of the Unique
Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
Date].
I went through the BOL and found that this is the ugly side of PArtitions.
Does that mean that I cannot define a primary key on the natural key when the
natural key is not a Partition Key? I'm left with either creating a nonunique
key or including the Partition Key along with the Natural Key. Is this a
common practice?
Now, with the FACPOLICY table, the UniqID adds another level of intrigue
since the Primary Key would be the UniqID (assuming that there is no
Partitions). With partitions, would it mean that I have to include
TransactionDate to UniqID to create a Primary Key ?
I'm looking for some common practices. Please help.
Thanks,
rgn
Hello All,
Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working on
Partitioning some of the tables for a datawarehousing project and need some
guidance/help.
I have a Policy Master table whose uniqueness is identified by the following
columns via a Primary Key Clustered constraint.
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction came into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
I created a Partition Function, Partition Scheme and the Created the table
on the Partition Scheme with the [Transaction Date] as the Partitioning
Column. Things looked good till this point. However, when I tried to create
the Primary Key on the above 4 columns, I got the following error:
Msg 1908, Level 16, State 1, Line 1
Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors
I understand what this error means. But I cannot modify the Primary Key by
adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
help?
Thanks,
rgn> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
Another other alternative is to not partition the natural key. The downside
is that you can't use SWITCH to quickly load data with the non-partitioned
index in place. If SWITCH is an important part of you partitioning
strategy, you can drop the non-partitioned unique constraint/index, SWITCH
in the new data and then recreate the constraint/index afterward.
I wouldn't go as far as to say that it is a common practice to add the
partitioning column to a unique constraint/index solely to facilitate
partitioning, but I suspect it is not all that unusual. Bending the
database design rules in favor of performance and manageability is an option
as long as you have mechanisms in place to ensure data integrity.
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
All partitioned indexes include the partitioning column and unique
partitioned indexes must explicitly include the partitioning column in the
key. The partitioning column is implicitly included in non-clustered
indexes (but not part of the key) if not already part of the clustered or
non-clustered key. So, like the natural key, you'll need to add the
partitioning column to the surrogate key to form a composite surrogate key.
It's a little more inconvenient to deal with the composite key but more
palatable that adding the partitioning key to a natural key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:7B2D7BEE-F322-46B0-8CB4-4493C7C7E86B@.microsoft.com...
> Hello All,
> I posted a simillar question sometime back but I did not see any response.
> I
> have modified my question quite a bit with more info and posting it hoping
> to
> see some response this time :)
> I have a Policy Master data file whose uniqueness is identified by the
> following
> columns (Business Key).
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction was entered into the system and since it is
> a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> We loaded this file into the Staging table of SQLServer 2005 database --
> STGPOLICY. The data in this staging table, STGPOLICY, will be processed
> and
> populated into FACPOLICY table.
> STGPOLICY Table has these 5 important columns apart from the other columns
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date]
> Surrogate Key, UniqID, is an identity column that gets incremented as
> records are inserted into the table.
> For better performance and Maintainability, we decided to use Partitions
> on
> FACPOLICY table with [Transaction Date] being the Partition Key.
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
>
> For faster loading and processing, I have to Partition the STGPOLICY table
> on [Transaction Date] column as this column is used for extensive
> search/processing. So, I created the partition with [TransactionDate] as
> the
> Partition column. However, when I try to create a unique index on the
> natural
> keys the system gives an error as Transactiondate is not part of the
> Unique
> Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
> Date].
> I went through the BOL and found that this is the ugly side of PArtitions.
> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
> I'm looking for some common practices. Please help.
>
> Thanks,
> rgn
>
>
> Hello All,
> Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working
> on
> Partitioning some of the tables for a datawarehousing project and need
> some
> guidance/help.
> I have a Policy Master table whose uniqueness is identified by the
> following
> columns via a Primary Key Clustered constraint.
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction came into the system and since it is a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> I created a Partition Function, Partition Scheme and the Created the table
> on the Partition Scheme with the [Transaction Date] as the Partitioning
> Column. Things looked good till this point. However, when I tried to
> create
> the Primary Key on the above 4 columns, I got the following error:
> Msg 1908, Level 16, State 1, Line 1
> Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
> Partition columns for a unique index must be a subset of the index key.
> Msg 1750, Level 16, State 0, Line 1
> Could not create constraint. See previous errors
> I understand what this error means. But I cannot modify the Primary Key by
> adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
> help?
> Thanks,
> rgn
>