April 5, 2013 at 10:44 am
Hello,
My problem is that I do not know what to do from here. I am trying to find schedule conflicts. I used to have the data on a MySQL server and I was just using ASP.NET code to generate the conflicts list. Now one category of the data is coming from a different server, Oracle. I already own a MS SQL server that i am usually using for my apps.
What I did, is I created a stored procedure that uses a union all from 2 openquery select statements. That works and I can get all the data I need:
ALTER PROCEDURE [dbo].[getConflicts0]
@repsd varchar(50),
@reped varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
declare @sqltext varchar(max)
select @sqltext='
SELECT * FROM OPENQUERY(MySQL)
UNION ALL
select * from openquery(ORACLE)
'
exec (@sqltext)
END
My next step would be to get this data in a table that has 2 more columns, to identify the conflicts and pair them.
I understand I cannot use this inside a function.
Would you guys have any suggestion on how to proceed with this?
Thank you
April 5, 2013 at 11:35 am
if you create a permanent or temp table that has the shape of the data, you can insert into it;
a rough example, but it works on my side, assuming the proper tables and databases and stuff:
CREATE TABLE [dbo].[#tmp] (
[STATETBLKEY] INT NOT NULL,
[INDEXTBLKEY] INT NOT NULL,
[STATECODE] CHAR(2) NOT NULL,
[STATENAME] VARCHAR(50) NOT NULL,
[FIPS] CHAR(3) NULL)
declare @sqltext varchar(max)
select @sqltext='
SELECT * FROM OPENQUERY(My2005LinkedServer,''SELECT * FROM LHC.dbo.TBSTATE'')'
--insert into instead of just a select
INSERT INTO #tmp
exec (@sqltext)
Lowell
April 5, 2013 at 11:49 am
Thank you. It was exactly what i was trying right now. I have a question, how do I refer to the server I am on in the openquery? Is there such a thing as linked server local?
April 5, 2013 at 12:00 pm
adinica (4/5/2013)
Thank you. It was exactly what i was trying right now. I have a question, how do I refer to the server I am on in the openquery? Is there such a thing as linked server local?
well it's possible, but i'm not sure why you'd want to do it.
if you are already connected to the sql server itself, there's no real need to create a "Loopback" linked server to itself, is there?
you can query data directly already.
I've tested this, and it does work, but i think we need to figure out WHY you think you want to use a linked server?
in this case, my developer machine is "DEV223", and here's my loopback linked server to itself:
/****** Object: LinkedServer [MyLoopbackServer] Script Date: 04/05/2013 13:57:06 ******/
EXEC master.dbo.sp_addlinkedserver
@server = N'MyLoopbackServer',
@provider=N'SQLNCLI',
@datasrc=N'DEV223',
@catalog=N'SandBox'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MyLoopbackServer',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL
Lowell
April 5, 2013 at 12:08 pm
Oh, I see you were not suggesting I use the stored procedure but rather work in it further. I was thinking that the openquery in your example was using the stored procedure.
My idea was, ok, now I have the stored procedure, how do I use it in some other element.
Ok, thanks, I (think I) understand now. I will post later with my result or some other question. Thank you again.
April 5, 2013 at 12:22 pm
i'm not clear ont he question, i may be reading it too fast.
if the question is "how do I execute a stored procedure via OPENQUERY,a dn capture the results, the answer is the same:
you need a pre-exisitng table that will capture the results of the openquery.
here's anotehr example, where i execute sp_WHO on a diffferent SQL server, is that close to what you are asking?
--drop table #MYRESULTS
CREATE TABLE #MYRESULTS (
[SPID] CHAR (5) NULL,
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL,
[REQUESTID] INT NULL
)
declare @sqltext varchar(max)
select @sqltext='
SELECT * FROM OPENQUERY(My2005LinkedServer,''SET FMTONLY OFF; EXEC sp_WHO2'')'
INSERT INTO #MYRESULTS
exec (@sqltext)
Lowell
April 5, 2013 at 12:32 pm
Hi.
No, you were plenty helpful. I misunderstood your answer. I had this stored procedure that has a union with 2 openquery in it. One openquery goes to a MySQL server and the other goes to an Oracle server. I am running it from a MSSQL server. When I first read your first response I understood that you wanted me to implement that code and do an openquery on the stored procedure that I had. Only, I was already on the MS SQL server, so I assumed you wanted me to somehow create another linked server to the server I was on (MS SQL). After you answered (#2), I understood what you answered in the first place.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply