PRB: Win2k3srv/SQL2kSP3 --OLE DB-->Ora92

I have a problem connecting to Oracle from SQL2000sp3/linkedservers
on Win2003srv using any OLE DB provider.
[QUOTE]
Machines configuration
=======================
Machine: IVAN (Development workstation)
Intel PC P4/1.5, 512MB
WinXP Pro SP1
SQL2000 SP3, Office XP Pro
ADO 2.71.9030, ODBC 3.520.9030/9041
Oracle Provider for OLE DB 9.2.0.4.0
Oracle ODBC Driver 9.2.0.5.4
Oracle Services for MSTSC 9.2.0.4.0
Machine: GRSK03 (Development server)
Siemens P2/400 Server, 512MB
Win2003 Server Std - RRAS + Kerio WinRoute
SQL2000 SP3, Office XP Pro
ADO 2.80.1022, ODBC 3.525.1022
Oracle Provider for OLE DB 9.2.0.4.0
Oracle ODBC Driver 9.2.0.5.4
Oracle Services for MSTSC 9.2.0.4.0
Machine: GRSK02 (Production server)
Dell Server P3/1.3, 1GB
Win2000 Server Std SP4 - DC
Oracle Database 9.2.0.1.0
Network scheme
==============
|--LAN 1--| |--LAN
2--|
dsl-gw__ws1__ws2
isdn-gw/rras/fw___switch_______dc/db__pc1/20
IVAN <--/--ISDN--/--> GRSK03 <--100BaseT--> GRSK02
Oracle client configuration
===========================
TNSNAMES.ORA & SQLNET.ORA are same on both IVAN and GRSK03
# SQLNET.ORA Network Configuration File:
C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
# TNSNAMES.ORA Network Configuration File:
C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = grsk02.grskoviceva.local)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
SQL2000 Linked server configuration
===================================
On both IVAN and GRSK03
EXEC sp_addlinkedserver
@.server = 'Point',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'ORCL'
GO
EXEC sp_addlinkedsrvlogin 'Point', 'false', NULL, '*****', '******'
PROBLEM
=======
For example following expression:
SELECT TOP 10 * FROM OpenQuery(Point,'SELECT * FROM FK350')
returns resultset on IVAN (works fine) while on GRSK03 returns following
error?
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: The provider did not give any information about the
error.].
The same error on GRSK03 is returned if I try making a linked server using
MSDASQL (ODBC) provider.
I've configured (in registry) Oracle provider for OLE DB (OraOLEDB.dll).
Again on IVAN it works fine, on GRSK03 runs infinitely (no error message).
ODBC DSN on Oracle works fine (for example linking view FK350 in Access) on
both machines.
[QUOTE]
Someone knows why? I suspect security features of Win2003 or RRAS maybe
but for what I've checked all ports are opened and if there is a problem
with router/firewall
it would be on IVAN not GRSK03 which is on LAN side-by-side with GRSK02.
Regarding security I've tried logging using 'sa' but with no results.
I've also tried disabling the Kerio firewall on GRSK03 but it didn't help.
If someone is familiar with the issue, please tell me your opinion.
I'm suspecting mssqlsrv installation although it's been recently installed
and
configured like any other of 6 SQL Servers on other LANs.
Ivan Nikolic
SAIL Company Croatia Ltd.
DB developer
ivan@.sail.hrI would start with this article:
280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

PRB: Single Query to build database and schema.

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

PRB: "use database" not working after "create database"

PRB: "use database" not working after "create database"
Please help,
I have the following query:
set XACT_ABORT on
begin transaction
create database MY_DB
use MY_DB
commit transaction
The "use" statement fails saying the database does not exists, but I get no
error on the "create". And when I go to the server, sure enough the DB is no
t
there, which it should not be if the TX rolled back. So what is wrong? If th
e
"create" is bad, why do I not see an error on it?You will not see it until you commit the transaction.
set XACT_ABORT on
begin transaction
create database MY_DB
commit transaction
use MY_DB
AMB
"ATS" wrote:

> PRB: "use database" not working after "create database"
> Please help,
> I have the following query:
> set XACT_ABORT on
> begin transaction
> create database MY_DB
> use MY_DB
> commit transaction
> The "use" statement fails saying the database does not exists, but I get n
o
> error on the "create". And when I go to the server, sure enough the DB is
not
> there, which it should not be if the TX rolled back. So what is wrong? If
the
> "create" is bad, why do I not see an error on it?|||Thanks for the reply, but that doesn't work in Query Analyzer.|||Try,
use master
go
create database MY_DB
go
select
*
from
sysdatabases
where
[name] = 'MY_DB'
go
drop database MY_DB
go
AMB
"ATS" wrote:

> Thanks for the reply, but that doesn't work in Query Analyzer.|||One other thing I've noticed. Even with or without TX handling it fails in
Query Analyzer.sql

PRB SQL Express Installation not installing Manager

PRB SQL Express Installation not installing Manager.
Please help,
We are trying to work with SQL Server Express 2005, but we are having
several installation problems.
#1) Despite all efforts, the Database Manager program (ssmsee.exe) is NOT
being installed on some XP-Pro machines even though the user has FULL admin
right (and then some) to their machine. And the machine is fully 100% updated
with all MS patches, including IE 7. This includes many uninstall-reinstall
efforts.
#2) On some machines that HAD the Database MAnager, the user logs in, and
then suddenly it is GONE. The file and the short cut link.
#3) We need a profiler tool like SQL Server's 2000 Profiler which worked on
MSDE, but does not seem to be able to work on SQL Express 2005.
Thank you for the reply,
You have answered 1 and 2 through that link. Basically to explain 1 and 2 in
other words. We installed SQL Express 2005 and it DID install the management
tool, the same one that is in that link you provided. But it did not do this
on every machine. And on some machine, after a few days, it disappeared. And
no matter what we tried to do on the machines that never got it, or once had
it, we could never get it again. From this, I would have to say, that perhaps
the SQL Express 2005 installer has some bug that is making it install the
manager tool either from itself, or maybe the web, some of the time, and then
perhaps removing it from the SQL Server Express 2005 service. As nothing else
can explain this behavior. We have tested this out over 7 machines, including
2 of which were Windows 2000. Strangely, the Windows 2000 machines were the
only one to get the manager tool and keep them so far.
As for the #3, can one use the SQL Server 2000 profiler with a SQL Express
2005 database? Once upon a time, with MSDE, one could do that.
|||ONLY if you have a legitimately purchased and licensed copy of SQL Server
which gives you license to use Profiler.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"ATS" <ATS@.discussions.microsoft.com> wrote in message
news:94AFAA47-854B-493C-A6E8-3C9CFA365FFF@.microsoft.com...
> Thank you for the reply,
> You have answered 1 and 2 through that link. Basically to explain 1 and 2
> in
> other words. We installed SQL Express 2005 and it DID install the
> management
> tool, the same one that is in that link you provided. But it did not do
> this
> on every machine. And on some machine, after a few days, it disappeared.
> And
> no matter what we tried to do on the machines that never got it, or once
> had
> it, we could never get it again. From this, I would have to say, that
> perhaps
> the SQL Express 2005 installer has some bug that is making it install the
> manager tool either from itself, or maybe the web, some of the time, and
> then
> perhaps removing it from the SQL Server Express 2005 service. As nothing
> else
> can explain this behavior. We have tested this out over 7 machines,
> including
> 2 of which were Windows 2000. Strangely, the Windows 2000 machines were
> the
> only one to get the manager tool and keep them so far.
> As for the #3, can one use the SQL Server 2000 profiler with a SQL Express
> 2005 database? Once upon a time, with MSDE, one could do that.