call an ASP page using xp_cmdShell

Hi, here is what I'm trying to accomplish. I'm using Win2k, IIS, SQL Server 2000 sp3a. I'm trying to call an ASP page from the SQL environment. The ASP page updates a record in a database.

Code in Query Analyzer:
exec master..xp_cmdshell 'c:\temp\test.vbs'

If I double-click the vbs file from Windows Explorer, the vbs file calls the asp file and the database updates. When I run the code in SQL I get a message telling me 1 row was affected, yet no record was written to the DB. I've read an article telling me it was possible to call an ASP page from SQL. http://www.eggheadcafe.com/articles/20010426.asp . But it does not work for me?

Code for VBS and ASP files, I don't think the problem is here though?

VBS:
Set WshShell = WScript.CreateObject("WScript.Shell")
URL = "http://localhost/spg/sprocData.asp"
WshShell.Run(URL)
set WshShell = Nothing

ASP:
dim objConn1,objCmd1, strSQL, adCmdText
adCmdText = 1
strSQL = "INSERT INTO tblXMLInsert (SEQ, VENDORID) VALUES ('test','test')"

set objConn1 = Server.CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Server=SERVER;Database=SPG;UID=t est;PWD=test"
set objCmd1 = Server.CreateObject("ADODB.Command")
set objCmd1.ActiveConnection = objConn1
objCmd1.CommandText = strSQL
objCmd1.CommandType = adCmdText
objCmd1.Execute
set objConn1 = Nothing
set objCmd1 = Nothing

Thanks for your help.Sorry dude....

I have to say this...Why the heck are you making life hard for yourself?

Web stuff stays on the web server
SQL stuff stays on the SQL Server.

xp_cmdshell is a VERY DANGEROUS utility - it usually operates at sa level and unless you want to trash your SQL server - dont use it. Just dont!

It may be the permissions of the user you are using to access SQL server from the seb server. Make sure the test user has change permissions on the table you are trying to alter. Start SQL Analyser & connect to the database using the test user. If you manually run the TSQL code in SQL Analyser on the server & it works, then its not permissions issue for the user.
It may be your vbs code.

Post back if problems.

Cheers,

SG|||hahahaha... thats right, dont mix...

I never cant do that... If you can... tell me how :)|||Let me explain what I'm trying to accomplish, it sounds like there is a better way to do this. I have an insert/update trigger that will use the xp_cmdshell command. I call the ASP page with 1 variable. The ASP page queries the database for a bunch of records, based on the variable I receive from the trigger. The returned recordset is parsed and converted to XML then sent to an external server. The ASP page receives the status of the file transfer and I update our database accordingly. The code posted above obviously does not show all these steps. Thanks|||Hi

CAN I BE MORE BLUNT???

DONT USE XP_CMDSHELL FOR THIS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ASP is designed to interact with a database, and send/retrieve information and I'm guessing you are still learning how to do this. Best method of attack is not to short circuit security ( and any good DBA will tell you this ) by not uising xp_cmdshell ( A good DBA will also "break" your fingers for using xp_cmdshell so be warned ). Not all you read on development boards is correct nor security minded.

Get all your development in ASP correct from the ground up & with solid engineering approach. You will be better off in the long run AND if someone managed to exploit this xp_cmdshell link, they could easily compromise your site security - ( www.sqlsecurity.com ) .

Fancy a pink slip for this effort? It would be worth doing to get rid of someone who KNOWINGLY compromised my site security.

SQL 2000 will return records in XML natively so you can do everything within TSQL and pass the trigger result back to an ASP page easily enough. Work it out correctly - dont make nasty short cuts.

HTH

Cheers,

SG.|||Hi, sqlguy7777. Your last statement says:
SQL 2000 will return records in XML natively so you can do everything within TSQL and pass the trigger result back to an ASP page easily enough. Work it out correctly - dont make nasty short cuts.

Creating XML in TSQL is no problem, my problem is passing the trigger results back to the ASP page. How do I do that, if xp_cmdShell is not the way to go what is the better way. Thanks|||Howdy,

I think you may have to change the approach to the problem & how you are handling the changes to the data & the database.

I was thinking through the problem, and it occurs to me you are making life difficult for yourself.

Instead of messing around with vbs scripts & triggers shooting info backwards & forwards, keep all the database functionality within the database. You could write a trigger result to a table, then scan the table frequently via a script to look for a change and then kick an action off. If you are only scanning a small table it would take milliseconds for a scan so unless your server is being flogged into the ground, speed should not be an issue. ( assuming you are not doing this 1000s of times a second, in which case using a RAM based pinned table would be much quicker. )

Keep it simple & it stays robust/reliable. As soon as it gets messy, the chance of things going wrong goes throught he roof. I speak from hard learned experience. Of course, you can totally dismiss what I'm saying ( which is you choice of course ) but I am trying to save you some grief.

I'd also suggest you talk to your DBA - quite often they can help with design issues and it makes long term support of systems easier & cheaper & faster.

HTH,

Cheers,

SG.

Call ALTER LOGIN

I am trying to create a stored procedure to Call ALTER LOGIN based on the the username passed in. However, the Alter login statement chokes on any parameter. Is there a way I can alter sql logins from a web form ?

I try the following and it bombs

ALTER

LOGIN @.LoginNameWITH PASSWORD= @.Password

But this works

ALTER LOGIN 'TestUser' WITH PASSWORD = '123test'

I guess the alter login statement does not work with Parameters.

Any thoughts ?

EXEC('ALTER LOGIN ' + @.loginName + ' WITH PASSWORD= ' + @.Passrowd)

|||

I get an error when I set the password. Should I add double quotes to the password ?

|||

yes you are right. You need to escape the quotes..Sorry about that.

EXEC('ALTER LOGIN ' + @.loginName + ' WITH PASSWORD= ''' + @.Passrowd + '''')

|||Perfect Thanks!

Call AddRow() after all ProcessInputRow() are called in a Script Component

Hi Guys,

I am new to SSIS. Heree is the transformation I need to do. In database one, I have tables:

A

B

C

Where B is a detailed table of A, and C is a "derived" table of B. There is a one to one relationship between B and C, but there may be more than one record in B for each record in A.

In database two, we have the table structure:

AA

CC

And there is a one to one relationship between AA and CC. And I need to design a transformation to migrate data from database one to database two.

Table A(->AA) and B(->BB) will be easy, just one to one migration.

The mapping rule for table C(->CC) they decided was: I need to concate each record in B and C and for a record in CC. For example, suppose we have:

A1

B1(C1)

B2(C2)

A2

B3(C3)

B4(C4)

B5(C5)

Then we will have the following records in database two:

A_1

(B1+C1+B2+C2)

A2

(B3+C3 + B4+C4 + B5+C5)

I looked through all the stock data flow components, and it seems to me that none of them can perform this task, so I am thinking to design a Script transform component to do the task.

I have written the script:

in each ProcessInputRow() sub, I check record's foreign key to A, and if they are the same I concate the records, then I put them into a VB.NET collection. Once all the records are processed, in the PostExecute() function, I count the number of new rows, (in the above example 2), then I call AddRow to add the rows, by:

OutputBuffer.AddRow()

But this does not work, I got "Object reference not set to an instance of an object". It seems that in PostExecute, the OutputBuffer is not longer valid?

Help please! :)

Is there a better way of doing what I am trying to do?

Thanks!

Wenbiao

Wenbiao wrote:

But this does not work, I got "Object reference not set to an instance of an object". It seems that in PostExecute, the OutputBuffer is not longer valid?

That's right, the output buffer is no longer valid in PostExecute().

It IS available in CreateNewOutputRows() so you can create your output rows in there.

-Jamie

Call a Web Server or external program inside of report

How can I call a web service and/or an external program (not a
referenced assembly) from inside of Report Services 2005? Can I do
both? Thank you for your help.On Jan 31, 6:11 am, ieg...@.gmail.com wrote:
> How can I call a web service and/or an external program (not a
> referenced assembly) from inside of Report Services 2005? Can I do
> both? Thank you for your help.
I would advise building an assembly to call the web service/external
program and then referencing this assembly.

Call a WCF service from SQL CLR sproc

It is possible to call WCF from SQL CLR, can I add a web reference or use the proxy created with svcutil ?hi check out this link

http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6050.aspx

Call a WCF service from SQL CLR sproc

It is possible to call WCF from SQL CLR, can I add a web reference or use the proxy created with svcutil ?hi check out this link

http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6050.aspx

call a UDF from another server <> Authentication

Hello

I'm trying to call a UDF from another server with the following command:

select i fromopenquery([10.0.10.240],'[survey].[dbo].[ufnGetAxis_Ana] as i')

error:

[OLE/DB provider returned message: Invalid authorization specification]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Msg 7399, Level 16, State 1, Line 3

OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.

Can someone give me some advice on how to pass username and password to access an object on another server?

Many thanks!

hi, first thing is it the SQL Server or other one?

if it is SQL Server, then it quit easy, you need to create a Linked Server(Direct IP Address will not work) using

sp_addlinkedserver [ @.server= ] 'server' [ , [ @.srvproduct= ] 'product_name' ] [ , [ @.provider= ] 'provider_name' ] [ , [ @.datasrc= ] 'data_source' ] [ , [ @.location= ] 'location' ] [ , [ @.provstr= ] 'provider_string' ] [ , [ @.catalog= ] 'catalog' ] then use the openquery asOPENQUERY ( linked_server ,'query' )

linked_server

Is an identifier representing the name of the linked server.

'query'

Is the query string executed in the linked server. The maximum length of the string is 8 KB

now try

select i fromopenquery(<linked server name>,'[survey].[dbo].[ufnGetAxis_Ana] as i')

Regards,

Thanks.

Gurpreet S. Gill

|||

Many thanks Gurpreet

Are you sure about the syntax?
I get a syntax error when executing select i from openquery(myserver,'survey.dbo.ufnGetAxis_Ana(2,7) as i')

Many thanks!

Worf

|||

hi try this

select i FROMopenquery(ha9,'select pubs.dbo.myFunction() as i')

this is the command, where 'ha9' is linked server , at the remote end 'pubs' is the database name, 'dbo' is owner and 'myFunction' is the name of the function, in your case it should be

select i from openquery(myserver,'select survey.dbo.ufnGetAxis_Ana(2,7) as i')

NOTE: if your Remote Serever is SQL Server, then , name of the server is the Linked server name, also need to set the login & password.

Regards,

Thanks.

Gurpreet S. Gill

|||

It works Gurpreet!!

Many many thanks!!!

Worf