SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MSSQL, Oracle, MySQL openquery...


MSSQL, Oracle, MySQL openquery...

Author
Message
adinica
adinica
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
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
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 40942
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
adinica
adinica
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
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?
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 40942
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
adinica
adinica
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
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.
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 40942
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
adinica
adinica
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search