How to run TSQL from vb net 2003

Hi All
I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
In vb6 I called a function...
retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
which ran the TSQL script against OSQL. The script basically created the
Database in MSDE then imports a heap of tables from Access 2000.
Private Function ExecCmd(cmdline$)
'used for MSDE to launch osql
Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret&
' Initialize the STARTUPINFO structure:
start.cb = Len(start)
' Start the shelled application:
ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
'Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
Call GetExitCodeProcess(proc.hProcess, ret&)
Call CloseHandle(proc.hThread)
Call CloseHandle(proc.hProcess)
ExecCmd = ret&
End Function
What is the preferred method of running a TSQL script from VB Net 2003 using
sqlclient class?
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am upgrading a VB6 program to VB net 2003 which uses MSDE 2000 RelA
> In vb6 I called a function...
> retval = ExecCmd("osql -E -i """ & path & "\sql\CreateTramcars.sql""")
> which ran the TSQL script against OSQL. The script basically created
> the Database in MSDE then imports a heap of tables from Access 2000.
> Private Function ExecCmd(cmdline$)
> 'used for MSDE to launch osql
> Dim proc As PROCESS_INFORMATION
> Dim start As STARTUPINFO
> Dim ret&
> ' Initialize the STARTUPINFO structure:
> start.cb = Len(start)
> ' Start the shelled application:
> ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
> NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
> 'Wait for the shelled application to finish:
> ret& = WaitForSingleObject(proc.hProcess, INFINITE)
> Call GetExitCodeProcess(proc.hProcess, ret&)
> Call CloseHandle(proc.hThread)
> Call CloseHandle(proc.hProcess)
> ExecCmd = ret&
> End Function
> What is the preferred method of running a TSQL script from VB Net
> 2003 using sqlclient class?
http://www.absistemi.it/permalink=tn169.ashx
this will use the very same feature with .Net..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to return the result of an EXEC from a function

Hi,

I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.

This is my function:

ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@.TypeID int,
@.CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1
BEGIN
SET @.RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END

FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND P.CaseID = ' + CAST(@.CaseID as varchar)
END
ELSE
BEGIN
SET @.RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND CaseID = ' + CAST(@.CaseID as varchar)
END

RETURN @.RTN

END

Each time I try " RETURN EXEC(@.RTN) " or something similar I get an error.

I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.

Anyone with any ideas?

You need to give a look to the CREATE FUNCTION article in books online. There are a number of constraints on the DML that can be used inside of a function. In this case you are being stopped by the constraint that does not allow use of the EXEC ( @.anSQLString ) inside of a function. You are also not allowed to execute stored procedures from inside the body of a function. I would suggest that if you want to run an EXEC ( @.someKindOfString ) that you really ought to use a stored procedure instead of a function. Since the problem is dynamic SQL you might want to first see if you can eliminate the dynamic SQL.

Kent

|||

Following on from Kent, in looking at your query do you actually need to build a dynamic string at all?

Code Snippet

CREATE FUNCTION [dbo].[ReturnPickItemValue]

(

-- Add the parameters for the function here

@.TypeID int,

@.CaseID int

)

RETURNS varchar(max)

AS

BEGIN

-- Declare the return variable here

DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1

BEGIN

SET @.Rtn = (SELECT I.PickItem +

CASE D.IsStartDateEstimated

WHEN 0 THEN CAST(StartDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)

END +

CASE D.IsEndDateEstimated

WHEN 0 THEN CAST(EndDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)

END

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID

LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID

WHERE L.PickTypeID = @.TypeID

AND P.CaseID = @.CaseID)

END

ELSE

BEGIN

SET @.Rtn = ( SELECT I.PickItem

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID

WHERE L.PickTypeID = @.TypeID

AND CaseID = @.CaseID)

END

RETURN @.Rtn

END

HTH!

|||cheers richbrownesq

That works a treat!!

How to retrieve values from one db to another db

Hi
Could someone please give me the correct TSQL statement, for the below
"pseudo".
USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
given that tbl_1 and tbl_2 is identical.
Any hints appreciated
Regrds.
Mr. SmithAssuming both tables are owned by dbo:
INSERT INTO db_a.dbo.tbl_1
SELECT * FROM db_b.dbo.tbl_2
Jacco Schalkwijk
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Hi,
No need of USE Database command here. you could use:-
Insert into database1.tableowner.tablename select * from
database2.tableowner.tablename
Thanks
Hari
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||INSERT INTO DB1.tbl_1
SELECT * from DB2.tbl_2
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Thanks all of you! Hari, Jacco and Jens for a quick and easy answer.
Regards
Mr. Smith
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Sorry, forgot the owner
INSERT INTO DB1.dbo.tbl_1
SELECT * from DB2.dbo.tbl_2
Jens Smeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:ur9bJ6ARFHA.3288@.TK2MSFTNGP14.phx.gbl...
> INSERT INTO DB1.tbl_1
> SELECT * from DB2.tbl_2
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
> news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
Try using OpenRowSet.
User db_a
INSERT INTO tbl('col1','col2')
SELECT a.col1,a.col2
FROM OPENROWSET('SQLOLEDB','servername';'user
id';'password','SELECT
col1,col2 FROM db_b.dbo.tbl_2') a
Hope this helps.
Regards,
Sambath
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>

How to retrieve name of current database in TSQL query

How can I get the name of the current database within a TSQL query (SQL
2000)? There doesn't seem to be an @.@.variable for it.Use the DB_NAME() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uDJEBSofGHA.2188@.TK2MSFTNGP04.phx.gbl...
> How can I get the name of the current database within a TSQL query (SQL
> 2000)? There doesn't seem to be an @.@.variable for it.
>|||SELECT DB_NAME()
Denis the SQL Menace
http://sqlservercode.blogspot.com/

How to retrieve name of current database in TSQL query

How can I get the name of the current database within a TSQL query (SQL
2000)? There doesn't seem to be an @.@.variable for it.Use the DB_NAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uDJEBSofGHA.2188@.TK2MSFTNGP04.phx.gbl...
> How can I get the name of the current database within a TSQL query (SQL
> 2000)? There doesn't seem to be an @.@.variable for it.
>|||SELECT DB_NAME()
Denis the SQL Menace
http://sqlservercode.blogspot.com/

How to retrieve name of current database in TSQL query

How can I get the name of the current database within a TSQL query (SQL
2000)? There doesn't seem to be an @.@.variable for it.Use the DB_NAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uDJEBSofGHA.2188@.TK2MSFTNGP04.phx.gbl...
> How can I get the name of the current database within a TSQL query (SQL
> 2000)? There doesn't seem to be an @.@.variable for it.
>|||SELECT DB_NAME()
Denis the SQL Menace
http://sqlservercode.blogspot.com/