getting a store procedure's result

Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,

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 a final version of a person into a DW

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first?

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

Fuzzy could be of help here. The fuzzy grouping can be used for deduplicating, the fuzzy lookup to check whether you already have a record that resembles your new one.

You will need to spend some time (by testing) to figure out a similarity value that suits your situation. In a DW environment, I think this should be a business decision.

From what I understand, ssno probably needs to be involved. In the lookup (haven't used the grouping yet) you can set similarity values for ssno addressno and name. For example, ssno (or birthdate?) needs a similarity of 1, and the name needs to have a similarity > 0.6

Regards,

Pipo

Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and executes the contents a number of other "children" .sql files
Would anybody know how to do this
What happens if one of the .sql files fails? Will the others keep on running
ThankDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
--
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and
executes the contents a number of other "children" .sql files.
Would anybody know how to do this?
What happens if one of the .sql files fails? Will the others keep on runnin
g?
ThanksDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting a .rdl from a ReportBuilder save

I have created and deployed a report model.
I can go to http://localhost/reports not problem
I can create reports no problem using the Report Builder.
My question is: Is there a way of grabbing some sort of .rdl file or
something of that report so I can publish it to another server? After
I save the report I cannot find it anywhere.
Using the Report builder is way simpler than building reports in a VS
report project.
Is there something I am missing? Can you build .rdls in VS using the
same simple UI that is in Report Builder?
Thanks,
BillBill,
By default the report builder will save the RDLin Reporting Services in the
folder where your model is located. You can navigate to this folder via the
Report Manager Website (http://{server}/Reports), navigate to where your
model is located and your RDL should be there.
OR
You can save the RDL to your local hard drive through the Report Builder
application via the File \ Save To File and specify on your local hard drive
where u'd like to save the RDL.
Hope this helps.
-Rick
"bill" wrote:
> I have created and deployed a report model.
> I can go to http://localhost/reports not problem
> I can create reports no problem using the Report Builder.
> My question is: Is there a way of grabbing some sort of .rdl file or
> something of that report so I can publish it to another server? After
> I save the report I cannot find it anywhere.
> Using the Report builder is way simpler than building reports in a VS
> report project.
> Is there something I am missing? Can you build .rdls in VS using the
> same simple UI that is in Report Builder?
> Thanks,
> Bill
>

GetScheduleProperties()

I create a report subscription in code. Everything works fine:
string id = service.CreateSubscription(reportName, extensionSettings,
description, eventType,matchDataParam, parameters );
I get the id back and store it in the database. When I try to pass the id
to GetScheduleProperties() I get the following error:
"The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
schedule identifier that is provided to an operation cannot be located in
the report server database. "
What's going on? I looked in the Subscriptions table in the ReportServer
database and I see the subscription ID there...You want to call GetSubscriptionProperties, not GetScheduleProperties.
GetScheduleProperties returns information about shared schedules only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:%23lBbzT65EHA.2180@.TK2MSFTNGP12.phx.gbl...
>I create a report subscription in code. Everything works fine:
> string id = service.CreateSubscription(reportName, extensionSettings,
> description, eventType,matchDataParam, parameters );
> I get the id back and store it in the database. When I try to pass the id
> to GetScheduleProperties() I get the following error:
> "The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
> schedule identifier that is provided to an operation cannot be located in
> the report server database. "
> What's going on? I looked in the Subscriptions table in the ReportServer
> database and I see the subscription ID there...
>

getdate() in user defined function

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.

Regards,

hmscott

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()

Then reference v_getdate.ThisDate in your function.

GETDATE() Hangs periodically

We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
Mark
Did you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Mark

GETDATE() Hangs periodically

We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
MarkDid you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Marksql

GETDATE() Hangs periodically

We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
MarkDid you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx
.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Mark

GETDATE() and the local time zone

I have used the GETDATE() function within an expression to create a directory name based on the current date. I am in the Sydney time zone and the new day's folder name doesn't change until after 11 am - so GETDATE() is picking up the date and not adjusting for the time zone. How do I either set the time zone within the package or make the GETDATE() function look at time zone of the system on which it is run?You'll have to explain, because getdate() returns the system time where it is executed, time zone included. So.......|||Hmm.. that set me thinking about another date issue we have. We run in a citrix environment and I have noticed odd date displays before. This function behaves differently depending on whether I use RDP or ICA. I tested it by logging in under both types of session and creating an expression with GETDATE() in it and then evaluating it. The results were correct for RDP but not for ICA - so it looks like the problem is outside SSIS.

Getdate Problem

Hi ,

I have problem with Getdate()

i want to create Guest book with VWD 2005 and SQL 2005

I have same problem here forQ1,

http://forums.asp.net/thread/1513111.aspx

I put the Defualt value for the date : getdate()

and then I create insert page, but i got the error below

I tried to [Allow Null] for the data field in sql 2005, but when I add new entries, there is no date entered to the DB.

Can you Help.

thank you

------------

Cannot insert the value NULL into column 'guestdate', table 'tour2.dbo.gues'; column does not allow nulls. INSERT fails.
The statement has been terminated

Given that a record can be entered when allow nulls is set to true, I'm guessing that there is a problem with how getdate() is written in the default value. Try changing the default value to include outside parenthesis, like: (getdate())

Good luck!

|||

Hey

What is the create statement for the table?

You could execute insert statement in Sql Server Management studio to see if it works.

Here is an Example:

create table test_default_value(column1datetime defaultgetdate(),column2varchar(32))insert into test_default_value (column2)values('test')

sql

getdate

Hi
I have to create a series of reports that look back over certain days ie.
day before
current w
current month, quarter, year
The reports will be scheduled to be run between the hours of 10pm to 6am
when the business is shut.
I am using getdate - days to get my time span an example is
WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime,
CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
CONVERT(varchar(11), GETDATE() + 0, 102), 102))
this would get me my range for yesterdays results
However having run a query with the above where clause it pulled some out
for today, which make smy results wrong because it has used some of today, i
am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hours
giving me 2006-01-04 16:30:32 , this may not be a problem if the reports are
scheduled to run out of hours but is there a way of using the date and
adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
sure my reports were accurate, plus it would be nice to know
hope i made sense
regardsHave a look at the DATEDIFF function is SQL Books
HTH. Ryan
"Steven Scaife" <sp@.nospam.com> wrote in message
news:ey0ywdhEGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN
> CONVERT(datetime, CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND
> CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
> i am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24
> hours giving me 2006-01-04 16:30:32 , this may not be a problem if the
> reports are scheduled to run out of hours but is there a way of using the
> date and adding my own time constraint in eg 00:00:01 and 23:59:59 this
> would make sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>|||Hope that following syntax can help you. This will give yout todays date at
10:00 PM.
select convert(datetime,convert(char(8),getdate
(),112) + ' 22:00:00',120)
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||For yesterday's results, try
WHERE DATEDIFF(dd, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last w's results
WHERE DATEDIFF(wk, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last month's results
WHERE DATEDIFF(mm, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||Also you can use a calendar table|||There are several ways to remove the timestamp from getdate() and retaining
it as a datetime that I know of.
Here is what I typically do
1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
I would avoid the "adding your own time" and just use comparisons against
dates without times (ie. Midnight of that day).
For Instance, if i wanted all rows where dateFromTable is Today the WHERE
clause would be:
WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
DATETIME)
AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||This works well too
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
Returns today's date at 00:00:00.000
"Ryan Powers" wrote:
> There are several ways to remove the timestamp from getdate() and retainin
g
> it as a datetime that I know of.
> Here is what I typically do
> 1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
> I would avoid the "adding your own time" and just use comparisons against
> dates without times (ie. Midnight of that day).
> For Instance, if i wanted all rows where dateFromTable is Today the WHERE
> clause would be:
> WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
> DATETIME)
> AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Steven Scaife" wrote:
>|||Nice. Thanks Mark.
That is a little cleaner that what I proposed. Good to know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark Williams" wrote:
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>|||thank you it is much appreciated
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:0DBCD236-D731-4201-B577-558AA3708FE3@.microsoft.com...
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>

Get XML On Its Merry Way

Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>

Get XML On Its Merry Way

Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>

get trigger schema within CLR code

Hi.

I am trying to get the schema in which the trigger is created within the CLR code.

1)create a new schema MySchema.

2) created a table MySchema.MyTable

3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.

Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.

So how do dynamically get the trigger's schema name ?

Thanks for your help.

Nach

There is no way to get the schema of the currently executing trigger from clr. With T-SQL, you code would have to create dynamic sql based on the current trigger, or rely on the implict schema name binding that occurs.

There are alternatives
1. Use execute as user and have the specified user have a default schema of MySchema
2. Create a T-SQL trigger that calls your clr trigger converted to a proc, passing it the @.@.ProcId of the T-SQL trigger as a parameter, and you can then get object_name & schema id from the clr procedure