SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[10.0.158 BETA] - Retrieving DDL from an SQL Server (in SE)

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[10.0.158 BETA] - Retrieving DDL from an SQL Server (in SE)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1506
Country: Hungary

Post [10.0.158 BETA] - Retrieving DDL from an SQL Server (in SE) Reply with quote
I'm still unable to reliably retrieve table creation scripts using SA. If the DDL Extraction Utility setting in DB Options is set to Let SQL Assistant choose best method, clicking the Code from the popup or selecting the Show DDL in Database Explorer's table context menu I get an exception in the pane where the code should appear:

Quote:

/*
Failed getting DDL: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server GFSQL. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.RuntimePropertyInfo.GetValue(Object obj, Object[] index)
at SqlAssist.DDLLoader.GetProp(Object obj, String propName, Type[] types, Object[] pars)
at SqlAssist.DDLLoader.GetDDL(String databaseName, String schemaName, String objectType, String objectName)

-------------------------------

HINT: It looks like you may need to upgrade your local SQL Server client to resolve issues with SQL Management Objects. Search Microsoft site for "download Microsoft.SqlServer.SqlManagementObjects"

-------------------------------
*/


I suppose there's nothing wrong with SMO because if do the same for a stored procedure, its creation code is retrieved without any errors.

I remember this being a problem in the past than was never really solved, but I cannot find the relevant posts. I recall the root cause being the way how the part trying to request the script/object/code/whatsoever from the server made (tried to make) the connection to the server. As the exception shows
Quote:

Failed to connect to server GFSQL

it tries to connect to the server using the name the server reported back upon establishing the connection, instead of its IP (or the name associated with that IP in the hosts file), that is, the way the connection was originally made. Using the name would work if the server was on the same network as the computer running SA but that's not the case here. Actually, I'm connected to several servers, each having the name GFSQL, but each having different IPs.

I thought it might not even be possible to make that darned SMO connect to the server using a different method so I changed DDL Extraction Utility set to Using SQL scripts only. Retrieving for stored procedure keeps working, for tables the error message in the code preview pane changed to:
Quote:

/*
Cannot find source code for [dbo].[Adatblokk].
Check the "DDL Code (MSSQL)" query in SQL Assistant Options.

*/


I'm pretty much stuck with this here and still have to use my home-brew scripts and snippets to retrieve table DDLs. Sadly, those are not the most reliable either but at least do retrieve something.

Could you fix this, please, or provide a viable workaround? I remember a batch file that could be configured by hardwiring server name, user name and password but besides that obviously being a security issue, it's also painfully slow to edit that file over and over again. I have to admin/develop on dozens of servers and doing it this way would be slower than ditching SE and using SQL Server Management Studio instead, and I don't want to rant about it, but SSMS is, well, rubbish compared to SE, at least performance-wise.
Thu Aug 30, 2018 9:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6796

Post Reply with quote
When you assume that there is nothing wrong with SMO, you assume that procedural code is retrieved using SMO too, but that's not the case, it's a simple SELECT from sys.sql_modules. That's what "let SQL Assistant choose" option does. If the code can be retrieved from sql_modules, go for it, else use SMO to reverse-engineer it. As far as I can tell from the exception call stack there is an issue with connection that SMO cannot shallow somehow, don't know why. Maybe you need to add an alias to your Windows hosts file to help it connect to the correct IP (just in case... https://gist.github.com/zenorocha/18b10a14b2deb214dc4ce43a2d2e2992)
Fri Aug 31, 2018 6:03 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1506
Country: Hungary

Post Reply with quote
Then my assumptions were unfounded. This still refers to, though, why the SMO fails the connection. I'll try to explain it another way. Take a look at the screenshot:




The screenshot shows two names. #1 is used to connect to the SQL Server. This could either be a name or an IP, what you can see in the picture is actually an alias to the server's IP, stored in my hosts file.

#2 is displayed after the connection has been successfully established. I guess this the reported by the SQL Server itself, and that this is the name of the Windows machine the SQL Server resides on. This also could be used as #1 in case the two machines (the client with SA + SE on it and the one hosting the SQL Server) were on the same network, which is not the case here.

I don't know the exact details as they are hidden behind a benevolent shroud of mystery, but the relevant part is that the network the SQL Server is on is connected to mine through a VPN connection, and I cannot reach the SQL Server machine using its name. To make things even more complicated, there are several other networks from different clients, all connected through VPN, and all sporting SQL Servers with that very same name, GFSQL, so the name is ambiguous. But that makes no problem, the servers are connected to using their IP, which is unique (and they have their alias in my hosts file so that I can avoid memorizing tons of meaningless numbers).

As far as I can tell, the exception stack is pretty clear about what goes awry. SA/SE makes the connection using #1. SMO goes the other way around, and instead of using the same connection or establishing its own one using the same method, it is trying to make the connection using #2, which is doomed to fail. Now, I've got to admit, I've no idea (ehh, shame on me) how SMO works. Maybe it can only connect to the server using its name and using #1 might not even be an option for it, in which case this all pointless and I'm screwed :(
Fri Aug 31, 2018 7:47 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6796

Post Reply with quote
I think I got it first time. That's why I suggested to remap in the host files #2 to #1. I would try adding two entries, one for the machine name, whatever is returned by SA"s Context query and another for the IP address to remap it too. This way you can make the client to connect to whichever endpoint you want to send it too instead of endpoint it wants to connect to. SMO runs on the client side, this is just a kind of client side API for SQL Server Management .
Sat Sep 01, 2018 1:59 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1506
Country: Hungary

Post Reply with quote
No, no, no, you're missing essential details.

On one side:

#2 is what's returned by SA's Context query, namely, the machine name. I think that's clear to both of us (or I hope I get it correctly) so we can ignore that.

I cannot remap #2 to #1. Actually, I could if #1 was an IP but #1 is already an alias remapped to an IP. Regardless, we can ignore that too, because even that would not matter if I remapped #2 to the same IP and that could even work brilliantly...

Except , and I cannot ignore this one, that I have multiple servers that return that same server name on Context query. Meaning that I would have to remap multiple IPs to the same alias (already being mapped to other aliases as well), which not only makes no sense but is sadly not forbidden in hosts file. Instead of complaining about duplicates or asking "er, well, which one of the 14 GFSQL mapped in hosts do you want to connect exactly?" that darned thing simply returns the first match. This would require constant monitoring and editing the hosts file to check and assure that the aliases are pointing where they're supposed to. I can understand that this might not be an everyday scenario for all the developers and dbas out there but I hop these servers several times a day, sometimes in a single hour, and this is a crucial factor to me. I doubt I'm the only one, though I might be wrong on that one. Nevertheless, critical mistakes can be made this way.


On the other hand:

SMO runs on the client side (which was pretty much the only thing I knew about it), and as you stated, being a client-side API means that something is calling its interface, orders it to connect to the server and passes parameters that tell it how to connect to the server and what to do once connected. In my opinion, the problem is that this "something" tells it to use #2 instead of telling it to use #1. Making it use the same connection parameters as SA/SE (if that is possible at all, of course) would be a real fix to this issue. Messing with the hosts file is just a workaround, and a very bad and dangerous one, too.
Sat Sep 01, 2018 11:02 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6796

Post Reply with quote
Ok. I got it wrong. The default context query isn't helpful and that's the root cause of the issue as far as I can tell. We need to customize it somehow for your situation so that it returns a real endpoint or correct alias instead of non useful server name as reported by ServerProperty.

If SA can connect to the server, SMO should be able to connect too, we just need to feed it the correct server IP. Now the question is how to get the correct IP from server settings instead of the server name. Maybe SQL Server has a system function supporting just that, or from the performance views we can extract connection endpoints and their IPs. Maybe it can be somehow passed via custom connection properties in the saved connection profile. But in the end, what comes out of Context query is passed to SMO.


Last edited by SysOp on Mon Sep 03, 2018 3:01 pm; edited 1 time in total
Sat Sep 01, 2018 11:57 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1506
Country: Hungary

Post Reply with quote
What you say is interesting. I don't think SQL Server has system functions for this, and even if it has, VPN might add to the confusion. I mean because of it one might see another IP than the SQL Server thinks it is.

DB Queries use a bunch of different variables prefixed with a colon. Is there any that refers to the original connection property and could be used in this role? Or can anything be fetched from custom connection properties pushed into Context query?

As a last resort (and another workaround) I could still create a connection property table in master schema on the server and modify Context query to look for that table and use its data if it exists. That will still fail if the connection properties (in VPN) change or the server has no such auxiliary table defined and/or populated but then at least I'll know the reason and how to address it. Of course, this could still lead to catastrophic mistakes but on a way more easier path :)
Sat Sep 01, 2018 12:21 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1506
Country: Hungary

Post Reply with quote
gemisigo wrote:
What you say is interesting. I don't think SQL Server has system functions for this, and even if it has, VPN might add to the confusion. I mean because of it one might see another IP than the SQL Server thinks it is.


Actually, there's something, here:
Code:

SELECT dec.local_net_address FROM sys.dm_exec_connections AS dec


I'll explore this further. Might prove to be a solution.

EDIT:
okay, that's not a solution, the table/view is not accessible (or doesn't exist in 2k8 and earlier). However, the function CONNECTIONPROPERTY does. But even that has some side-effects. In theory,
Quote:

you only get IP addresses if you're connective via TCP/IP. If you're local and using Shared Memory then those attributes don't exist.


So I modified the Context. I imagine this works, using IP when you connect using IP, and using the servername otherwise. I hope. The new Context is :

Code:

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
or @@version like '%SQL Azure%'
   EXEC ('select
    ISNULL(CONNECTIONPROPERTY(''local_net_address''), SERVERPROPERTY(''ServerName'')),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(SCHEMA_NAME(), ''dbo'')')
else
   EXEC ('select
    SERVERPROPERTY(''ServerName''),
    SYSTEM_USER,
    DB_NAME(),
    ISNULL(USER_NAME(), ''dbo'')')


Of course, the party could still get ruined by VPN, but that's SA-unrelated, and I'll handle it myself. Thanks for the hints, it's great to see this feature finally working for me too.


EDIT2:
Yes, this seems to work properly. Let me ask though, why is SMO feeded the thing returned by the Context query instead of the connection properties SA uses to connect?
Mon Sep 03, 2018 3:13 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6796

Post Reply with quote
Quote:
why is SMO feeded the thing returned by the Context query instead of the connection properties SA uses to connect?


The code generating DDL is kind of abstracted from the connection. Moreover, the connection can be shared with the host application, for example, in case of SSMS, the only thing it knows about the connection is its pointer to the internal connection COM object or connection handle. It doesn't know what was entered in SSMS connection dialog. That's why part of the Context query is used to retrieve the server name SA is connected to. The same applies to other database types and editors. When it comes to the point of DDL generation, it doesn't have the details for connection parameters.

Here is what I found out about passing additional parameters via connection string after reading https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=netframework-4.7.2.
The only connection properties that I think can be changed safely without side effects are Application Name and Workstation ID. You can use them to pass additional string parameters, which you can make the connect query to grab using a subquery like

Code:
SELECT program_name AS appname, HOST_NAME AS wsname
FROM sys.dm_exec_sessions
WHERE session_id = @@spid


If you append to Application Name the alias of the server that you want SMO to connect to, and then extract it in the Context query, basically replace SERVERPROPERTY(''ServerName'') with your custom code to extract the alias name, you can return the alias name that you want SMO to connect to. Please see my screenshot below. Please let me know if you need any additional information.




Last edited by SysOp on Tue Sep 04, 2018 10:10 am; edited 1 time in total
Mon Sep 03, 2018 3:27 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1506
Country: Hungary

Post Reply with quote
I ended up with a rather ugly solution. I defined a table variable, filled it with (IP, alias) pairs, retrieved the alias by matching it with IP fetched from the server using CONNECTIONPROPERTY('local_net_address') and coalesced that one with the IP reported as second and ServerName as last. This means that I practically raped the Context query. Your solution is much more elegant and moves the tinkering where it should be done: to the connection properties. I'm going to adapt it to my needs and keep mine as a plan B. Or C.

Again, thank you very much for your help, it is much appreciated.

EDIT: I added a slight enhancement to this. Since sys.dm_exec_sessions is not available on versions 2k8 R2 (and prior), I'm going to use the function APP_NAME(), which luckily is.
Mon Sep 03, 2018 4:38 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6796

Post Reply with quote
Thank you. I'm glad we have found a workable solution for your network setup.
Tue Sep 04, 2018 10:11 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.