Can I hide data fields in a Chart?

I have a Bar/Line Chart with two data fields. Field #1 is displayed as a bar,
Field #2 a line. I would like to allow the user to choose via a parameter if
the line is displayed.
I tried the following expression for the Field:
=iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
But rather than hiding the line, it makes all values zero with the line
still showing.
Is there some way to hide the line?
Thanks.You may want to try:
=iif(Parameters!ShowTrend.Value=True, Fields!Trend.Value, Nothing)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bill" <bill@.discussions.microsoft.com> wrote in message
news:745AF66D-CC9D-4D91-845C-FD4227C08E59@.microsoft.com...
> I have a Bar/Line Chart with two data fields. Field #1 is displayed as a
bar,
> Field #2 a line. I would like to allow the user to choose via a parameter
if
> the line is displayed.
> I tried the following expression for the Field:
> =iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
> But rather than hiding the line, it makes all values zero with the line
> still showing.
> Is there some way to hide the line?
> Thanks.
>|||Show/hide is not supported in charts.
But why don't you try a dynamic series grouping and filter those series
groupings that you don't want show (the Grouping&Sorting dialog for every
chart grouping has a filter tab where you can define expressions to filter
data). This should also take care of the chart legend issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bill" <bill@.discussions.microsoft.com> wrote in message
news:6B4516F9-D415-4522-838F-BCBAF0789F30@.microsoft.com...
> Thanks for the reply, but "Nothing" gives me the same results as "False"
...
> the line shows-up on bottom with zero values.
> I can get the effect of hiding (sort of) by setting color to transparent.
> But the data series still displays in the legend box.
> Is there no way to dynamically let the user specify what data elements
they
> want to show/hide on a chart?
> Bill
> "Robert Bruckner [MSFT]" wrote:
> > You may want to try:
> > =iif(Parameters!ShowTrend.Value=True, Fields!Trend.Value, Nothing)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "bill" <bill@.discussions.microsoft.com> wrote in message
> > news:745AF66D-CC9D-4D91-845C-FD4227C08E59@.microsoft.com...
> > > I have a Bar/Line Chart with two data fields. Field #1 is displayed as
a
> > bar,
> > > Field #2 a line. I would like to allow the user to choose via a
parameter
> > if
> > > the line is displayed.
> > >
> > > I tried the following expression for the Field:
> > >
> > > =iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
> > >
> > > But rather than hiding the line, it makes all values zero with the
line
> > > still showing.
> > >
> > > Is there some way to hide the line?
> > >
> > > Thanks.
> > >
> > >
> >
> >
> >

Can I deploy a runtime version

Is is possible to deploy MSDE with a database in such a way that an end user
can only access that database via a custom application? In other words, I
don't want the end user to be able to use osql (or any other tool) to get
into the database, and I don't want them to be able to detach that database
and attach it to a full blown SQL Server.
Thanks.
No, it is not possible. If the user has full right (Admin right) to his
computer, he can do anything to SQL Server/MSDE on this computer:
attaching/detaching it, peeking into the database structure. The most you
can do is to encrypt the data content, if you can find a reliable tool to do
that.
"Tom Iarusso" <TomIarusso@.discussions.microsoft.com> wrote in message
news:E2A8F5F9-A707-4985-8E00-C419353C82D9@.microsoft.com...
> Is is possible to deploy MSDE with a database in such a way that an end
> user
> can only access that database via a custom application? In other words, I
> don't want the end user to be able to use osql (or any other tool) to get
> into the database, and I don't want them to be able to detach that
> database
> and attach it to a full blown SQL Server.
> Thanks.

Can I copy a DTS Package?

The scenario:
9 db tables populated by 9 Excel Import Files via DTS.
Will I need to create a DTS package for each import? Columns are identical in all 9 - the only thing different is the destination table name and source file name.

I've had to map over 80 columns using DTS and don't want to do it for each instance!

Any help would be appreciated..1. Rightclick the DTS-package in Enterprise Manager
2. Choose "design package"
3. Make the changes you want
4. Go to menuitem "Package" and choose "Save as"
5. You now have a copy of your DTS-package|||Nice one! Cheers.|||This is not the best way -
Dude - create a table in the destination db called tblFileSource that has:
ID, SourceFile, DestinationTable, importDate

Define FileSource and DestinationTable as Global Variables in the package.
Then, the first step of you package use a Execute SQL task that will set you global Varaiables to the result of

select top 1 SourceFile, DestinationTable
from tblFileSource
where importDate is null


Then, use a Dynamic Properties Task to change the source and destination in the Data Transformation Step.

Then, after the Transformation, do another Execute SQL Task (on Success):


Update tblFileSource
Set importDate = getDate()
where SourceFile = ?

Where the ? is the global Variable FileSource.

Isn't this a more professional method - comes in handy when the number of files increases.

Can I connect to SQL Server via PPPoE?

I am a fresh guy for networking and here is a question seeking help.
If SQL Server was located in internet and i use a PPPoE (ADSL) line to
connect to internet, can I connect to SQL Server via PPPoE?
Thanks a lot.
Hi
Yes. As long as you use the IP address, your routing is correct and you have
the correct ports open. The default port for SQL Server is 1433.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevegao2003@.163.com> wrote in message
news:1135330289.992162.176130@.g43g2000cwa.googlegr oups.com...
> I am a fresh guy for networking and here is a question seeking help.
> If SQL Server was located in internet and i use a PPPoE (ADSL) line to
> connect to internet, can I connect to SQL Server via PPPoE?
> Thanks a lot.
>
|||Thanks.
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> Yes. As long as you use the IP address, your routing is correct and you have
> the correct ports open. The default port for SQL Server is 1433.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> <stevegao2003@.163.com> wrote in message
> news:1135330289.992162.176130@.g43g2000cwa.googlegr oups.com...

Can I connect to SQL Server via PPPoE?

I am a fresh guy for networking and here is a question seeking help.
If SQL Server was located in internet and i use a PPPoE (ADSL) line to
connect to internet, can I connect to SQL Server via PPPoE?
Thanks a lot.Hi
Yes. As long as you use the IP address, your routing is correct and you have
the correct ports open. The default port for SQL Server is 1433.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevegao2003@.163.com> wrote in message
news:1135330289.992162.176130@.g43g2000cwa.googlegroups.com...
> I am a fresh guy for networking and here is a question seeking help.
> If SQL Server was located in internet and i use a PPPoE (ADSL) line to
> connect to internet, can I connect to SQL Server via PPPoE?
> Thanks a lot.
>|||Thanks.
Mike Epprecht (SQL MVP) wrote:
> Hi
> Yes. As long as you use the IP address, your routing is correct and you have
> the correct ports open. The default port for SQL Server is 1433.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> <stevegao2003@.163.com> wrote in message
> news:1135330289.992162.176130@.g43g2000cwa.googlegroups.com...
> >
> > I am a fresh guy for networking and here is a question seeking help.
> >
> > If SQL Server was located in internet and i use a PPPoE (ADSL) line to
> > connect to internet, can I connect to SQL Server via PPPoE?
> >
> > Thanks a lot.
> >

Can I connect to SQL Server via PPPoE?

I am a fresh guy for networking and here is a question seeking help.
If SQL Server was located in internet and i use a PPPoE (ADSL) line to
connect to internet, can I connect to SQL Server via PPPoE?
Thanks a lot.Hi
Yes. As long as you use the IP address, your routing is correct and you have
the correct ports open. The default port for SQL Server is 1433.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<stevegao2003@.163.com> wrote in message
news:1135330289.992162.176130@.g43g2000cwa.googlegroups.com...
> I am a fresh guy for networking and here is a question seeking help.
> If SQL Server was located in internet and i use a PPPoE (ADSL) line to
> connect to internet, can I connect to SQL Server via PPPoE?
> Thanks a lot.
>|||Thanks.
Mike Epprecht (SQL MVP) wrote:[vbcol=seagreen]
> Hi
> Yes. As long as you use the IP address, your routing is correct and you ha
ve
> the correct ports open. The default port for SQL Server is 1433.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> <stevegao2003@.163.com> wrote in message
> news:1135330289.992162.176130@.g43g2000cwa.googlegroups.com...

Can I assign rowguid from the client code instead of using default newid()?

Do I have to use default newid() for the rowguid? I would prefer to pass a
sequential GUID from the client code instead.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1
Or can I at least use newsequentialid() instead of newid() as the ROWGUID
default?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1
|||Alex,
Yes,
CREATE TABLE dbo.Table1
(
test uniqueidentifier NOT NULL ROWGUIDCOL
) ON [PRIMARY]
ALTER TABLE dbo.Table1 ADD CONSTRAINT
DF_Table1_test DEFAULT (newid()) FOR test
-- Bill
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:6bd9f60658969@.uwe...
> Or can I at least use newsequentialid() instead of newid() as the ROWGUID
> default?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1
>
|||Thank you for your response. It seems your code is still using newid(), but
anyway it is not what I was asking. The MS documentation says that one can
create replication ROWGUID column manually as long as it have the following
characteristics:
* The data type as UNIQUEIDENTIFIER.
* The default as NEWID().
* The ROWGUIDCOL property.
* A unique index on the column.
My questions are:
1. Can I use newsequentialid() instead of newid() ?
2. Can I supply GUID from my client code instead of using this default value?
I am interested in transactional replication model with updating subscribers.
AlterEgo wrote:[vbcol=seagreen]
>Alex,
>Yes,
>CREATE TABLE dbo.Table1
> (
> test uniqueidentifier NOT NULL ROWGUIDCOL
> ) ON [PRIMARY]
>ALTER TABLE dbo.Table1 ADD CONSTRAINT
> DF_Table1_test DEFAULT (newid()) FOR test
>-- Bill
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1

Can I access SQL Server 2005 via a Window XP pro workgroup network?

Hi,

I am new to SQL Server and I am trying to install SQL Server 2005 Express or Stnadard Edition on a computer runing XP pro on a XP's workgroup network using a router. Can I access the the database from another computer running XP pro on the same network? If I can, what setting do I need when installing SQL 2005 in order to do this?

Any help is appricated.

Make only sure that you enable remote connections, see the Screencast for this on my site for a detailed explanation.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Can I access SQL Server 2000 tables via SSIS/SQL Server 2005

Can I access SQL Server 2000 tables via SSIS/SQL Server 2005?Yes, by using OLE DB or ADO.NET connections.

Can connect to sqlexpress via asp.net but not via asp

I can connect to a sqlexpress database using aspx (asp.net 2.0) pages
but not via classic asp pages. Both the asp and aspx pages are within
the same website. The sqlexpress database is also on the same server.
I have tried using a variety of connection strings within my asp pages,
including dsn, dsn-less, windows authentication, sql authentication
etc. (The sql express database by the way has mixed authenatication and
both work fine)
The errors I am getting are the following:
1. When I put a windows authentication string in my asp
page I get this error:
************
Provider error '80040e21'
Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done.
?, line 0
***************
2. When I use a sql authenticaion string, I get this
error:
********************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
?, line 0
**********************
3. When I use a DSN connection string (with the DSN
connecting successfully within the ODBC utility) I get
this error:
******************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database "xxxx" requested by the login. The login
failed.
?, line 0
***********************
As mentioned before I have no problems in connecting to
the database via aspx pages within the same website. I can also
successfully connect to the database via visual web developer or sql
express manager on the same machine.
Any ideas?
Sorry, solved this!
|||You took the time to post that you found an answer, could you possibly post the solution?
~Another Steve

Quote:

Originally posted by Steve
Sorry, solved this!

Can connect to sqlexpress via asp.net but not via asp

I can connect to a sqlexpress database using aspx (asp.net 2.0) pages
but not via classic asp pages. Both the asp and aspx pages are within
the same website. The sqlexpress database is also on the same server.
I have tried using a variety of connection strings within my asp pages,
including dsn, dsn-less, windows authentication, sql authentication
etc. (The sql express database by the way has mixed authenatication and
both work fine)
The errors I am getting are the following:
1. When I put a windows authentication string in my asp
page I get this error:
************
Provider error '80040e21'
Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done.
?, line 0
***************
2. When I use a sql authenticaion string, I get this
error:
********************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
?, line 0
**********************
3. When I use a DSN connection string (with the DSN
connecting successfully within the ODBC utility) I get
this error:
******************
Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database "xxxx" requested by the login. The login
failed.
?, line 0
***********************
As mentioned before I have no problems in connecting to
the database via aspx pages within the same website. I can also
successfully connect to the database via visual web developer or sql
express manager on the same machine.
Any ideas?Sorry, solved this!|||You took the time to post that you found an answer, could you possibly
post the solution?
~Another Steve
Steve wrote:
> *Sorry, solved this! *
larmores
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1889178.html

Can connect in VB but not VBA!

Hello,
We are experiencing a frustrating problem where we can connect to an interna
l
SQL Server via any application EXCEPT Office apps (Excel, Word, etc.) using
VBA. The error is:
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or a
ccess
denied.
We're using very elementary code, that succeeds in VB programs, for example,
but fails in Excel VBA. The code is:
on error resume next
Dim conn As ADODB.Connection
Dim strw As String
Set conn = New ADODB.Connection
strw = "Provider=sqloledb;User ID=the_user;Password=the_password;" & _
"Data Source=192.168.0.8;Initial Catalog=the_catalog;Network
Library=DBMSSOCN;"
' errors out here
conn.Open strw
If Err.Number = 0 Then
MsgBox "seems alright."
Else
MsgBox "error: " & Err.Description
End If
conn.close
set conn = nothing
We're stumped. We've never heard of any ADODB VB code, that works perfectly
fine in a VB application, that suddenly stops working when ported to Excel
VBA. We've tried specifying the port, changing the Network Library, etc., as
well...all sorts of things.
Help!I would guess there is still some difference some way in how
you are connecting in VB apps and how you are connecting in
VBA - especially if you always connect fine from VB to the
same server from the same PC and using the exact same code
in VBA to the same server from the same PC does not work.
And of course this would imply that the VB app and the VBA
app have the exact same references in the exact same order.
It's doesn't sound like it's really a SQL Server issue but
you could investigate the possibilities listed in this
article:
Potential causes of the "SQL Server does not exist or access
denied" error message
http://support.microsoft.com/?id=328306
-Sue
On Thu, 07 Sep 2006 05:30:26 GMT, "brianUE" <u26284@.uwe>
wrote:

>Hello,
>We are experiencing a frustrating problem where we can connect to an intern
al
>SQL Server via any application EXCEPT Office apps (Excel, Word, etc.) using
>VBA. The error is:
> [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist
or access
>denied.
>We're using very elementary code, that succeeds in VB programs, for example
,
>but fails in Excel VBA. The code is:
> on error resume next
> Dim conn As ADODB.Connection
> Dim strw As String
> Set conn = New ADODB.Connection
> strw = "Provider=sqloledb;User ID=the_user;Password=the_password;" & _
> "Data Source=192.168.0.8;Initial Catalog=the_catalog;Network
>Library=DBMSSOCN;"
> ' errors out here
> conn.Open strw
> If Err.Number = 0 Then
> MsgBox "seems alright."
> Else
> MsgBox "error: " & Err.Description
> End If
> conn.close
> set conn = nothing
> We're stumped. We've never heard of any ADODB VB code, that works perfect
ly
>fine in a VB application, that suddenly stops working when ported to Excel
>VBA. We've tried specifying the port, changing the Network Library, etc., a
s
>well...all sorts of things.
>Help!

Can backups be done via stored procedures?

That's my question.
Thanks,
Tom
To add on to Tibor, This procedure will Backup Master, MSDB and all the User
databases. This script will create the unique Backup
files names, this will ensure that old backup sets were not overwritten.
Script
CREATE PROCEDURE BACKUP_SP AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT @.NAME='C:\backup\'+@.DBNAME+'_'+ltrim (rtrim (convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute:
EXEC BACKUP_SP
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
"Tom Glasser" <TomGlasser@.discussions.microsoft.com> wrote in message
news:4125EFFF-9C29-491D-87F0-BD29969F0EF9@.microsoft.com...
> That's my question.
> Thanks,
> Tom

Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a method
generates the error listed below. Please note that a straight disco call does work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFac tory.CoreGetHandler(Type type, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFac tory.GetHandler(HttpContext context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpCont ext context, String requestType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.Http Application+IExecutionStep.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean& completedSynchronously) +173
</pre></code>
There are a couple different ways to do this in SQL Server 2005 but there
aren't ant really good ways to do this in SQL Server 2000. Several people
have used external com objects called through the sp_OA* stored procedures
but that's not a very scaleable solution. I would suggest writing the
information you want to add to AD into a table and then writing an
application that occasionally reads the table and does the AD update. AD
updates are somewhat asynchronous anyway so the delay is probably tolerable.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Garth Wells" <nobody@.ishome.com> wrote in message
news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> The client wants to update Active Directory from a Sproc via a Web
> Service.
> I found an example in Google Archives, but it won't work...the call to a
> method
> generates the error listed below. Please note that a straight disco call
> does work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFac tory.CoreGetHandler(Type
> type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFac tory.GetHandler(HttpContext
> context, String verb, String url, String filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpCont ext context, String
> requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.Http Application+IExecutionStep.Execute()
> +95
> System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean&
> completedSynchronously) +173
> </pre></code>
>
|||Adding the following to the web.config file fixed the problem..
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> The client wants to update Active Directory from a Sproc via a Web Service.
> I found an example in Google Archives, but it won't work...the call to a method
> generates the error listed below. Please note that a straight disco call does work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFac tory.CoreGetHandler(Type type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFac tory.GetHandler(HttpContext context, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpCont ext context, String requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.Http Application+IExecutionStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionS tep step, Boolean& completedSynchronously) +173
> </pre></code>
>

Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a met
hod
generates the error listed below. Please note that a straight disco call doe
s work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type t
ype, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContex
t context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpContext context, String reques
tType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep
.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& com
pletedSynchronously) +173
</pre></code>Adding the following to the web.config file fixed the problem...
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:uXC2hXTYFHA.4024@.TK2MSFTNGP10.phx.g
bl...
> The client wants to update Active Directory from a Sproc via a Web Service
.
> I found an example in Google Archives, but it won't work...the call to a m
ethod
> generates the error listed below. Please note that a straight disco call d
oes work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(T
ype type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContext c
ontext, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String r
equestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutio
nStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
; completedSynchronously) +173
> </pre></code>
>
>

Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a met
hod
generates the error listed below. Please note that a straight disco call doe
s work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type t
ype, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContex
t context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpContext context, String reques
tType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep
.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& com
pletedSynchronously) +173
</pre></code>There are a couple different ways to do this in SQL Server 2005 but there
aren't ant really good ways to do this in SQL Server 2000. Several people
have used external com objects called through the sp_OA* stored procedures
but that's not a very scaleable solution. I would suggest writing the
information you want to add to AD into a table and then writing an
application that occasionally reads the table and does the AD update. AD
updates are somewhat asynchronous anyway so the delay is probably tolerable.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Garth Wells" <nobody@.ishome.com> wrote in message
news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> The client wants to update Active Directory from a Sproc via a Web
> Service.
> I found an example in Google Archives, but it won't work...the call to a
> method
> generates the error listed below. Please note that a straight disco call
> does work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + '
> description:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type
> type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpCont
ext
> context, String verb, String url, String filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String
> requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionSt
ep.Execute()
> +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously) +173
> </pre></code>
>|||Adding the following to the web.config file fixed the problem..
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.g
bl...
> The client wants to update Active Directory from a Sproc via a Web Service
.
> I found an example in Google Archives, but it won't work...the call to a m
ethod
> generates the error listed below. Please note that a straight disco call d
oes work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(T
ype type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContext c
ontext, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String r
equestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutio
nStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
; completedSynchronously) +173
> </pre></code>
>

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

You cannot use SET or EXEC with a UDF. You need to use

SELECT @.ReturnVar = [dbo].fnYourFunctionName ( @.InputVar )

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||

I beleive this post belongs in the SS Integration Services Forum.

Alle

|||

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.

HTH
Alle

|||

Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.

The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.

Thanks

Neil

|||Moved to the SQL Server integration services forum :).|||

Did you resolve this? I'm having the same prob. too!

Cheers,

Tamim.

|||

Tamin,

The origianl poster seems to be providing a worng sintax to call the function...

Can you provide the syntax your are using and the error generated?

Calling user-defined functions in OLE DB Command transformation

Hi

We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.

The following was inserted for the SQLCommand property:

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

However, when the Refresh button is pressed we are presented with the error below:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".

If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)) the following error is produced:

Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Any assistance would be greatly appreciated.

Thanks

Neil

I apologize if I missed something obvious, but can you tell me where in the product you're using this? Is it in a dgen plan?|||

I beleive this post belongs in the SS Integration Services Forum.

Alle

|||

EXEC ? = dbo.GetOrderlineStatus(@.dt_required = ?, @.dt_invoice = ?, @.dt_despatch = ?, @.ch_status = ?, @.si_suffix = ?, @.re_quantity = ?, @.vc_invoice_id = ?, @.vc_order_id = ?)

Even though you are in the wrong forum, I think the first parameter in red could be your problem. Check the assignment in the Input / Output properties of the Ole DB Command component. The names of the Inputs and Outputs must be specific "Param_0", "Param_1", etc.. Check SSIS help on the subject.

HTH
Alle

|||

Correct, this is an Integration Services question - I posted to the wrong forum, apologies for that.

The Input and Output Properties tab does not yet display any Inputs or Outputs, as the error prevents the creation of the parameters. As soon as the statement is entered into the SqlCommand box and the Refresh button pressed, the error is produced with no parameters created, hence preventing further progress.

Thanks

Neil

|||Moved to the SQL Server integration services forum :).|||

Did you resolve this? I'm having the same prob. too!

Cheers,

Tamim.

|||

Tamin,

The origianl poster seems to be providing a worng sintax to call the function...

Can you provide the syntax your are using and the error generated?

calling subscription via the web service

I'd like to force a subscription to run in an adhoc fashion using the web
service provided. Is this possible? If not, does anyone know a work around
to make this happen?Create the subscription as a TimedSubscription with a schedule that fires
once in the past. When you want the schedule to fire, call FireEvent
passing in TimedSubscription and the id of the subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Stewart" <ChrisStewart@.discussions.microsoft.com> wrote in message
news:D8586F2B-EC5B-421B-A167-9A312FCFB8E5@.microsoft.com...
> I'd like to force a subscription to run in an adhoc fashion using the web
> service provided. Is this possible? If not, does anyone know a work
> around
> to make this happen?