Error in Execution of a Job..

I am getting an error while executing a job. Can any one
give an idea how to resolve this...
Executed as user: GECCEFDBY\##MSSQLServer.
cdspcoin1ccefge.master.dbo.xp_fixeddrives [SQLSTATE 01000]
(Message 0) Access to the remote server is denied because
no login-mapping exists. [SQLSTATE 42000] (Error 7416).
The step failed.
Thanks much in advance..If this is a linked server, you may need to set up
an explicit sql server login in order to run this command.
Otherwise, logging sql services in as a domain user or
using kerberos may be required.
"Harcharan" <harcharan.jassal@.seepz.tcs.co.in> wrote in message
news:3b5f01c34086$c8e804f0$a601280a@.phx.gbl...
> I am getting an error while executing a job. Can any one
> give an idea how to resolve this...
>
> Executed as user: GECCEFDBY\##MSSQLServer.
> cdspcoin1ccefge.master.dbo.xp_fixeddrives [SQLSTATE 01000]
> (Message 0) Access to the remote server is denied because
> no login-mapping exists. [SQLSTATE 42000] (Error 7416).
> The step failed.
> Thanks much in advance..

Error in executing stored procedure:Not a valid identifier

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

Hey

I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]

@.whereClause nvarchar(2000)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

declare @.sqlstr asvarchar(max)

set @.sqlstr='SELECT Site.siteid as siteid,'

set @.sqlstr=@.sqlstr+'Site.Sitename as sitename, '

set @.sqlstr= @.sqlstr+'Customer.customerid,'

set @.sqlstr= @.sqlstr+'Customer.customername as CustomerName,'

set @.sqlstr= @.sqlstr+'Site.City as City,'

set @.sqlstr= @.sqlstr+'site.Address as Address,'

set @.sqlstr =@.sqlstr+'Site.state , '

set @.sqlstr= @.sqlstr+'Country.countryid as countryid,'

set @.sqlstr= @.sqlstr+'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '

set @.sqlstr= @.sqlstr+'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '

set @.sqlstr= @.sqlstr+'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '

set @.sqlstr= @.sqlstr+'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '

set @.sqlstr= @.sqlstr+@.whereClause

--

--set @.sqlstr=@.sqlstr+' WHERE GSUStatus.GSUStatusID=' +@.GSUStatusID

--if @.BusinessUnitID <> 0

--set @.sqlstr=@.sqlstr+'and site.BusinessUnitID ='+@.BusinessUnitID

--if @.CountryID <> 0

--set @.sqlstr=@.sqlstr+'and site.countryid='+@.CountryID

--if @.CustomerID <> 0

--set @.sqlstr=@.sqlstr+'and site.customerid='+@.CustomerID

--if @.SystemTypeID <> 0

--set @.sqlstr=@.sqlstr+'and site.SystemTypeID='+@.SystemTypeID

--if @.SiteName <> ''

--set @.sqlstr=@.sqlstr+'and site.Sitename like ' + @.SiteName

--if @.Address <> ''

--set @.sqlstr=@.sqlstr+'site.Address like '+ @.Address

--if @.City <> ''

--set @.sqlstr=@.sqlstr+'site.City like '+ @.City

--if @.State <> ''

--set @.sqlstr=@.sqlstr+'and site.state like '+ @.State

print @.sqlstr

exec @.sqlstr

END

I executed the procedure by pasing parameters

Exec [GSU_Site_ReterieveActiveSitesOnSearch]

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and getting the following error

- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}

Please let me know the problem in this.

Thanks

Kusuma

|||

First off, I'm not sure why you're constructing a dynamic select inside your procedure...the procedure should be the select statement, using any input parameters you defined.

But to solve the problem, you need to change

exec @.sqlstr

to

exec(@.sqlstr)

I'd rewrite the entire piece of code...

|||

This is a duplicate post.

Please see answer in your other posting.

|||

Use the following satement to execute the SP,

Code Snippet

Exec [GSU_Site_ReterieveActiveSitesOnSearch]' where GSUStatus.GSUStatusID=1and site.Sitename like''lakshmisite'' '

|||

Kusuma,

Instead passing the this value " where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' ", use:

' where GSUStatus.GSUStatusID=1 and site.Sitename like ''lakshmisite'''

Notice that I am using two apostrophes per each one inside the string.

As you can see, you are setting QUOTED_IDENTIFIER to on, when creating the sp, so anything enclosed by double quote will be interprete as an identifier (name of a column, table, etc.), so when you pass that value to the sp, it will look like

...

SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID +

" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "

and there is not such identifier in your db.

you can set QUOTED_IDENTIFIER to OFF, but I prefer to leave it as ON and use the other method to escape apostrophes.

AMB

|||

If you call it from any UI, the single quote will be automatically taken care by the providers/ADO classes. (since it is a parameter)

But when you test the sp, you have to use either escape sequence or as AMB sujest use the QUOTED_IDENTIFER OFF config.

|||

Thanks Mani :-)

Now it is working.

There were two problems. One

1)setQUOTED_IDENTIFIERON should be OFF

2)exec@.sqlstr should be exec(@.sqlstr)

Kusuma

|||

Hai Dalej,

Sorry for posting two times.

I need dynamic query for a searching -sitenames,Businessunit etc......... ( searching based on columns in a table)

Now the problem is solved by giving exec(@.sqlstr) instead of exec @.sqlstr.

Thanks for your help :-)

Kusuma

Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
Thanx
San
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>
|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:

> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>

Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
ThanxSan
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
--
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>