PRB: Single Query to build database and schema.
Please help, I'm quite frustrated with a problem.
I want to issue a SINGLE query to completely build my database (IF IT
DOESN'T ALREADY EXIST), "use" it, build an entire schema of tables, stored
procedures, triggers, and even populate tables with data.
Envision a typical ASP/ADO using program calling the query as such:
========================================
====
<%@. Language=JavaScript %>
<%
var Connection_Temp;
var ResultSet_Temp;
var csMY_QRY, csOPTION, csMY_DB_NAME;
csMY_QRY = "~~~~~~~ What ever ~~~~~~~~";
csMY_QRY = csMY_QRY.replace("<%=DB%>", csMY_DB_NAME);
csMY_QRY = csMY_QRY.replace("<%=OPTION%>", csOPTION);
.
.
.
Connection_Temp = Server.CreateObject("ADODB.Connection");
Connection_Temp.ConnectionTimeout = 45;
Connection_Temp.Open("DRIVER={SQL
Server};SERVER=My_SVR;UID=MY_UID;PWD=MY_
PWD");
ResultSet_Temp = Connection_Temp.Execute(csMY_QRY);
%>
<html>
</html>
========================================
====
There are several "issues" which are making this very difficult to solve:
1) "CREATE DATABASE" and "USE" do not accept variables.
2) "USE" can not be used in a stored procedure or trigger.
3) sp_executesql only accepts NVARCHAR, NTEXT.
4) NVARCAHR can only be declared up to 4000 bytes.
5) NTEXT data types can only be declared as a parameter, not with "DECLARE".
6) NTEXT defined as parameters can not be assigned with SELECT statements.
7) A typical schema will surely require more than 4000 characters to be
represented
as a string.
8) Even tables with NTEXT can not be fetched into a NTEXT variable such as
this:
create table TEST(JUNK ntext, NAME nvarchar(50) primary key)
insert into TEST(NAME, JUNK) values('ME', 'JUNK')
create procedure TRY(ntext @.csTRY) as
begin
select @.csTRY = JUNK from TEST where NAME = 'ME' -- This fails...
-- Can't assign the NTEXT.
end
9) "GO" can not be used in a stored procedure"
10) "GO" can not be used in a single query passed to ADO as in the above
example.
Put another way, imagine setting csMY_QRY as such:
csMY_QRY = "go \r\n exec sp_help \r\n go exec sp_help";
This will not work in the sample above. SQL Server will say
[Microsoft][ODBC SQL Server Driver][SQL Server]The object 'go' does not
exist in database 'master'.
11) Building the schema MUST be done with transaction handling, so that if
one "CREATE TABLE" fails, they all roll back.
12) THIS HAS TO BE A QUERY.
I am convinced that this can be done. It may require a LOT of sp_executesql
calls, but I feel it can be done.
Any ideas and/or help would be much appreciated.
If nothing else, I would very much like for Microsoft to publish the final
outcome of this PRB as a full blown MSDN article to help everyone. I've
noticed here in the discussion groups that many people are trying to
accomplish this, but are typically getting hung on "CREATE DATABASE" or "USE
"
limitations.I have an update already.
I believe that this WHOLE PRB can be solved if we can do one of the followin
g:
Use some sort of SP to select from a temporary table that has an NTEXT, to
fetch a list of queries, where each query is then executed by something like
sp_executesql, where if ANY failure occurs, the caller will know it, and the
n
be able to roll that query back, and any previous query.
The key is to somehow have an NTEXT variable to get the query, and then pass
it to a sp_executesql as such:
~~~~ somehow DECALRE ~~~~~ @.QRY ntext
select * from QRY_TABLE_WITH_NTEXT_QUERIES
~~~ CURSOR: for each row returned, select into @.QRY ~~~
exec sp_executesql @.QRY
~~~ fetch next - do loop|||Yet another update.
I'm almost there.
The NTEXT is the KEY issue.
If ONLY I could programmatically create and populate an NTEXT variable, WITH
data from a "select * from TABLE_WITH_NTEXT_COLUMN", then I'm sure this PRB
would be solved.
Here is a sample QRY I have made so far. It fails on the FETCH NEXT, where
the error is "[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot fetch
into text, ntext, and image variables. "
===============================
begin transaction
set XACT_ABORT on
set NOCOUNT on
declare @.csQRY nvarchar(4000)
declare @.CrLf nvarchar(5)
declare @.QQ nvarchar(5)
select @.CrLf = char(13) + char(10)
select @.QQ = char(39) + char(39)
select @.csQRY = 'create table QTs([NAME] nvarchar(50), [QRY] ntext)'
exec sp_executesql @.csQRY
select @.csQRY = 'insert into QTs([NAME], [QRY]) values(' + char(39) + 'QRY'
+ char(39) + ', ' + char(39) + 'create database MY_DB' + char(39) + ')'
exec sp_executesql @.csQRY
select @.csQRY = 'insert into QTs([NAME], [QRY]) values(' + char(39) + 'QRY'
+ char(39) + ', ' + char(39) + 'create table MY_DB.dbo.[FUN]([NAME]
nvarchar(50))' + char(39) + ')'
exec sp_executesql @.csQRY
select @.csQRY =
'create procedure DO_QRY(@.QRY ntext = ' + @.QQ + ') as' + @.CrLf +
'begin' + @.CrLf +
' begin transaction' + @.CrLf +
' declare TRY_CURSOR cursor for select [QRY] from QTs' + @.CrLf +
' open TRY_CURSOR' + @.CrLf +
' fetch next from TRY_CURSOR into @.QRY' + @.CrLf +
' while @.@.FETCH_STATUS = 0' + @.CrLf +
' begin' + @.CrLf +
' exec sp_executesql @.QRY' + @.CrLf +
' if (@.@.ERROR <> 0)' + @.CrLf +
' begin' + @.CrLf +
' rollback transaction' + @.CrLf +
' return' + @.CrLf +
' end' + @.CrLf +
' fetch next from TRY_CURSOR into @.QRY' + @.CrLf +
' end' + @.CrLf +
' commit transaction' + @.CrLf +
'end' + @.CrLf
exec sp_executesql @.csQRY
select @.csQRY = 'exec DO_QRY'
exec sp_executesql @.csQRY
commit transaction|||Back to being dead in the water.
I replaced the NTEXT with NVARCHAR(3000), and tried the same login. It
failed on the create database. Now what?
begin transaction
set XACT_ABORT on
set NOCOUNT on
declare @.csQRY nvarchar(4000)
declare @.CrLf nvarchar(5)
declare @.QQ nvarchar(5)
select @.CrLf = char(13) + char(10)
select @.QQ = char(39) + char(39)
select @.csQRY = 'create table QTs([NAME] nvarchar(50), [QRY] nvarchar(3900))'
exec sp_executesql @.csQRY
select @.csQRY = 'insert into QTs([NAME], [QRY]) values(' + char(39) + 'QRY'
+ char(39) + ', ' + char(39) + 'create database MY_DB' + char(39) + ')'
exec sp_executesql @.csQRY
select @.csQRY = 'insert into QTs([NAME], [QRY]) values(' + char(39) + 'QRY'
+ char(39) + ', ' + char(39) + 'create table MY_DB.dbo.[FUN]([NAME]
nvarchar(50))' + char(39) + ')'
exec sp_executesql @.csQRY
select @.csQRY =
'create procedure DO_QRY as' + @.CrLf +
'begin' + @.CrLf +
' begin transaction' + @.CrLf +
' declare @.QRY nvarchar(4000)' + @.CrLf +
' declare TRY_CURSOR cursor for select [QRY] from QTs' + @.CrLf +
' open TRY_CURSOR' + @.CrLf +
' fetch next from TRY_CURSOR into @.QRY' + @.CrLf +
' while @.@.FETCH_STATUS = 0' + @.CrLf +
' begin' + @.CrLf +
' exec sp_executesql @.QRY' + @.CrLf +
' if (@.@.ERROR <> 0)' + @.CrLf +
' begin' + @.CrLf +
' rollback transaction' + @.CrLf +
' return' + @.CrLf +
' end' + @.CrLf +
' fetch next from TRY_CURSOR into @.QRY' + @.CrLf +
' end' + @.CrLf +
' commit transaction' + @.CrLf +
'end' + @.CrLf
exec sp_executesql @.csQRY
select @.csQRY = 'exec DO_QRY'
exec sp_executesql @.csQRY
commit transaction
=================================
In QA, the return is as such:
==================================
Server: Msg 266, Level 16, State 2, Procedure DO_QRY, Line 14
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
Server: Msg 266, Level 16, State 1, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
Server: Msg 226, Level 16, State 1, Line 1
CREATE DATABASE statement not allowed within multi-statement transaction.
Server: Msg 3902, Level 16, State 1, Line 38
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
===============================
Any ideas?|||SOLVED!!!!!!
The CREATE DATABASE can be issued. I just had to commit any TX that may have
existed before the CREATE DATABASE for it to work. Then I could resume
another "BEGIN TRANSACTION" and continue on. I also needed to use a
WHILE-LOOP to be sure to commit all transactions that may be created by the
various CREATE TABLEs I would execute. But I did get it to work. Final sampl
e
below:
============ This should be published into MSDN, no fooling!
=======================================
begin transaction
set XACT_ABORT on
set NOCOUNT on
declare @.csQRY nvarchar(4000)
declare @.CrLf nvarchar(5)
declare @.QQ nvarchar(5)
select @.CrLf = char(13) + char(10)
select @.QQ = char(39) + char(39)
select @.csQRY = 'create database OptiDoc_3X'
commit transaction
exec sp_executesql @.csQRY
begin transaction
select @.csQRY = 'create table QTs([NAME] nvarchar(50), [QRY] nvarchar(3900))'
exec sp_executesql @.csQRY
--select @.csQRY = 'insert into QTs([NAME], [QRY]) values(' + char(39) +
'QRY' + char(39) + ', ' + char(39) + 'create database MY_DB' + char(39) + ')
'
select @.csQRY = 'insert into QTs([NAME], [QRY]) values(' + char(39) + 'QRY'
+ char(39) + ', ' + char(39) + 'create table MY_DB..MY_TBL([NAME]
nvarchar(50))' + char(39) + ')'
exec sp_executesql @.csQRY
begin transaction
select @.csQRY =
'create procedure DO_QRY as' + @.CrLf +
'begin' + @.CrLf +
' begin transaction' + @.CrLf +
' declare @.QRY nvarchar(4000)' + @.CrLf +
' declare TRY_CURSOR cursor for select [QRY] from QTs' + @.CrLf +
' open TRY_CURSOR' + @.CrLf +
' fetch next from TRY_CURSOR into @.QRY' + @.CrLf +
' while @.@.FETCH_STATUS = 0' + @.CrLf +
' begin' + @.CrLf +
' select @.QRY' + @.CrLf +
' exec sp_executesql @.QRY' + @.CrLf +
' if (@.@.ERROR <> 0)' + @.CrLf +
' begin' + @.CrLf +
' select ' + @.QQ + '-- Error!!!' + @.QQ + @.CrLf +
' rollback transaction' + @.CrLf +
' return' + @.CrLf +
' end' + @.CrLf +
' fetch next from TRY_CURSOR into @.QRY' + @.CrLf +
' end' + @.CrLf +
' close TRY_CURSOR' + @.CrLf +
' deallocate TRY_CURSOR' + @.CrLf +
' commit transaction' + @.CrLf +
'end' + @.CrLf
exec sp_executesql @.csQRY
select @.csQRY = 'exec DO_QRY'
exec sp_executesql @.csQRY
select @.csQRY = 'drop procedure DO_QRY'
exec sp_executesql @.csQRY
select @.csQRY = 'drop table QTs'
exec sp_executesql @.csQRY
while @.@.TRANCOUNT > 0
begin
commit transaction
end
by software or humans.
DarrenSQLIS wrote: