Problem with linking servers

Hi.

I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL
Server' to link one to the other and also vise versa.

Each server has two users permissioned.

My problem is when ever I try to do something that does a remote write I get
the follow error message
Microsoft OLE DB Provider for SQL Server error '80040e14'

[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Also

when I try and manually run a stored procedure, I get:

Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.

(1 row(s) affected)

(1 row(s) affected)

(50 row(s) affected)

Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
65
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Can anyone suggest how I resolve this.

FYI

Database Creation Script: (both database are the same scripts but db names
are changed)

CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' , SIZE
= 2000, FILEGROWTH = 10%)

COLLATE Latin1_General_CI_AS

GO

exec sp_dboption N'msmprim', N'autoclose', N'false'

GO

exec sp_dboption N'msmprim', N'bulkcopy', N'false'

GO

exec sp_dboption N'msmprim', N'trunc. log', N'false'

GO

exec sp_dboption N'msmprim', N'torn page detection', N'true'

GO

exec sp_dboption N'msmprim', N'read only', N'false'

GO

exec sp_dboption N'msmprim', N'dbo use', N'false'

GO

exec sp_dboption N'msmprim', N'single', N'false'

GO

exec sp_dboption N'msmprim', N'autoshrink', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI null default', N'false'

GO

exec sp_dboption N'msmprim', N'recursive triggers', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI nulls', N'false'

GO

exec sp_dboption N'msmprim', N'concat null yields null', N'false'

GO

exec sp_dboption N'msmprim', N'cursor close on commit', N'false'

GO

exec sp_dboption N'msmprim', N'default to local cursor', N'false'

GO

exec sp_dboption N'msmprim', N'quoted identifier', N'false'

GO

exec sp_dboption N'msmprim', N'ANSI warnings', N'false'

GO

exec sp_dboption N'msmprim', N'auto create statistics', N'true'

GO

exec sp_dboption N'msmprim', N'auto update statistics', N'true'

GO

User Creation Script:

/****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/

use [msmprim]

GO

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GO

use [msmprim]

GO

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GOI have got a little further, by adding 'SET XACT_ABORT ON' I have mangaged
to get the remote write to work.

Can someone tell me what the implication of setting 'SET XACT_ABORT ON' is
and what effect will it have on my SPs. Will they still roll back as
before?

Sample SP

procedure [msm].ams_Insert_Audit
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strSCRIPT varchar(128),
@.strLOGINID varchar(32),
@.strVFEID varchar(16),
@.strBILLORGID varchar(16),
@.strRETAILERID varchar(16),
@.strLOCATIONID varchar(16),
@.strPOSTID varchar(16),
@.strXACTIONID varchar(16),
@.strCODE varchar(64),
@.strDATA varchar(1024)
as
declare @.strStep varchar(32)
declare @.trancount int

set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
begin tran ams_Insert_Audit
else
save tran ams_Insert_Audit

SET XACT_ABORT ON

set @.strStep = 'Writing MSM Audit'

insert into
HAN.msmprim.msm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

set @.strStep = 'Writing PSM Audit'

insert into
ACSPSM.psmprim.psm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

if (@.@.error <> 0)
begin
rollback tran ams_Insert_Audit
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : Error Occured'
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran ams_Insert_Audit

return 0

Regards

Steve

"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkeqcl$h14$1@.titan.btinternet.com...
> Hi.
> I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM',
N'SQL
> Server' to link one to the other and also vise versa.
> Each server has two users permissioned.
> My problem is when ever I try to do something that does a remote write I
get
> the follow error message
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
> Also
> when I try and manually run a stored procedure, I get:
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (50 row(s) affected)
> Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
> 65
> Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
> nested transaction was required because the XACT_ABORT option was set to
> OFF.
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
>
> Can anyone suggest how I resolve this.
>
>
> FYI
> Database Creation Script: (both database are the same scripts but db names
> are changed)
> CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
> N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
> (NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' ,
SIZE
> = 2000, FILEGROWTH = 10%)
> COLLATE Latin1_General_CI_AS
> GO
> exec sp_dboption N'msmprim', N'autoclose', N'false'
> GO
> exec sp_dboption N'msmprim', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'msmprim', N'trunc. log', N'false'
> GO
> exec sp_dboption N'msmprim', N'torn page detection', N'true'
> GO
> exec sp_dboption N'msmprim', N'read only', N'false'
> GO
> exec sp_dboption N'msmprim', N'dbo use', N'false'
> GO
> exec sp_dboption N'msmprim', N'single', N'false'
> GO
> exec sp_dboption N'msmprim', N'autoshrink', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'msmprim', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'msmprim', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'msmprim', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'msmprim', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'msmprim', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'msmprim', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'msmprim', N'auto update statistics', N'true'
> GO
>
>
> User Creation Script:
> /****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO

Problem with linked servers

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561632
I have two servers that are linked together ServerA and ServerB. I can do
selects from remote tables on both servers with no problems. The problem
that i am running into is when I am doing an insert into a local table via a
join with a local and remote table.
Both Win2k3 server with SQL 2000 SP3
ie (I know this code isnt correct, just an example)
insert into ServerA.DB.dbo.table
select *
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
I can run the selects just fine, but when adding the inserts, it craps out.
----
---
this is the error that i am getting
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].try this, it might help
select *
into #tmpData
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
insert into ServerA.DB.dbo.table
select *
from
#tmpData
remember that 'select *' shouldn't be used in production code.
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> I have two servers that are linked together ServerA and ServerB. I can do
> selects from remote tables on both servers with no problems. The problem
> that i am running into is when I am doing an insert into a local table via
a
> join with a local and remote table.
> Both Win2k3 server with SQL 2000 SP3
> ie (I know this code isnt correct, just an example)
> insert into ServerA.DB.dbo.table
> select *
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> I can run the selects just fine, but when adding the inserts, it craps
out.
> ----
---
> this is the error that i am getting
> The operation could not be performed because the OLE DB provider
'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561776
I had given thought to the temp table, but i would like to know if there is
configuration problem with my server keeping me from doing this.
TIA
Kevin E.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4360865d$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> try this, it might help
> select *
> into #tmpData
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> insert into ServerA.DB.dbo.table
> select *
> from
> #tmpData
> remember that 'select *' shouldn't be used in production code.
>
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> a
> out.
> ---
> 'SQLOLEDB'
>

problem with installation of sqlserver 2000

I have a problem with installation of sqlserver 2000.
I have a pc at home, my pc is not in a network. The
operative system installed is Windows XP professional.
When I try to install sqlserver I receive the following
message: " sqlserver 2000 enterprise edition component is
not supported in this operative system. You can install
only client components"; so I install only client
components.
I must use Query Analizer, but when I try to connect to
my computer (local server) I get this error message: "
Unable to connect to server (my pc) Msg 17, level 16,
[Microsoft][odbc sqlserver driver][Shared Memory]
sqlserver does not exist or access denied".
I cannot change system operative and I must use Query
Analizer. How can I do? Please help me!Hi,
Since you have not installed a SQL server , you can not connect Query
Analyser with out having access to a server.
In Windows XP try installing SQL 2K Personal Edition.
Thanks
Hari
MCDBA
"Giulia" <giulia74z@.libero.it> wrote in message
news:050701c3cadd$dca77110$a101280a@.phx.gbl...

quote:

---

> I have a problem with installation of sqlserver 2000.
> I have a pc at home, my pc is not in a network. The
> operative system installed is Windows XP professional.
> When I try to install sqlserver I receive the following
> message: " sqlserver 2000 enterprise edition component is
> not supported in this operative system. You can install
> only client components"; so I install only client
> components.
> I must use Query Analizer, but when I try to connect to
> my computer (local server) I get this error message: "
> Unable to connect to server (my pc) Msg 17, level 16,
> [Microsoft][odbc sqlserver driver][Shared Memory]
> sqlserver does not exist or access denied".
> I cannot change system operative and I must use Query
> Analizer. How can I do? Please help me!
>

---

|||Giulia (giulia74z@.libero.it) writes:

quote:

---

> I have a problem with installation of sqlserver 2000.
> I have a pc at home, my pc is not in a network. The
> operative system installed is Windows XP professional.
> When I try to install sqlserver I receive the following
> message: " sqlserver 2000 enterprise edition component is
> not supported in this operative system. You can install
> only client components"; so I install only client
> components.

---

This is because on Windows XP you can only install Deverloper Edition,
Personal Edition, Evaulation Edition and MSDE. You cannot install
Standard Edition and Enterprise Edition. These two edition require
a server operating system.

quote:

---

> I must use Query Analizer, but when I try to connect to
> my computer (local server) I get this error message: "
> Unable to connect to server (my pc) Msg 17, level 16,
> [Microsoft][odbc sqlserver driver][Shared Memory]
> sqlserver does not exist or access denied".
> I cannot change system operative and I must use Query
> Analizer. How can I do? Please help me!

---

Since you did not install server components, there is no local server
to connect to. So either you need to connect to network where there
is an SQL Server available, install an edition of SQL Server that
runs on XP.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for help!
I installed MSDE from Office 2000 and client components
of sqlserver 2000.
Then I started sql server service manager, in this way I
can connect to local server from Query Analizer!
thank you another

quote:

---

>--Original Message--
>Giulia (giulia74z@.libero.it) writes:
following[QUOTE]
is[QUOTE]
install[QUOTE]
>This is because on Windows XP you can only install

---

Deverloper Edition,

quote:

---

>Personal Edition, Evaulation Edition and MSDE. You

---

cannot install

quote:

---

>Standard Edition and Enterprise Edition. These two

---

edition require

quote:

---

>a server operating system.
>
to[QUOTE]
>Since you did not install server components, there is no

---

local server

quote:

---

>to connect to. So either you need to connect to network

---

where there

quote:

---

>is an SQL Server available, install an edition of SQL

---

Server that

quote:

---

>runs on XP.
>
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techin...uctdoc/2000/boo

---

ks.asp

quote:

---

>.
>

---

|||Thank you for help!
I installed MSDE from Office 2000 and client components
of sqlserver 2000.
Then I started sql server service manager, in this way I
can connect to local server from Query Analizer!

quote:

---

>--Original Message--
>Hi,
>Since you have not installed a SQL server , you can not

---

connect Query

quote:

---

>Analyser with out having access to a server.
>In Windows XP try installing SQL 2K Personal Edition.
>Thanks
>Hari
>MCDBA
>"Giulia" <giulia74z@.libero.it> wrote in message
>news:050701c3cadd$dca77110$a101280a@.phx.gbl...
is[QUOTE]
>
>.
>

---

Problem with GROUP BY/COMPUTE : error message 8120

Hi,
I have this query ( it is Ok with Sybase SQLServer)
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
I want to migrate it under MS SQLServer 2000, but I have this error message
:
Serveur : Msg 8120, Niveau 16, tat 1, Ligne 1
Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
If I delete the last line, the query is Ok :
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
The problem is with COMPUTE claude, but I can't understand why.
please help me to solve this.
thanks in advance
regards
LaurentWhat kind of error or warning appears when you drop that line? Apparently
syntax for COMPUTE is fine.
"Laurent CLAUDEL" wrote:

> Hi,
> I have this query ( it is Ok with Sybase SQLServer)
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
>
> I want to migrate it under MS SQLServer 2000, but I have this error messag
e
> :
> Serveur : Msg 8120, Niveau 16, état 1, Ligne 1
> Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> If I delete the last line, the query is Ok :
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> The problem is with COMPUTE claude, but I can't understand why.
> please help me to solve this.
> thanks in advance
> regards
> Laurent
>
>|||if I drop the last line (COMPUTE), there is no more error.
"Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
> What kind of error or warning appears when you drop that line? Apparently
> syntax for COMPUTE is fine.
> "Laurent CLAUDEL" wrote:
>|||But I want a sum by Year, so i have to keep the COMPUTE clause
"Laurent CLAUDEL" <laurent.claudel@.steria.com> a crit dans le message de
news: OhOqg8Y1FHA.1108@.TK2MSFTNGP14.phx.gbl...
> if I drop the last line (COMPUTE), there is no more error.
> "Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
> B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
>|||I suggest you don't use COMPUTE / COMPUTE BY unless it's essential to
maintain Sybase compatibility. COMPUTE is legacy stuff that was
deprecated long ago. Take a look at CUBE / ROLLUP in Books Online -
it's a much more powerful feature.
David Portas
SQL Server MVP
--sql

Problem with export of a table to a text file

Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

DanielPosting the table definition (CREATE TABLE) would help a lot.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:

Quote:

Originally Posted by

>Dear MSSQL- experts,
>
>I have a strange problem with SQLSERVER 2000.
>I tried to export a table of about 40000 lines into a text file using
>the Enterprise manager
>export assitant. I was astonished to get an exported text file of about
>400 MB instead 16 MB which is the normal size of that data.
>By examining this file with a text editor I found that the file
>included alongside the data of my table MANY zeros which caused the big
>file size.
>
>Does someone of you have an idea what could cause the export of
>trillions zeros into my textfile and how to only export the significant
>data of my table ?
>
>Best regards,
>
>Daniel

|||Hi Roy,

thanks for your answer.

Here's the table definition :

CREATE TABLE [dbo].[Variables] (
[ID] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Var_ref] [int] NOT NULL ,
[Group_Ref] [int] NULL ,
[Machines_Ref] [int] NULL ,
[TransfersID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Compatibility] [int] NULL ,
[CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Category] [int] NULL ,
[CategoryL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AnalysisResponsible] [tinyint] NULL ,
[AnalysisPriority] [int] NULL ,
[AnalysisDelta] [int] NULL ,
[AnalysisDeltaValue] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Length] [int] NULL ,
[Address] [int] NULL ,
[Offset] [int] NULL ,
[Title1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle1] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle2] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle3] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle4] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Best regards,

Daniel

Roy Harvey schrieb:

Quote:

Originally Posted by

Posting the table definition (CREATE TABLE) would help a lot.
>
Roy Harvey
Beacon Falls, CT
>
On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:
>

Quote:

Originally Posted by

Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

Daniel

|||There were no surprises in the table definition. Storage of a row in
SQL Server, with all the varying length columns at their upper limit,
is around 2000 bytes, for whatever that is worth. Output of a maxed
out row to a unicode text file should be no more than a few hundred
bytes longer.

Did you save the DTS package that the export wizard created? Have you
tried running the wizard again? Did you specify fixed-width or
delimited? ASCII or UNICODE? (I expect UNICODE to handle the
NVARCHAR columns.)

I would run the wizard again, being sure to save the package. That at
least will give something to inspect on the code side if the output
file is still screwy.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 06:11:12 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:

Quote:

Originally Posted by

>Hi Roy,
>
>thanks for your answer.
>
>Here's the table definition :
>
>
>CREATE TABLE [dbo].[Variables] (
>[ID] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Var_ref] [int] NOT NULL ,
>[Group_Ref] [int] NULL ,
>[Machines_Ref] [int] NULL ,
>[TransfersID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[Compatibility] [int] NULL ,
>[CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
>[Category] [int] NULL ,
>[CategoryL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[AnalysisResponsible] [tinyint] NULL ,
>[AnalysisPriority] [int] NULL ,
>[AnalysisDelta] [int] NULL ,
>[AnalysisDeltaValue] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[Value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Length] [int] NULL ,
>[Address] [int] NULL ,
>[Offset] [int] NULL ,
>[Title1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Title2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Title3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Title4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle1] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle2] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle3] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle4] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
>
>Best regards,
>
>Daniel
>
>
>
>Roy Harvey schrieb:
>

Quote:

Originally Posted by

>Posting the table definition (CREATE TABLE) would help a lot.
>>
>Roy Harvey
>Beacon Falls, CT
>>
>On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
><Daniel.Wetzler@.sig.bizwrote:
>>

Quote:

Originally Posted by

>Dear MSSQL- experts,
>
>I have a strange problem with SQLSERVER 2000.
>I tried to export a table of about 40000 lines into a text file using
>the Enterprise manager
>export assitant. I was astonished to get an exported text file of about
>400 MB instead 16 MB which is the normal size of that data.
>By examining this file with a text editor I found that the file
>included alongside the data of my table MANY zeros which caused the big
>file size.
>
>Does someone of you have an idea what could cause the export of
>trillions zeros into my textfile and how to only export the significant
>data of my table ?
>
>Best regards,
>
>Daniel

problem with dtexec

Hi

I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.

Following is the command I use to run my application

Dtexec /FILE "N:\Temp\LoadFirewallData.dtsx" /CONFIGFILE "N:\Temp\LoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[User::RunID]";41

Regards

Meghana

Are you running DTExecUi and DTExec on the same machine, same use? is the DTExec command line exactly the same as that for DTExecUI (look at the Command Line page in DTExecUI if you have not already)?|||

I ran to similar problems. My package has several variables. When I passed them to DTExec, it does not work and it always takes the values I set when I design the package.

Should I design the package differently? No configurations?

Guangming

|||I am using the exact same command line which shows up in dtexecui. It is not working.|||I'm afraid I cannot see why you should get any problems between the two like this, so I assume it is the package. The only other idea I have is to turn on some detailed logging in the package and see what happens prior to it getting stuck.|||

Following is the error which occurs before it gets hung. The variable is of type string. This error occurs on multi proc machine when executed using dtexec. But the same does not happen when we execute it with dtexecui.

The variable "User::stripfilename" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Also, when executed through dtexec command line, it is not consistent enough to show the same error, sometimes it runs through fine and sometimes it errors and hangs till we kill the job from task manager

It seems like couple of other folks are also facing the similar issue

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=222351&SiteID=1

Please let us know if there is any way to resolve this as this is very crucial part of our project.

Regards

Meghana

|||have you tried running the tasks in sequence, rarther than parallel? Have you tried MaxConcurrentExecutables? Seems there is a bug here, but in the meantime one of those should allow you to get going again.|||If it's an issue with variables, and you're not using this in the script, try using

Public Shared Function getVar(ByVal varName As String) As String
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead(varName, vars)
Return vars(varName).Value.ToString()
End Function

rather than referencing the variable in the script properties.

|||

Finally I found the problem:

The way to put the command string is not right after "dtexec /SQL. "

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

problem with dtexec

Hi

I have a SSIS package which pulls files from a network share and loads data into SQLServer Database. When I execute the application using DTExecUI , It runs fine without any issues , but where as when I run it using the command line arguments, It seems to go in sleep mode and nothing happens. I need to kill the package from Task manager.

Following is the command I use to run my application

Dtexec /FILE "N:\Temp\LoadFirewallData.dtsx" /CONFIGFILE "N:\Temp\LoadFirewallData.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[User::RunID]";41

Regards

Meghana

Are you running DTExecUi and DTExec on the same machine, same use? is the DTExec command line exactly the same as that for DTExecUI (look at the Command Line page in DTExecUI if you have not already)?|||

I ran to similar problems. My package has several variables. When I passed them to DTExec, it does not work and it always takes the values I set when I design the package.

Should I design the package differently? No configurations?

Guangming

|||I am using the exact same command line which shows up in dtexecui. It is not working.|||I'm afraid I cannot see why you should get any problems between the two like this, so I assume it is the package. The only other idea I have is to turn on some detailed logging in the package and see what happens prior to it getting stuck.|||

Following is the error which occurs before it gets hung. The variable is of type string. This error occurs on multi proc machine when executed using dtexec. But the same does not happen when we execute it with dtexecui.

The variable "User::stripfilename" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Also, when executed through dtexec command line, it is not consistent enough to show the same error, sometimes it runs through fine and sometimes it errors and hangs till we kill the job from task manager

It seems like couple of other folks are also facing the similar issue

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=222351&SiteID=1

Please let us know if there is any way to resolve this as this is very crucial part of our project.

Regards

Meghana

|||have you tried running the tasks in sequence, rarther than parallel? Have you tried MaxConcurrentExecutables? Seems there is a bug here, but in the meantime one of those should allow you to get going again.|||If it's an issue with variables, and you're not using this in the script, try using

Public Shared Function getVar(ByVal varName As String) As String
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead(varName, vars)
Return vars(varName).Value.ToString()
End Function

rather than referencing the variable in the script properties.

|||

Finally I found the problem:

The way to put the command string is not right after "dtexec /SQL. "

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

problem with distributed transaction via linked server

hi,
i am trying to connect to oracle 9i using sqlserver 2000 linked server.
here the linked server name is intersql. when i put this statement in begin
and end transaction block and try to execute it is giving the following
error
Server: Msg 7391, Level 16, State 1, Procedure
ihmtproc_emp_assmbly_interface, Line 93
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
INSERT INTO eemp_assembly_interface
(
[drawing_file_nm],
[part_number_txt],
[drawing_status_txt],
[drawing_revision_txt],
[model_file_nm],
[model_title_txt],
[parent_part_number_txt],
[release_status_txt],
[emp_desc],
[created_by_user_id],
[created_on_dt]
)
SELECT drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description,
999999999 as created_by_user_id,
GETDATE() as created_on_dt
FROM OPENQUERY(productcenter,
'SELECT
drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description
FROM EMP1 WHERE FLAG=''N'''
)
please help me
- Thanks
ravi sankar gvsIs it because you are specifying [productcenter] instead of [intersql] as
your linked server name?
Raj Moloye
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:uha5aF2aFHA.1456@.TK2MSFTNGP15.phx.gbl...
> hi,
> i am trying to connect to oracle 9i using sqlserver 2000 linked server.
> here the linked server name is intersql. when i put this statement in
> begin
> and end transaction block and try to execute it is giving the following
> error
> Server: Msg 7391, Level 16, State 1, Procedure
> ihmtproc_emp_assmbly_interface, Line 93
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> INSERT INTO eemp_assembly_interface
> (
> [drawing_file_nm],
> [part_number_txt],
> [drawing_status_txt],
> [drawing_revision_txt],
> [model_file_nm],
> [model_title_txt],
> [parent_part_number_txt],
> [release_status_txt],
> [emp_desc],
> [created_by_user_id],
> [created_on_dt]
> )
> SELECT drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description,
> 999999999 as created_by_user_id,
> GETDATE() as created_on_dt
> FROM OPENQUERY(productcenter,
> 'SELECT
> drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description
> FROM EMP1 WHERE FLAG=''N'''
> )
> please help me
> - Thanks
> ravi sankar gvs
>