Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonHello Simon,
The attachment didn't work here. Please contact me at ktegels@.develop.com
about this one.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
Simon
Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.co m:

> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception.
Force of habit really, just so I can see what the exception was a bit easier.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon
|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.co m...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>
|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
sql

Getting a simple function to run in the CLR . Please help :-(

Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

Getting 404 when trying to run http://localhost/reports

I have a several reports I want to load to the Report Manager I have run the report configuration manager and setup everything. Every time I try to deploy to the I keep getting a 404 http error. I also added several reports fro another machine to this server and I reset the config file but the report cant find the dataset ??

have you successfully deployed your report server? (in vs, from the solution explorer, right click your project and select 'Deploy')

Getting "infinity" when running this expression

When I run this expression, percentage, I'm still getting infinity as my
return when I have a zero in one of the columns
=IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1,
Fields!APR_CNT.Value,0)) = 0, 1, SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
= DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
Any ideas where I'm screwing up... Thanks in advanceThe IIF is a VB function. All function arguments are evaluated immediately
before the function is called. So this will fail: IIF(1 = 1, 1/1, 1/0) even
though it seems like 1/0 should not be evaluated because the condition (1=1)
is true.
This is what most likely happening in your expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:C3944D44-31B9-449C-ABC3-DFF6036BC46D@.microsoft.com...
> When I run this expression, percentage, I'm still getting infinity as my
> return when I have a zero in one of the columns
> =IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
> SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
> IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW())-1,
> Fields!APR_CNT.Value,0)) = 0, 1,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
> = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
>
> Any ideas where I'm screwing up... Thanks in advance

Getdate() overflowing datetime

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

Chris Honcoop wrote:

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

That sounds strange. Can you use an error output on the destination component to capture the erroring records?

-Jamie

|||

I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.

Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).

|||

Chris, have you recently installed any service packs or made other changes?

Thanks
Mark

|||

Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.

FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).

|||

FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.

So if this happens to you be on the lookout for this!!!

It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....

Honestly this is quite scary....

|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.

Getdate() overflowing datetime

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

Chris Honcoop wrote:

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

That sounds strange. Can you use an error output on the destination component to capture the erroring records?

-Jamie

|||

I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.

Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).

|||

Chris, have you recently installed any service packs or made other changes?

Thanks
Mark

|||

Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.

FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).

|||

FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.

So if this happens to you be on the lookout for this!!!

It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....

Honestly this is quite scary....

|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.

Getdate() overflowing datetime

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

Chris Honcoop wrote:

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

That sounds strange. Can you use an error output on the destination component to capture the erroring records?

-Jamie

|||

I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.

Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).

|||

Chris, have you recently installed any service packs or made other changes?

Thanks
Mark

|||

Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.

FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).

|||

FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.

So if this happens to you be on the lookout for this!!!

It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....

Honestly this is quite scary....

|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.sql

GetDate() Does not Return Milliseconds ?

Ladies / Gentlemen
If you run the following select statement you will find as I did
that GetDate() does no obtain Milliseconds - Why Not and can I use something
that does ?
Mark Moss
SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
CONVERT(varchar(20), GETDATE(), 109) AS Expr1Hi Mark
The getdate() function ALWAYS returns milliseconds. It's only when you
convert it to character that the milliseconds might be discarded.
In this situation, you have not provided enough space to hold the
milliseconds. Try varchar(25) instead of varchar(20)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <markmoss@.adelphia.net> wrote in message
news:%234V0y1ucGHA.4428@.TK2MSFTNGP03.phx.gbl...
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use
> something that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>|||Mark,
GETDATE() returns a datetime, which includes a millisec part. If you do
"SELECT DATEPART(ms,GETDATE())" you'll see this. Your problem is that
you're converting to a 20 char string and the millisec bit it getting
truncated. It's a string truncation issue, not a datetime issue. Try
using a varchar(30) or something bigger.
Also, you ought to use CURRENT_TIMESTAMP rather than GETDATE() as
CURRENT_TIMESTAMP is the ANSI equivalent (GETDATE() is Microsoft
proprietary) and in the majority of cases you should leave presentation
of the data up to the presentation layer (ie. the client) rather than
the DB engine.
*mike hodgson*
http://sqlnerd.blogspot.com
msnews.microsoft.com wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||The best way to get the format you want is through an user interface. VB
converts the SQL GETDATE() with milliseconds very well.
You can use
SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr2,
CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr1
and your milliseconds will show
Thanks Kllyj64
"msnews.microsoft.com" wrote:

> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use somethi
ng
> that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||lol..or you could just change the size of your VARCHAR()....
--
Thanks Kllyj64
"kllyj64" wrote:
> The best way to get the format you want is through an user interface. VB
> converts the SQL GETDATE() with milliseconds very well.
> You can use
> SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3)
,
> DATEPART(MS,GETDATE())) AS Expr2,
> CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
> DATEPART(MS,GETDATE())) AS Expr1
> and your milliseconds will show
>
> --
> Thanks Kllyj64
>
> "msnews.microsoft.com" wrote:
>|||Just increase VARCHAR to 40. 20 is too short and thus the reason why
msec is left off.
Mark
On Mon, 8 May 2006 16:19:10 -0600, "msnews.microsoft.com"
<markmoss@.adelphia.net> wrote:

>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>

GetDate not working

Hi,
I have a very simple query as follows. When I run, it returns no records. I
know there are records that should be in th result. If I insert today's
date instead of getdate() I get all the records for today's date. Getdate()
works if I use > or < instead of =. Any idea why is this behavior? Or is
there another way of accomplishing this.
Select COURSE_NBR as ItemValue
FROM ED_COURSE_CL_1
Where CLASS_DATE = GETDATE()Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
you think match the exact point in time when you run the query? The way to
do this is to use a range query, as you have already discovered. >= {date}
AND < {date + 1} ...
A
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records.
> I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date.
> Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||It is because datetime include both a date and a time portion. See
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records. I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date. Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||Aaron,
Select getdate() is working good and it returns todays date. For today's
date in my query should return one record, which I can verify it by inserting
today's date instead of getdate(). How can I build a range query to only get
records with today's date?
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
> like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
> you think match the exact point in time when you run the query? The way to
> do this is to use a range query, as you have already discovered. >= {date}
> AND < {date + 1} ...
> A
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> > Hi,
> > I have a very simple query as follows. When I run, it returns no records.
> > I
> > know there are records that should be in th result. If I insert today's
> > date instead of getdate() I get all the records for today's date.
> > Getdate()
> > works if I use > or < instead of =. Any idea why is this behavior? Or is
> > there another way of accomplishing this.
> >
> > Select COURSE_NBR as ItemValue
> > FROM ED_COURSE_CL_1
> > Where CLASS_DATE = GETDATE()
> >
> >
>
>|||> Select getdate() is working good and it returns todays date.
Correction : it returns today's date AND TIME.
> How can I build a range query to only get
> records with today's date?
DECLARE @.today SMALLDATETIME;
SET @.today = DATEDIFF(DAY, 0, GETDATE());
SELECT
...
WHERE DateColumn >= @.today
AND DateColumn < (@.today + 1);|||Thanks Aaron it's working great.
Cheers!!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Select getdate() is working good and it returns todays date.
> Correction : it returns today's date AND TIME.
> > How can I build a range query to only get
> > records with today's date?
> DECLARE @.today SMALLDATETIME;
> SET @.today = DATEDIFF(DAY, 0, GETDATE());
> SELECT
> ...
> WHERE DateColumn >= @.today
> AND DateColumn < (@.today + 1);
>
>
>

GET VS. POST METHODS and Querystring lengths

We've run into a rather sizable problem here.

When passing querystring values using GET, to our SQL Server Reporting Services Reports, we've discovered that we've exceeded the maximum URL length,.

We are thinking that we need to change how we're passing this data to our reports.

Can we use POST and transfer the data throught hte HTTP header and bypass this limitation?

Thanks.

Doug

What in the world could you be passing as parameters? Is it that there are so many parameters or that the parameter values are long strings?|||yes u can use hidden variables , param tags or Session variable to overcome this problem|||

good question.

We have data issues on our back end...which translates to list box items with multiple sometimes up to 10 values per selection. With multiple selections in a wizard that has 5 steps, they add up...

yeah it's ugly...

|||Maybe you should create a record in a temp database with all the keys/values. Then pass in the key to that record to your report. A datasource goes out gets the crazy values.

Get Today's Records

Hello, I have a query that needs to run daily and only collect records for
that day's activity. The table has a date/time field called TranDateSold.
Could someone please advise the proper syntax for the following logic:
SELECT * FROM tablename
WHERE TranDateSold (is today only)
I experimented with GETDATE() but it seemed to want to match today's date
and time to the hour and minute. Any ideas would be most appreciated.Hi,
Strip the time portion from the date returned by getdate() function before
comparison. Something like this:
select *
from tablename
where trandatesold = convert(vachar(8), getdate(), 112)
This assumes that the time is already zero-ed in trandatesold column.
hth,
Dean
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0BA6C334-70D4-4637-8528-1B838763F5DD@.microsoft.com...
> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
> Could someone please advise the proper syntax for the following logic:
> SELECT * FROM tablename
> WHERE TranDateSold (is today only)
> I experimented with GETDATE() but it seemed to want to match today's date
> and time to the hour and minute. Any ideas would be most appreciated.|||Try this
SELECT * FROM tablename
WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
If there can be nothing in the future:
DECLARE @.dt SMALLDATETIME
SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt;
If there may be future-dated rows:
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt
AND TranDateSold < @.dt + 1;
A|||Oops, I goofed the = should be >=|||This will only work if TranDateSold is intentionally stored with no time
value. Otherwise you will be asking for rows where '2006-04-21 13:26' =
'2006-04-21 00:00';
If you have to do the convert on the left-hand side to get rid of the time
component, you've just wiped out any chance of using an index.
http://www.aspfaq.com/2280
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649232.038005.231380@.g10g2000cwb.googlegroups.com...
> Try this
> SELECT * FROM tablename
> WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks to everyone for your posts. The convert function is what I needed fo
r
my vendor, and the DECLARE stmt gave me all records after midnight today.
Have a nice wend, Pancho.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If there can be nothing in the future:
> DECLARE @.dt SMALLDATETIME
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt;
> If there may be future-dated rows:
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt
> AND TranDateSold < @.dt + 1;
> A
>
>

get time of querry?

is there a way to get the time that it takes a querry to run like down to the millsecondon 2005 you can use this timer class mladen wrote:

http://weblogs.sqlteam.com/mladenp/archive/2006/12/02/39124.aspx

otherwise you can use the datediff, etc functions in sql server for timing. This won't give you ms accuracy though, since the clock in sql server only has 3ms resolution. perhaps that's enough for you though.|||is there a way to get the time that it takes a querry to run like down to the millsecond

Use SQL Profiler. The duration columns records the time it takes to run a query down to the ms.

Or use SET STATISTICS TIME ON

Or are you trying to access this info programmatically?

Regards,

hmscott

Get this error while i run DBCC INDEXDEFRAG

Hi,
I run a DBCC INDEXDEFRAG on a table and it abends with
this error :
DBCC INDEXDEFRAG (0,stops,1)
=> 1 being clustered index. There's enough space on the
disk.
Msg 3624, Sev 20: Location: pageref.cpp:1191
Expression: 0 == pageFull SPID: 65
Process ID: 324 [SQLSTATE 01000]
what does this mean and how do I fix it ?
Error seems to be consistent.
MOYou should contact PSS (http://support.microsoft.com) who will help you.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:960601c40547$bafdf940$a101280a@.phx.gbl...
> Hi,
> I run a DBCC INDEXDEFRAG on a table and it abends with
> this error :
> DBCC INDEXDEFRAG (0,stops,1)
> => 1 being clustered index. There's enough space on the
> disk.
> Msg 3624, Sev 20: Location: pageref.cpp:1191
> Expression: 0 == pageFull SPID: 65
> Process ID: 324 [SQLSTATE 01000]
> what does this mean and how do I fix it ?
> Error seems to be consistent.
> MO

Get the Sql server query result in Excel sheet.

Hi All,

My question is, when i run a query in sql server the result should go to an Excel sheet automatically.what would be the effective and easiest way.

Im using Sql server2000,MS office 2003.

hi,
you can create a package in DTS..

-clintz|||

This is the frequent question in this forum. Check the following posts to get your answer.

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

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

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