RE: Transactions in SQl Server

[kaihirst]

Hi,

going to be changing a conente management system that used dynamic queries to transactions within its structure, and would like to ask the following.

When using the ADO commandset object in Visual Basic, whats best to retrieve these SQl fiels that contain the transction, file or virtual?

file

CNNcommandSet = "H:\CMS\QUERIES\T-SQLstoredtrans.sql"

Or

CNNcommandSet = Server.MapPath("//Queries/T-SQLstoredProc.sql")

Any ideas or does anybody have a better way of doing this?

Ta

Kai

[szlamany]

I have never used a commandset - what's in the .SQL file?

Do you need to "record" the results of that .SQL file being executed?

[kaihirst]

Hi.

I dont currently need to record the results of the SQL command, but the window of development is always an open one, and the topic of this, did com into play at some point, and as a reputable developer, i like my clieents to have some scope for further progress and deveopment when they deal with me, so i may come of this, then again it way not, but recording the statement may be sueful in future.

Yes you do need a recordset, to which I do have one to retrieve and and display the results.

whats in the SQL file? SEQUAL of course!!! No really, here is one of the queries. admitedly, you dont have to use a trannsaction, but there is a method behind this reasoning.

BEGIN TRANSACTION WeddingCardShop
SELECT DISTINCT ProductName AS Product, 
Product_Catalogue.ProductHeader AS Header, 
Product_Catalogue.ProductSubText AS SubHeader, 
Product_Catalogue.Price AS PriceTag,
Product_Catalogue.Tax AS VAT, 
Product_Catalogue.Department AS DisplayDept,
Product_Images.Image_1 AS ProductImage,
Product_Images.ALT AS ImageName
FROM Product_Catalogue INNER JOIN Product_Images 
ON Product_Catalogue.productID = Product_Images.productID
WHERE Product_Catalogue.Shop = 'wedding'
AND Product_Catalogue.Department = 'Cards'
COMMIT TRANSACTION

[szlamany]

Interesting...

So basically you have these queries written into .SQL text files...

You want to execute them.

I cannot find COMMANDSET as a valid ADO object or method. What is CNNCommandSet?

[kaihirst]

Hi,

CNNcommandSet is this

[Highlight=VB]

set CNNcommandSet = Server.CreateObject("ADODB.Command")

[Highlight=VB]

ta

KAi

[szlamany]

I have never seen assigning the "command" object to a text file.

I see in my ADO book stuff about COMMANDSTREAM and ASP pages - but it doesn't look like what you are doing.

Does this work?

Help me understand your question better.

[kaihirst]

Hi,

duno whether thsi works or not.. thats what I want to find out and explore prelimenary.

i would have thought so as you generally assign this attribute

CNNCommandSet.CommandText = STRING

of the ADODB.Command object is a string containing SQL, and therefore assigning a file shoudl work within the same context, but nto sure.

Any thoughts?

kai

[szlamany]

kai - I do not think so...

I think you would have to load the SQL from the file into a string and then put it into the CommandText.

And at that point I'm not sure it's going to like having multiple commands in the string (I've never done that so I question if it's possible).

[techgnome]

Look up the SQLDMO object.... If I remember right, there's a method in the Database object that will allow you to load and run SQL scripts from a file.

-tg