Calculate Time Off

Hi,
I need a query that can return the total time off between 2 dates.
I have a table call tblTimeOff which has the following fields
StartTimeOff, Interval (minute), Wend
Sample data for tblTimeOff:-
10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on Wday
12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on Wday
15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on Wday
10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on Wend
12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on Wend
Saturday and Sunday are considered as Wend.
Mon - Fri are Wend
I need a query when user provide me with 2 date:-
Condition 1:
--
Start :- 2nd March 8.30am
End :- 4th March 11.00am
The result for total time off should be:- 195 mins
2nd March - 15+60+15, 3rd March - 15+60+15, 4th March (wend) - 15
Condition 2:
--
Start :- 2nd March 8.30am
End :- 2nd March 5.00pm
The result for total time off should be:- 90 mins
2nd March - 15+60+15
Anyone help ?
Thank You,
mfwooWhere are your dates stored in your tables?
Posting the full DDL may help.
http://www.aspfaq.com/etiquette.asp?id=5006
"Woo Mun Foong" <mfwoo@.yahoo.com> wrote in message
news:B108A0E8-4084-435C-9C6E-815AD808C4AB@.microsoft.com...
> Hi,
> I need a query that can return the total time off between 2 dates.
> I have a table call tblTimeOff which has the following fields
> StartTimeOff, Interval (minute), Wend
> Sample data for tblTimeOff:-
> 10.00AM, 15, 0 - Timeoff period 10.00am to 10.15am on Wday
> 12.00pm, 60, 0 - Timeoff period 12.00pm to 1.00pm on Wday
> 15.00pm,15, 0 - - Timeoff period 3.00pm to 3.15pm on Wday
> 10.30AM, 15, 1 - Timeoff period 10.30am to 10.45am on Wend
> 12.30pm, 60, 1 - Timeoff period 12.30pm to 1.30pm on Wend
> Saturday and Sunday are considered as Wend.
> Mon - Fri are Wend
> I need a query when user provide me with 2 date:-
> Condition 1:
> --
> Start :- 2nd March 8.30am
> End :- 4th March 11.00am
> The result for total time off should be:- 195 mins
> 2nd March - 15+60+15, 3rd March - 15+60+15, 4th March (wend) - 15
> Condition 2:
> --
> Start :- 2nd March 8.30am
> End :- 2nd March 5.00pm
> The result for total time off should be:- 90 mins
> 2nd March - 15+60+15
> Anyone help ?
> Thank You,
> mfwoo
>sql

Calculate duration whilst excluding non working time

HI

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATETABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOTNULL,

[worktodt] [datetime] NOTNULL

Sample data:

WorkFromDt WorkToDt

02/01/2007 08:00:00 02/01/2007 18:00:00
03/01/2007 08:00:00 03/01/2007 18:00:00
04/01/2007 08:00:00 04/01/2007 18:00:00
05/01/2007 08:00:00 05/01/2007 18:00:00
06/01/2007 06/01/2007
07/01/2007 07/01/2007

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

AS

BEGIN

RETURN
(
SELECT CAST((SUM(DATEDIFF(MINUTE,workfromdt,worktodt)) -
DATEDIFF(MINUTE,MIN(workfromdt),@.fromdt) -
DATEDIFF(MINUTE,@.todt,MAX(worktodt))) AS DECIMAL(9,2))
AS working_hours
FROM ihlpWorkingHours
WHERE NOT (@.fromdt >= worktodt OR @.todt <= workfromdt)
HAVING MIN(workfromdt) <= @.fromdt AND MAX(worktodt) >= @.todt
)
END

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

This works fine when a call has been opened or closed within working hours M-F but for calls that have been opened or closed outside of these times (after 18:00 and before 08:00) M-F or Weekends the function returns a null value for the call duration.

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

I think this will give you the logic you need. The nested query builds a set of all to-from ranges valid for the query. It corrects the from and to dates when a full date hasn't expired. It then calculates the minutes in the ranges and sums them.

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

If you are using the code in a function, the function must return some value. Declare a variable of an appropriate type, assign the results of the SELECT statement to that variable, and then return the variable.

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan

sql

Calculate duration whilst excluding non working time

HI

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATE TABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOT NULL ,

[worktodt] [datetime] NOT NULL

Sample data:

WorkFromDt WorkToDt

02/01/2007 08:00:00 02/01/2007 18:00:00
03/01/2007 08:00:00 03/01/2007 18:00:00
04/01/2007 08:00:00 04/01/2007 18:00:00
05/01/2007 08:00:00 05/01/2007 18:00:00
06/01/2007 06/01/2007
07/01/2007 07/01/2007

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

AS

BEGIN

RETURN
(
SELECT CAST((SUM(DATEDIFF(MINUTE,workfromdt,worktodt)) -
DATEDIFF(MINUTE,MIN(workfromdt),@.fromdt) -
DATEDIFF(MINUTE,@.todt,MAX(worktodt))) AS DECIMAL(9,2))
AS working_hours
FROM ihlpWorkingHours
WHERE NOT (@.fromdt >= worktodt OR @.todt <= workfromdt)
HAVING MIN(workfromdt) <= @.fromdt AND MAX(worktodt) >= @.todt
)
END

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

This works fine when a call has been opened or closed within working hours M-F but for calls that have been opened or closed outside of these times (after 18:00 and before 08:00) M-F or Weekends the function returns a null value for the call duration.

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

I think this will give you the logic you need. The nested query builds a set of all to-from ranges valid for the query. It corrects the from and to dates when a full date hasn't expired. It then calculates the minutes in the ranges and sums them.

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

If you are using the code in a function, the function must return some value. Declare a variable of an appropriate type, assign the results of the SELECT statement to that variable, and then return the variable.

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan

Caching UDF

Hi All,

I have an application that reads data from a very slow database link
(like 10 seconds per call) though what I am looking for would be of
generic use for anyone who has long-running queries that are
frequently repeated.

I would like to be able to cache the results of a query so that I do
not have to re-execute that query if it is reissued. Ideally I
believe that this could be implemented by hiding the query inside a
UDF and exposing the UDF through a view. The UDF could then "Check
the cache" and only run the slow query if there wasn't a match (or if
the match was too old). From what I understand the best way to do
this would be for the cache to be an extended stored procedure.

Has anyone done or seen this? Has someone written a copy that I
could purchase? Does anyone care to offer their opinnion of how or if
this could work?

Thanks in Advance,

StevenHi

Caching like this is usually the function of a middle tier rather than the
database.

John

"Steven Ensslen" <ensslen@.planet-save.com> wrote in message
news:73ce0e91.0405141350.716061eb@.posting.google.c om...
> Hi All,
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.
> Has anyone done or seen this? Has someone written a copy that I
> could purchase? Does anyone care to offer their opinnion of how or if
> this could work?
> Thanks in Advance,
> Steven|||[posted and mailed, please reply in news]

Steven Ensslen (ensslen@.planet-save.com) writes:
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.

Unless I am misunderstanding something, this won't fly at all. The UDF
and the extended stored procedure still executes on the server, so there
is no cache you could retrieve data from. SQL Server maintains a cache, but
that is from disk to local memory, so from your point of view, this is
still on the remote side of your link.

For such a cache to be meaningful, you must have it on your side of the
link. Thus, the typical place to fix this would be in the application
itself (unless there is a separate middle tier between the application
and the database).

If this is an application you cannot modify, you might still be able to
do it, but it will be hairy. In this case you would point your application
to a local SQL Server, which use linked servers to access the remote
server, and this local server would implement a cache. But how you would
load the cache and keep int current is far from trivial. To develop this,
I wold need some more information to proceed.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the replies, but I guess that I haven't explained my idea
clearly enough.

> Unless I am misunderstanding something, this won't fly at all. The UDF
> and the extended stored procedure still executes on the server, so there
> is no cache you could retrieve data from. SQL Server maintains a cache, but
> that is from disk to local memory, so from your point of view, this is
> still on the remote side of your link.
> For such a cache to be meaningful, you must have it on your side of the
> link. Thus, the typical place to fix this would be in the application
> itself (unless there is a separate middle tier between the application
> and the database).

I'm looking for a custom-coded,programmer-activated, server-side
cache. I want to be able to store an arbitrary string so that it
persists for my entire database session and I do not have to execute
the expensive query that generated that string more than once.

> If this is an application you cannot modify, you might still be able to
> do it, but it will be hairy. In this case you would point your application
> to a local SQL Server, which use linked servers to access the remote
> server, and this local server would implement a cache. But how you would
> load the cache and keep int current is far from trivial. To develop this,
> I wold need some more information to proceed.

You're correct that I can't modify the application. So I'd like the
local server to implement a cache of the remote server.

Has anyone done this? Does anyone have an example or know of a 3rd
party program/extension that will perform this function?

Steven|||Steven Ensslen (ensslen@.planet-save.com) writes:
> I'm looking for a custom-coded,programmer-activated, server-side
> cache. I want to be able to store an arbitrary string so that it
> persists for my entire database session and I do not have to execute
> the expensive query that generated that string more than once.

I'm afraid that I don't really follow. Can you give an overview the
architecture of the application as it works now? I mean which boxes
you have, and where the slow link is.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

c0000005 EXCEPTION_ACCESS_VIOLATION

I have searched this newsgroup and Google. It seems that with this error I
should just call PSS? The SQL (see error.log attached) runs fine from Query
Analyzer, but not from my application (other queries run fine though). It
worked up until yesterday. I rebooted the server. I had an associate apply
SP4, but it still throws this exception. I'm having my partner run Dell
diags on it during lunch. The server is in California, I'm in Tennessee.
I also get this Events in the Event Log when the query is fired:
Event 17052, SOURCE: MSSQLSERVER
Error: 0, Severity: 19, State: 0
language_exec: Process 57 generated an access violation. SQL Server is
terminating this process.
AND
Event 17052, SOURCE: MSSQLSERVER
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
begin 666 error.log
M4W%L1'5M<$5X8V5P=&EO;DAA;F1L97(Z(%!R;V-E<W,@.-3(@.9V5N97)A=&5D
M(&9A=&%L(&5X8V5P=&EO;B!C,# P,# P-2!%6$-%4%1)3TY?04-#15-37U9)
M3TQ!5$E/3BX@.#0I344P@.4V5R=F5R(&ES('1E<FUI;F%T:6YG('1H:7,@.<' )O
M8V5S<RXN#0HJ("HJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ* BHJ*BHJ*BHJ
M*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ* BHJ*BHJ*BH-
M"BH-"BH@.0D5'24X@.4U1!0TL@.1%5-4#H-"BH@.(" P.2\P-R\P-2 P.#HR-#HQ
M-"!S<&ED(#4R#0HJ#0HJ(" @.17AC97!T:6]N($%D9')E<W,@./2 P,#0T1#%%
M,PT**B @.($5X8V5P=&EO;B!#;V1E(" @.(#T@.8S P,# P,#4@.15A#15!424].
M7T%#0T534U]624],051)3TX-"BH@.("!!8V-E<W,@.5FEO;&%T:6]N(&]C8W5R
M<F5D(')E861I;F<@.861D<F5S<R P,S(Y,# P, T**B!);G!U="!"=69F97(@.
M,S<V(&)Y=&5S("T-"BH@.(%-%3$5#5"!#05-%(&QE;BAR97!L86-E*&QO8V%T
M:6]N+"=B9RTG+"<G*2D@.5TA%3B Q(%1(14X@.)T)'+3 G("L@.<F5P;&%C90T*
M*B @.*&QO8V%T:6]N+"=B9RTG+"<G*2!%3%-%(&QO8V%T:6]N($5.1"P@.<&%R
M=&YU;2P@.=F5N9&]R+"!C87-E;G5M($923TT@.8V5N#0HJ("!T<F%L+F1B;RYT
M8FQG;'-I;G9S;F%P(%=(15)%(&1I=CTG3$U))R!!3D0@.;&]C871I;VX@.3$E+
M12 G0D<E)R!!3D0@.0T].5D4-"BH@.(%)4*&YV87)C:&%R+'-N87!D871E+#$P
M,2D])S Y+S Q+S(P,#4G($]21$52($)9($-!4T4@.;&5N*')E<&QA8V4H;&]C
M871I;PT**B @.;BPG8F<M)RPG)RDI(%=(14X@.,2!42$5.("="1RTP)R K(')E
M<&QA8V4H;&]C871I;VXL)V)G+2<L)R<I($5,4T4@.;&]C871I#0HJ("!O;B!%
M3D0L('!A<G1N=6T@.#0HJ(" -"BH-"BH@.($U/1%5,12 @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.0D%312 @.(" @.($5.1" @.(" @.("!325I%#0HJ('-Q;'-E
M<G9R(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,#0P,# P," @.,#!#0D%&1D8@.
M(# P.&)B,# P#0HJ(&YT9&QL(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W
M0S@.P,# P," @.-T,X0D9&1D8@.(# P,&,P,# P#0HJ(&ME<FYE;#,R(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" W-T4T,# P," @.-S=&-#%&1D8@.(# P,3 R,# P
M#0HJ($%$5D%023,R(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W-T8U,# P," @.
M-S=&14)&1D8@.(# P,#EC,# P#0HJ(%)00U)4-" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" W-T,U,# P," @.-S=#145&1D8@.(# P,#EF,# P#0HJ($U35D-0
M-S$@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W0S-!,# P," @.-T,T,4%&1D8@.
M(# P,#=B,# P#0HJ($U35D-2-S$@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W
M0S,T,# P," @.-T,S.35&1D8@.(# P,#4V,# P#0HJ(&]P96YD<S8P(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" T,3 V,# P," @.-#$P-C5&1D8@.(# P,# V,# P
M#0HJ(%-(14Q,,S(@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W0SA$,# P," @.
M-T0P1#)&1D8@.(# P.# S,# P#0HJ($=$23,R(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" W-T,P,# P," @.-S=#-#=&1D8@.(# P,#0X,# P#0HJ(%5315(S
M,B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W-S,X,# P," @.-S<T,3%&1D8@.
M(# P,#DR,# P#0HJ(&US=F-R=" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W
M-T)!,# P," @.-S="1CE&1D8@.(# P,#5A,# P#0HJ(%-(3%=!4$D@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" W-T1!,# P," @.-S=$1C%&1D8@.(# P,#4R,# P
M#0HJ('-Q;'-O<G0@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" T,D%%,# P," @.
M-#)"-D9&1D8@.(# P,#DP,# P#0HJ('5M<R @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" T,3 W,# P," @.-#$P-T1&1D8@.(# P,#!E,# P#0HJ(&-O;6-T
M;#,R(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" W-S0R,# P," @.-S<U,C)&1D8@.
M(# P,3 S,# P#0HJ('-Q;&5V;C<P(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" T
M,3 X,# P," @.-#$P.$%&1D8@.(# P,#!B,# P#0HJ($Y%5$%023,R(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P,S(P,# P," @.,#,R-3=&1D8@.(# P,#4X,# P
M#0HJ($%55$A" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,S(V,# P," @.
M,#,R-S-&1D8@.(# P,#$T,# P#0HJ($-/35)%4R @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P,S8Q,# P," @.,#,V1#5&1D8@.(# P,&,V,# P#0HJ(&]L93,R
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,S9%,# P," @.,#,X,3-&1D8@.
M(# P,3,T,# P#0HJ(%A/3$5(3% @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M,SA!,# P," @.,#,X035&1D8@.(# P,# V,# P#0HJ($U31%1#4%)8(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P,SA",# P," @.,#,Y,C=&1D8@.(# P,#<X,# P
M#0HJ(&US=F-P-C @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,SDS,# P," @.
M,#,Y.3!&1D8@.(# P,#8Q,# P#0HJ($U46$-,52 @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P,SE!,# P," @.,#,Y0CA&1D8@.(# P,#$Y,# P#0HJ(%9%4E-)
M3TX@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,SE#,# P," @.,#,Y0S=&1D8@.
M(# P,# X,# P#0HJ(%=33T-+,S(@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M,SE$,# P," @.,#,Y1#A&1D8@.(# P,# Y,# P#0HJ(%=3,E\S,B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P,SE%,# P," @.,#,Y1C9&1D8@.(# P,#$W,# P
M#0HJ(%=3,DA%3% @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,T$P,# P," @.
M,#-!,#=&1D8@.(# P,# X,# P#0HJ($],14%55#,R(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P,T$Q,# P," @.,#-!.4)&1D8@.(# P,#AC,# P#0HJ($-,55-!
M4$D@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,T%%,# P," @.,#-!1C%&1D8@.
M(# P,#$R,# P#0HJ(%)%4U5424Q3(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M,T(P,# P," @.,#-",3)&1D8@.(# P,#$S,# P#0HJ(%5315)%3E8@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P,T(R,# P," @.,#-"13-&1D8@.(# P,&,T,# P
M#0HJ('-E8W5R,S(@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,T)&,# P," @.
M,#-#,#)&1D8@.(# P,#$S,# P#0HJ($EN=F%L:60@.061D<F5S<R @.(" @.(" @.
M(" @.(" @.(" P,T,R,# P," @.,#-#-C!&1D8@.(# P,#0Q,# P#0HJ($EN=F%L
M:60@.061D<F5S<R @.(" @.(" @.(" @.(" @.(" P,T,W,# P," @.,#-#.3A&1D8@.
M(# P,#(Y,# P#0HJ('=I;G)N<B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M,T-%,# P," @.,#-#139&1D8@.(# P,# W,# P#0HJ(%=,1$%0,S(@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P,T-&,# P," @.,#-$,41&1D8@.(# P,#)E,# P
M#0HJ(')A<V%D:&QP(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,T0T,# P," @.
M,#-$-#1&1D8@.(# P,# U,# P#0HJ($Y434%25$$@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P,$4R,# P," @.,#!%-#%&1D8@.(# P,#(R,# P#0HJ(%-!34Q)
M0B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P,$4U,# P," @.,#!%-45&1D8@.
M(# P,#!F,# P#0HJ(%-33D543$E"(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M,$4W,# P," @.,#!%.#5&1D8@.(# P,#$V,# P#0HJ('-E8W5R:71Y(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P,$4Y,# P," @.,#!%.3-&1D8@.(# P,# T,# P
M#0HJ(&AN971C9F<@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-#@.T,# P," @.
M,#0X.3A&1D8@.(# P,#4Y,# P#0HJ('=S:'1C<&EP(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-#A%,# P," @.,#0X13=&1D8@.(# P,# X,# P#0HJ(%-3;7-,
M4$-N(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-#DW,# P," @.,#0Y-S=&1D8@.
M(# P,# X,# P#0HJ(%-3;FU03C<P(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-#DX,# P," @.,#0Y.#9&1D8@.(# P,# W,# P#0HJ(&YT9'-A<&D@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-$$Q,# P," @.,#1!,C1&1D8@.(# P,#$U,# P
M#0HJ(&ME<F)E<F]S(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-3!#,# P," @.
M,#4Q,3=&1D8@.(# P,#4X,# P#0HJ(&-R>7!T9&QL(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-3$R,# P," @.,#4Q,D)&1D8@.(# P,#!C,# P#0HJ($U305-.
M,2 @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-3$S,# P," @.,#4Q-#%&1D8@.
M(# P,#$R,# P#0HJ(%-13$9445)9(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-$(T,# P," @.,#1"-C5&1D8@.(# P,#(V,# P#0HJ('AP<W R<F5S(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" Q,# P,# P," @.,3 R0S1&1D8@.(# P,F,U,# P
M#0HJ($-,0D-A=%$@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-$(W,# P," @.
M,#1"1C)&1D8@.(# P,#@.S,# P#0HJ('-Q;&]L961B(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-$,R,# P," @.,#1#03!&1D8@.(# P,#@.Q,# P#0HJ($U31$%2
M5" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-$-",# P," @.,#1#0SE&1D8@.
M(# P,#%A,# P#0HJ($U31$%43#,@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-$-$,# P," @.,#1#131&1D8@.(# P,#$U,# P#0HJ(&]L961B,S(@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-34V,# P," @.,#4U1#A&1D8@.(# P,#<Y,# P
M#0HJ($],141",S)2(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-35%,# P," @.
M,#4U1C!&1D8@.(# P,#$Q,# P#0HJ(')S865N:" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-38P,# P," @.,#4V,D5&1D8@.(# P,#)F,# P#0HJ(%!305!)
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-31$,# P," @.,#4T1$%&1D8@.
M(# P,#!B,# P#0HJ(&US=C%?," @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-38S,# P," @.,#4V-39&1D8@.(# P,#(W,# P#0HJ(&EP:&QP87!I(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-38V,# P," @.,#4V-SE&1D8@.(# P,#%A,# P
M#0HJ('AP<W1A<B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-38Y,# P," @.
M,#4V1$-&1D8@.(# P,#1D,# P#0HJ(%-13%)%4TQ$(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-39%,# P," @.,#4V14)&1D8@.(# P,#!C,# P#0HJ(%-13%-6
M0R @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-39&,# P," @.,#4W,$%&1D8@.
M(# P,#%B,# P#0HJ($]$0D,S,B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-3<Q,# P," @.,#4W-$-&1D8@.(# P,#-D,# P#0HJ($-/34-43#,R(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-3<U,# P," @.,#4W139&1D8@.(# P,#DW,# P
M#0HJ(&-O;61L9S,R(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-3=&,# P," @.
M,#4X,SE&1D8@.(# P,#1A,# P#0HJ(&]D8F-B8W @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-3@.T,# P," @.,#4X-#5&1D8@.(# P,# V,# P#0HJ(%<Y-5-#
M32 @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-3@.U,# P," @.,#4X-4-&1D8@.
M(# P,#!D,# P#0HJ(%-13%5.25),(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-3@.V,# P," @.,#4X.$-&1D8@.(# P,#)D,# P#0HJ(%=)3E-03T],(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-3@.Y,# P," @.,#4X0C9&1D8@.(# P,#(W,# P
M#0HJ(%-(1D],1$52(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-3A#,# P," @.
M,#4X0SA&1D8@.(# P,# Y,# P#0HJ(&]D8F-I;G0@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-4%!,# P," @.,#5!0C9&1D8@.(# P,#$W,# P#0HJ($Y$1$5!
M4$D@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-4)$,# P," @.,#5"1#9&1D8@.
M(# P,# W,# P#0HJ(%-13%-60R @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-4)%,# P," @.,#5"135&1D8@.(# P,# V,# P#0HJ('AP<W1A<B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-4)&,# P," @.,#5"1CA&1D8@.(# P,# Y,# P
M#0HJ($%#5$E61413(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-4,P,# P," @.
M,#5#,S)&1D8@.(# P,#,S,# P#0HJ(&%D<VQD<&,@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-4,T,# P," @.,#5#-C9&1D8@.(# P,#(W,# P#0HJ(&-R961U
M:2 @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-4,W,# P," @.,#5#.41&1D8@.
M(# P,#)E,# P#0HJ($%43" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-4-!,# P," @.,#5#0C=&1D8@.(# P,#$X,# P#0HJ(&%D<VQD<" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" P-40R,# P," @.,#5$-$1&1D8@.(# P,#)E,# P
M#0HJ(%-84R @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-45$,# P," @.
M,#5&.$)&1D8@.(# P,&)C,# P#0HJ('AP;&]G-S @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" P-48Y,# P," @.,#5&.45&1D8@.(# P,#!F,# P#0HJ('AP;&]G
M-S @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P-49!,# P," @.,#5&031&1D8@.
M(# P,# U,# P#0HJ(&1B9VAE;' @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" P
M-C!",# P," @.,#8Q049&1D8@.(# P,3 P,# P#0HJ#0HJ(" @.(" @.("!%9&DZ
M(# P,# P,# Q.B -"BH@.(" @.(" @.($5S:3H@.,#,R.3 S0T$Z( T**B @.(" @.
M(" @.16%X.B P,S(X1D9&13H@.#0HJ(" @.(" @.("!%8G@.Z(# U-35&,D,P.B P
M,# P,# P-B @.,# P,# P,#4@.(#0S-#8Q-S8P(" P,38R.$9#," @.-#)#-44Y
M138@.(#0S,C@.Y,D,X(" -"BH@.(" @.(" @.($5C>#H@.,#,R.$9&0T4Z(# P-D4P
M,#4U(" P,#<T,# V.2 @.,# W.# P-C4@.(# P-#<P,#(P(" P,#8Q,# V0R @.
M,# W,S P-S,@.( T**B @.(" @.(" @.161X.B P,# P,#%&13H@.#0HJ(" @.(" @.
M("!%:7 Z(# P-#1$,44S.B!&.3@.S,#@.X0B @.,3DX-C!&-T8@.(#A",# P-#DR
M("!&,#A"1D,T1" @.1D5$,48Q,D(@.(#@.T,$9$,C@.U(" -"BH@.(" @.(" @.($5B
M<#H@.,#4U-48R.#0Z(# U-35&-$%#(" P,#4S-C-!02 @.,# P,# P,#$@.(# U
M-35&,D$X(" P,# P,#%&12 @.,#4U-48U-#@.@.( T**B @.(" @.(%-E9T-S.B P
M,# P,# Q0CH@.#0HJ(" @.("!%1FQA9W,Z(# P,#$P,C@.S.B T1C P,# P," @.
M-3 P,#1$,# @.(#4T,# T,3 P(" S1# P-#@.P," @.,T$P,#0S,# @.(#4P,# U
M0S P(" -"BH@.(" @.(" @.($5S<#H@.,#4U-48R-S Z(# S,CA&1D-%(" P,# P
M,#1%-" @.-#,T-C@.P0S @.(# P,# P-$4T(" P,S(X1D9#12 @.,#4U-48T04,@.
M( T**B @.(" @.(%-E9U-S.B P,# P,# R,SH@.#0HJ("HJ*BHJ*BHJ*BHJ*BHJ
M*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ* BHJ*BHJ*BHJ
M*BHJ*BHJ*BHJ*BHJ*BHJ*BHJ*BH-"BH@.+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+0T**B!3:&]R="!3=&%C:R!$=6UP#0HJ(# P-#1$,44S
M($UO9'5L92AS<6QS97)V<BLP,# T1#%%,RD-"BH@.,# U,S8S04$@.36]D=6QE
M*'-Q;'-E<G9R*S P,3,V,T%!*0T**B P,#4S-C,R."!-;V1U;&4H<W%L<V5R
M=G(K,# Q,S8S,C@.I#0HJ(# P-#(Y1D$Y($UO9'5L92AS<6QS97)V<BLP,# R
M.49!.2D-"BH@.,# T,$,S03<@.36]D=6QE*'-Q;'-E<G9R*S P,#!#,T$W*0T*
M*B P,#0Q1#$W."!-;V1U;&4H<W%L<V5R=G(K,# P,40Q-S@.I#0HJ(# P-#(Y
M.30Q($UO9'5L92AS<6QS97)V<BLP,# R.3DT,2D-"BH@.,# T,CE%04$@.36]D
M=6QE*'-Q;'-E<G9R*S P,#(Y14%!*0T**B P,#0Q-40P-"!-;V1U;&4H<W%L
M<V5R=G(K,# P,35$,#0I#0HJ(# P-#$V,C$T($UO9'5L92AS<6QS97)V<BLP
M,# Q-C(Q-"D-"BH@.,# T,35&,C@.@.36]D=6QE*'-Q;'-E<G9R*S P,#$U1C(X
M*0T**B P,#0Y0S,R12!-;V1U;&4H<W%L<V5R=G(K,# P.4,S,D4I#0HJ(# P
M-#E#-#9!($UO9'5L92AS<6QS97)V<BLP,# Y0S0V02D-"BH@.-#$P-S4S,#D@.
M36]D=6QE*'5M<RLP,# P-3,P.2D@.*%!R;V-E<W-7;W)K4F5Q=65S=',K,# P
M,# R1#D@.3&EN92 T-38K,# P,# P,# I#0HJ(#0Q,#<T.3<X($UO9'5L92AU
M;7,K,# P,#0Y-S@.I("A4:')E8613=&%R=%)O=71I;F4K,# P,# P.3@.@.3&EN
M92 R-C,K,# P,# P,#<I#0HJ(#=#,S0Y-#!&($UO9'5L92A-4U9#4C<Q*S P
M,# Y-#!&*2 H96YD=&AR96%D*S P,# P,$%!*0T**B W-T4V-C V,R!-;V1U
M;&4H:V5R;F5L,S(K,# P,C8P-C,I("A'971-;V1U;&5&:6QE3F%M94$K,# P
M,# P14(I#0HJ("TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM+2TM
;+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
`
end
"Mike Smith" <msmith@.larrymethvin.com> wrote in message
news:OS7pVV9sFHA.3752@.TK2MSFTNGP09.phx.gbl...
> I have searched this newsgroup and Google. It seems that with this error I
> should just call PSS? The SQL (see error.log attached) runs fine from
Query
> Analyzer, but not from my application (other queries run fine though). It
> worked up until yesterday. I rebooted the server. I had an associate apply
> SP4, but it still throws this exception. I'm having my partner run Dell
> diags on it during lunch. The server is in California, I'm in Tennessee.
> I also get this Events in the Event Log when the query is fired:
> Event 17052, SOURCE: MSSQLSERVER
> Error: 0, Severity: 19, State: 0
> language_exec: Process 57 generated an access violation. SQL Server is
> terminating this process.
> AND
> Event 17052, SOURCE: MSSQLSERVER
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
Sorry, specs:
Dell PowerEdge, Windows 2003 SP1, SQL2000 SP4.
The only change I am aware of is SP4, previously (yesterday), it had SP3a.
Regards,
Mike Smith
msmith@.larrymethvin.com
|||"Mike Smith" <msmith@.larrymethvin.com> wrote in message
news:OS7pVV9sFHA.3752@.TK2MSFTNGP09.phx.gbl...
>I have searched this newsgroup and Google. It seems that with this error I
> should just call PSS?
Yes.
David
|||"Mike Smith" <msmith@.larrymethvin.com> wrote in message
news:OS7pVV9sFHA.3752@.TK2MSFTNGP09.phx.gbl...
> I have searched this newsgroup and Google. It seems that with this error I
> should just call PSS? The SQL (see error.log attached) runs fine from
Query
> Analyzer, but not from my application (other queries run fine though). It
> worked up until yesterday. I rebooted the server. I had an associate apply
> SP4, but it still throws this exception. I'm having my partner run Dell
> diags on it during lunch. The server is in California, I'm in Tennessee.
> I also get this Events in the Event Log when the query is fired:
> Event 17052, SOURCE: MSSQLSERVER
> Error: 0, Severity: 19, State: 0
> language_exec: Process 57 generated an access violation. SQL Server is
> terminating this process.
> AND
> Event 17052, SOURCE: MSSQLSERVER
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
For the sake of the list, and anyone else that may have this problem. I
checked my log files, they were HUGE! We had moved servers, and whoever
setup the backups didn't back up the logs. I shrank the logs using the
method in: http://support.microsoft.com/kb/272318

C# UDF project call C++ model (SQL Server 2005)?

I have some legacy C++ code and I am creating a C# project for UDF function
and another project for C++ classes. I always got error message when I am
trying to add reference to the class lib project:
A reference to 'classModel' could not be added. SQL Server projects can
reference only other SQL Server projects.
I tried to create the C++ project as SQL Server project too and the error
message is the same.examnotes <nick@.discussions.microsoft.com> wrote in
news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:

> I have some legacy C++ code and I am creating a C# project for UDF
> function and another project for C++ classes. I always got error
> message when I am trying to add reference to the class lib project:
> A reference to 'classModel' could not be added. SQL Server projects
> can reference only other SQL Server projects.
That is because the VS SQL Server Project doesn't allow you to reference
any other project types (or assemblies already defined in the database).
You can instead use my project type for this:
http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
I wonder if that is the real problem, see below.

> I tried to create the C++ project as SQL Server project too and the
> error message is the same.
OK, so is the C++ project managed code? If not you can not use it inside
SQL Server. In that case you have to either do COM interop against the
C++ classes, or P/Invoke.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||IC, thanks.
I avoid to create the UDF in C++(Managed) because not much example, support
information about C++ user defined function programming. And I am not famila
r
with managed C++ syntax.
"Niels Berglund" wrote:

> examnotes <nick@.discussions.microsoft.com> wrote in
> news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
>
> That is because the VS SQL Server Project doesn't allow you to reference
> any other project types (or assemblies already defined in the database).
> You can instead use my project type for this:
> http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
> 26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
> I wonder if that is the real problem, see below.
>
> OK, so is the C++ project managed code? If not you can not use it inside
> SQL Server. In that case you have to either do COM interop against the
> C++ classes, or P/Invoke.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb at develop dot com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

Bypass PDF open/save prompt

I have figured out how to call a report and pass parameters via a URL,
but I get the dreaded Open/Save prompt. Is there any way to bypass
this and just open the PDF file? Also, is it even possible to simply
print the report with the user's default printer when a button is
clicked?
I am using SSRS 2005 and SQL 2005 June CTP with Visual Studio 2005 Beta
2. My application is ASP.NET with VB
Thanks in advance.RS SP2 has print support from the report viewer. I think that the open/save
prompt is a security feature of the web browser. If you want to get the PDF
without the prompt, you can use the Web Service' Render method
--
Floyd
<chrishalldba@.yahoo.com> wrote in message
news:1123779652.686768.275080@.g49g2000cwa.googlegroups.com...
>I have figured out how to call a report and pass parameters via a URL,
> but I get the dreaded Open/Save prompt. Is there any way to bypass
> this and just open the PDF file? Also, is it even possible to simply
> print the report with the user's default printer when a button is
> clicked?
> I am using SSRS 2005 and SQL 2005 June CTP with Visual Studio 2005 Beta
> 2. My application is ASP.NET with VB
> Thanks in advance.
>|||Thanks for the fast reply! Do you know if I could use this render
method with SSRS 2005? I'm using the June CTP if that makes a
difference. Do you have any code examples of this?
Chris|||From what I've seen, SP2's print is a button on the report viewer web page.
When that button is clicked, an activex control is downloaded that does the
printing. I don't know what happens behind the scenes.
--
Floyd
"Chris" <chrishalldba@.yahoo.com> wrote in message
news:1123780980.603308.323720@.o13g2000cwo.googlegroups.com...
> Thanks for the fast reply! Do you know if I could use this render
> method with SSRS 2005? I'm using the June CTP if that makes a
> difference. Do you have any code examples of this?
> Chris
>|||You can compile and install the Server Side printing example that comes with
SQL 2005. A user can then subscribe to a report and have it printed to the
named printer.. THe printer must be installed on the server...
--
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
<chrishalldba@.yahoo.com> wrote in message
news:1123779652.686768.275080@.g49g2000cwa.googlegroups.com...
>I have figured out how to call a report and pass parameters via a URL,
> but I get the dreaded Open/Save prompt. Is there any way to bypass
> this and just open the PDF file? Also, is it even possible to simply
> print the report with the user's default printer when a button is
> clicked?
> I am using SSRS 2005 and SQL 2005 June CTP with Visual Studio 2005 Beta
> 2. My application is ASP.NET with VB
> Thanks in advance.
>

Bulkloading external xml file

Can I call an external url which requests a xml file when executing a
BulkLoad operation? I'm getting the error message:
Error opening the datafile. Code:80004005.
There is no problem with my database connection and I am able to access the
url from an internet browser.
Below is my code:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=db0681;uid=sa;pwd=as"
objBL.ErrorLogFile = "d:\xml\error.log"
objBL.Execute "AddressSchema.xml",
"http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw ord=pwd"
set objBL=Nothing
I do not believe that the execute method implements http resolution - you
will need to resolve and retrieve your data either into a stream or to a
local file and pass the stream or file reference to the execute method.
dlr
"Paulo" <paulo@.msdn.com> wrote in message
news:7FDD076C-0CA9-4FCD-A93B-45AF015D5917@.microsoft.com...
> Can I call an external url which requests a xml file when executing a
> BulkLoad operation? I'm getting the error message:
> Error opening the datafile. Code:80004005.
> There is no problem with my database connection and I am able to access
the
> url from an internet browser.
> Below is my code:
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=db0681;uid=sa;pwd=as"
> objBL.ErrorLogFile = "d:\xml\error.log"
> objBL.Execute "AddressSchema.xml",
>
"http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw o
rd=pwd"
> set objBL=Nothing
|||I do not think that you can use a URL. It should be a file name that is
reachable from the machine.
Best regards
Michael
"Paulo" <paulo@.msdn.com> wrote in message
news:7FDD076C-0CA9-4FCD-A93B-45AF015D5917@.microsoft.com...
> Can I call an external url which requests a xml file when executing a
> BulkLoad operation? I'm getting the error message:
> Error opening the datafile. Code:80004005.
> There is no problem with my database connection and I am able to access
> the
> url from an internet browser.
> Below is my code:
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=db0681;uid=sa;pwd=as"
> objBL.ErrorLogFile = "d:\xml\error.log"
> objBL.Execute "AddressSchema.xml",
> "http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw ord=pwd"
> set objBL=Nothing
|||Alright, but how can I retrieve it and save it to a local file? Would please
write a VBScript example?
"Dennis Redfield" wrote:

> I do not believe that the execute method implements http resolution - you
> will need to resolve and retrieve your data either into a stream or to a
> local file and pass the stream or file reference to the execute method.
> dlr
> "Paulo" <paulo@.msdn.com> wrote in message
> news:7FDD076C-0CA9-4FCD-A93B-45AF015D5917@.microsoft.com...
> the
> "http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw o
> rd=pwd"
>
>
|||I have nothing in script (although I remember seeing a internet sample using
classic ASP script to resolve and convert to stream - look on the msdn site,
there MAY be an example in the BOL for SQLXML ).
Pretty straight forward in C# to do so.
do some research.
dlr
"Paulo" <paulo@.msdn.com> wrote in message
news:01E99E0B-5CCC-4049-B715-405B139E21B4@.microsoft.com...
> Alright, but how can I retrieve it and save it to a local file? Would
please[vbcol=seagreen]
> write a VBScript example?
> "Dennis Redfield" wrote:
you[vbcol=seagreen]
access[vbcol=seagreen]
"http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw o[vbcol=seagreen]