Getting the past 24 hours worth of data?

I have a need to query my data and return only the last 24 hours (versus last day) worth of data.

My date and time fields are called:

calc_date = the date
Calc_time= the time
RealTime = Calc_date+Calc_time as realtime

I know if I use getdate()-1 that will get me the last day, but I need the last 24 hours. Any ideas on how would I do this?

I think what you're after is the DATEADD() function, so to get the start of the last 24 hours you could use:

DATEADD(hh,-24,GetDate())

e.g. SELECT DATEADD(hh,-24,GetDate()) AS [24HoursAgo]

hh = hours

|||

Hi Jim,

Follow this example, I hope it'll help you.

declare @.date nvarchar(10),

@.time nvarchar(8),

@.lastday datetime

select

@.date = '2007-07-08',

@.time = '15:45:20',

@.lastday = convert(datetime, (@.date + ' ' + @.time))

select

'Before:' as [Info],

@.date as [Date],

@.time as [Time],

@.lastday as [DateTime]

select

'After:' as [Info],

@.date as [Date],

@.time as [Time],

dateadd(hh, -24, @.lastday) as [DateTime]

|||

Actually, Jim, if you want the last 24 hours GETDATE() - 1 should work just fine. Look at this:

Code Snippet

select getdate() as now,
getdate()-1 as [24 hours ago]

/*
now 24 hours ago
-
2007-08-16 07:22:42.340 2007-08-15 07:22:42.340

|||

Your query might be like this..

Code Snippet

select

*

From

<Table Name>

Where

Cast(Calc_date+Calc_timeas datetime) between Getdate() and Getdate() -1

Getting the first period's data - OpeningPeriod function

Hello,

I have a Time hierarchy (FY - Period Num) as follows:

- Fiscal Year

- - Period Number

I am trying to get the number of just PeriodNumber 1 for each year for Actives. I have tried the following:

([Measures].[Active] , OpeningPeriod( [Time].[FY - Period Num].[Time].[FY - Period Num].currentmember ) )

That returns nothing.

([Measures].[Active] , OpeningPeriod( [Time].[FY - Period Num].[Period Number] , [Time].[FY - Period Num].currentmember ) )

That just returns the current period number.

I had also tried to do stuff like:

([Measures].[Active] , [Time].[Period Number] .currentmember)

But that again only gave me the current period data

([Measures].[Active] , [Time].[Period Number] .[&1])

Gives me a #VALUE! error

I also tried:

([Measures].[Active] , [Time].[Period Number] .[&1], [Time].[Fiscal Year] .currentmember)

I can do this:

([Measures].[Active] , [Time].[FY - Period Num].[Period Number].[&1]&[2006])

Here I get data, but it is only for 2006.

And on, and on. Obviously, I don't know what I am doing, but I think I am close.

How can I get the first period data from the current year?

Thank you.

-Gumbatman

Hello Again GumbatMan.

The answere to this question depends on if you have a time dimension that streches further away than the current year.

If you only have the current year as the last member on the year level you could write MDX like:

YourTimeDimension.(FY - Period Num).LastChild.FirstChild.

This will give you the first child member of the last year in your time dimension/hierarchy.

In the enterprise edition of SSAS2005 you have aggregation functions that will take care of this problem. Have a look at the properties for the measure in the relevant measure group.

Here is a link to another approach provided that you are looking for the current time member in reality:

http://blogs.conchango.com/christianwade/archive/2006/06/23/MDX-Script_3A00_-Current_2F00_Relative-Period.aspx

Think about if your problem is about aggregations(key wordemi additive measure) or the time member on the column or row axis in a client.

HTH

Thomas Ivarsson

|||

Thomas,

Once again you've been very, very helpful.

Thank you!

getting the date

hey all,
when i use the GetDate() function it puts the time in there as well: how do
i get just the date?
thanks,
ariTry select convert(char(10),getdate(),101)
HTH,
Srinivas Sampangi
"ari" <ari@.discussions.microsoft.com> wrote in message
news:93DA4FC3-6F9E-455F-8D9E-D9E392EA6B15@.microsoft.com...
> hey all,
> when i use the GetDate() function it puts the time in there as well: how
> do
> i get just the date?
> thanks,
> ari|||On Sat, 15 Oct 2005 07:43:01 -0700, ari wrote:

>hey all,
>when i use the GetDate() function it puts the time in there as well: how do
>i get just the date?
>thanks,
>ari
Hi ari,
To get the date without time in datetime format (i.e. the time is set to
midnight, since you can't really _remove_ the time from a datetime), use
SELECT DATEADD(day,
DATEDIFF(day, '20050101', CURRENT_TIMESTAMP),
'20050101')
(You can replace '20050101' with any valid date, but make sure to
replacec it in both places).
To get the date without time in character format, use
SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
The style parameter 112 results in the ANSI standard date format
yyyymmdd. You can get other formats as well (check out the item on "CAST
and CONVERT" in Books Online for a full list), but they are less safe in
globally operating systems.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Impossible, if you want to have it as datetime. See
http://www.karaszi.com/SQLServer/info_datetime.asp for more info and recomme
ndations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ari" <ari@.discussions.microsoft.com> wrote in message
news:93DA4FC3-6F9E-455F-8D9E-D9E392EA6B15@.microsoft.com...
> hey all,
> when i use the GetDate() function it puts the time in there as well: how d
o
> i get just the date?
> thanks,
> ari|||date and time in SQL SERVER ARE SIAMY TWINS, You can't separate them. But yo
u
can view it whatever format you want.
Regards
R.D
--Knowledge gets doubled when shared
"Tibor Karaszi" wrote:

> Impossible, if you want to have it as datetime. See
> http://www.karaszi.com/SQLServer/info_datetime.asp for more info and recom
mendations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ari" <ari@.discussions.microsoft.com> wrote in message
> news:93DA4FC3-6F9E-455F-8D9E-D9E392EA6B15@.microsoft.com...
>

Getting the average results based on time.

Hello~,
The table has columns like this.
__
time smalldatetime
value1 int
value2 int
-

for example,
....
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220
....

The record is inserted at every hour.

I want get daily,monthly,yearly average and display the result ordered by time.

SELECT CONVERT(CHAR(8),time,112) as [Day],
AVG(value1) AS value1,
AVG(value2) AS value2
FROM MyTable
GROUP BY CONVERT(CHAR(8),time,112)
ORDER BY 1

SELECT CONVERT(CHAR(6),time,112) as [Month],
AVG(value1) AS value1,
AVG(value2) AS value2
FROM MyTable
GROUP BY CONVERT(CHAR(6),time,112)
ORDER BY 1

SELECT CONVERT(CHAR(4),time,112) as [Year],
AVG(value1) AS value1,
AVG(value2) AS value2
FROM MyTable
GROUP BY CONVERT(CHAR(4),time,112)
ORDER BY 1

sql

getting table last accessed time

is there a possibility to get table last accessed time?Z.F
Look, SQL Server does not track such kind of operations , however you can
add additional column to the table with GETDATE() DEFAULT.
At the end of the day you could query the table with MAX() of this (new
added) column.
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
> is there a possibility to get table last accessed time?
>|||Hi Uri,
For SELECT statements your suggestion will not work out. I feel the only
solution is to run the profiler.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#og6ifucEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Z.F
> Look, SQL Server does not track such kind of operations , however you can
> add additional column to the table with GETDATE() DEFAULT.
> At the end of the day you could query the table with MAX() of this (new
> added) column.
>
> "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
>|||Hari
Yes , you are right ,did not think about SELECT statements
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ujdcwvvcEHA.1644@.tk2msftngp13.phx.gbl...
> Hi Uri,
> For SELECT statements your suggestion will not work out. I feel the only
> solution is to run the profiler.
> Thanks
> Hari
> MCDBA
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#og6ifucEHA.1356@.TK2MSFTNGP09.phx.gbl...
can[vbcol=seagreen]
>sql

getting table last accessed time

is there a possibility to get table last accessed time?Z.F
Look, SQL Server does not track such kind of operations , however you can
add additional column to the table with GETDATE() DEFAULT.
At the end of the day you could query the table with MAX() of this (new
added) column.
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
> is there a possibility to get table last accessed time?
>|||Hi Uri,
For SELECT statements your suggestion will not work out. I feel the only
solution is to run the profiler.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#og6ifucEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Z.F
> Look, SQL Server does not track such kind of operations , however you can
> add additional column to the table with GETDATE() DEFAULT.
> At the end of the day you could query the table with MAX() of this (new
> added) column.
>
> "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
> > is there a possibility to get table last accessed time?
> >
> >
>|||Hari
Yes , you are right ,did not think about SELECT statements
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ujdcwvvcEHA.1644@.tk2msftngp13.phx.gbl...
> Hi Uri,
> For SELECT statements your suggestion will not work out. I feel the only
> solution is to run the profiler.
> Thanks
> Hari
> MCDBA
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#og6ifucEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > Z.F
> > Look, SQL Server does not track such kind of operations , however you
can
> > add additional column to the table with GETDATE() DEFAULT.
> > At the end of the day you could query the table with MAX() of this (new
> > added) column.
> >
> >
> >
> > "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> > news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
> > > is there a possibility to get table last accessed time?
> > >
> > >
> >
> >
>

getting table last accessed time

is there a possibility to get table last accessed time?
Z.F
Look, SQL Server does not track such kind of operations , however you can
add additional column to the table with GETDATE() DEFAULT.
At the end of the day you could query the table with MAX() of this (new
added) column.
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
> is there a possibility to get table last accessed time?
>
|||Hi Uri,
For SELECT statements your suggestion will not work out. I feel the only
solution is to run the profiler.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#og6ifucEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Z.F
> Look, SQL Server does not track such kind of operations , however you can
> add additional column to the table with GETDATE() DEFAULT.
> At the end of the day you could query the table with MAX() of this (new
> added) column.
>
> "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> news:uqPkscucEHA.2812@.TK2MSFTNGP11.phx.gbl...
>
|||Hari
Yes , you are right ,did not think about SELECT statements
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ujdcwvvcEHA.1644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Uri,
> For SELECT statements your suggestion will not work out. I feel the only
> solution is to run the profiler.
> Thanks
> Hari
> MCDBA
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#og6ifucEHA.1356@.TK2MSFTNGP09.phx.gbl...
can
>

Getting System Time From SQL Server

I'm using an Access MDB as a front end to a SQL 7 database. When I place the
system time in a field in Access, it used the user's clock. I'd like to use
the SQL Server system time, so that the times will be synchronized. I
suppose I can just do a pass-through query with "select getdate()." But I
was wondering if there was some built-in method, either in Access or SQL
Server. I'm concerned about the overhead of running a pass-through query
every time I need the current time.

Thanks.Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

I'm using an Access MDB as a front end to a SQL 7 database. When I place
the system time in a field in Access, it used the user's clock. I'd like
to use the SQL Server system time, so that the times will be
synchronized. I suppose I can just do a pass-through query with "select
getdate()." But I was wondering if there was some built-in method,
either in Access or SQL Server. I'm concerned about the overhead of
running a pass-through query every time I need the current time.

It you want the time on the server, "SELECT getdate()" is probably the
simplest way to do it. But you could of course make a remote procedure
call outside SQL Server as a little exercise in Windows programming
if you like. I can't see that's worth the pain, though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99F6EF225864CYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>I'm using an Access MDB as a front end to a SQL 7 database. When I place
>the system time in a field in Access, it used the user's clock. I'd like
>to use the SQL Server system time, so that the times will be
>synchronized. I suppose I can just do a pass-through query with "select
>getdate()." But I was wondering if there was some built-in method,
>either in Access or SQL Server. I'm concerned about the overhead of
>running a pass-through query every time I need the current time.

>
It you want the time on the server, "SELECT getdate()" is probably the
simplest way to do it. But you could of course make a remote procedure
call outside SQL Server as a little exercise in Windows programming
if you like. I can't see that's worth the pain, though.
>

:-) Thanks! Just wanted to make sure I wasn't missing something.|||"Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>I'm using an Access MDB as a front end to a SQL 7 database. When I place the
>system time in a field in Access, it used the user's clock. I'd like to use
>the SQL Server system time, so that the times will be synchronized. I
>suppose I can just do a pass-through query with "select getdate()." But I
>was wondering if there was some built-in method, either in Access or SQL
>Server. I'm concerned about the overhead of running a pass-through query
>every time I need the current time.

In a corporate network everyone should be using a common time source.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/|||Well, unfortunately that doesn't seem to be the case. Individual computers
vary from the server time by as much as 10 minutes! Is there some sort of
setting to use to use the server time. Maybe I'll pass that along to the
admin and see if he implements it.

"Tony Toews [MVP]" <ttoews@.telusplanet.netwrote in message
news:4a3uk39afrlojib6hgn1f3j59frc32livn@.4ax.com...

Quote:

Originally Posted by

"Neil" <nospam@.nospam.netwrote:
>

Quote:

Originally Posted by

>>I'm using an Access MDB as a front end to a SQL 7 database. When I place
>>the
>>system time in a field in Access, it used the user's clock. I'd like to
>>use
>>the SQL Server system time, so that the times will be synchronized. I
>>suppose I can just do a pass-through query with "select getdate()." But I
>>was wondering if there was some built-in method, either in Access or SQL
>>Server. I'm concerned about the overhead of running a pass-through query
>>every time I need the current time.

>
In a corporate network everyone should be using a common time source.
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

|||"Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>Well, unfortunately that doesn't seem to be the case.

Which is why I put the weasel words "should". <smile>

Quote:

Originally Posted by

>Individual computers
>vary from the server time by as much as 10 minutes! Is there some sort of
>setting to use to use the server time.

Yes, there is however I'm not at all sure how to do it. I think it involves ensuring
a server is hitting an Internet time source on a regular basis. Then, presumably
using group policy, ensure everyone on the network get thier time from the server.
But I'm not an IT person so I have no idea.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/|||"Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>Well, unfortunately that doesn't seem to be the case. Individual computers
>vary from the server time by as much as 10 minutes! Is there some sort of
>setting to use to use the server time. Maybe I'll pass that along to the
>admin and see if he implements it.

BTW there are functions to also get the system time from a server and
compare that to the time on the PC. You can use that to show the IT
department.

API: Retrieve NT Server's Time
http://www.mvps.org/access/api/api0039.htm
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/|||The command line (which should be included in the logon script) is:

net time /DOMAIN:DomainName /SET

HtH

Pieter

"Tony Toews [MVP]" <ttoews@.telusplanet.netwrote in message
news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@.4ax.com...

Quote:

Originally Posted by

"Neil" <nospam@.nospam.netwrote:
>

Quote:

Originally Posted by

>>Well, unfortunately that doesn't seem to be the case. Individual computers
>>vary from the server time by as much as 10 minutes! Is there some sort of
>>setting to use to use the server time. Maybe I'll pass that along to the
>>admin and see if he implements it.

>
BTW there are functions to also get the system time from a server and
compare that to the time on the PC. You can use that to show the IT
department.
>
API: Retrieve NT Server's Time
http://www.mvps.org/access/api/api0039.htm
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

|||

Quote:

Originally Posted by

"Tony Toews [MVP]" <ttoews@.telusplanet.netwrote in message
news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@.4ax.com...

Quote:

Originally Posted by

>"Neil" <nospam@.nospam.netwrote:
>>

Quote:

Originally Posted by

>>>Well, unfortunately that doesn't seem to be the case. Individual
>>>computers
>>>vary from the server time by as much as 10 minutes! Is there some sort of
>>>setting to use to use the server time. Maybe I'll pass that along to the
>>>admin and see if he implements it.

>>
>BTW there are functions to also get the system time from a server and
>compare that to the time on the PC. You can use that to show the IT
>department.
>>
>API: Retrieve NT Server's Time
>http://www.mvps.org/access/api/api0039.htm
>>
>Tony
>--
>Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
>read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
>http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

>
>

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nos pam.please@.online.replace.with.norway>
wrote in message news:O6o2%23EyMIHA.2432@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

The command line (which should be included in the logon script) is:
>
net time /DOMAIN:DomainName /SET
>
HtH
>
Pieter
>

Just to be clear, putting that into the user's logon script will set their
PC's time to the server's time?|||"Neil" <nospam@.nospam.netwrote in message
news:8WT3j.25110$JD.20049@.newssvr21.news.prodigy.n et...

Quote:

Originally Posted by

>

Quote:

Originally Posted by

>"Tony Toews [MVP]" <ttoews@.telusplanet.netwrote in message
>news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@.4ax.com...

Quote:

Originally Posted by

>>"Neil" <nospam@.nospam.netwrote:
>>>
>>Well, unfortunately that doesn't seem to be the case. Individual
>>computers
>>vary from the server time by as much as 10 minutes! Is there some sort
>>of
>>setting to use to use the server time. Maybe I'll pass that along to the
>>admin and see if he implements it.
>>>
>>BTW there are functions to also get the system time from a server and
>>compare that to the time on the PC. You can use that to show the IT
>>department.
>>>
>>API: Retrieve NT Server's Time
>>http://www.mvps.org/access/api/api0039.htm
>>>
>>Tony
>>--
>>Tony Toews, Microsoft Access MVP
>> Please respond only in the newsgroups so that others can
>>read the entire thread of messages.
>> Microsoft Access Links, Hints, Tips & Accounting Systems at
>>http://www.granite.ab.ca/accsmstr.htm
>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

>>
>>

>
"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nos pam.please@.online.replace.with.norway>
wrote in message news:O6o2%23EyMIHA.2432@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

>The command line (which should be included in the logon script) is:
>>
>net time /DOMAIN:DomainName /SET
>>
>HtH
>>
>Pieter
>>

>
Just to be clear, putting that into the user's logon script will set their
PC's time to the server's time?
>

To the DOMAIN controller's time.

The SQL server (which in most cases is not a DC) should also synch to the
domain controller.

There's also a registry setting to force it to synch that can be set under
domain policies.

The only time I've had to do the net time /domain: in recent memory was when
the time of the client was so far off that it wouldn't log in properly.

Quote:

Originally Posted by

>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Getting system time from SQL

Hi All
Does anyone know how to call the current system time
(using transact) and then write that data into a table?
Thanks
JUPDATE table SET curtime = CURRENT_TIMESTAMP
OR
UPDATE table SET curtime = GETDATE()
Kurt
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:10fc01c3b3a7$32a74840$a001280a@.phx.gbl...
> Hi All
> Does anyone know how to call the current system time
> (using transact) and then write that data into a table?
> Thanks
> J

Getting started?

Hi,
I'm looking to implement Reporting Services for the first time and have a
couple quiestions, which I hope someone can answer for me. Or database is
SQL Server 2000, currently with sp1. We do have a license for SQL Server
2005 but our business application does not support it yet.
Someone has recommended that we use RS from the SQL 2005 because of it's
better features than the previous version.
Reading the prerequisits for installation I see that SQL Sever 2000 should
be at sp3a, how hard a requisit is this? Can I get by with sp1?
Regarding the report designer, do I really have to have Visual Studio .NET
2003 to use the designer? I don't have it yet so could I get by with Visual
Studio Express?
I guess those are the two biggest questions I have right now.
Thanks in advance,
LinnOn Oct 16, 1:03 pm, "Linn Kubler" <lkub...@.chartwellwisc2.com> wrote:
> Hi,
> I'm looking to implement Reporting Services for the first time and have a
> couple quiestions, which I hope someone can answer for me. Or database is
> SQL Server 2000, currently with sp1. We do have a license for SQL Server
> 2005 but our business application does not support it yet.
> Someone has recommended that we use RS from the SQL 2005 because of it's
> better features than the previous version.
> Reading the prerequisits for installation I see that SQL Sever 2000 should
> be at sp3a, how hard a requisit is this? Can I get by with sp1?
> Regarding the report designer, do I really have to have Visual Studio .NET
> 2003 to use the designer? I don't have it yet so could I get by with Visual
> Studio Express?
> I guess those are the two biggest questions I have right now.
> Thanks in advance,
> Linn
I would suggest upgrading to SP3a, it has added functionality since
SP1 in terms of SQL Server. You might be able to get away w/VS Express
w/Advanced Services temporarily; however, SSRS Express Edition does
not have all the functionality that SSTS Std Edition has (http://
www.microsoft.com/sql/prodinfo/features/compare-features.mspx ). Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1192586314.331811.84030@.e9g2000prf.googlegroups.com...
> On Oct 16, 1:03 pm, "Linn Kubler" <lkub...@.chartwellwisc2.com> wrote:
>> Hi,
>> I'm looking to implement Reporting Services for the first time and have a
>> couple quiestions, which I hope someone can answer for me. Or database
>> is
>> SQL Server 2000, currently with sp1. We do have a license for SQL Server
>> 2005 but our business application does not support it yet.
>> Someone has recommended that we use RS from the SQL 2005 because of it's
>> better features than the previous version.
>> Reading the prerequisits for installation I see that SQL Sever 2000
>> should
>> be at sp3a, how hard a requisit is this? Can I get by with sp1?
>> Regarding the report designer, do I really have to have Visual Studio
>> .NET
>> 2003 to use the designer? I don't have it yet so could I get by with
>> Visual
>> Studio Express?
>> I guess those are the two biggest questions I have right now.
>> Thanks in advance,
>> Linn
>
> I would suggest upgrading to SP3a, it has added functionality since
> SP1 in terms of SQL Server. You might be able to get away w/VS Express
> w/Advanced Services temporarily; however, SSRS Express Edition does
> not have all the functionality that SSTS Std Edition has (http://
> www.microsoft.com/sql/prodinfo/features/compare-features.mspx ). Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Hi Enrique,
Did you mean SSRS Std Edition? Not sure what SSTS is. Assuming that's the
case, I do have SQL Server 2005 Standard Edition, it is the Visual Studio I
don't have currently so was wondering about the free version of that. From
the article you linked it looks like Advanced Services is a component of
Reporting Services out of SQL Server Express Edition.
First hurdle is to install SQL Server 2000 sp3a though.
Thanks for the help,
Linn

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/d.../>
07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_ref_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?
See srv_pfieldex,
http://msdn.microsoft.com/library/de...ef_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

Getting rid of the time value in GetDate()

Sorry to be such a dufus but I can't figure out a slick way of getting a date that is just the month, day and year, e.g. 7/1/2003 and still a date data type. I need to edit a DTS package that is slamming data into tables. The previous programmer used GetDate() to insert EnteredOn dates into the tables and the time value is blowing up some of the later processing.

I tried replacing GetDate() with (DatePart(m, GetDate()) + '/' + DatePart(d, GetDate()) + '/' + DatePart(yyyy, GetDate())) AS EnteredOn but that blew up. First it didn't like the '/' strings and if I removed them I got 2011 as my result--AUGH! I also fear that whatever I end up with will not be suitable for stuffing in a date field without then doing a Cast or something.

HELP!

I've tried to use input parameters and a transformation script to change the value of but they all barf on me. Can't use an input parameter in the query, e.g. Select ? as 'EnteredOn' and set the input parameter to the results of VB Date(), either. Changing the value of the input column in a transformation script blows up also.

Grrr! Surely someone out there does this kind of thing on a regular basis?

Thanks for your help!select convert(varchar(10),getdate(),120)

the string gets converted back in to a date when inserted into a datetime/smalldatetime datatype.|||That seems to work. I appreciate the help.

If you're ever in the Denver/Boulder area, I owe you a biplane ride!

Reply to this post!|||careful, I would travel to Denver for a biplane ride!|||Well, give me an email at gailschipper@.yahoo.com if you do, and we'll go.

Cheers!|||This really ought to be a supplied sql server function, it is so common.

Here is the same solution as a User-defined function:

CREATE FUNCTION dbo.DateOnly
(@.RawDateTime datetime )
RETURNS DateTime
AS
BEGIN
RETURN (Convert(Datetime,CONVERT(varchar(10),@.RawDateTime ,101)))
END|||I fully agree! It boggles the mind that it is so difficult to get something so basic. I did pick up the double convert so that the end product is a datetime data type.

Sure wish there was better documentation for these DTS packages. It's putting grey hairs on us VB programmers! After all, we only got into this because it was fun and (relatively) easy.

Thanks for the help!|||DTS is very powerful, but also unwieldy. My personal opinion is that I have never come across a task that had to be done in DTS, and I have never found one that couldn't be more easily implemented and maintained as SQL code, batch files, etc...

I stay away from it.|||Unfortunately, whoever came before me loved them--but wasn't very good at getting the details right. I have miles of DTS editing ahead of me.|||When you take over somebody elses work, you quickly realize that an expert is someone who knows how to use a technology, but a guru is someone who knows when to use a technology!|||I came into SQL Server from Oracle world. I don't have the right words to explain the pain in the transition !!! Anyway one of the first things I realized is the lack of powerful Date functions in SQL Server. Now I have a function that does most of the magic with date columns. If anyone needs it, let me know and I can mail it.|||sbaru Why not zip it up and post it here?sql

Getting rid of the time value from a datetime field

I want to get rid of the time stamp in a datetime field and also i need to include that in a group by query.

For Ex:

select join_dt, avg(salary) from employee
group by join_dt

Here in the above example, join_dt is a datetime field, i need some function similar datepart which gives only the date part of the join_dt.you can use the function DATEPART. See Books Online for more information.|||I think you just answered you own question...

why don't u use datepart?

you could do something like this:

select join_dt, avg(salary) from employee
group by datepart("datepartchoice",join_dt)

or are u trying to say something else?|||@.Patrick: hopefully you are not insulted by this remark, but didn't I post just the same answer as you did?|||No offence taken, I was just wondering what was getbabs really asking, since his question was already containing the answer :

getbabs>>i need some function similar datepart which gives only the date part of the join_dt.

:)|||I am looking for a formatted date ("dd/mm/yy") from a datetime field
and group by the same.|||select convert(varchar(12), getDate(), 6) where the 6 stands for the format used. Look under CONVERT in Books Online for the formats.|||i think the real problem here is why does join_dt have a time component

i could see the need for a time component in other datetime fields, but not this one

if you need to use DATEPART in order to use GROUP BY on the date portion only, this a sure sign that you have been manouevered into a very bad situation by poor design or coding

using DATEPART usually rules out indexes, and the query will perform poorly

do yourself a favour, and reset the time portion of all the join_dt to midnight

then you can go ahead and write simple queries like this --

select join_dt, avg(salary) from employee
group by join_dt

;)|||As an extension to Rudy's post, you could consider an additional time column if time is really required. Update the time column with the time component from the join_dt field and then blank the join_dt field to midnight. Having said that, I agree with him inasmuch as what's a time doing in a join date?

Getting rid of DOUBLE SPACING!

I just took over admin control of a website at http://www.lyricster.com and
I am having a hell of a time trying to pin down the problem of double
spacing when I add new lyrics to the database.

The thing is, most all of the db items (lyrics) on the website are single
spaced, and yet when I try to post new lyrics, it comes out double-spaced.
I even tried to log in as a normal user and post lyrics that way...same
problem.

If you need example of the problem go to the site, click on "Runnin' With
The Devil - Van Halen" and then click on a few other of the song
titles...all of the ones that have double spaced text are mine.

HELP!!!!I should add that the double spacing problem is not on the input side, but
how the database info is parsed out to the screen. Hope this clears it up
a bit more.

"Moviesounds.com" <aarrowstar@.yahoo.com> wrote in message
news:oy8Mb.13906$6y6.377982@.bgtnsc05-news.ops.worldnet.att.net...
> I just took over admin control of a website at http://www.lyricster.com
and
> I am having a hell of a time trying to pin down the problem of double
> spacing when I add new lyrics to the database.
> The thing is, most all of the db items (lyrics) on the website are single
> spaced, and yet when I try to post new lyrics, it comes out double-spaced.
> I even tried to log in as a normal user and post lyrics that way...same
> problem.
> If you need example of the problem go to the site, click on "Runnin' With
> The Devil - Van Halen" and then click on a few other of the song
> titles...all of the ones that have double spaced text are mine.
> HELP!!!!|||Moviesounds.com (aarrowstar@.yahoo.com) writes:
> I just took over admin control of a website at http://www.lyricster.com
> and I am having a hell of a time trying to pin down the problem of
> double spacing when I add new lyrics to the database.
> The thing is, most all of the db items (lyrics) on the website are single
> spaced, and yet when I try to post new lyrics, it comes out double-spaced.
> I even tried to log in as a normal user and post lyrics that way...same
> problem.
> If you need example of the problem go to the site, click on "Runnin' With
> The Devil - Van Halen" and then click on a few other of the song
> titles...all of the ones that have double spaced text are mine.

The HTML for the Van Halen thing looks like this:

So tell me why can't this be love <br />
<BR><br />
Straight from my heart oh tell me why <br />
<BR><br /
The extra <BR><br /> explains the double spacing. But neither the
single-spaced are OK:

you\'re always there <br />
<br />
\'Cause you\'re everywhere to me <br />
And when I close my eyes it\'s you I see <br /
What is that backslash doing before the single quotes?

Now, if you believe that this is a problem with SQL Server, then you
really have to provide your information. When you perform a SELECT
on the tables from Query Analyzer what do you see?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns946DA89388BC0Yazorman@.127.0.0.1...
> Moviesounds.com (aarrowstar@.yahoo.com) writes:
> > I just took over admin control of a website at http://www.lyricster.com
> > and I am having a hell of a time trying to pin down the problem of
> > double spacing when I add new lyrics to the database.
> > The thing is, most all of the db items (lyrics) on the website are
single
> > spaced, and yet when I try to post new lyrics, it comes out
double-spaced.
> > I even tried to log in as a normal user and post lyrics that way...same
> > problem.
> > If you need example of the problem go to the site, click on "Runnin'
With
> > The Devil - Van Halen" and then click on a few other of the song
> > titles...all of the ones that have double spaced text are mine.
> The HTML for the Van Halen thing looks like this:
> So tell me why can't this be love <br />
> <BR><br />
> Straight from my heart oh tell me why <br />
> <BR><br />
> The extra <BR><br /> explains the double spacing. But neither the
> single-spaced are OK:
> you\'re always there <br />
> <br />
> \'Cause you\'re everywhere to me <br />
> And when I close my eyes it\'s you I see <br />
> What is that backslash doing before the single quotes?
> Now, if you believe that this is a problem with SQL Server, then you
> really have to provide your information. When you perform a SELECT
> on the tables from Query Analyzer what do you see?
>
No idea, dude...I'm just a hack trying to figure out why the single-spaced
text I'm inputting into the myPHPserver interface with the SQL server is
parsing out as double spaced text when the request is made for a database
item.

In other words, I have no idea what a query analyzer is or where I could
find it. The myPHP documentation is definitely written for those already in
the know.

Also, I am not inputting any markup tags on the text...the server is doing
that on it's own.|||Moviesounds.com (aarrowstar@.yahoo.com) writes:
> No idea, dude...I'm just a hack trying to figure out why the
> single-spaced text I'm inputting into the myPHPserver interface with the
> SQL server is parsing out as double spaced text when the request is made
> for a database item.
> In other words, I have no idea what a query analyzer is or where I could
> find it. The myPHP documentation is definitely written for those
> already in the know.

Query Analyzer is a tool that comes with SQL Server, unless you only have
MSDE, in which case it is not included. I believe that with MSDE you may
still have OSQL, a command-line tool with which you can submit queries.

PHP I don't know anything about.

> Also, I am not inputting any markup tags on the text...the server is
> doing that on it's own.

Maybe the myPHPserver thing does. SQL Server does not, unless they are
part of the queries.

I'm afraid that you will not get much help here. If you don't know what
your system is doing, don't exepect people who don't have access to it
to find out.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Moviesounds.com" <aarrowstar@.yahoo.com> wrote in message news:<GrmMb.4866$VS4.160767@.bgtnsc04-news.ops.worldnet.att.net>...
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns946DA89388BC0Yazorman@.127.0.0.1...
> > Moviesounds.com (aarrowstar@.yahoo.com) writes:
> > > I just took over admin control of a website at http://www.lyricster.com
> > > and I am having a hell of a time trying to pin down the problem of
> > > double spacing when I add new lyrics to the database.
> > > > The thing is, most all of the db items (lyrics) on the website are
> single
> > > spaced, and yet when I try to post new lyrics, it comes out
> double-spaced.
> > > I even tried to log in as a normal user and post lyrics that way...same
> > > problem.
> > > > If you need example of the problem go to the site, click on "Runnin'
> With
> > > The Devil - Van Halen" and then click on a few other of the song
> > > titles...all of the ones that have double spaced text are mine.
> > The HTML for the Van Halen thing looks like this:
> > So tell me why can't this be love <br />
> > <BR><br />
> > Straight from my heart oh tell me why <br />
> > <BR><br />
> > The extra <BR><br /> explains the double spacing. But neither the
> > single-spaced are OK:
> > you\'re always there <br />
> > <br />
> > \'Cause you\'re everywhere to me <br />
> > And when I close my eyes it\'s you I see <br />
> > What is that backslash doing before the single quotes?
> > Now, if you believe that this is a problem with SQL Server, then you
> > really have to provide your information. When you perform a SELECT
> > on the tables from Query Analyzer what do you see?
> No idea, dude...I'm just a hack trying to figure out why the single-spaced
> text I'm inputting into the myPHPserver interface with the SQL server is
> parsing out as double spaced text when the request is made for a database
> item.
> In other words, I have no idea what a query analyzer is or where I could
> find it. The myPHP documentation is definitely written for those already in
> the know.
> Also, I am not inputting any markup tags on the text...the server is doing
> that on it's own.

I believe that MyPHP is used to link PHP to MySQL, not to MSSQL
(Microsoft SQL Server). So you will probably get a better response in
a newsgroup for MySQL and/or PHP than for MSSQL.

If I had to guess (and I do, since I know nothing about PHP or MySQL),
I would say that the problem is more likely to be in the PHP code, not
in the database.

Simon

Getting Return Value from system SPROC

I'm trying to learn how to write/use system sprocs for the 1st time. SYSTEM
SPROC below is a working sproc I have in the master db that returns a 1 if a
file exists and 0 if it doesn't. If I run QA CODE 1, my SYSTEM SPROC returns
the correct 1 or 0.
However, if I use my sproc as in QA CODE 2 to act on the returned value, I
get syntax errors.
How can I write QA CODE 2 so I can use an IF statement to test and act on
the returned value of my SYSTEM SPROC?
-- SYSTEM SPROC ************
CREATE procedure [dbo].[usp_FileExists]
@.physname nvarchar(260)
as
SET nocount ON
DECLARE @.i int
EXEC master..xp_fileexist @.physname,@.i out
SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END
-- QA CODE 1 ****************
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
-- QA CODE 2 ****************
IF EXEC master.dbo.usp_FileExists @.physname =
'D:\data\sql_data\myData_Data.MDF' = 0
PRINT 'Does not Exist 'You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||I need a little more explanation. I tried your code on my "System Sproc" and
it just returns the correct 1 or 0, not the PRINT message. So why would the
last IF test be ignored?
Should my "System Sproc" be an extended system Sproc?
If you know of any books that teach system and extended sprocs please let me
know. I'd really like to learn things like when I have to use a function vs.
sproc.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a
> function.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You do NOT want a 'extended' stored procedure. That is a different animal to
tally.
---
Here is a slightly revised version of the FileExists function (master..xp_Fi
leExist is an 'UnDocumented' system function. There is no guarantee it will
be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be co
nsistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx
.gbl...
>I need a little more explanation. I tried your code on my "System Sproc" an
d
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
> Should my "System Sproc" be an extended system Sproc?
>
> If you know of any books that teach system and extended sprocs please let
me
> know. I'd really like to learn things like when I have to use a function v
s.
> sproc.
>
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>
>|||thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OeixErImGHA.4696@.TK2MSFTNGP05.phx.gbl...
You do NOT want a 'extended' stored procedure. That is a different animal
totally.
---
Here is a slightly revised version of the FileExists function
(master..xp_FileExist is an 'UnDocumented' system function. There is no
guarantee it will be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be
consistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I need a little more explanation. I tried your code on my "System Sproc"
>and
> it just returns the correct 1 or 0, not the PRINT message. So why would
> the
> last IF test be ignored?
> Should my "System Sproc" be an extended system Sproc?
> If you know of any books that teach system and extended sprocs please let
> me
> know. I'd really like to learn things like when I have to use a function
> vs.
> sproc.
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>|||scott wrote:
> I need a little more explanation. I tried your code on my "System Sproc" a
nd
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
I think you ran all of the code together, so that the print statement is
inside the function. Try this instead:
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF' ,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
Run that, then run this:
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '

> Should my "System Sproc" be an extended system Sproc?
>
NO!!!

Getting one record at a time

I have a stored procedure that I call from a VB6 program:
DECLARE @.myid UNIQUEIDENTIFIER
BEGIN TRAN
SET @.myid = NEWID()
UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
WHERE ID=
(SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=1)
COMMIT TRAN
SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
The ID column is a clustered index.
This gives me the next available row for each user (there are 40 users), I
am regularly getting deadlocks.
Any help would be greatly appreciated.
Thanks,
JkJamz
I'm not sure undertsabd what you are trying to do but read up please this
chapter
SET TRANSACTION ISOLATION LEVEL in the BOL
"Jamz" <Jamz@.discussions.microsoft.com> wrote in message
news:D406B8AE-1DF6-4425-A4C0-DCEC4A328B87@.microsoft.com...
> I have a stored procedure that I call from a VB6 program:
> DECLARE @.myid UNIQUEIDENTIFIER
> BEGIN TRAN
> SET @.myid = NEWID()
> UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
> WHERE ID=
> (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and
ready=1)
> COMMIT TRAN
> SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
>
> The ID column is a clustered index.
> This gives me the next available row for each user (there are 40 users), I
> am regularly getting deadlocks.
> Any help would be greatly appreciated.
> Thanks,
> Jk|||Because XLOCK = <snip from Books OnLine>Use an exclusive lock that will be
held until the end of the transaction on all data processed by the statement
.
This lock can be specified with either PAGLOCK or TABLOCK, in which case the
exclusive lock applies to the appropriate level of granularity.</snip from
Books OnLine>
"HELD UNTIL THE END OF THE TRANSACTION ON ALL DATA PROCESSED... "
Your statement "Select Top 1..." Processes all rows in the table With Done
= 0, and Ready = 1, So it will pur a row Lock on every one of those rows an
d
hold it until the Commit Tran call...
This is a good example of why, unless you know exactly what you are doing,
and have carefully and identified a specific scenario where a lock hint is
necessary and appropriate, you should avoid them...
now, Next point...
Your query updates the row whose id is equal to the output of the subquery,
which returns the TOP 1 id from the table... But there is no Order By in the
subquery... This means that the value returned could be any ID in the table,
and SQL is under no oblication to return any one of the rows rather than any
other. Unless Order By clause is included, the Order of the rows in a
resultset is not guaranteed, and so the row returned by a TOP 1 select is
also not gauranteed...
Next point... assuming you put in an Order By (What that might be I don;t
know Order By PostCode, or what...) But assuming it's Order By ID, and that
you really want the Lowest id value (or the highest?) then you don;t need
Order By or top 1... and you can do all this without any of the Lock Hints o
r
explicit Transactions...
Select @.ID = Min(ID) -- [Or Max(ID)]
FROM data
WHERE done=0 and ready=1
-- --
UPDATE data SET
UID = NewID(), Done=1,
DateTimeDone = GetDate()
WHERE ID=@.ID
-- --
Select PostCode, MPN, ID
FROM data
WHERE ID = @.ID
-- --
"Jamz" wrote:

> I have a stored procedure that I call from a VB6 program:
> DECLARE @.myid UNIQUEIDENTIFIER
> BEGIN TRAN
> SET @.myid = NEWID()
> UPDATE data WITH (ROWLOCK) SET UID=@.myID, Done=1, DateTimeDone=GetDate()
> WHERE ID=
> (SELECT TOP 1 ID FROM data WITH ( XLOCK, READPAST) WHERE done=0 and ready=
1)
> COMMIT TRAN
> SELECT PostCode,MPN,ID FROM data WITH (NOLOCK) WHERE UID=@.myID
>
> The ID column is a clustered index.
> This gives me the next available row for each user (there are 40 users), I
> am regularly getting deadlocks.
> Any help would be greatly appreciated.
> Thanks,
> Jk|||Thanks for your help, I'll try that.
I don't really care which record is brought back, I only care that it is not
used by anyone else.
When I wrote something similar to what you are suggesting I got the same
record being returned to different users.
Admittedly I had not ordered (or done a Min(x)) on it.
When I wrapped things in a transaction it did not help either.
Thanks,
JK
"CBretana" wrote:
> Because XLOCK = <snip from Books OnLine>Use an exclusive lock that will b
e
> held until the end of the transaction on all data processed by the stateme
nt.
> This lock can be specified with either PAGLOCK or TABLOCK, in which case t
he
> exclusive lock applies to the appropriate level of granularity.</snip from
> Books OnLine>
> "HELD UNTIL THE END OF THE TRANSACTION ON ALL DATA PROCESSED... "
> Your statement "Select Top 1..." Processes all rows in the table With Do
ne
> = 0, and Ready = 1, So it will pur a row Lock on every one of those rows
and
> hold it until the Commit Tran call...
> This is a good example of why, unless you know exactly what you are doing,
> and have carefully and identified a specific scenario where a lock hint is
> necessary and appropriate, you should avoid them...
> now, Next point...
> Your query updates the row whose id is equal to the output of the subquery
,
> which returns the TOP 1 id from the table... But there is no Order By in t
he
> subquery... This means that the value returned could be any ID in the tabl
e,
> and SQL is under no oblication to return any one of the rows rather than a
ny
> other. Unless Order By clause is included, the Order of the rows in a
> resultset is not guaranteed, and so the row returned by a TOP 1 select is
> also not gauranteed...
> Next point... assuming you put in an Order By (What that might be I don;t
> know Order By PostCode, or what...) But assuming it's Order By ID, and tha
t
> you really want the Lowest id value (or the highest?) then you don;t need
> Order By or top 1... and you can do all this without any of the Lock Hints
or
> explicit Transactions...
> Select @.ID = Min(ID) -- [Or Max(ID)]
> FROM data
> WHERE done=0 and ready=1
> -- --
> UPDATE data SET
> UID = NewID(), Done=1,
> DateTimeDone = GetDate()
> WHERE ID=@.ID
> -- --
> Select PostCode, MPN, ID
> FROM data
> WHERE ID = @.ID
> -- --
>
> "Jamz" wrote:
>|||Sorry, I understand what you are doing... Then you do need a transaction, bu
t
more importantly, you need to set the Isolation level one level higher than
Read Committed, (which is the default), to precvent concurrent users from
attempting t oread the min (ID) until you have set the done flag...
try this:
NOTE: Remember to reset the Isolation level after the commit...
Declare @.ID Integer
Set Isolation Level Repeatable Read
Begin Transaction
Select @.ID = Min(ID) FROM data
WHERE done=0 and ready=1
-- --
UPDATE data SET
UID = NewID(), Done=1,
DateTimeDone = GetDate()
WHERE ID=@.ID
Commit Transaction
Set Isolation Level Read Committed
-- --
Select PostCode, MPN, ID
FROM data
WHERE ID = @.ID
-- --
"Jamz" wrote:
> Thanks for your help, I'll try that.
> I don't really care which record is brought back, I only care that it is n
ot
> used by anyone else.
> When I wrote something similar to what you are suggesting I got the same
> record being returned to different users.
> Admittedly I had not ordered (or done a Min(x)) on it.
> When I wrapped things in a transaction it did not help either.
> Thanks,
> JK
> "CBretana" wrote:
>|||Thanks for that. I think that will fix it. I was missing the transaction
level setting, I went down the locking hint route instead.
Cheers
JK
"CBretana" wrote:
> Sorry, I understand what you are doing... Then you do need a transaction,
but
> more importantly, you need to set the Isolation level one level higher tha
n
> Read Committed, (which is the default), to precvent concurrent users from
> attempting t oread the min (ID) until you have set the done flag...
> try this:
> NOTE: Remember to reset the Isolation level after the commit...
> Declare @.ID Integer
> Set Isolation Level Repeatable Read
> Begin Transaction
> Select @.ID = Min(ID) FROM data
> WHERE done=0 and ready=1
> -- --
> UPDATE data SET
> UID = NewID(), Done=1,
> DateTimeDone = GetDate()
> WHERE ID=@.ID
> Commit Transaction
> Set Isolation Level Read Committed
> -- --
> Select PostCode, MPN, ID
> FROM data
> WHERE ID = @.ID
> -- --
>
> "Jamz" wrote:
>

Getting Msg 18456 in referencing a database from a linked server

Trying to use linked server for the first time and having some problems.
I have 2 servers "ServerA" and "ServerB". From ServerA I want to be able to
access data from ServerB. I ran the following on ServerA
USE master
GO
EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
GO
Then I ran the following SQL on ServerA
SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
And I get the following message:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone tell me how I can get past this problem?
Thanks in advance!
What security type are you using?
Standard or Windows?
Your sp_addlinkedserver will default to use "current security credentials"
to authenticate to serverb.
You should try adding your linked server passing an SQL
authentication/Valid Windows account depending on your security type.
e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
Immy
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Trying to use linked server for the first time and having some problems.
> I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
able to
> access data from ServerB. I ran the following on ServerA
> USE master
> GO
> EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> GO
> Then I ran the following SQL on ServerA
> SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
> And I get the following message:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> Can someone tell me how I can get past this problem?
> Thanks in advance!
>
>
|||Thanks Immy...sp_addlinkedsrvlogin was exactly what I needed!
"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u8xksz7TEHA.1472@.TK2MSFTNGP12.phx.gbl...
> What security type are you using?
> Standard or Windows?
> Your sp_addlinkedserver will default to use "current security
credentials"[vbcol=seagreen]
> to authenticate to serverb.
> You should try adding your linked server passing an SQL
> authentication/Valid Windows account depending on your security type.
> e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
> Immy
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
problems.
> able to
>

Getting Msg 18456 in referencing a database from a linked server

Trying to use linked server for the first time and having some problems.
I have 2 servers "ServerA" and "ServerB". From ServerA I want to be able to
access data from ServerB. I ran the following on ServerA
USE master
GO
EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
GO
Then I ran the following SQL on ServerA
SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
And I get the following message:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Can someone tell me how I can get past this problem?
Thanks in advance!What security type are you using?
Standard or Windows?
Your sp_addlinkedserver will default to use "current security credentials"
to authenticate to serverb.
You should try adding your linked server passing an SQL
authentication/Valid Windows account depending on your security type.
e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
Immy
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> Trying to use linked server for the first time and having some problems.
>
> I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
able to
> access data from ServerB. I ran the following on ServerA
> USE master
> GO
> EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> GO
>
> Then I ran the following SQL on ServerA
> SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
>
> And I get the following message:
> Server: Msg 18456, Level 14, State 1, Line 1
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>
> Can someone tell me how I can get past this problem?
>
> Thanks in advance!
>
>
>|||Thanks Immy...sp_addlinkedsrvlogin was exactly what I needed!
"Immy" <imtiaz_ullah@.hotmail.com> wrote in message
news:u8xksz7TEHA.1472@.TK2MSFTNGP12.phx.gbl...
> What security type are you using?
> Standard or Windows?
> Your sp_addlinkedserver will default to use "current security
credentials"
> to authenticate to serverb.
> You should try adding your linked server passing an SQL
> authentication/Valid Windows account depending on your security type.
> e.g. sp_addlinkedsrvlogin ServerB ,false ,null , SQLUSER, USERPASSWORD"
> Immy
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:ed748v7TEHA.3420@.TK2MSFTNGP09.phx.gbl...
> > Trying to use linked server for the first time and having some
problems.
> >
> > I have 2 servers "ServerA" and "ServerB". From ServerA I want to be
> able to
> > access data from ServerB. I ran the following on ServerA
> > USE master
> > GO
> > EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
> > GO
> >
> > Then I ran the following SQL on ServerA
> > SELECT * FROM SERVERB.DBNAME.dbo.TABLENAME
> >
> > And I get the following message:
> > Server: Msg 18456, Level 14, State 1, Line 1
> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> >
> > Can someone tell me how I can get past this problem?
> >
> > Thanks in advance!
> >
> >
> >
>