Creating a view from a linked server

Hi
i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following
select * from openquery (AccessLinkedServer,'select * from mytable')
i press run and i see the data ok .but when i try to save the view i get
the following
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].
*** Sent via Developersdex http://www.examnotes.net ***try creating the view via query analyzer. there are all kinds of funky
issues with em.
-oj
"tolisss" <nospam@.devdex.com> wrote in message
news:e7H8X6ZRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>
> Hi
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: [DataDirect][ODBC dBase driver]Optional feature not
> implemented.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
> [OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
> 0x8004d00a].
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Creating a view from a linked server

Hi

i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following

select * from openquery (AccessLinkedServer,'select * from mytable')

i press run and i see the data ok .but when i try to save the view i get
the following

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].

*** Sent via Developersdex http://www.developersdex.com ***tolisss (nospam@.devdex.com) writes:
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.

Try creating the view from Query Analyzer. I seem to recall
that when you create a view from Enterprise Manager, it starts a
transaction. Later when you come to create the view itself, the OLE DB
provider cannot upgrade the transaction to a distributed one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a table inside a stored procedure

I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.Is that like the type of sex you want...is this mail order?

Anyway, you need to take out the GO...that's a scope terminator

And are you sure you want a GLOBAL Temp table instead of a local one?

Try this

CREATE PROCEDURE [dbo].[Test2]
AS
BEGIN
CREATE TABLE ##OPTIONSEX (
OPTION_PLAN VARCHAR(50)
, TOT_OPTIONS_EXCHANGED FLOAT NULL
)

INSERT ##OPTIONSEX
SELECT B.COMPONENT
, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A
INNER JOIN TBLCOMPONENT B
ON B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

DROP TABLE ##OPTIONSEX
GO|||Thanks for the reply. Worked fine only after I removed the BEGIN line. For some reason using BEGIN returned a syntax error.

Thanks again Brett.|||dooooooh

I forgot the END

BEGIN
.....some code
END

And you'll need those constructs if you do an control of flow logic

IF some condition
BEGIN
.........some code line 1
.........some code line 2
END

WHILE some Cond
BEGIN
.........some code line 1
.........some code line 2
END

Good luck

creating a table column that contains long string

I have made a database inside a C# project using project --> Add New Item --> SQL Database.

I have made its tables and define the columns, but I need a data type that allows inserting a long string , as a news paper .

When I use the 'text' data type , the inserted document can't entered as a whole, only subdocument can be entered.

I need also to know how can I create a table that contains Objects .

of any created class.

Thanks,

Aya.

The text type can hold up to 2GB of data. Should be enough no?

Could you show us your insert procedure?

|||

Ok thanks.

I just detect an error in my inserting process.

But I need to know whether I can create a table that contains Objects or not.

And also if I can create a table that contains lists or not.

Thanks,

Aya.

|||

Hi,

You should be aware that the 'text' datatype is being deprecated, please look at varchar(max)/nvarchar(max) as a replacement, which should give you a better user experience in general.

Could you define what do you mean by Objects and Lists? Maybe there are other ways to achieve what you are trying to do. Please elaborate.

Thanks!

-Mat

Creating a job with many steps including DTS

Hi,
(using Windows 2000 Server/SQL2K Ent Ed SP3a)
I am looking to create a job with a number of steps inside it. I need
the job to run on a sunday afternoon when our OLTP system is at our
quietest in terms of user connections and throughput. The job purpose
is to copy data (24,000,000 rows) from one table to another on the same
database, via a DTS package. I then need to rename the old table,
removing the triggers and views on it, then rename the new table, and
add the relevent objects (including indexs and constraints) then add
the permissions on it.
These are the steps:
1) Set restricted user mode on database
2) Run the DTS package
3) Remove triggers and views on old table
4) Rename old table
5) Rename new table
6) Add indexs and constraints to new table
7) Add triggers/views to new table
8) Set permissions to new table
9) Set multi user mode on database
My question would be does the above seem logical, or would there be a
better way of performing this process? Do I need the DTS package, would
a INSERT INTO be better, or could I use a DTS package for the entire
process?
Thanks and rgds,
qhHi
INSERT..SELECT may be a better option.
You may want to try something like the following to avoid the rename steps:
Create Archive Table
Insert Into Archive Table
Delete Archived Data from Live Table
ReIndex Live Table
You may also be able to do this in smaller increments each night instead of
once a w.
John
"Scott" wrote:

> Hi,
> (using Windows 2000 Server/SQL2K Ent Ed SP3a)
> I am looking to create a job with a number of steps inside it. I need
> the job to run on a sunday afternoon when our OLTP system is at our
> quietest in terms of user connections and throughput. The job purpose
> is to copy data (24,000,000 rows) from one table to another on the same
> database, via a DTS package. I then need to rename the old table,
> removing the triggers and views on it, then rename the new table, and
> add the relevent objects (including indexs and constraints) then add
> the permissions on it.
> These are the steps:
> 1) Set restricted user mode on database
> 2) Run the DTS package
> 3) Remove triggers and views on old table
> 4) Rename old table
> 5) Rename new table
> 6) Add indexs and constraints to new table
> 7) Add triggers/views to new table
> 8) Set permissions to new table
> 9) Set multi user mode on database
>
> My question would be does the above seem logical, or would there be a
> better way of performing this process? Do I need the DTS package, would
> a INSERT INTO be better, or could I use a DTS package for the entire
> process?
> Thanks and rgds,
> qh
>