Hi,
Does anyone knows the select syntax for getting a date in the past but
close the current date.
For example: i have a table of addresses with an id, startdate, street,
etc. Now what i would like to do, is get the date that is close to the
current date. The outcome of it, is the current address of a person.
Is this possible with use of the columns id and startdate or just startdate?Please post DDL if you are refering to columns in your tables
http://www.aspfaq.com/5006
Select TOP 1 <columnlist>
>From SomeTable
Where id = <Someid>
Order by Startdate desc
HTH, Jens Suessmeyer.|||select DATEDIFF(dd, StartDate, getdate()), * from YourTable
order by DATEDIFF(dd, StartDate, getdate())
dd = Days. This can be substitued for hours, minutes, seconds etc. Have a
look at DATEDIFF function in SQL Books Online
HTH. Ryan
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:%23Spc$gZIGHA.1876@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone knows the select syntax for getting a date in the past but
> close the current date.
> For example: i have a table of addresses with an id, startdate, street,
> etc. Now what i would like to do, is get the date that is close to the
> current date. The outcome of it, is the current address of a person.
> Is this possible with use of the columns id and startdate or just
> startdate?
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Thursday, March 29, 2012
Friday, March 23, 2012
getGeneratedKeys
Does the current driver support getGeneratedKeys? If not when will it?
I can't believe there is nobody who can answer this seemingly obvious
question. Doesn't anybody from microsoft ever look at this forum?
"bobmanc" wrote:
> Does the current driver support getGeneratedKeys? If not when will it?
|||The current driver is JDBC 2.0 spec and doesn't support getGeneratedKeys.
It's on the feature list for the SQL Server 2005 driver.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"bobmanc" <bobmanc@.discussions.microsoft.com> wrote in message
news:E99AA36E-0040-4121-8F75-A7B094AB0276@.microsoft.com...[vbcol=seagreen]
> I can't believe there is nobody who can answer this seemingly obvious
> question. Doesn't anybody from microsoft ever look at this forum?
> "bobmanc" wrote:
I can't believe there is nobody who can answer this seemingly obvious
question. Doesn't anybody from microsoft ever look at this forum?
"bobmanc" wrote:
> Does the current driver support getGeneratedKeys? If not when will it?
|||The current driver is JDBC 2.0 spec and doesn't support getGeneratedKeys.
It's on the feature list for the SQL Server 2005 driver.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"bobmanc" <bobmanc@.discussions.microsoft.com> wrote in message
news:E99AA36E-0040-4121-8F75-A7B094AB0276@.microsoft.com...[vbcol=seagreen]
> I can't believe there is nobody who can answer this seemingly obvious
> question. Doesn't anybody from microsoft ever look at this forum?
> "bobmanc" wrote:
GETDATE() with a user defined function
Hi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.Nope sorry...
Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?
Anyone know how to create an external sproc?
USE Northwind
GO
CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
SELECT @.myDate99
CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO
SELECT dbo.udf_myFunction99(0)
GO
DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO|||Nope sorry...
Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?
Anyone know how to create an external sproc?
USE Northwind
GO
CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
SELECT @.myDate99
CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO
SELECT dbo.udf_myFunction99(0)
GO
DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO
Pass GetDate() as a function argument.
Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
-PatP|||Pass GetDate() as a function argument.
-PatP
Did I already say that?
Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
oooo scary...
Did you see Hendersons work for Arrays in SQL Server?
And you're right...I chickened out...was going to build them...but I figured why bother...a tables an array, and with the table variable is was even easier...|||If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
Yeah...I called that "midlife crisis", and a few thousands of dollars and several handcuff burns later...I tend to agree ;) Although..."that which does not kill us..." ;)|||Thousands of dollars ?!?! Was she worth it?
-PatP|||*LOL* Nope...not even a "she" dammit (though, it's probably best to assume the "she" connection first in any such situations)...what a wasted midlife crisis...just partyin' too much with m'homeboys Jose' C and Jack D|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...No, no, no! He didn't say it was a normal Tuesday afternoon. This was something special!
I figured with thousands of dollars and handcuff burns, there just HAD to be a "she" in there somewhere!
You do have to be wary when playing with Jack and Jose. Those fellas play kinda rough sometimes. Glad to know that you survived it anyway!
-PatP|||Thanks...and no, no trees or blood involved, but Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it ;)
Perhaps needless to say, I lost quite a few brain cells that would come in handy now trying to figure out how to debug in SQL Server, and how to put non-deterministic functions into user-defined functions!
(hey, how's THAT for coming back on-topic ;) )|||Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it
Did you get a room with the view of the lake?
This is good Yak Corral stuff...
And Pat do you use debugger or not?|||On my own code, I've only used the debugger once or twice in order to show other people how my code worked. I've never needed it for actually debugging code that I've written.
The debugger has come in handy more than once trying to finger out what in blazes some of the code that I've inherited actually does. Some of that stuff can be most charitably described as bizarre.
-PatP
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.Nope sorry...
Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?
Anyone know how to create an external sproc?
USE Northwind
GO
CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
SELECT @.myDate99
CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO
SELECT dbo.udf_myFunction99(0)
GO
DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO|||Nope sorry...
Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?
Anyone know how to create an external sproc?
USE Northwind
GO
CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
SELECT @.myDate99
CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO
SELECT dbo.udf_myFunction99(0)
GO
DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO
Pass GetDate() as a function argument.
Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
-PatP|||Pass GetDate() as a function argument.
-PatP
Did I already say that?
Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
oooo scary...
Did you see Hendersons work for Arrays in SQL Server?
And you're right...I chickened out...was going to build them...but I figured why bother...a tables an array, and with the table variable is was even easier...|||If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!
Yeah...I called that "midlife crisis", and a few thousands of dollars and several handcuff burns later...I tend to agree ;) Although..."that which does not kill us..." ;)|||Thousands of dollars ?!?! Was she worth it?
-PatP|||*LOL* Nope...not even a "she" dammit (though, it's probably best to assume the "she" connection first in any such situations)...what a wasted midlife crisis...just partyin' too much with m'homeboys Jose' C and Jack D|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...No, no, no! He didn't say it was a normal Tuesday afternoon. This was something special!
I figured with thousands of dollars and handcuff burns, there just HAD to be a "she" in there somewhere!
You do have to be wary when playing with Jack and Jose. Those fellas play kinda rough sometimes. Glad to know that you survived it anyway!
-PatP|||Thanks...and no, no trees or blood involved, but Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it ;)
Perhaps needless to say, I lost quite a few brain cells that would come in handy now trying to figure out how to debug in SQL Server, and how to put non-deterministic functions into user-defined functions!
(hey, how's THAT for coming back on-topic ;) )|||Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it
Did you get a room with the view of the lake?
This is good Yak Corral stuff...
And Pat do you use debugger or not?|||On my own code, I've only used the debugger once or twice in order to show other people how my code worked. I've never needed it for actually debugging code that I've written.
The debugger has come in handy more than once trying to finger out what in blazes some of the code that I've inherited actually does. Some of that stuff can be most charitably described as bizarre.
-PatP
Wednesday, March 21, 2012
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()
I'm using SQL Server 2005 Repoting Services to find all funds availilble
before the current date. When I use GETDATE() I get an error ORA-00904. Any
other suggestions? I'm using Visual Studio to run SSRS.
ThanksJohn, I believe that you may be looking for NOW(). - RLF
"John" <John@.discussions.microsoft.com> wrote in message
news:64BFEA2C-8D4C-4215-B3B0-39B8930F0E6D@.microsoft.com...
> I'm using SQL Server 2005 Repoting Services to find all funds availilble
> before the current date. When I use GETDATE() I get an error ORA-00904.
> Any
> other suggestions? I'm using Visual Studio to run SSRS.
> Thanks|||GetDate() is specific to MSSQL. Try Sysdate instead.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"John" <John@.discussions.microsoft.com> wrote in message
news:64BFEA2C-8D4C-4215-B3B0-39B8930F0E6D@.microsoft.com...
> I'm using SQL Server 2005 Repoting Services to find all funds availilble
> before the current date. When I use GETDATE() I get an error ORA-00904.
> Any
> other suggestions? I'm using Visual Studio to run SSRS.
> Thanks
before the current date. When I use GETDATE() I get an error ORA-00904. Any
other suggestions? I'm using Visual Studio to run SSRS.
ThanksJohn, I believe that you may be looking for NOW(). - RLF
"John" <John@.discussions.microsoft.com> wrote in message
news:64BFEA2C-8D4C-4215-B3B0-39B8930F0E6D@.microsoft.com...
> I'm using SQL Server 2005 Repoting Services to find all funds availilble
> before the current date. When I use GETDATE() I get an error ORA-00904.
> Any
> other suggestions? I'm using Visual Studio to run SSRS.
> Thanks|||GetDate() is specific to MSSQL. Try Sysdate instead.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"John" <John@.discussions.microsoft.com> wrote in message
news:64BFEA2C-8D4C-4215-B3B0-39B8930F0E6D@.microsoft.com...
> I'm using SQL Server 2005 Repoting Services to find all funds availilble
> before the current date. When I use GETDATE() I get an error ORA-00904.
> Any
> other suggestions? I'm using Visual Studio to run SSRS.
> Thanks
GetDate()
G'day,
I want to display the current time minus 5 minutes. say
2004-03-29 11:43:49.123 to be 2004-03-29 11:38:49.123.
Is there any Equivalent to the DB2's CURRENT TIMESTAMP - 5 MINUTES which will display the current day say 2004-03-29 11:43:49.123 as
2004-03-29 11:38:49.123
cheers
Melbselect dateadd(mi, -5, getdate())
but: "Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds)." from BOL 'datetime and smalldatetime'.|||Thanks Kaiowas
cheers
Melb
I want to display the current time minus 5 minutes. say
2004-03-29 11:43:49.123 to be 2004-03-29 11:38:49.123.
Is there any Equivalent to the DB2's CURRENT TIMESTAMP - 5 MINUTES which will display the current day say 2004-03-29 11:43:49.123 as
2004-03-29 11:38:49.123
cheers
Melbselect dateadd(mi, -5, getdate())
but: "Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds)." from BOL 'datetime and smalldatetime'.|||Thanks Kaiowas
cheers
Melb
getDate not working in function
Hi,
Using MSSQL 2000
How can I get hold of the current datetime inside a function?
declare @.dt datetime;
select @.dt = getdate();
gives an "Invalid use of 'getdate' within a function" error.
Any help will be greatly appreciated.
Regards,
arnoudgot it
select @.dt = dbo.GETDATE();
does the job.
"arnoud oortwijk" <abc> wrote in message
news:ONNpGsWzFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Using MSSQL 2000
> How can I get hold of the current datetime inside a function?
> declare @.dt datetime;
> select @.dt = getdate();
> gives an "Invalid use of 'getdate' within a function" error.
> Any help will be greatly appreciated.
> Regards,
> arnoud
>|||No. That would reference a user-defined function called dbo.GETDATE. I
don't think that's what you wanted.
You can't use the system function GETDATE() in a user-defined function.
Easiest method is to pass the time as a parameter.
David Portas
SQL Server MVP
--|||David
> Easiest method is to pass the time as a parameter
I think you meant
Easiest method is to pass the GETDATE() function as a parameter
:-)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128929659.370492.41730@.g14g2000cwa.googlegroups.com...
> No. That would reference a user-defined function called dbo.GETDATE. I
> don't think that's what you wanted.
> You can't use the system function GETDATE() in a user-defined function.
> Easiest method is to pass the time as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>
Using MSSQL 2000
How can I get hold of the current datetime inside a function?
declare @.dt datetime;
select @.dt = getdate();
gives an "Invalid use of 'getdate' within a function" error.
Any help will be greatly appreciated.
Regards,
arnoudgot it
select @.dt = dbo.GETDATE();
does the job.
"arnoud oortwijk" <abc> wrote in message
news:ONNpGsWzFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Using MSSQL 2000
> How can I get hold of the current datetime inside a function?
> declare @.dt datetime;
> select @.dt = getdate();
> gives an "Invalid use of 'getdate' within a function" error.
> Any help will be greatly appreciated.
> Regards,
> arnoud
>|||No. That would reference a user-defined function called dbo.GETDATE. I
don't think that's what you wanted.
You can't use the system function GETDATE() in a user-defined function.
Easiest method is to pass the time as a parameter.
David Portas
SQL Server MVP
--|||David
> Easiest method is to pass the time as a parameter
I think you meant
Easiest method is to pass the GETDATE() function as a parameter
:-)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128929659.370492.41730@.g14g2000cwa.googlegroups.com...
> No. That would reference a user-defined function called dbo.GETDATE. I
> don't think that's what you wanted.
> You can't use the system function GETDATE() in a user-defined function.
> Easiest method is to pass the time as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>
Getdate
I believe set @.today=getdate will give me the current time.
How can I get the system date without the time?GETDATE() gives you the current date & time. There are no separate date and
time datatypes in SQL Server. You can use CONVERT() to get a string that
represents the Date portion. Something like
SELECT CONVERT(VARCHAR(10),GETDATE(),101)
Check out CONVERT in BOL for more details. Also have a look here:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
Andrew J. Kelly SQL MVP
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CCA1E736-0033-4025-8BC8-671B0BB1C793@.microsoft.com...
>I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||DECLARE @.today DATETIME
SET @.today = GETDATE()
SELECT @.today
SET @.today = DATEADD(day,DATEDIFF(day,0,@.today), 0) --Strip off the time
part
SELECT @.today
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CCA1E736-0033-4025-8BC8-671B0BB1C793@.microsoft.com...
>I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||Hi
GETDATE() / CURRENT_TIMESTAMP will return the current date and time.
CONVERT(CHAR(8), GETDATE, 112) will return the date.
CONVERT(CHAR(12), GETDATE, 114) will return the time.
Regards
Mike
"Arne" wrote:
> I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||Arne,
If you want the date in ISO standard format, you can use the
following:
declare @.tst char(8)
set @.tst = convert(char(8),getdate(),112)
print @.tst
Look up the CONVERT function in BOL.
On Mon, 21 Feb 2005 06:35:13 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>I believe set @.today=getdate will give me the current time.
>How can I get the system date without the time?
How can I get the system date without the time?GETDATE() gives you the current date & time. There are no separate date and
time datatypes in SQL Server. You can use CONVERT() to get a string that
represents the Date portion. Something like
SELECT CONVERT(VARCHAR(10),GETDATE(),101)
Check out CONVERT in BOL for more details. Also have a look here:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
Andrew J. Kelly SQL MVP
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CCA1E736-0033-4025-8BC8-671B0BB1C793@.microsoft.com...
>I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||DECLARE @.today DATETIME
SET @.today = GETDATE()
SELECT @.today
SET @.today = DATEADD(day,DATEDIFF(day,0,@.today), 0) --Strip off the time
part
SELECT @.today
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:CCA1E736-0033-4025-8BC8-671B0BB1C793@.microsoft.com...
>I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||Hi
GETDATE() / CURRENT_TIMESTAMP will return the current date and time.
CONVERT(CHAR(8), GETDATE, 112) will return the date.
CONVERT(CHAR(12), GETDATE, 114) will return the time.
Regards
Mike
"Arne" wrote:
> I believe set @.today=getdate will give me the current time.
> How can I get the system date without the time?|||Arne,
If you want the date in ISO standard format, you can use the
following:
declare @.tst char(8)
set @.tst = convert(char(8),getdate(),112)
print @.tst
Look up the CONVERT function in BOL.
On Mon, 21 Feb 2005 06:35:13 -0800, "Arne"
<Arne@.discussions.microsoft.com> wrote:
>I believe set @.today=getdate will give me the current time.
>How can I get the system date without the time?
Monday, March 12, 2012
get the windows user from SQL server
Hello,
I have an application that use SQL server, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Look at BOL, in the Functions:
CURRENT_USER
SYSTEM_USER
USER
USER_NAME
(SUSER_NAME)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an application that use SQL server, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
|||these function return the sql users
i am thinking that it isn't possible.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Look at BOL, in the Functions:
> CURRENT_USER
> SYSTEM_USER
> USER
> USER_NAME
> (SUSER_NAME)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
|||Now i understand ;-)
No dont think thats possible
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> these function return the sql users
> i am thinking that it isn't possible.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you are using SQL Authentication, SQL Server does not know the NT user
name as it is not presented by the ODBC/OLE DB stack.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||I have found something related with the envirnment variables, but is needed
have admin rights, and on my application there are any users wihtout this
rights.
(if somebody need this way, please send me a mail ask it
pecharroman2001@.hotmail.com)
so i will search another ways.
Thanks Jens!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||Short of sticking with Windows Authentication, I don't think this is
possible. You can get the host name from master..sysprocesses or by
using host_name().
David Gugick
Imceda Software
www.imceda.com
|||"Petxa" wrote:
> I have found something related with the envirnment variables, but is
> needed have admin rights, and on my application there are any users
> wihtout this rights.
> (if somebody need this way, please send me a mail ask it
> pecharroman2001@.hotmail.com)
> so i will search another ways.
A terrible, horrible, really bad hack that might work for you if...
- You have control over your app
- You don't care what app_name() returns
You could set the user name in the connection string on the application
side. See the Win32 API function GetUserName and add the name/value combo
"Application Name=what you got from GetUserName" to your connection string.
You might also want to add some kind of identifying prefix and/or postfix to
the name so that you can check to make sure the value you get from app_name
was set with this technique. You can also manually include the application
name so that connections you view with EM or sp_who are easier to identify.
Then in T-SQL, you can get the current connection's user name with something
like
declare @.uid varchar(50)
set @.uid = app_name()
Craig
|||It is realy a good idea, i haven't still tested but it looks great!
Thank Craig! (and all for your help)
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message
news:qCB7e.579079$w62.560396@.bgtnsc05-news.ops.worldnet.att.net...
> "Petxa" wrote:
>
> A terrible, horrible, really bad hack that might work for you if...
> - You have control over your app
> - You don't care what app_name() returns
> You could set the user name in the connection string on the application
> side. See the Win32 API function GetUserName and add the name/value combo
> "Application Name=what you got from GetUserName" to your connection
string.
> You might also want to add some kind of identifying prefix and/or postfix
to
> the name so that you can check to make sure the value you get from
app_name
> was set with this technique. You can also manually include the
application
> name so that connections you view with EM or sp_who are easier to
identify.
> Then in T-SQL, you can get the current connection's user name with
something
> like
> declare @.uid varchar(50)
> set @.uid = app_name()
> Craig
>
I have an application that use SQL server, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Look at BOL, in the Functions:
CURRENT_USER
SYSTEM_USER
USER
USER_NAME
(SUSER_NAME)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an application that use SQL server, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
|||these function return the sql users
i am thinking that it isn't possible.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Look at BOL, in the Functions:
> CURRENT_USER
> SYSTEM_USER
> USER
> USER_NAME
> (SUSER_NAME)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
|||Now i understand ;-)
No dont think thats possible
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> these function return the sql users
> i am thinking that it isn't possible.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you are using SQL Authentication, SQL Server does not know the NT user
name as it is not presented by the ODBC/OLE DB stack.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||I have found something related with the envirnment variables, but is needed
have admin rights, and on my application there are any users wihtout this
rights.
(if somebody need this way, please send me a mail ask it
pecharroman2001@.hotmail.com)
so i will search another ways.
Thanks Jens!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||Short of sticking with Windows Authentication, I don't think this is
possible. You can get the host name from master..sysprocesses or by
using host_name().
David Gugick
Imceda Software
www.imceda.com
|||"Petxa" wrote:
> I have found something related with the envirnment variables, but is
> needed have admin rights, and on my application there are any users
> wihtout this rights.
> (if somebody need this way, please send me a mail ask it
> pecharroman2001@.hotmail.com)
> so i will search another ways.
A terrible, horrible, really bad hack that might work for you if...
- You have control over your app
- You don't care what app_name() returns
You could set the user name in the connection string on the application
side. See the Win32 API function GetUserName and add the name/value combo
"Application Name=what you got from GetUserName" to your connection string.
You might also want to add some kind of identifying prefix and/or postfix to
the name so that you can check to make sure the value you get from app_name
was set with this technique. You can also manually include the application
name so that connections you view with EM or sp_who are easier to identify.
Then in T-SQL, you can get the current connection's user name with something
like
declare @.uid varchar(50)
set @.uid = app_name()
Craig
|||It is realy a good idea, i haven't still tested but it looks great!
Thank Craig! (and all for your help)
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message
news:qCB7e.579079$w62.560396@.bgtnsc05-news.ops.worldnet.att.net...
> "Petxa" wrote:
>
> A terrible, horrible, really bad hack that might work for you if...
> - You have control over your app
> - You don't care what app_name() returns
> You could set the user name in the connection string on the application
> side. See the Win32 API function GetUserName and add the name/value combo
> "Application Name=what you got from GetUserName" to your connection
string.
> You might also want to add some kind of identifying prefix and/or postfix
to
> the name so that you can check to make sure the value you get from
app_name
> was set with this technique. You can also manually include the
application
> name so that connections you view with EM or sp_who are easier to
identify.
> Then in T-SQL, you can get the current connection's user name with
something
> like
> declare @.uid varchar(50)
> set @.uid = app_name()
> Craig
>
Get the windows user from SQL
Hello,
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks DiegoAnswered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks DiegoAnswered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
Get the windows user from SQL
Hello,
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Answered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Answered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
Get the User Name for a login user
Hi Guys,
How can I get the current loged in UserName in windows in MSSQL?
The problem is:
Users login into their PC (Windows Login)Enter a URL of the intranet applicationThey would then be able to access the web base application (Intranet)The web application has a string connection to make a connection to the databaseI want to have a Trigger for my "products" table so if the table is modified (Insert/Update) all those changes be entered in "products_Audit" tableNow on "products_Audit" table I have a field "ChangedBy" where I want to store the windows loged in User Name (SYSTEM_USER just return the Username for the SQL connection that the web application use)I would really appreciate all your help and suggestions.
Regards,
Mehdi
Hello,
In case of IntraNet where directory security does not allow anonymous users, then you can retrieve domain user information.
dim user as string = Request.ServerVariables("LOGON_USER")
and pass this variable for your database action query.
Friday, March 9, 2012
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KR
Why not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegro ups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>
|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf .googlegroups.com...
>
>
>
>
>
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KR
Why not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegro ups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>
|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf .googlegroups.com...
>
>
>
>
>
> - Show quoted text -
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.
123200@.e9g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.
123200@.e9g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Get the list of variables in a package inside a custom component
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
Why do you need a list of variables?
Normally the two things you would do in a component is to validate a variable exists and read or write the value. Both of these can be achieved with the VariableDispenser class that is avilable from the base PipelineComponent object.
For example, in validate I would do something like this-
object obj1 = ComponentHelper.GetPropertyValue("OutputRowCountVariable", base.ComponentMetaData());
if ((obj1 != null) && (obj1.ToString().Length > 0))
{
if (!base.VariableDispenser().Contains(obj1.ToString()))
{
this.PostError(string.Format(Resources.ErrorPropertyInvalidVariableNotExist, "OutputRowCountVariable", obj1.ToString()));
return 1;
}
if (!this.ValidateVariableType(obj1.ToString(), out code1))
{
this.PostError(string.Format(Resources.ErrorInvalidVariableType, "OutputRowCountVariable", code1.ToString()));
return 1;
}
}
|||
Hi Darren
I am developing a oracle source component , I need to get the table or view name which are stored as variables. Iam not getting the ComponentHelper class .
thanks
Mani
|||So you do not need a list of variables, you just need to get the variable value. Use the VariableDispenser. Ignore the ComponenHelper, that is just a wrapper of mine, and in that instance I am just getting the value, nothing more than that.|||hi thanks darren i got it i used the code
IDTSVariables90 var;
ArrayList tableOrViewName = new ArrayList();
this.VariableDispenser.LockForRead("TableName");
this.VariableDispenser.GetVariables(out var);
foreach (IDTSVariable90 variable in var)
{
tableOrViewName.Add(variable.Value);
}
Thanks
Mani
|||A minor point but you could save the loop and array. You are only reading from one variable, so you could use LockOneForRead, e.g.
string tableName = "";
IDTSVariables90 variables = null;
VariableDispenser.LockOneForRead("TableName", ref variables);
tableName = variable.Value.ToString();
variables.Unlock()
You should probably check that the Value of teh variable is not null as well, before calling ToString. Always call Unlock as soon as you can.
If you expect multiple tables to be selected, then this would need to be a delmited list in your variable value, you cannot have multiple variables of the same name. You could have a more complex type for the variable value, but I would use a delimited string so it is easier to manage for both design-time setting and also persistance. You can set a string through an expression for example, but not an object.
Get the last 100 records
My sql database table gets filled automatically.
Every record gets a current date/time stamp.
I want to select the last 100 records, ordered by the date/time stamp.
The newest records should be the last record in the 100 recordset.
How can I do this?
select id, createdon from|||
(select top 100 id, createdon
from table
order by createdondesc) a
order by createdon
I get an incorrect syntax error on the last ")":
SELECTDT, VALUE
FROM
(SELECTTOP 10DT, VALUE
FROM [CAS SHORT HISTORY]
ORDERBY DTDESC)
|||
Make sure you've given the derived table an alias. Here's a working example:
declare @.table1table (idint identity (1,1), createdondatetime)declare @.startdatedatetimedeclare @.enddatedatetimeset @.startdate ='20060101'set @.enddate ='20070101'while @.startdate < @.enddatebegininsert @.table1values (@.startdate)set @.startdate = @.startdate + 1endselect id, createdonfrom (select top 100 id, createdonfrom @.table1order by createdondesc) aorder by createdon|||
Hi ca8msm,
You are filling a new table, but I'm having a table [CAS SHORT HISTORY] that is already filled, how should I create an alias for this table?
|||The above is just an example table. Use the query at the bottom and change the table and field names.
|||select id, createdonfrom
(select top 100 id, createdon
from @.table1
order by createdondesc) a
order by createdon
What is the "a" doing?
|||It's creating an alias for the derived table.
|||Bingo! Found it!
SELECT*
FROM(SELECTTOP 10DT
FROM[CAS SHORT HISTORY] CASALIAS
ORDERBYDT
DESC)
CASALIAS
ORDERBYDT
ASC
Get the description of INFORMATION_SCHEMA.COLUMNS
I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
Get the description of INFORMATION_SCHEMA.COLUMNS
I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?
USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
David Portas
SQL Server MVP
Get the description of INFORMATION_SCHEMA.COLUMNS
I understand that sp_help and sp_columns can give a description of a table
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
--
David Portas
SQL Server MVP
--
for me.
However, sp_help sees only the tables in the current schema, and
sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
returns an empty result, too.
How can I query the descriptions of tables in other than the current schema?USE master
EXEC sp_help 'INFORMATION_SCHEMA.COLUMNS'
EXEC sp_columns 'COLUMNS'
--
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)