Getting a permission denied - but not using that user?

In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Con
nection=False"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.

Getting a permission denied - but not using that user?

In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql

getting a list of user created tables ONLY

sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:

> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a list of user access to which databases - help

Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:

> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs

Getting a cached report on subsequent previews

I have and application that creates and publishes reports to the Reporting
server. I allow the user to preview the report. If the user creates a
report named 'Test Report', for example, and preview it the first time - all
is fine. If the user makes small changes to the report (say reordering the
ORDER BY in the SQL statement) and keeps the same report name, then
publishes and previews, the report reflecting the latest changes isn't
displayed. The first report is displayed. The user has to hit the refresh
button on the reporting toolbar to get the recently updated report. It's
like I'm getting a cached report because the changes to the report where
minimal. Any way to get around this'
Thx
AdrianAdrian,
I believe there are caching option settings in the System Settings page. You
may want to review this to see if there is an option to fix your issue.
- peteZ
"adrianM" <no_spam@.no_email.org> wrote in message
news:%23Gjq9OVtEHA.904@.TK2MSFTNGP11.phx.gbl...
>I have and application that creates and publishes reports to the Reporting
> server. I allow the user to preview the report. If the user creates a
> report named 'Test Report', for example, and preview it the first time -
> all
> is fine. If the user makes small changes to the report (say reordering
> the
> ORDER BY in the SQL statement) and keeps the same report name, then
> publishes and previews, the report reflecting the latest changes isn't
> displayed. The first report is displayed. The user has to hit the
> refresh
> button on the reporting toolbar to get the recently updated report. It's
> like I'm getting a cached report because the changes to the report where
> minimal. Any way to get around this'
> Thx
> Adrian
>|||For the benefit of the group, in order to avoid the issue mentioned in my
original post you have to add the [rs:ClearSession=true] to the URL.
Adrian

Getting - Setting Environment Variables

Hi,

1st. Issue

I have written a package that uses an environmet variable which contains certain information that the user must enter. A sql script task will then get the information from the environment variable and manipulate it.

I don't have a problem programmatically getting information from the environment variable. However, when I manually edit the contents of the environment variable, the changes won't take place unless the box is rebooted. Is there a way to refresh environment variables without rebooting?

2nd. Issue

I have written another package that also reads data from an environment variable but it must also manipulate the data and save the changes to the environment variable.

I have accomplished this programmatically by using GetEnvironmentVariable() and SetEnvironmentVariable(), but unfortunately the changes live during program execution only. Afte the program executes, the changes are wiped out.

How can I make changes stick to environment variables (programmatically)?

-- I found a solution to this:

When programmatically getting and/or setting an environment variable the 'target' parameter must be specified in the method call. By 'target' I mean the location in the registry where your environment variable is stored.

GetEnvironmentVariable( var, target )

SetEnvironmentVariable( OldValue, NewValue, target )

Please visit this link for more info:

http://msdn2.microsoft.com/en-us/library/96xafkes.aspx

GetReportParameters ignores language="en-gb"?

Hello

I've integrated my reports into an app using the ReportViewer control, and I've created custom parameter selection controls. I want the user to be able to enter dates in dd/mm/yy format into a textbox. I've set the language of my reports to en-gb.

The SetParametes method of the ReportViewer control copes with this fine, but when I pass a date string in dd/mm/yy format into GetReportParameters, I get a 'parameter value not valid for its type' error. But it works for date strings in mm/dd/yy format, so it's as if GetReportParameters is ignoring my language setting.

Has anyone got any idea as to where I'm going wrong?

Thanks in advance

Dominic

After a little more playing around with this I've found that, on my machine, the SetParameters method always wants dates in dd/mm/yy format, whilst the GetReportParameters web method of the Report Service always wants dates in mm/dd/yy format, regardless of what I set the language of the report to.

Does anyone know what's going on?

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

GetDate() in User Defined Functions, Parameters in View

1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus

> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.

> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields

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() in Constraint using user's system time, Stored Proc using Server's.

All,

I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().

I performed the following test:

1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.

2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.

*note: These servers are on separate domains and therefore could have different system times.

This causes my select stored procedure to NOT return the values I just entered.

Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?

If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||

Todd:

Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.

Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.

sql

GetDate inside a Function

Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIAGETDATE() is nondeterinistic and, thus, cannot be used in a function. You
can, however, create a view and use it:
create view Now
as
select getdate () RightNow
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIA|||do this
declare @.date datetime
select @.date =max(last_XXXXX) from master.dbo.sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||I was wondering if maybe the developer was having a bad day with the
wife/mother-in-law when he designed the Last_XXXXX column <grin>
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
> oops sorry, nasty typo there
>
> declare @.date datetime
> select @.date =max(last_bAtch) from sysprocesses
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <someone@.microsoft.com> wrote in message
> news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||ROFL!
I must admit, it was the very first thing that caught my eye when I
glanced at Hilary's code snippet. Great Freudian slip there Hilary!
*mike hodgson*
http://sqlnerd.blogspot.com
Dave Frommer wrote:

>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>
>
>|||Thanks for pointing that out. Needed a good laugh in the morning. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl.
.
>I was wondering if maybe the developer was having a bad day with the
> wife/mother-in-law when he designed the Last_XXXXX column <grin>
>|||:-))))))))))))))))
"Dave Frommer" <anti@.spam.com> wrote in message
news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>|||last_XXXXX? Must be a dog show database. :)
ML
http://milambda.blogspot.com/|||The mask has slipped... ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>

Get values from database query

I have written the following lines

myConnection =New MySqlConnection("server=" + dbServer +"; user id=" + dbUserID +"; password=" + dbPassword +"; database=" + dbName +"; pooling=false;")strSQL ="SELECT * FROM user where type=1;"

user table has name, tel, addr, id, type fields

I would like to know how to use a string array to store the name in the result of strSQL?

Thank you

Hi thtang

You can use fllowing code:

SqlCommand cmd = new SqlCommand(myConnection ,strSQL );

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())

{

stringList.Add(dr.GetString(0));

}

Get user syntax

I am building a view for a report and one of the requirements is to grab the
user who is running the report. What would this syntax look like? I thought
it was getuser(), but no luck there.
Thanks,
Ryan
Ryan,
Depending on whether you want a User or a Login, you can try one of the
following: CURRENT_USER, SYSTEM_USER, USER_NAME, SUSER_SNAME. Check them
out in the Books Online, or just run them and choose what you prefer to use.
RLF
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:266EBEEE-3FB5-448D-A58B-0EF6D8F006F1@.microsoft.com...
>I am building a view for a report and one of the requirements is to grab
>the
> user who is running the report. What would this syntax look like? I
> thought
> it was getuser(), but no luck there.
> Thanks,
> Ryan

get user db roles

Hi NG members,
I'm searching for a simple sql statement to get all database roles the
current user. Regards A. SpieringArnd
Look at sp_helprolemember system SP
"Arnd Spiering" <arnd.spiering@.gon.de> wrote in message
news:MPG.1c190eb7205b8a4d989681@.msnews.microsoft.com...
> Hi NG members,
> I'm searching for a simple sql statement to get all database roles the
> current user. Regards A. Spiering

Get user data in sub query

Let me preface this by saying that am relatively new to SQL. I have a
database that is updated every10 to 15 seconds with broadcasts from our
customer. These broadcasts come from 2 distinct points in their
operation. The first point - 39 - tells me what to build and
ship to them. The second point - 105 - tells me when they have used my
product on the line. Every thing is controlled by a serial number. So,
for example, at 8 AM they will send a broadcast saying that serial
number 1234 is at point 39 (my cue to build and ship the part), then
about 3 hours later I will get a broadcast from point 105 that serial
number 1234 has been built (my cue that my parts have been consumed).
In the intervening 3 hours, there will have been a bunch of broadcasts
through each point. Each broadcast is writted to a single table with
the following fields:
ProcessDate - The date/time stamp that the broadcast was received
SerialNumber - The serial number referenced by the broadcast
ReportingPoint - The point that generated the broadcast (either 39 or
105)
OK, so here is the query I want to build:
Whenever the query is run, it should prompt the user for a date and
time, then find the serial number of the closest broadcast from 105 to
that date and time. Then it should use that serial number to find out
the date/time that unit passed through 39. Then it should return a
list of everything that has passed through 39 between that
time and the current time.
I have a query that does all of this, but it doesn't ask the user for a
date and time, it just finds the most recent serial number broadcast
and goes from there. I tried putting an @.UserDate parameter in the sub
query, but it generates an error saying you can't do that. Here is
that existing query:
SELECT SerialNumber
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND (ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP
1 ProcessDate, SerialNumber
FROM
[Broadcast]
WHERE
ReportingPoint = '105'
ORDER BY
ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY SerialNumber
ORDER BY SerialNumber
If you need more info, or if this isn't clear, please ask. I don't
even know where to get started on this one.
Thanks!
TimTry this:
declare @.ProcessDate datetime
,@.SerialNumber varchar(50)
select @.ProcessDate = '8/7/2006 12:54:00 PM'
,@.SerialNumber = 'fsa679fsda679fdsa'
SELECT SerialNumber
FROM [Broadcast] A
WHERE (ReportingPoint = '39')
AND (ProcessDate >= (
SELECT W.ProcessDate
FROM [Broadcast] AS W
JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
SerialNumber) AS X
ON W.SerialNumber = X.SerialNumber
AND W.ProcessDate < X.ProcessDate
))
GROUP BY SerialNumber
ORDER BY SerialNumber
Timothy.Rybak@.gmail.com wrote:
> Let me preface this by saying that am relatively new to SQL. I have a
> database that is updated every10 to 15 seconds with broadcasts from our
> customer. These broadcasts come from 2 distinct points in their
> operation. The first point - 39 - tells me what to build and
> ship to them. The second point - 105 - tells me when they have used my
> product on the line. Every thing is controlled by a serial number. So,
> for example, at 8 AM they will send a broadcast saying that serial
> number 1234 is at point 39 (my cue to build and ship the part), then
> about 3 hours later I will get a broadcast from point 105 that serial
> number 1234 has been built (my cue that my parts have been consumed).
> In the intervening 3 hours, there will have been a bunch of broadcasts
> through each point. Each broadcast is writted to a single table with
> the following fields:
> ProcessDate - The date/time stamp that the broadcast was received
> SerialNumber - The serial number referenced by the broadcast
> ReportingPoint - The point that generated the broadcast (either 39 or
> 105)
> OK, so here is the query I want to build:
> Whenever the query is run, it should prompt the user for a date and
> time, then find the serial number of the closest broadcast from 105 to
> that date and time. Then it should use that serial number to find out
> the date/time that unit passed through 39. Then it should return a
> list of everything that has passed through 39 between that
> time and the current time.
> I have a query that does all of this, but it doesn't ask the user for a
> date and time, it just finds the most recent serial number broadcast
> and goes from there. I tried putting an @.UserDate parameter in the sub
> query, but it generates an error saying you can't do that. Here is
> that existing query:
> SELECT SerialNumber
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP
> 1 ProcessDate, SerialNumber
> FROM
> [Broadcast]
> WHERE
> ReportingPoint = '105'
> ORDER BY
> ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY SerialNumber
> ORDER BY SerialNumber
> If you need more info, or if this isn't clear, please ask. I don't
> even know where to get started on this one.
> Thanks!
> Tim|||I am not at a place where I can try this out, but I can't see where it
will take a user entered value to base the report. It looks like you
are hard coding a date/time. Is this the case?
TIm
paul.8.martin@.gmail.com wrote:
> Try this:
> declare @.ProcessDate datetime
> ,@.SerialNumber varchar(50)
> select @.ProcessDate = '8/7/2006 12:54:00 PM'
> ,@.SerialNumber = 'fsa679fsda679fdsa'
>
> SELECT SerialNumber
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39')
> AND (ProcessDate >= (
> SELECT W.ProcessDate
> FROM [Broadcast] AS W
> JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
> SerialNumber) AS X
> ON W.SerialNumber = X.SerialNumber
> AND W.ProcessDate < X.ProcessDate
> ))
> GROUP BY SerialNumber
> ORDER BY SerialNumber
>
>
> Timothy.Rybak@.gmail.com wrote:
> > Let me preface this by saying that am relatively new to SQL. I have a
> > database that is updated every10 to 15 seconds with broadcasts from our
> > customer. These broadcasts come from 2 distinct points in their
> > operation. The first point - 39 - tells me what to build and
> > ship to them. The second point - 105 - tells me when they have used my
> > product on the line. Every thing is controlled by a serial number. So,
> > for example, at 8 AM they will send a broadcast saying that serial
> > number 1234 is at point 39 (my cue to build and ship the part), then
> > about 3 hours later I will get a broadcast from point 105 that serial
> > number 1234 has been built (my cue that my parts have been consumed).
> > In the intervening 3 hours, there will have been a bunch of broadcasts
> > through each point. Each broadcast is writted to a single table with
> > the following fields:
> >
> > ProcessDate - The date/time stamp that the broadcast was received
> > SerialNumber - The serial number referenced by the broadcast
> > ReportingPoint - The point that generated the broadcast (either 39 or
> > 105)
> >
> > OK, so here is the query I want to build:
> > Whenever the query is run, it should prompt the user for a date and
> > time, then find the serial number of the closest broadcast from 105 to
> > that date and time. Then it should use that serial number to find out
> > the date/time that unit passed through 39. Then it should return a
> > list of everything that has passed through 39 between that
> > time and the current time.
> >
> > I have a query that does all of this, but it doesn't ask the user for a
> > date and time, it just finds the most recent serial number broadcast
> > and goes from there. I tried putting an @.UserDate parameter in the sub
> > query, but it generates an error saying you can't do that. Here is
> > that existing query:
> >
> > SELECT SerialNumber
> > FROM [Broadcast] A
> > WHERE (ReportingPoint = '39') AND (ProcessDate >=> > (SELECT W.ProcessDate
> > FROM [Broadcast] AS W JOIN
> > (SELECT TOP
> > 1 ProcessDate, SerialNumber
> > FROM
> > [Broadcast]
> > WHERE
> > ReportingPoint = '105'
> > ORDER BY
> > ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> > W.ProcessDate < X.ProcessDate
> > WHERE W.ReportingPoint = '39'))
> > GROUP BY SerialNumber
> > ORDER BY SerialNumber
> >
> > If you need more info, or if this isn't clear, please ask. I don't
> > even know where to get started on this one.
> > Thanks!
> > Tim|||Hi Timothy.
If i understand your concern you want to take current date as
Default Date and Current time as default time.It should prompt the user
to enter the date Every time when ever it is run.right?
Let me give some possible solutions you can achieve this but i am not
sure
1) Go to Report properties,Click Auto refresh for 15 secs of time
2) Go to Report parameters,Add Userdate and User time as Parameter
.Give Default values as
=Today for date and =format(Now(),"hh:mi") for time.use this
parameters in your query as @.userdate and @.usertime
If this also doesn't work for you ,You need to build a custom code
asking to take the values from the user
Regards
Raj Deep.A
Timothy.Rybak@.gmail.com wrote:
> I am not at a place where I can try this out, but I can't see where it
> will take a user entered value to base the report. It looks like you
> are hard coding a date/time. Is this the case?
> TIm
> paul.8.martin@.gmail.com wrote:
> > Try this:
> >
> > declare @.ProcessDate datetime
> > ,@.SerialNumber varchar(50)
> >
> > select @.ProcessDate = '8/7/2006 12:54:00 PM'
> > ,@.SerialNumber = 'fsa679fsda679fdsa'
> >
> >
> > SELECT SerialNumber
> > FROM [Broadcast] A
> > WHERE (ReportingPoint = '39')
> > AND (ProcessDate >= (
> > SELECT W.ProcessDate
> > FROM [Broadcast] AS W
> > JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
> > SerialNumber) AS X
> > ON W.SerialNumber = X.SerialNumber
> > AND W.ProcessDate < X.ProcessDate
> > ))
> > GROUP BY SerialNumber
> > ORDER BY SerialNumber
> >
> >
> >
> >
> >
> > Timothy.Rybak@.gmail.com wrote:
> > > Let me preface this by saying that am relatively new to SQL. I have a
> > > database that is updated every10 to 15 seconds with broadcasts from our
> > > customer. These broadcasts come from 2 distinct points in their
> > > operation. The first point - 39 - tells me what to build and
> > > ship to them. The second point - 105 - tells me when they have used my
> > > product on the line. Every thing is controlled by a serial number. So,
> > > for example, at 8 AM they will send a broadcast saying that serial
> > > number 1234 is at point 39 (my cue to build and ship the part), then
> > > about 3 hours later I will get a broadcast from point 105 that serial
> > > number 1234 has been built (my cue that my parts have been consumed).
> > > In the intervening 3 hours, there will have been a bunch of broadcasts
> > > through each point. Each broadcast is writted to a single table with
> > > the following fields:
> > >
> > > ProcessDate - The date/time stamp that the broadcast was received
> > > SerialNumber - The serial number referenced by the broadcast
> > > ReportingPoint - The point that generated the broadcast (either 39 or
> > > 105)
> > >
> > > OK, so here is the query I want to build:
> > > Whenever the query is run, it should prompt the user for a date and
> > > time, then find the serial number of the closest broadcast from 105 to
> > > that date and time. Then it should use that serial number to find out
> > > the date/time that unit passed through 39. Then it should return a
> > > list of everything that has passed through 39 between that
> > > time and the current time.
> > >
> > > I have a query that does all of this, but it doesn't ask the user for a
> > > date and time, it just finds the most recent serial number broadcast
> > > and goes from there. I tried putting an @.UserDate parameter in the sub
> > > query, but it generates an error saying you can't do that. Here is
> > > that existing query:
> > >
> > > SELECT SerialNumber
> > > FROM [Broadcast] A
> > > WHERE (ReportingPoint = '39') AND (ProcessDate >=> > > (SELECT W.ProcessDate
> > > FROM [Broadcast] AS W JOIN
> > > (SELECT TOP
> > > 1 ProcessDate, SerialNumber
> > > FROM
> > > [Broadcast]
> > > WHERE
> > > ReportingPoint = '105'
> > > ORDER BY
> > > ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> > > W.ProcessDate < X.ProcessDate
> > > WHERE W.ReportingPoint = '39'))
> > > GROUP BY SerialNumber
> > > ORDER BY SerialNumber
> > >
> > > If you need more info, or if this isn't clear, please ask. I don't
> > > even know where to get started on this one.
> > > Thanks!
> > > Tim|||No, I must not have explained it correctly.
The way the query works now is that it finds the most current serial
number from point 111, then finds the same serial number at point 39,
then returns a summary of what was broadcast from point 39 from that
time until the current time.
What I want it to do now is have the user enter a date/time. Use that
date/time to find the closest serial number from point 111. Find that
serial number at point 39, and return a summary of everything broadcast
between that time, and the time that the user entered.
Does this make more sense?
Tim
RajDeep wrote:
> Hi Timothy.
> If i understand your concern you want to take current date as
> Default Date and Current time as default time.It should prompt the user
> to enter the date Every time when ever it is run.right?
> Let me give some possible solutions you can achieve this but i am not
> sure
> 1) Go to Report properties,Click Auto refresh for 15 secs of time
> 2) Go to Report parameters,Add Userdate and User time as Parameter
> .Give Default values as
> =Today for date and =format(Now(),"hh:mi") for time.use this
> parameters in your query as @.userdate and @.usertime
> If this also doesn't work for you ,You need to build a custom code
> asking to take the values from the user
> Regards
> Raj Deep.A
>
> Timothy.Rybak@.gmail.com wrote:
> > I am not at a place where I can try this out, but I can't see where it
> > will take a user entered value to base the report. It looks like you
> > are hard coding a date/time. Is this the case?
> >
> > TIm
> >
> > paul.8.martin@.gmail.com wrote:
> > > Try this:
> > >
> > > declare @.ProcessDate datetime
> > > ,@.SerialNumber varchar(50)
> > >
> > > select @.ProcessDate = '8/7/2006 12:54:00 PM'
> > > ,@.SerialNumber = 'fsa679fsda679fdsa'
> > >
> > >
> > > SELECT SerialNumber
> > > FROM [Broadcast] A
> > > WHERE (ReportingPoint = '39')
> > > AND (ProcessDate >= (
> > > SELECT W.ProcessDate
> > > FROM [Broadcast] AS W
> > > JOIN (SELECT @.ProcessDate as ProcessDate, @.SerialNumber as
> > > SerialNumber) AS X
> > > ON W.SerialNumber = X.SerialNumber
> > > AND W.ProcessDate < X.ProcessDate
> > > ))
> > > GROUP BY SerialNumber
> > > ORDER BY SerialNumber
> > >
> > >
> > >
> > >
> > >
> > > Timothy.Rybak@.gmail.com wrote:
> > > > Let me preface this by saying that am relatively new to SQL. I have a
> > > > database that is updated every10 to 15 seconds with broadcasts from our
> > > > customer. These broadcasts come from 2 distinct points in their
> > > > operation. The first point - 39 - tells me what to build and
> > > > ship to them. The second point - 105 - tells me when they have used my
> > > > product on the line. Every thing is controlled by a serial number. So,
> > > > for example, at 8 AM they will send a broadcast saying that serial
> > > > number 1234 is at point 39 (my cue to build and ship the part), then
> > > > about 3 hours later I will get a broadcast from point 105 that serial
> > > > number 1234 has been built (my cue that my parts have been consumed).
> > > > In the intervening 3 hours, there will have been a bunch of broadcasts
> > > > through each point. Each broadcast is writted to a single table with
> > > > the following fields:
> > > >
> > > > ProcessDate - The date/time stamp that the broadcast was received
> > > > SerialNumber - The serial number referenced by the broadcast
> > > > ReportingPoint - The point that generated the broadcast (either 39 or
> > > > 105)
> > > >
> > > > OK, so here is the query I want to build:
> > > > Whenever the query is run, it should prompt the user for a date and
> > > > time, then find the serial number of the closest broadcast from 105 to
> > > > that date and time. Then it should use that serial number to find out
> > > > the date/time that unit passed through 39. Then it should return a
> > > > list of everything that has passed through 39 between that
> > > > time and the current time.
> > > >
> > > > I have a query that does all of this, but it doesn't ask the user for a
> > > > date and time, it just finds the most recent serial number broadcast
> > > > and goes from there. I tried putting an @.UserDate parameter in the sub
> > > > query, but it generates an error saying you can't do that. Here is
> > > > that existing query:
> > > >
> > > > SELECT SerialNumber
> > > > FROM [Broadcast] A
> > > > WHERE (ReportingPoint = '39') AND (ProcessDate >=> > > > (SELECT W.ProcessDate
> > > > FROM [Broadcast] AS W JOIN
> > > > (SELECT TOP
> > > > 1 ProcessDate, SerialNumber
> > > > FROM
> > > > [Broadcast]
> > > > WHERE
> > > > ReportingPoint = '105'
> > > > ORDER BY
> > > > ProcessDate DESC) AS X ON W.SerialNumber = X.SerialNumber AND
> > > > W.ProcessDate < X.ProcessDate
> > > > WHERE W.ReportingPoint = '39'))
> > > > GROUP BY SerialNumber
> > > > ORDER BY SerialNumber
> > > >
> > > > If you need more info, or if this isn't clear, please ask. I don't
> > > > even know where to get started on this one.
> > > > Thanks!
> > > > Tim

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it?
Pls reply as soon as possible.
Cheers
Hi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it?
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>