Question about returning a smalldatetime from a Function

I've been working this for a while. Kind of new to SQL Server
functions and not seeing what I am doing wrong. I have this function

CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS smalldatetime AS
BEGIN
Declare @.retVal varchar(10)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

The error I get is
Server: Msg 296, Level 16, State 3, Procedure test, Line 6
The conversion of char data type to smalldatetime data type resulted in
an out-of-range smalldatetime value.

1) I tried declaring @.retVal as a smalldatetime and get the error "Must
declare the variable '@.retVal'.'
2) If I run that same query in query analyzer (manually inserting the
parm) it returns 11/14/2006. That's what I want.

If I change the function to this and run it
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS varchar(50) AS
BEGIN
Declare @.retVal varchar(50)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

It now works but the return value is Nov 14 2006 12:00AM

What am I doing wrong?

TIASQL Server (alderran666@.gmail.com) writes:
> I've been working this for a while. Kind of new to SQL Server
> functions and not seeing what I am doing wrong. I have this function
> CREATE FUNCTION dbo.test (@.Group varchar(50))
> RETURNS smalldatetime AS
> BEGIN
> Declare @.retVal varchar(10)
> (SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@.Group))
> return convert(smalldatetime, @.retVal, 1)
> END
> The error I get is
> Server: Msg 296, Level 16, State 3, Procedure test, Line 6
> The conversion of char data type to smalldatetime data type resulted in
> an out-of-range smalldatetime value.
> 1) I tried declaring @.retVal as a smalldatetime and get the error "Must
> declare the variable '@.retVal'.'
> 2) If I run that same query in query analyzer (manually inserting the
> parm) it returns 11/14/2006. That's what I want.

What data type is t_master_schedules.date? If it is varchar(10), and
it returns 11/14/2006, the query looks, eh, funny to me. First,
11/14/2006 does not look like a date to me. :-) But even if I assume
that 11 is supposed to be a month, it seems strange that you consider
2006-11-14 to be less than 2004-12-12. Shouldn't your query read
MIN(convert(smalldatetime, [date], 101) in such case?

Alternatively, the column is datetime or smalldatetime, but in such
there is no need to incolve varchar at all.

Anyway, when I try:

select convert(smalldatetime, '11/14/2006', 1)

I get:

Server: Msg 295, Level 16, State 3, Line 1
Syntax error converting character string to smalldatetime data type.

Whereas

select convert(smalldatetime, '11/14/2006', 101)

returns 2006-11-14.

> If I change the function to this and run it
> CREATE FUNCTION dbo.test (@.Group varchar(50))
> RETURNS varchar(50) AS
> BEGIN
> Declare @.retVal varchar(50)
> (SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@.Group))
> return convert(smalldatetime, @.retVal, 1)
> END
> It now works but the return value is Nov 14 2006 12:00AM

Here you are first converting to smalldatetime, and then convert
back to varchar without any format specification, why you get this
default format.

--
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|||On 6 Jun 2006 01:50:03 -0700, SQL Server wrote:

(snip)
>1) I tried declaring @.retVal as a smalldatetime and get the error "Must
>declare the variable '@.retVal'.'

Hi SQL Server,

And yet, that is exactly what you should do. Never convert unless you
have to.

The error message you got is not a result of declaring @.retVal as a
smalldatetime, but a result of "something" that was off in the code when
you tried that. Unfortunately, you didn't post that version of the code,
so I can't tell you what went wrong. Maybe, if you still have tat
version archived, you could post it here?

Meanwhile, try if this works:

CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS smalldatetime
AS
BEGIN
DECLARE @.retVal smalldatetime
SELECT @.retVal = MIN([date])
FROM dbo.t_master_schedules
WHERE event_id = 13
AND group_ = @.Group
RETURN @.retVal
END

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> The error message you got is not a result of declaring @.retVal as a
> smalldatetime, but a result of "something" that was off in the code when
> you tried that. Unfortunately, you didn't post that version of the code,
> so I can't tell you what went wrong. Maybe, if you still have tat
> version archived, you could post it here?
> --
> Hugo Kornelis, SQL Server MVP

This is okay
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS varchar(50) AS
BEGIN
Declare @.retVal varchar(50)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

This is okay too (change Returns from varchar(50) to datetime)
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS datetime AS
BEGIN
Declare @.retVal varchar(50)
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

But change it to this
This is okay too (change Returns from varchar(50) to datetime)
CREATE FUNCTION dbo.test (@.Group varchar(50))
RETURNS datetime AS
BEGIN
Declare @.retVal datetime
(SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
(event_id = 13) AND (group_ =@.Group))
return convert(smalldatetime, @.retVal, 1)
END

Here is a link to a screen capture of the error.
http://i12.photobucket.com/albums/a...erran/error.jpg

the column [date] in the table t_master_schedules is a datetime.

I actually do want @.retVal to be a varchar because the end result
should be a string that shows the first date for a particular group and
the last date in a particular group. So I would be running a select
with a Max([date]) and returning a string

11/14/2006 and 02/03/2007

The problem is that I am not able to get the date formated into the
mm/dd/yyyy format that I want.|||SQL Server (alderran666@.gmail.com) writes:
> CREATE FUNCTION dbo.test (@.Group varchar(50))
> RETURNS datetime AS
> BEGIN
> Declare @.retVal datetime
> (SELECT @.retVal= MIN([date]) FROM dbo.t_master_schedules WHERE
> (event_id = 13) AND (group_ =@.Group))
> return convert(smalldatetime, @.retVal, 1)
> END
>...
> the column [date] in the table t_master_schedules is a datetime.
> I actually do want @.retVal to be a varchar because the end result
> should be a string that shows the first date for a particular group and
> the last date in a particular group. So I would be running a select
> with a Max([date]) and returning a string
> 11/14/2006 and 02/03/2007
> The problem is that I am not able to get the date formated into the
> mm/dd/yyyy format that I want.

If you want a string back, why do you then insist on converting to
smalldatetime? Should you not convert to char(10) and return char(10)?

Anyway, I would suggest that you scrap the function entirely. I don't
know where you use this function, but data access from scalar functions
should be avoided, as it can affect performance considerably if
you stick into a query. This is because the query more or less get
converted to a cursor behind the scenes. So it is much better to
integrate the logic in the main query.

As for the date formatting, you should avoid formatting dates in
SQL Server, but format them client side, so the the client's
regional settings are respected.
--
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 wrote:

> If you want a string back, why do you then insist on converting to
> smalldatetime? Should you not convert to char(10) and return char(10)?
..
> --
> 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

All I want to know is how to return
08/29/2006

from
'2006-08-29 00:00:00.000'

Looking at the SQL Server Books Online help resource it appears to me
that the convert function should be able to do this. But this doesn't
work. Why not and how can I format that date the way I want in the
output. In VB I'd just use the format function. Is there something
similar in T-SQL?
print convert(datetime, '2006-08-29 00:00:00.000', 101)|||SQL Server (alderran666@.gmail.com) writes:
> All I want to know is how to return
> 08/29/2006
> from
> '2006-08-29 00:00:00.000'
> Looking at the SQL Server Books Online help resource it appears to me
> that the convert function should be able to do this. But this doesn't
> work. Why not and how can I format that date the way I want in the
> output. In VB I'd just use the format function. Is there something
> similar in T-SQL?
> print convert(datetime, '2006-08-29 00:00:00.000', 101)

That converts a string value to datetime. You want to convert a datetime
value to a string.

A datetime value is a internally a numeric value and does not have any
format. The format code in the above example tells SQL Server how to
interpret the string.

But as I said, while you can format date values to string in your SQL code,
you should avoid doing so. This should be done client-side, so that the
client's regional settings can be respected. I can tell you that if you
give me an app that spits out strings like 08/29/2006, you will have a bug
report back in ten seconds, because that is not a date as far as I'm
concerned.

--
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.mspxsql

question about optimistic locking

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:555714
Hi there,
I am working for a bigger team that develops VS.NET/SQL Server business app.
We have to implement application-wide solution for optimistic locking, so
for instance, we can inform end-user that the data was changed by somebody
else.
One of the ideas is to add a special OPLOCKID column to every table in our
database. That column is meant to be of decimal type and incremented every
time the record is updated. When we need to updates or delete the column,
"where" clause is to include the last known OPLOCKID. This way we can
guarantee that either we update the right record (unchanged between our read
and "now") or update will fail. The same for deletion - we will use "where"
with last known OPLOCKID.
The motivation behind this approach is that (a) using standard ADONET
mechanism for optimistic data locking is expensive because it compares all
"old" column values with "current" column values (b) since we control the
way OPLOCKID is incremented, we do not need to re-read it after successfull
update. Instead we just increment last known value and now it becomes
"current" value. A variation of this solution is to use trigger for updating
OPLOCKID filed but our architect says that we should avoid triggers in SQL
Server (although he was not too clear why).
To me all of that seems to be a way that was correct 10 years ago, when SQL
databases did not have a lot of functionality they have now. However I am
not a database guru, so I would like to get help on this group. My question
is: does all of that make sense ? Is there any better method in MS SQL that
allows achieveing the same result ?
Thank you very much,
Marek.You need to use a rowversion (timestamp) column. It's insane to develop
some custom mechanism to do this when the capability already exists.
This mechanism works very well, and doesn't require a trigger or other
mechanism to generate a new value for the column. Re-reading a row that was
just updated is not a problem because the chances are that the row will
still be in memory. If you have an immutable primary key (IDENTITY), then
all you need to verify is that the value of the rowversion column hasn't
changed for a row since it was read.
The only problem with this mechanism is if you have a mutable primary key.
But that's a problem regardless of the mechanism used for optimistic
concurrency.
You should be using stored procedures to perform updates to keep transaction
processing within the data tier wherever possible. This will simplify
troubleshooting and minimizing lock contention, blocking and deadlocks. It
will also improve scalability because it minimizes the time that locks are
held by waiting 'til the last possible instant to initiate the transaction.
Declare the procedure parameters as input/output, indicate whether a
collision occurred using the return value and pass the current
values--including the new rowversion back in the procedure parameters.
It sounds like you need a new architect.
"Marek" <nospam@.nospam.com> wrote in message
news:41J_e.2080$l03.436012@.news20.bellglobal.com...
> Hi there,
> I am working for a bigger team that develops VS.NET/SQL Server business
> app.
> We have to implement application-wide solution for optimistic locking, so
> for instance, we can inform end-user that the data was changed by somebody
> else.
> One of the ideas is to add a special OPLOCKID column to every table in our
> database. That column is meant to be of decimal type and incremented
> every
> time the record is updated. When we need to updates or delete the column,
> "where" clause is to include the last known OPLOCKID. This way we can
> guarantee that either we update the right record (unchanged between our
> read
> and "now") or update will fail. The same for deletion - we will use
> "where"
> with last known OPLOCKID.
> The motivation behind this approach is that (a) using standard ADONET
> mechanism for optimistic data locking is expensive because it compares all
> "old" column values with "current" column values (b) since we control the
> way OPLOCKID is incremented, we do not need to re-read it after
> successfull
> update. Instead we just increment last known value and now it becomes
> "current" value. A variation of this solution is to use trigger for
> updating
> OPLOCKID filed but our architect says that we should avoid triggers in SQL
> Server (although he was not too clear why).
> To me all of that seems to be a way that was correct 10 years ago, when
> SQL
> databases did not have a lot of functionality they have now. However I am
> not a database guru, so I would like to get help on this group. My
> question
> is: does all of that make sense ? Is there any better method in MS SQL
> that
> allows achieveing the same result ?
> Thank you very much,
> Marek.
>

Question about Hyperlink

Hi!

We have over 500 reports working with report server. In most of those reports we want to use the Hyperlink option to link those reports to some pages of our web application. So i've added javascript code to do a window.open. My problem is that i have to put all the URL for example www.myApp.com/page1.aspx. Our application will be install to different server (for different client) with different name so the adresse www.myApp.com will change with the server. In our application we use the webconfig file so when the server change all our page still working because there are using the connection in the webconfig. Is it possible to do something like that for the reports ? I know that i could create a dll then add a reference in our report. I,ve tried to do it using this http://msdn2.microsoft.com/en-us/library/ms155034.aspx but it never really work and I found this solution a bit complicated. What i mean is when we have a new client and that we want to install the application on his server we want to have a solution that will be quick and easy. We don't want to recreate the a new dll then change all config file to allow this dll.

Is there a simple way to change the reference of hyperlink in all our report. ? Right now the best solution i've think is to make a script that will read the xml file of the report and change all the name for the new one.

Thanks and sorry about my English !

There is value in the global collection that should give you what you are looking for. Try using the following expression in your hyperlink field.

=Globals!ReportServerUrl & "page1.aspx"

|||Thanks !! This is exactly what i was looking for !!!

Question about GroupBY and Aggregate functions.

Ok I know this is totally a noob question but I'm working with a table
of the following structure
ID (int), FK (int), Date (datetime)
I would like to select the ID & Max(Date) grouped by the FK. I don't
want to group by the ID but I want it included in the result set.
If I do a simple select like
select ID, FK, Max(Date) from tbl group by FK, ID
I get a result set that includes discreet dates for each ID, not the
max date for a given FK having this ID.
for example
ID FK Date
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
w/ the previous sql returns
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
What I would want is something like this
2 100 1/2/2006
4 150 1/2/2006
Thanks in advance.
S*untested*
select t1.ID,t1.FK,t1.Date
from tbl t1
inner join(
select FK, Max(Date)
from tbl
group by FK ) t2(FK,Date) on t2.FK=t1.FK and t2.Date=t1.Date
Note that if there are multiple IDs sharing the same
maximum date, the query will return both IDs.|||Thanks, I think that works, but is there any way to filter duplicate
dates? Perhaps a distinct on the subquery?
Also is there any way to do it without performing a date comparison?
That's what makes this particular solution work, but wouldn't that be
fairly costly from a resource perspective?|||
> Thanks, I think that works, but is there any way to filter duplicate
> dates? Perhaps a distinct on the subquery?
Not sure I understand your question. If your sample expected
results aren't what you really want (because they share
the same date), can you post some more information.

> Also is there any way to do it without performing a date comparison?
> That's what makes this particular solution work, but wouldn't that be
> fairly costly from a resource perspective?
You can't get away from doing some sort of date comparison
here since your requirements are based on the maximum date.

question about different query results with wildcard

Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.

There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.

Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results

Could someone explain or point me to more documentation on the difference results we get?
Thanks

To see the 'wildcards' used with T-SQL, look up topic: LIKE in Books Online.

The asterisk is not a T-SQL wildcard. You queries using an asterisk are most likely returning you counts where the last character is an asterisk. AND you MUST use the LIKE equality instead of equals (=).

Here is a small example of using Wildcards in T-SQL:

SET NOCOUNT ON

CREATE TABLE #MyTable
( MyTableID int IDENTITY,
MyString varchar(50)
)

INSERT INTO #MyTable VALUES ( '1234' )
INSERT INTO #MyTable VALUES ( '1234-1' )
INSERT INTO #MyTable VALUES ( '1234*' )
INSERT INTO #MyTable VALUES ( '1234-45' )
INSERT INTO #MyTable VALUES ( '1234-ab' )

SELECT '1234', count(1) FROM #MyTable WHERE MyString = '1234'
SELECT '1234*', count(1) FROM #MyTable WHERE MyString = '1234*'
SELECT 'LIKE ''1234''', count(1) FROM #MyTable WHERE MyString LIKE '1234'
SELECT 'LIKE ''1234%''', count(1) FROM #MyTable WHERE MyString LIKE '1234%'

DROP TABLE #MyTable

|||Hi Arnie, thanks for your response.

My situation is more basic than what appears in your example. Unfortunately, I am working solely through this application so I do not have access to the SQL to test out what you supplied, so my question is more of a need for an explanation of the search results. I do find lots of references to LIKE, Wildcards and pattern matching, but I don't find a way to explain to the users the best and most complete way to search. They mostly need a "this will always get us the results without missing anything" search technique and then to be able to select from a smaller group. I guess I need a basic course in understanding search results: how to get different ones and what they mean.

Using
Note contains 94949-'%'
returned one more result than when using an asterisk. I don't understand this difference.

Note contains 94949-'%1' or
Note contains 94949-'1%'
brings nothing nor does not using quotes. But there are hundreds of records which have the string starting with 94949-1 and a varying number of characters after that.
?Does the dash read not as a character in the string but as an expression?

When I use WITHIN 3 characters, I get too few results (eight). If I use AND, I get text unrelated to the account number I am looking for.

Again when I tried to narrow the search by adding one digit to the string to be matched, I did not get any results, but 500 results from the more general search is too much to scan by opening individual records.

Thanks for pondering this with me.

|||Assuming that the application is using SQL Server Full-Text Indexing, the rules would be defined here
http://msdn2.microsoft.com/en-us/library/ms187787.aspx

Question about Aggragation in Measure Group?

Hello,

I am working on a project using SQL Server BI Developement Studio 2005. While designing a cube, I would like create a Measure Group that shows me a count of students grouped by department or school. However, I am not able to see an aggragate function under Measure Group's properties that would take care of this? For a while I was thinking about using "ByAccount" function but my dimension doesn't have anything called Account. My dimension has following columns: studentid, term, majorid, [dept id], [dept name], [school id], [school name]. Is there a way to get a count of students grouped by dept or school under a Measure Group?

Thanks,

Below, you can see functions that I am able to see when designing Meaure Groups.

Sum

Count

Min

Max

DistinctCount

None

ByAccount

AverageOfChildren

FirstChild

LastChild

FirstNonEmpty

LastNonEmpty

Hi,

I suspect that you might didn't understand the concept of OLAP yet (sorry if I'm wrong with that assumption).

You first have to think about your data... what are facts, what are dimensions. Facts are the base of your measures. Dimensions are your grouping creterias.

In your case the fact table and the dimension table might be the same. You define a "count" measure on any of your fields.

You also define one or more dimensions with your grouping creterias (department, school or whatever you want)...

When you open your cube you will see the count of records you processed. You then can drilldown on each dimension you defined to group your count...

Sorry, this is only a very brief hint how you might start. I really can't give you a complete training on OLAP in this forum...

|||

Thank you Thomas. I am new to Olap and still struggling a little bit to get the concepts right. My understading of a Fact table was that it was the only place where you have columns that can be summed, addded ,aggragated and so on. However, it looks like it is not the case. Do you know where I could learn more about BI in SQL 2005? Is there a website or something with some info?

|||

Take a look for Mosha & Teo Lachev on Google.

Mosha has some good resources listed on his web site. Teo has a great book out.

Question about Aggragation in Measure Group?

Hello,

I am working on a project using SQL Server BI Developement Studio 2005. While designing a cube, I would like create a Measure Group that shows me a count of students grouped by department or school. However, I am not able to see an aggragate function under Measure Group's properties that would take care of this? For a while I was thinking about using "ByAccount" function but my dimension doesn't have anything called Account. My dimension has following columns: studentid, term, majorid, [dept id], [dept name], [school id], [school name]. Is there a way to get a count of students grouped by dept or school under a Measure Group?

Thanks,

Below, you can see functions that I am able to see when designing Meaure Groups.

Sum

Count

Min

Max

DistinctCount

None

ByAccount

AverageOfChildren

FirstChild

LastChild

FirstNonEmpty

LastNonEmpty

Hi,

I suspect that you might didn't understand the concept of OLAP yet (sorry if I'm wrong with that assumption).

You first have to think about your data... what are facts, what are dimensions. Facts are the base of your measures. Dimensions are your grouping creterias.

In your case the fact table and the dimension table might be the same. You define a "count" measure on any of your fields.

You also define one or more dimensions with your grouping creterias (department, school or whatever you want)...

When you open your cube you will see the count of records you processed. You then can drilldown on each dimension you defined to group your count...

Sorry, this is only a very brief hint how you might start. I really can't give you a complete training on OLAP in this forum...

|||

Thank you Thomas. I am new to Olap and still struggling a little bit to get the concepts right. My understading of a Fact table was that it was the only place where you have columns that can be summed, addded ,aggragated and so on. However, it looks like it is not the case. Do you know where I could learn more about BI in SQL 2005? Is there a website or something with some info?

|||

Take a look for Mosha & Teo Lachev on Google.

Mosha has some good resources listed on his web site. Teo has a great book out.

Question about "servername\instancename"

I'm working on the setup .ini file and wondering about
RSDATABASESERVER="servername\instancename"
If the server has only one instance is the instancename the same as the
servername?Normally you would just refer to it as the servername. But occasionally
in 2005 I've seen the default instance referred as MSSQLServer.
Check this page in BOL;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/60e0a0b2-8a47-4eda-a
5df-3e5e403dbdbc.htm
especially note that it says;
InstanceName
Specifies the name of the report server instance. Report server
instancing is based on SQL Server instancing. This value specifies a
SQL Server instance name. By default, this value is MSSQLSERVER. Do not
modify this setting.
There is a configuration tool to save you having to mod the ini files
direct called rsconfig, look it up in BOL.
--
Regards
Chris
Al wrote:
> I'm working on the setup .ini file and wondering about
> RSDATABASESERVER="servername\instancename"
> If the server has only one instance is the instancename the same as
> the servername?

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
Thanxs
Hi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs
|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
ThanxsHi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
ThanxsHi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs