March 16, 2017 at 10:54 am
Hi,
I am sorry because I have duplicated this topic, Some one else tried to help me but I couldn't make it work
I have a script who retrieve data from cross apply table and insert it into a temp table, then I use this temp table in an inner join, my code works fine when I set the database name statically, but I need to put dynamic database names.
I know that I need to put my code inside quotes and call exec, but I don't know where I should put my quotes to make it work.
This is my initial code
DECLARE @sql varchar(max), @ssid varchar(max), @qtype int, @ApplicationID int, @DBname varchar(max)
set @ssid = 74
set @qtype = 14
-- @DBname will be the Database name that I will use instead of iQMPlatformDemo
SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )
--IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvalue
select * into #tblfullvalue from (
select
a.value as val,
qst.id as qst,
ca.cValues as fullval
FROM iQMPlatformDemo.[dbo].[Answers] as a
cross apply (select
stuff((select
',' + l.name
from
iQMPlatformDemo.dbo.Files l
inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
inner join iQMPlatformDemo.dbo.SurveySessions ss
on a.sessionId = ss.id
inner join iQMPlatformDemo.dbo.Questions as qst
on a.questionId = qst.id
where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
and ss.id = @ssid
and (helper is null or helper = '')
and a.value is not null ) as tab
select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
from iQMPlatformDemo.dbo.SurveySessions ss
inner join iQMPlatformDemo.dbo.Questions qst
on ss.surveyId = qst.surveyId
inner join iQMPlatformDemo.dbo.Answers ans
on ans.questionId = qst.id
inner join #tblfullvalue
on #tblfullvalue.val = ans.value
Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
and ans.sessionId = convert (varchar, @ssid)
drop table #tblfullvalue
Please can you help me to make it work.
thank you
March 16, 2017 at 12:17 pm
I recommend you create a procedure in the master db that can automatically run in the context of any db you need. Below is the code to create the proc. Note that the proc name must begin with sp_.
Here's the code to invoke the proc after it's created:
EXEC iQMPlatformDemo.dbo.sp_your_proc_name_here
EXEC someOtherDb.dbo.sp_your_proc_name_here
--or, more generically:
EXEC(@DBname + '.dbo.sp_your_proc_name_here')
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_your_proc_name_here
AS
SET NOCOUNT ON;
DECLARE @sql varchar(max), @ssid varchar(max),
@qtype int, @ApplicationID int
set @ssid = 74
set @qtype = 14
IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvalue
select * into #tblfullvalue from (
select
a.value as val,
qst.id as qst,
ca.cValues as fullval
FROM [dbo].[Answers] as a
cross apply (select
stuff((select
',' + l.name
from
dbo.Files l
inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
inner join dbo.SurveySessions ss
on a.sessionId = ss.id
inner join dbo.Questions as qst
on a.questionId = qst.id
where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
and ss.id = @ssid
and (helper is null or helper = '')
and a.value is not null ) as tab
select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
from dbo.SurveySessions ss
inner join dbo.Questions qst
on ss.surveyId = qst.surveyId
inner join dbo.Answers ans
on ans.questionId = qst.id
inner join #tblfullvalue
on #tblfullvalue.val = ans.value
Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
and ans.sessionId = convert (varchar, @ssid)
drop table #tblfullvalue
GO
EXEC sp_marksystemobject 'dbo.sp_your_proc_name_here'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 16, 2017 at 1:54 pm
ScottPletcher - Thursday, March 16, 2017 12:17 PMI recommend you create a procedure in the master db that can automatically run in the context of any db you need. Below is the code to create the proc. Note that the proc name must begin with sp_.Here's the code to invoke the proc after it's created:
EXEC iQMPlatformDemo.dbo.sp_your_proc_name_here
EXEC someOtherDb.dbo.sp_your_proc_name_here
--or, more generically:
EXEC(@DBname + '.dbo.sp_your_proc_name_here')
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_your_proc_name_here
AS
SET NOCOUNT ON;DECLARE @sql varchar(max), @ssid varchar(max),
@qtype int, @ApplicationID intset @ssid = 74
set @qtype = 14
IF OBJECT_ID('tempdb..#tblfullvalue') IS NOT NULL DROP TABLE #tblfullvalue
select * into #tblfullvalue from (
select
a.value as val,
qst.id as qst,
ca.cValues as fullval
FROM [dbo].[Answers] as a
cross apply (select
stuff((select
',' + l.name
from
dbo.Files l
inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
inner join dbo.SurveySessions ss
on a.sessionId = ss.id
inner join dbo.Questions as qst
on a.questionId = qst.id
where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
and ss.id = @ssid
and (helper is null or helper = '')
and a.value is not null ) as tabselect qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
from dbo.SurveySessions ss
inner join dbo.Questions qst
on ss.surveyId = qst.surveyId
inner join dbo.Answers ans
on ans.questionId = qst.id
inner join #tblfullvalue
on #tblfullvalue.val = ans.value
Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
and ans.sessionId = convert (varchar, @ssid)drop table #tblfullvalue
GO
EXEC sp_marksystemobject 'dbo.sp_your_proc_name_here'
Hi,
Thank you for the help,
The aim of this code was to create a stored procedure under iQMGlobal, even if I try to follow your idea when I try to run the script it says : Could not find stored procedure 'sp_marksystemobject'.
March 16, 2017 at 2:11 pm
I would try using sp_MSForEachDB
March 16, 2017 at 2:31 pm
Joe Torre - Thursday, March 16, 2017 2:11 PMI would try using sp_MSForEachDB
Is there another way to solve this issue without creating system stored procedure please , because the DBname is an input parameter?
March 16, 2017 at 2:43 pm
You don't need to create it, it still ships with SQL Server. You can use it to do what you want calling it from your code.
March 16, 2017 at 2:44 pm
Can you please explain again what you want to accomplish?
March 16, 2017 at 2:58 pm
Joe Torre - Thursday, March 16, 2017 2:44 PMCan you please explain again what you want to accomplish?
I want to create a stored procedure with 2 inputs : database name and another variable @ssid
in the first code I set @ssid = 74 and database name = iQMPlatformDemo
and it contains joins between the above database name and the master database name iQMGlobal
March 16, 2017 at 3:05 pm
What do you want the procedure to do? Insert some data in a table from tables in a set of databases on your SQL Server Instance? Please remember we know nothing about the databases on your server. I want to help but I'm not sure what you want.
March 16, 2017 at 3:16 pm
Joe Torre - Thursday, March 16, 2017 3:05 PMWhat do you want the procedure to do? Insert some data in a table from tables in a set of databases on your SQL Server Instance? Please remember we know nothing about the databases on your server. I want to help but I'm not sure what you want.
I have a master DB named iQMGlobal,
and other Databases : iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... with the same structure, it means the same tables and stored procedure...
In each Database we store the answers of the clients.
the creation of this stored procedurewith 2 input parameters : the Database name (example : iQMPlatformDemo ), and the sessionID = @ssid , allow me to fetch information from both iQMGlobal and iQMPlatformDemo for this sessionid
March 16, 2017 at 3:28 pm
benkraiemchedlia - Thursday, March 16, 2017 3:16 PMJoe Torre - Thursday, March 16, 2017 3:05 PMWhat do you want the procedure to do? Insert some data in a table from tables in a set of databases on your SQL Server Instance? Please remember we know nothing about the databases on your server. I want to help but I'm not sure what you want.I have a master DB named iQMGlobal,
and other Databases : iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... with the same structure, it means the same tables and stored procedure...
In each Database we store the answers of the clients.
the creation of this stored procedurewith 2 input parameters : the Database name (example : iQMPlatformDemo ), and the sessionID = @ssid , allow me to fetch information from both iQMGlobal and iQMPlatformDemo for this sessionid
Here is the begining of the stored procedure (the syntax is wrong)
USE [iQMGlobal]
GO
/****** Object: StoredProcedure [dbo].[sp_FetchSurveyFiles] Script Date: 3/16/2017 9:11:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_FetchSurveyFiles] @ApplicationID int, @ssid varchar(max),
AS
DECLARE @sql varchar(max), @qtype int, @DBname varchar(max)
set @qtype = 14
SET @DBname= (SELECT DatabaseName FROM [iQMGlobal].[dbo].[applications] WHERE id = @ApplicationID )
select * into #tblfullvalue from (
select
a.value as val,
qst.id as qst,
ca.cValues as fullval
FROM @DBname.[dbo].[Answers] as a
cross apply (select
stuff((select
',' + l.name
from
@DBname.dbo.Files l
inner join [iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
on l.id = ds.Item
order by
ds.ItemNumber
for xml path(''),type).value('.','varchar(max)'),1,1,'')) ca(cValues)
inner join @DBname.dbo.SurveySessions ss
on a.sessionId = ss.id
inner join @DBname.dbo.Questions as qst
on a.questionId = qst.id
where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype))
and ss.id = @ssid
and (helper is null or helper = '')
and a.value is not null ) as tab
select qst.id as qid, ss.id as ssid, ss.surveyId, #tblfullvalue.fullval as Response
from @DBname.dbo.SurveySessions ss
inner join @DBname .dbo.Questions qst
on ss.surveyId = qst.surveyId
inner join @DBname.dbo.Answers ans
on ans.questionId = qst.id
inner join #tblfullvalue
on #tblfullvalue.val = ans.value
Where qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), @qtype)) and ss.id=convert (varchar, @ssid)
and ans.sessionId = convert (varchar, @ssid)
drop table #tblfullvalue
March 16, 2017 at 3:31 pm
Are you inserting the data in from a query in each database, iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... into a table in iQMGlobal?
Try:
DECLARE
@sql1 nvarchar(100)='USE ?'
, @sql2 nvarchar(2000)='IF ''?'' IN (''iQMPlatformDemo'' , ''iQMPlatformDemo_1'', ''iQMPlatformDemo_2'')
INSERT INTO iQMGlobal.dbo.tblResults (...) SELECT ...'
, @replchar nchar(1)='?';
EXEC sys.sp_MSforeachdb @command1 = @sql1, @replacechar = @replchar, @command2 = @sql2;
March 16, 2017 at 3:57 pm
USE [iQMGlobal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_FetchSurveyFiles] @ApplicationID int, @ssid varchar(100)
AS
March 17, 2017 at 1:34 am
Joe Torre - Thursday, March 16, 2017 3:57 PMDECLARE @sql varchar(max), @qtype char(2) = 14, @DBname sysname;
USE [iQMGlobal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_FetchSurveyFiles] @ApplicationID int, @ssid varchar(100)
AS
SELECT
@DBname= DatabaseName
FROM [iQMGlobal].[dbo].[applications]
WHERE id = @ApplicationID;
SET @sql =
"WITH
fullvalue as
(
SELECT
a.value as val,
qst.id as qst,
ca.cValues as fullval
FROM
@DBname.[dbo].[Answers] as a
CROSS APPLY
(
SELECT
Stuff((select ',' + l.name
FROM
@DBname.dbo.Files l
INNER JOIN
[iQMGlobal].dbo.[DelimitedSplit8K](a.[value],',') ds
ON l.id = ds.Item
ORDER BY
ds.ItemNumber
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) ca(cValues)
INNER JOIN
"+@DBname+".dbo.SurveySessions ss ON a.sessionId = ss.id
INNER JOIN
"+@DBname+".dbo.Questions as qst ON a.questionId = qst.id
WHERE
qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), "+@qtype+"))
AND ss.id = "+@ssid+"
AND (helper is null or helper = '')
AND a.value is not null
)
)
SELECT
qst.id as qid
, ss.id as ssid
, ss.surveyId
, fullvalue.fullval as Response
FROM
"+@DBname+".dbo.SurveySessions ss
INNER JOIN
"+@DBname+".dbo.Questions qst ON ss.surveyId = qst.surveyId
INNER JOIN
"+@DBname+".dbo.Answers ans ON ans.questionId = qst.id
INNER JOIN
fullvalue ON fullvalue.val = ans.value
WHERE
qst.typeId=CONVERT(INT, CONVERT(VARCHAR(12), "+@qtype+"))
AND ss.id=convert (varchar, "+@ssid+")
AND ans.sessionId = convert (varchar, "+@ssid+")";
EXEC(@sql);
GO
I have an error message :
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '('.
March 17, 2017 at 1:38 am
Joe Torre - Thursday, March 16, 2017 3:31 PMAre you inserting the data in from a query in each database, iQMPlatformDemo , iQMPlatformDemo_1, iQMPlatformDemo_2... into a table in iQMGlobal?
Try:
DECLARE
@sql1 nvarchar(100)='USE ?'
, @sql2 nvarchar(2000)='IF ''?'' IN (''iQMPlatformDemo'' , ''iQMPlatformDemo_1'', ''iQMPlatformDemo_2'')
INSERT INTO iQMGlobal.dbo.tblResults (...) SELECT ...'
, @replchar nchar(1)='?';
EXEC sys.sp_MSforeachdb @command1 = @sql1, @replacechar = @replchar, @command2 = @sql2;
I am not inserting data, I want just to fetch data from Database. As I said the number of duplicated Databases maybe be greater then 3 (they may add iQMPlatformDemo_3, iQMPlatformDemo_4 .... )
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply