Dynamic sql for access database data retreival

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic sql for access database data retreival

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-11-25 : 02:32:55

HI, I need the file path/location to be a variable. Any ideas what I am ding wrong below? Thanks

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[SiteName_IncludeDeleted_RP] Script Date: 11/25/2010 17:06:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SiteName_IncludeDeleted_RP]

AS

Declare @Path nvarchar (200)
Declare @sql1 nvarchar(max)

SET @Path = (SELECT 'C:\Reports\Data\Customer_Site.MDB')
Print @Path

SET NOCOUNT ON

Declare @sql nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT DISTINCT SiteDetails.SiteName, SiteDetails.SiteID
FROM '+name+'.dbo.SiteDetails
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([SiteName] nvarchar(50), [SiteID] int)
Insert into #t

Exec(@sql)
--Print @sql

Select DISTINCT [SiteName], [SiteID]
from #t

Union All

Select @sql1=''
Select @sql1=@sql1+ 'SELECT SiteName, NULL
FROM OPENROWSET(
''Microsoft.Jet.OLEDB.4.0'',
' + @Path + ';
''admin'';
'''',
''Results'')
AS Customer_Site'

Drop table #t

SET NOCOUNT OFF

RETURN

Msg 141, Level 15, State 1, Procedure SiteName_IncludeDeleted_RP, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-25 : 03:44:03

I executed the statement posted by you and it doesn't give me any error.
Can you post the complete code of sp "SiteName_IncludeDeleted_RP"

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-11-25 : 18:43:22

I found this soulution;

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[SiteName_IncludeDeleted_RP] Script Date: 11/25/2010 17:06:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SiteName_IncludeDeleted_RP]

AS

Declare @Path nvarchar (200)
Declare @sql1 nvarchar(max)

SET @Path = (SELECT 'C:\Reports\Data\Customer_Site.MDB')
Print @Path

SET NOCOUNT ON

Declare @sql nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT DISTINCT SiteDetails.SiteName, SiteDetails.SiteID
FROM '+name+'.dbo.SiteDetails
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([SiteName] nvarchar(50), [SiteID] int)
Insert into #t

Exec(@sql)
Print @sql

Select @sql1=''
Select @sql1=@sql1+ 'SELECT SiteName, NULL
FROM OPENROWSET(
''Microsoft.Jet.OLEDB.4.0'',
''' + @Path + ''';
''admin'';
'''',
Results)
AS Customer_Site'

Insert into #t

Exec(@sql1)
Print @sql1

Select DISTINCT [SiteName], [SiteID]
from #t
ORDER BY SiteName

Drop table #t

SET NOCOUNT OFF

RETURN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources