create view with CTE SQL statement

Question

Post reply

create view with CTE SQL statement

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 14, 2016 at 1:49 am

#330183

Hi.
custom port number mentioned at one of the OLTP database.
I have successfully created Linked server with [Linkedserver,Portnumber]. but does not create view as below script.
Error Message
OLE DB provider "SQLNCLI10" for linked server "[Linkedserver,Portnumber" returned message "Deferred prepare could not be completed.".
Pls. suggest me and how to fix it?
create view Vw_DBSize as
SELECT *
FROM OPENQUERY (
[LinkedserverName,Portnumber],
'
WITH DataBase_Size (ServerName,DatabaseName,DatabaseSize,LogSize,TotalSize)
AS
-- Define the CTE query.
(
SELECT @@SERVERNAME ServerName,
db.name AS DatabaseName,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,
SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,
SUM(af.size / 128.0E) AS TotalSize
FROM [master].[dbo].[sysdatabases] AS db
INNER JOIN [master].[dbo].[sysaltfiles] AS af ON af.[dbid] = db.[dbid]
WHERE db.name NOT IN (''distribution'', ''Resource'', ''master'', ''tempdb'', ''model'', ''msdb'') -- System databases
AND db.name NOT IN (''AdventureWorks'', ''AdventureWorksDW'') -- Sample databases
GROUP BY db.name
)
-- Define the outer query referencing the name.
SELECT * FROM DataBase_Size
'
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you run just the select statement, does it return results?

Do you really have a linked server named "LinkedserverName,Portnumber"??? If so, why?

And what type of database is it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Remoteserver_Name -> target server which is created linked server

Port Number-> 96361 - this is custom port number was mentioned in target server at SQL 2012

SELECT *

FROM OPENQUERY (

[Remoteserver_Name,96361],

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The view has the correct information and you just obfuscated it for the forum?

If you run just the select statement, does it return results?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Yes, that view is correct SQL statement.. I have created many views with CTE Select statement in non custom port just like only Linked server name.

also SELECT query not working as below errors if mention custom port number in SELECT statement.

Msg 7202, Level 11, State 2, Procedure Vw_DBSize, Line 3

Could not find server 'targetserver' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Msg 7202, Level 11, State 2, Procedure Vw_DBSize, Line 3
Could not find server 'targetserver' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Is it there? Is the linked server created correctly?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Yes. it is there..

I checked - select * from sys.sysservers,

servername and datasource as below name

targetserver,96361

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

SQL Galaxy (9/14/2016)
Yes. it is there..
I checked - select * from sys.sysservers,
servername and datasource as below name
targetserver,96361

There's a linked server with a nondefault port on the instance I'm working with. It works - I can view the databases and tables. I scripted out the properties and gently obfuscated the result:

EXEC master.dbo.sp_addlinkedserver

@server=N'MyLinkedAccountsServer', -- this is the name you use in queries

@srvproduct=N'',

@provider=N'SQLNCLI11',

@datasrc=N'128.128.12.1,32768'

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'MyLinkedAccountsServer',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'Me',

@rmtpassword='MyPassword'

My best guess at this point is that you are confusing @server with @datasrc. Do the same - right-click on the linked-server entry in object explorer, choose [Script Linked Server as] then [DROP and CREATE to] and [New Query Editor window]. Compare your findings with mine.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

joeroshan SSChampion Points: 10377 More actions · Answer 8

SQL Galaxy (9/14/2016)
Yes, that view is correct SQL statement.. I have created many views with CTE Select statement in non custom port just like only Linked server name.
also SELECT query not working as below errors if mention custom port number in SELECT statement.
Msg 7202, Level 11, State 2, Procedure Vw_DBSize, Line 3
Could not find server 'targetserver' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Thanks

Your design is quite interesting. Are you having separate view for each of the target servers, if you have multiple servers to get the space?

Assuming you created a linked server with name [targetserver,portnumber]

and created a view with openquery calling [targetserver,portnumber], it should work provided the security is defined properly

If the linked server not created or not accessible, the error message should be

Could not find server 'targetserver,portnumber' in sys.servers.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 9

SQL Galaxy (9/14/2016)
Yes, that view is correct SQL statement.. I have created many views with CTE Select statement in non custom port just like only Linked server name.
also SELECT query not working as below errors if mention custom port number in SELECT statement.
Msg 7202, Level 11, State 2, Procedure Vw_DBSize, Line 3
Could not find server 'targetserver' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Thanks

Have you restored a database[/url] recently?

Pinal Dave
Well, this is quite a popular error one receive when they attempt to restore database containing references of the linked server. The solution is to create a link server and restore database. Here is the quick script which can fix your error.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 10

Dropped Existing Linked server. then view has been created & working as per Mr. ChrisM suggested and scripts.

Thank you all..

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 11

Glad you have it working. You might want to tag Chris's post as having worked for you.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.