Showing posts with label exec. Show all posts
Showing posts with label exec. Show all posts

Friday, March 30, 2012

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 server to Oracle

I run linked server from SQL Server 2000 to Oracle 9.i. In SQL Server oracle client is xa73.dll.
EXEC sp_addlinkedserver @.server='ORDB',
@.provider='MSDAORA',
@.srvproduct = 'Oracle',
@.datasrc='ORDB'
EXEC sp_addlinkedsrvlogin
@.rmtsrvname ='ORDB',
@.useself ='false',
@.locallogin =NULL,
@.rmtuser = 'ivr',
@.rmtpassword = 'jjcom'
SELECT * FROM OPENQUERY(ORDB,'SELECT * FROM SUBSCRIBER')
but get error like for the following :
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
Please help me to get a solution ?

Hi,

did you check those threads on the groups ?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=56755&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=19365&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408756&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417751&SiteID=1

The latter one including links for troubleshotting linked servers in Oracle.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Yes, I already checked those site and still get error.
|||

Please try to install MDAC 2.8.

Thanks

|||Hi Zoya,
I already install mdac 2.8 sp1 and try to execute query, the error message nos is :
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Error while trying to retrieve text for error ORA-01019
]

Wednesday, March 28, 2012

Problem with liinked server

Hi,
I have two sql server. One is sql 2000, and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
@.srvproduct = N' ',
@.provider = N'SQLNCLI',
@.datasrc = N'',
@.catalog = N'komitenti',
@.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
I got result.
How can I exec stored procedure on linked server, because
exec Zagreb_new.database.dbo.Mystored doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'.
Hi
"Radovan Dobri?" wrote:

> Hi,
> I have two sql server. One is sql 2000, and another is sql 2005
> On sql 2000 I try to add linked to sql 2005.
> (like this)
> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> @.srvproduct = N' ',
> @.provider = N'SQLNCLI',
> @.datasrc = N'',
> @.catalog = N'komitenti',
> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELTA555'
> GO
>
> I try
> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> and have followig error:
>
> OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordinal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed. The exact nature of the change is unknown].
> Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
>
> When I try:
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komitenti')
> I got result.
>
> How can I exec stored procedure on linked server, because
> exec Zagreb_new.database.dbo.Mystored doesn't work
> error is:
>
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'
The only thing I have found about this is that "it could be fixed in SP2"
which versions are the servers running? Is the client one of the servers?
Is a login defined for the linked server? See
http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using the
default mapping, in which case will the login have permissions on the remote
server? Your error message when running the remote procedure would indicate
that they don't have permissions to run this.
John
|||Hi,
I have sp2 on server (I try it on another server with sp2 in another firm
and have exactly same result).
I try it with sql server account (and did mapping on that account). That
account have admin privil.
radovan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> Hi
> "Radovan Dobri" wrote:
>
> The only thing I have found about this is that "it could be fixed in SP2"
> which versions are the servers running? Is the client one of the servers?
> Is a login defined for the linked server? See
> http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> the
> default mapping, in which case will the login have permissions on the
> remote
> server? Your error message when running the remote procedure would
> indicate
> that they don't have permissions to run this.
> John
>
|||Hi
Have you tried creating the linked server as
EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.datasrc = N'ZAGREB_NEW'
or
EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
John
"Radovan Dobri?" wrote:

> Hi,
> I have sp2 on server (I try it on another server with sp2 in another firm
> and have exactly same result).
> I try it with sql server account (and did mapping on that account). That
> account have admin privil.
> radovan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
>
>
|||That was my first try.
Linked server works fine with
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
komitenti.dbo.komitenti')
but not with
SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03897BB2-0508-4F7C-A923-090DB6AE439B@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you tried creating the linked server as
> EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
> @.srvproduct = N'',
> @.provider = N'SQLNCLI',
> @.datasrc = N'ZAGREB_NEW'
> or
> EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
>
> John
> "Radovan Dobria" wrote:
sql

Problem with liinked server

Hi,
I have two sql server. One is sql 2000, and another is sql 2005
On sql 2000 I try to add linked to sql 2005.
(like this)
exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
@.srvproduct = N' ',
@.provider = N'SQLNCLI',
@.datasrc = N'',
@.catalog = N'komitenti',
@.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELT
A555'
GO
I try
SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
and have followig error:
OLE DB error trace [Non-interface error: Column 'MB' (compile-time ordin
al 4) of object '"komitenti"."dbo"."komitenti"' was reported to have changed
. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metad
ata information was changed at execution time.
When I try:
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.komit
enti')
I got result.
How can I exec stored procedure on linked server, because
exec Zagreb_new.database.dbo.Mystored doesn't work
error is:
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'.Hi
"Radovan Dobri?" wrote:

> Hi,
> I have two sql server. One is sql 2000, and another is sql 2005
> On sql 2000 I try to add linked to sql 2005.
> (like this)
> exec sp_addlinkedserver @.server = N'ZAGREB_NEW',
> @.srvproduct = N' ',
> @.provider = N'SQLNCLI',
> @.datasrc = N'',
> @.catalog = N'komitenti',
> @.provstr = N'SERVER=ZAGREB_NEW;uid=radovan;pwd=DELT
A555'
> GO
>
> I try
> SELECT top 1 adresa from zagreb_new.komitenti.dbo.komitenti
> and have followig error:
>
> OLE DB error trace [Non-interface error: Column 'MB' (compile-time ord
inal 4) of object '"komitenti"."dbo"."komitenti"' was reported to have chang
ed. The exact nature of the change is unknown].
> Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Met
adata information was changed at execution time.
>
> When I try:
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM komitenti.dbo.kom
itenti')
> I got result.
>
> How can I exec stored procedure on linked server, because
> exec Zagreb_new.database.dbo.Mystored doesn't work
> error is:
>
> Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'spG_ProvjeriMB' on remote server 'zagreb_new'
The only thing I have found about this is that "it could be fixed in SP2"
which versions are the servers running? Is the client one of the servers?
Is a login defined for the linked server? See
http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using the
default mapping, in which case will the login have permissions on the remote
server? Your error message when running the remote procedure would indicate
that they don't have permissions to run this.
John|||Hi,
I have sp2 on server (I try it on another server with sp2 in another firm
and have exactly same result).
I try it with sql server account (and did mapping on that account). That
account have admin privil.
radovan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
> Hi
> "Radovan Dobri" wrote:
>
> The only thing I have found about this is that "it could be fixed in SP2"
> which versions are the servers running? Is the client one of the servers?
> Is a login defined for the linked server? See
> http://msdn2.microsoft.com/en-us/library/ms189811.aspx or are you using
> the
> default mapping, in which case will the login have permissions on the
> remote
> server? Your error message when running the remote procedure would
> indicate
> that they don't have permissions to run this.
> John
>|||Hi
Have you tried creating the linked server as
EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.datasrc = N'ZAGREB_NEW'
or
EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
John
"Radovan Dobri?" wrote:

> Hi,
> I have sp2 on server (I try it on another server with sp2 in another firm
> and have exactly same result).
> I try it with sql server account (and did mapping on that account). That
> account have admin privil.
> radovan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE5DF476-CF21-4F8C-8634-D4461EA9132F@.microsoft.com...
>
>|||That was my first try.
Linked server works fine with
SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
komitenti.dbo.komitenti')
but not with
SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03897BB2-0508-4F7C-A923-090DB6AE439B@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you tried creating the linked server as
> EXEC sp_addlinkedserver @.server = 'ZAGREB_NEW',
> @.srvproduct = N'',
> @.provider = N'SQLNCLI',
> @.datasrc = N'ZAGREB_NEW'
> or
> EXEC sp_addlinkedserver 'ZAGREB_NEW', N'SQL Server'
>
> John
> "Radovan Dobria" wrote:
>|||Hi
On May 23, 3:40 pm, "Radovan Dobri=E6" <rado...@.servis24.hr> wrote:
> That was my first try.
> Linked server works fine with
> SELECT * FROM OPENQUERY(ZAGREB_NEW, 'SELECT TOP 1 * FROM
> komitenti.dbo.komitenti')
> but not with
> SELECT TOP 1 * FROM zagreb_new.komitenti.dbo.komitenti'
>
The error you are getting would indicate that the user context that
each one is running under is different in each case.
Try mapping the local login to a different remote login using
sp_addlinkedsrvlogin
e=2Eg.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP',
'd89q3w4u'
and/or make sure that you can connect as the same user on the remote
system and execute the procedure.
John

Friday, March 23, 2012

Problem with Insert Exec in nested SP

Hello,

I want to share my experiences about using insert into exec which may
help others .
Using SQL Server 2000, SP3 .

Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
transactions .

I have defined a # table in Parent SP and calling a Child SP like this
insert into #temp exec childsp .....

Child SP has Select * from local # temp table ( local to child SP
) as the last statement .

When number of records are less ( around 1000 - 5000) Parent SP
executes but slow .

When the Child SP returns higher number of rows ( 1,00,000 or more )
the SP will be running for hours with out completion .
Although executing the child SP , with exec ChildSP ... with same
parameters it is completed in 2 mins for 3,00,000 rows .

Resolution : - Define a temp table (say #tempChild ) in the Parent SP
..
In the Child SP instead of select * replace with insert into
#tempChild select * from ...

Also note that this problem is not noticed in SQL 2000 Server with SP4
..

This may be due to SP executing in implicit transactions .(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I want to share my experiences about using insert into exec which may
help others .
Using SQL Server 2000, SP3 .
>
Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicit
transactions .
>
I have defined a # table in Parent SP and calling a Child SP like this
insert into #temp exec childsp .....
>
Child SP has Select * from local # temp table ( local to child SP
) as the last statement .
>
When number of records are less ( around 1000 - 5000) Parent SP
executes but slow .
>
When the Child SP returns higher number of rows ( 1,00,000 or more )
the SP will be running for hours with out completion .
Although executing the child SP , with exec ChildSP ... with same
parameters it is completed in 2 mins for 3,00,000 rows .
>
Resolution : - Define a temp table (say #tempChild ) in the Parent SP
.
In the Child SP instead of select * replace with insert into
#tempChild select * from ...


For further discussion on the problem of passing data between stored
procedures, there is an article on my web site:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 12, 2012

Problem with EXEC xp_cmdshell in UDF's

Hi All

Some what of a noob to UDF's and the EXEC statement...
I am confused on the following code....

CREATE FUNCTION DBO.DoesFileExist(@.FileLocation NVARCHAR(500))
RETURNS INT AS
BEGIN
DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd
RETURN @.return
END

GO

DECLARE @.FileLocation AS NVARCHAR(500)
SET @.FileLocation = 'C:\to\log.txt'

DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd

SELECT @.return AS Exp1

SELECT DBO.DoesFileExist(@.FileLocation) AS Exp2


RETURNS:
Exp1 = 0
Exp2 = 1

WHY IS THAT? Shouldn't Exp2 also be equal to 0? It's running the same function...

Any help would be much appreciated
Thanks
Mike
IF EXIST c:\MyFile.txt (ECHO 0) ELSE (ECHO 1)

Problem with EXEC command

I am try to populate a table based on a variable (it holds the table name).
I have managed to create the table using the EXEC command but am having
problems populating it with the following command. I receive "Error 156:
Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a solution
.
Thanks.
BEGIN
EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
However the following works fine into a regular table.
BEGIN
INSERT INTO SubsList (SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
@.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
ENDHi pete
try this:
BEGIN
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:

> I am try to populate a table based on a variable (it holds the table name)
.
> I have managed to create the table using the EXEC command but am having
> problems populating it with the following command. I receive "Error 156:
> Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a soluti
on.
> Thanks.
> BEGIN
> EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> However the following works fine into a regular table.
> BEGIN
> INSERT INTO SubsList (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
> @.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
> END|||Thanks Chandra but still no joy.
Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
"Chandra" wrote:
> Hi pete
> try this:
> BEGIN
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Hi pete
try now:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||Hi pete
just observed and found that, "(" was missing before "DateEntered"
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||'Fraid not. Same error.
"Chandra" wrote:
> Hi pete
> try now:
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||my mistake:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)'
i missed a ' at the end.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> 'Fraid not. Same error.
> "Chandra" wrote:
>|||Nope :-(
"Chandra" wrote:
> Noe then,
> this should work
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)')
> too many quotes and brackets, these are confusing..
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Why don't you try printing your query instead of executing it?
Then you'll see the error yourself. With what you're doing
now, you make a change, ask the parse "is this right yet?", and
listen to the parser's particular complaint, over and over.
If you've never looked at the query yourself, are you going to
assume it's correct once the parser stops complaining about it?
Once you get it right, then you can execute it instead of print
it.
Steve Kass
Drew University
Pete wrote:
>Getting closer..
>The following gives "Syntax error converting datetime from character string
"
>SET @.SqlString = N'INSERT INTO ' + @.NewSubsList + ' (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND (DateEntered >= ' +
>CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
>CONVERT(DATETIME, @.EndDate, 102) + ')'
>EXECUTE sp_executesql @.SqlString
>
>"Pete" wrote:
>
>|||hi
or do one thing,
first create the string that you want to execute.
just see if it clear in the syntax. and then pass it to sp_executesql
does this make sence.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Nope :-(
> "Chandra" wrote:
>

Saturday, February 25, 2012

problem with datetime value as parameter value

Hi all,
I created a stored proc that has input datetime parameters (begindate and enddate), I tried the command: exec storedproc '20060320' in the query command part in Microsoft SQL Server Management Studio and it works but when I try to create a report dataset using the stored proc and execute it using value 20060320 it does not work. I even try using '20060320' and "20060320" as the value and it did not work also. I received the following error:
TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.

ADDITIONAL INFORMATION:
Failed to convert parameter value from a String to a DateTime. (System.Data)

String was not recognized as a valid DateTime. (mscorlib)

BUTTONS:
OK

Anyone have any idea on how can I solve it or go about it? Thanks in advance.
Daren
Try using the format: "mm/dd/yyyy", so it would be: 03/20/2006.|||Thanks Deepak,
This solved my problem.
Daren