#SQL Server OpenQuery

[thedbahub]

Navigating SQL Server Data Integration: Linked Servers vs. OpenQuery

In today’s data-driven world, professionals tasked with managing and analyzing data are often confronted with the dilemma of choosing the most efficient and effective method for querying and integrating data across different servers. Two commonly employed techniques in SQL Server for such purposes are Linked Servers and OpenQuery. This blog article delves into the practical aspects of both,…

[ryadel]

Come creare un Linked Server tra SQL Server e MySQL

Avete mai avuto la necessità di accedere ai dati di un server MySQL da una istanza SQL Server? Ovviamente è possibile farlo utilizzando le tante funzionalità di export e import previste dai due sistemi, ma in questo caso intendiamo proprio la possibilità di effettuare delle vere e proprie query in tempo reale, così da poter utilizzare i dati presenti sul server MySQL all'interno di JOIN, MERGE e altri comandi similari. In questo articolo cercheremo di spiegare come è possibile farlo creando un apposito Linked Server (Server Collegato, nella traduzione italiana), una funzionalità di SQL Server che può essere utilizzata per accedere ad altre sorgenti di dati attraverso una serie di provider disponibili: nello specifico, vedremo come è possibile creare un Linked Server che punti a una istanza MySQL ubicata su una macchina accessibile tramite LAN.

Installare il MySQL Connector

La prima cosa da fare è scaricare il MySQL Connector per ODBC e installarlo sulla macchina che ospita l'istanza SQL Server sulla quale vogliamo creare il Linked Server. Questa operazione è fondamentale per rendere il server in grado di potersi connettere a MySQL. L'ultima versione del MySQL Connector per ODBC può essere scaricata al seguente indirizzo: https://dev.mysql.com/downloads/connector/odbc/ Assicuratevi di scaricare la versione adatta all'architettura della macchina (x86 o x64). Inoltre, potrebbe essere necessario installare l'ultima versione dei seguenti bundle: Microsoft Data Access Components (MDAC) (ad oggi 2.8 SP1), scaricabile a questo indirizzo. Microsoft Visual C++ Redistributable Package (ad oggi 2015-2019), scaricabile a questo indirizzo. Una volta installato il connector, sarà possibile creare il Linked Server.

Creare il Linked Server

Il Linked Server può essere creato in due modi: Aggiungendo un Data Source dedicato tramite lo strumento ODBC Data Source presente nel Pannello di Controllo di Windows. Senza aggiungere un Data Source dedicato, ovvero configurando le impostazioni di connessione direttamente all'interno del Linked Server. La prima modalità è indubbiamente più laboriosa, ma da un punto di vista di sicurezza è preferibile in quanto, come avremo modo di vedere tra poco, consente di evitare la presenza della connection string in chiaro, evitando quindi di dover esporre le credenziali di accesso a MySQL. Con Data Source dedicato Per creare il Linked Server mediante l'aggiunta di un Data Source dedicato, eseguire le seguenti operazioni: Navigare all'interno del Pannello di Controllo > Strumenti di amministrazione, quindi lanciate lo strumento Origini Dati ODBC (32 bit o 64 bit a seconda dell'architettura della macchina). Selezionare la tab DNS di sistema, quindi fare click su Aggiungi per aggiungere un nuovo Data Source. Nella finestra Crea nuova origine dati che si aprirà, selezionare il Driver MySQL Unicode, come illustrato nella screenshot seguente, quindi fare click su Fine.

Nella finestra successiva sarà possibile configurare il Data Source inserendo il nome, una breve descrizione, e ovviamente i dati di connessione, comprensivi di username, password e database predefinito. La screenshot seguente mostra un esempio di compilazione per la connessione a un database installato sulla porta MySQL di default raggiungibile tramite un dato indirizzo di rete locale.

IMPORTANTE: Prendete nota del Data Source Name che sceglierete, in quanto dovrete utilizzarlo più avanti. Subito dopo aver compilato i parametri del caso, sarà possibile verificare la connessione al server MySQL facendo click sul pulsante Test: inutile dire che, affinché il test funzioni, è necessario che il server risulti accessibile, quindi occorrerà configurare l'eventuale firewall nel modo opportuno. Una volta creato il Data Source, occorrerà creare il Linked Server vero e proprio. Il modo migliore per farlo è utilizzando SQL Server Management Studio (SSMS), lo strumento gratuito fornito da Microsoft per gestire istanze SQL Server locali e/o remote: l'ultima versione (ad oggi la 18.5) è disponibile a questo indirizzo. Una volta scaricato e installato SSMS, utilizzatelo per connettetevi all'istanza SQL Server sulla quale desiderate creare il Linked Server. Dalla finestra denominata Object Explorer espandete il nodo Server Objects, quindi fate click con il tasto destro su Linked Servers e selezionate New Linked Server, come nella screenshot seguente:

Nella schermata che si aprirà sarà possibile impostare i parametri del linked server nel seguente modo: Linked Server: il nome da assegnare al Linked Server, ovvero quello che dovrete utilizzare all'interno delle varie query. Server Type: il tipo di server a cui ci si connette: poiché intendiamo connetterci a un Server MySQL, selezionate Other data source. Provider: Il provider da utilizzare per la connessione: poiché il connector per MySQL funziona tramite ODBC, selezionare Microsoft OLE DB Provider for ODBC Drivers. Product name: Inserite MySQL. Data Source: inserite il Data Source Name relativo al Data Source creato in precedenza (nell'esempio illustrato nella screenshot di poco fa il nome è "Mantis Bug Tracker").

Una volta configurati i parametri generali, spostatevi nella tab Security, dove sarà necessario: Selezionare l'opzione Be made using this security context. Inserire nuovamente le credenziali di autenticazione per connettersi al Database MySQL.

Fatto questo, sarà possibile fare click sul pulsante OK e completare la creazione del Linked Server. Senza Data Source dedicato Nel caso in cui si preferisse creare il Linked Server senza un Data Source dedicato, sarà possibile farlo direttamente dello strumento SSMS, evitando quindi tutta la parte legata al Pannello di Controllo e allo strumento ODBC Data Source, svolgendo le seguenti operazioni: Lanciare SQL Server Management Studio (SSMS). Collegarsi al Server SQL su cui si desidera creare il Linked Server. Dalla finestra Object Explorer espandere il nodo Server Objects, quindi click con il tasto destro su Linked Servers e selezionare New Linked Server. Creare il Linked Server come illustrato nella screenshot seguente:

Come si può vedere, stavolta anziché specificare il nome del Data Source (che non abbiamo creato) è necessario indicare la Connection String da utilizzare per connettersi al Database, che dovrà avere questo formato: Driver={MySQL ODBC 8.0 Unicode Driver};DATABASE=database_name;OPTION=134217728;PWD=user_password;UID=user_identification;SERVER=server_name Una volta inserita la Connection String, passare alla tab Security e compilarla nello stesso modo indicato in precedenza, ovvero: selezionando l'opzione Be made using this security context. Inserendo le credenziali di autenticazione per connettersi al Database MySQL.

Utilizzare il Linked Server

Una volta creato il Linked Server è possibile interrogarlo nei seguenti modi: Tramite la cosiddetta "three dot notation", ovvero la sintassi utilizzata da SQL Server per effettuare query sui server collegati: SELECT * FROM ...; Tramite la funzione OPENQUERY, utilizzabile nel seguente modo: SELECT * FROM OPENQUERY(, 'SELECT * FROM mantis.mantis_bug_table'); In entrambi i casi, se i passaggi illustrati saranno stati svolti correttamente, riusciremo a visualizzare i dati presenti nella tabella MySQL direttamente da SQL Server. La differenza principale tra le due modalità illustrate è che la three dot notation prevede l'utilizzo della sintassi T-SQL, mentre la funzione OPENQUERY consente l'utilizzo della sintassi tipica di MySQL, con tutte le funzioni supportate dal server MySQL collegato: sarà quindi possibile utilizzare funzionalità specifiche di quel database, come ad esempio l'utilissimo comando LIMIT.

Conclusione

Per il momento è tutto: ci auguriamo che questa guida possa essere d'aiuto a tutti quegli sviluppatori e sistemisti alla ricerca di un modo sicuro ed efficace per connettere questi due utilissimi DBMS. Alla prossima!   Read the full article

[ec25393]

Oracle Combination with Microsoft CRM-- introduction for designer

Oracle has big variety of setup around the world and Microsoft Business Solutions CRM is acquiring its market share on the CRM market-- mainly for mid-size and little business. In some cases we see the scenario when big business has Oracle-based company system-- imaging you deliver your items worldwide and you do it in Oracle custom-made system for 10 years. In this case - even if you are trying to find spending plan CRM service-- you may still require combination in between Oracle and Microsoft CRM, no matter the reality that these are originating from various software application worlds: Microsoft and Java worlds. 

 If you are designer-- you must most likely be notified about 2 options: 

 Microsoft SQL Linked Server method-- utilizing ODBC motorist to Oracle-- produce connected server in MS SQL Server 2000/7.0 and after that utilize OPENQUERY, OPENROWSET method to gain access to Oracle information. You can develop cross-platform Transact SQL Stored treatments or views to facilitate your advancement 

 Oracle Transparent Gateways and Generic Connectivity-- these 2 provide you manage from Oracle side. More fascinating is 2nd item - Oracle Transparent Gateways. 

 Presently Oracle Transparent Gateways are offered for the following platforms: 

 Oracle Transparent Gateway for Informix offered on Solaris, HP/UX 

 Oracle Transparent Gateway for MS SQL Server readily available on NT. 

 Oracle Transparent Gateway for Sybase offered on Solaris, HP/UX, NT, AIX, Tru64 

 Oracle Transparent Gateway for Ingres readily available on Solaris, HP/UX 

 Oracle Transparent Gateway for Teradata readily available on Solaris, NT, HP/UX 

 Oracle Transparent Gateway for RDB readily available on Alpha OpenVMS 

 Oracle Transparent Gateway for RMS offered on Alpha OpenVMS 

 The downside is the reality that these items are not offered for all platforms and priced fairly high. 

 Pleased establishing and developing! If you would like us to do the task, offer as a call 1-630.961.5918 or 1-866.528.0577 help@albaspectrum.com

[siva3155]

300+ TOP T-SQL Interview Questions and Answers

T-SQL Interview Questions for freshers experienced :-

1. What is T-SQL? T-SQL stands for Transact Structured Query Language. It is an extension of SQL functionality supported by Microsoft SQL Server and Sybase ASE. 2. What is the difference between SQL and T-SQL? The difference between SQL and TSQL is that SQL is a query language to operate on sets, while TSQL is a proprietary procedural language used by MS SQL Server. Also, T-SQL has a different implementation of DELETE and UPDATE than SQL. 3. How tsql statements can be written and submitted to the Database engine? Tsql statements can be written and submitted to the Database engine in following ways, By using the SQLcmd Utility By using the SQL Server Management Studio By connecting from an application that you create 4. What is “GO” in T-SQL? “GO” is not a Transact-SQL statement but a batch separator. It is a command identified by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities read “GO” as a signal that they should send the current batch of TSQL statements to an instance of SQL Server. 5. What is the difference between TRUNCATE and DELETE statement? The difference between TRUNCATE and DELETE statement is that, TRUNCATE is used for unconditional removal of data records from Tables. Truncate Operations are not logged. DELETE is used for conditional removal of data records from Tables. These operations are logged. 6. How does a local variable is defined using T-SQL? A local variable is defined using TSQL by using statement “DECLARE” and the name of the local variable should begin with “@” sign as the first character of its name. For example, integer CNT we will define local variable as, DECLARE @CNT INT 7. What does the T-SQL command IDENT_CURRENT does? The TSQL command IDENT_CURRENT returns the last identity value produced for a specified table or view. The last identity value created can be for any session and any scope. 8. What does the T-SQL command IDENT_INCR does? TSQL command IDENT_INCR returns the increment value mentioned during the formation of an identity column in a table or view that has an identity column. 9. Explain if it is possible to import data directly from T-SQL commands without using SQL Server Integration Services? If yes, what are the commands? Yes, it is possible to import data directly from T-SQL commands without using SQL Server Integration Services. These commands include, BCP OpenRowSet Bulk Insert OPENQUERY OPENDATASOURCE Linked Servers 10. What is sub-query? A sub-query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. A sub-query can be used with the statements like Update, select, delete and insert with the operators like =, >, =, Read the full article

[igorjuniozoi89]

Crystal Reports-- Heterogeneous databases

Crystal Reports is rather universal reporting tool, which suggests that it does not have unique setup to be utilized for something particular, as in the case of monetary reporting tools. Crystal Reports is the most versatile tool on the market and it has market acknowledgment. Several software application suppliers use Crystal Reports as the primary reporting tool: Microsoft, Best Software, Oracle to call a couple of. 

 In some cases designers attempt to utilize Crystal straight to pull information from several database platforms - we comprehend the requirement to offer you extra ideas to increase Crystal report versatility. Typical case when Crystal report need to pull information from numerous database platforms. Presuming that you are developing Crystal Reports on Windows platform - we advise you to utilize Microsoft SQL Server as a database gain access to tool. 

 1. Usage Crystal when you have your question tuned-- regardless how excellent Crystal Reports is - it is reporting tool - not querying environment. Tune your question initially and make sure that you are pulling the best information. 

 2. Usage SQL Server Linked Servers. Usage expert tool, such as Microsoft SQL Server, where you can produce connected servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2, filePro, Fox Pro, FileMaker Pro and other platforms. 

 3. Usage Distributed Queries-- when it comes to SQL Server - research study the possibilities with OPENQUERY, OPENROWSET stipulations to link to non-SQL Server connected servers. 

 4. Produce Stored Procedures - when you require specifications to be moved to the SQL declaration - you require saved treatment. You require saved proc if temperature tables are needed to do intermediate task in your SQL inquiry. 

 5. Report Flexibility-- when you have actually report based upon the SQL view or kept proc - if you require to customize the information inquiry - you do not need to alter the report - you alter your SQL view/ saved treatment. 

 6. Develop SQL Query to penetrate the information-- once again - we constantly advise tuning your question and see that you are getting sufficient outcomes-- in any case-- Crystal Report is simply a good tool to reveal the outcomes of your inquiry. 

 Pleased personalizing, querying and creating! if you desire us to do the task - provide us a call 1-630-961-5918 or 1-866-528-0577! help@albaspectrum.com

[brendaasilva1324-blog]

Crystal Reports - Geterogeneus databases

Crystal Reports is the most versatile tool on the marketplace and it has market acknowledgment. Numerous software application suppliers use Crystal Reports as the primary reporting tool: Microsoft, Best Software, Oracle among others. 

 Often designers attempt to utilize Crystal straight to pull information from numerous database platforms - we comprehend the requirement to provide you extra suggestions to increase Crystal report versatility. Typical case when Crystal report need to pull information from numerous database platforms. Presuming that you are producing Crystal Reports on Windows platform - we suggest you to utilize Microsoft SQL Server as a database gain access to tool. 

 1. Usage Crystal when you have your question tuned-- regardless how excellent Crystal Reports is - it is reporting tool - not querying environment. Tune your inquiry initially and make sure that you are pulling the best information. 

 2. Usage SQL Server Linked Servers. Usage expert tool, such as Microsoft SQL Server, where you can develop connected servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 and other platforms. 

 3. Usage Distributed Queries-- when it comes to SQL Server - research study the possibilities with OPENQUERY, OPENROWSET provisions to link to non-SQL Server connected servers. 

 4. Produce Stored Procedures - when you require specifications to be moved to the SQL declaration - you require saved treatment. You require kept proc if temperature tables are needed to do intermediate task in your SQL inquiry. 

 5. Report Flexibility-- when you have actually report based upon the SQL view or kept proc - if you require to customize the information inquiry - you do not need to alter the report - you alter your SQL view/ kept treatment. 

 6. Develop SQL Query to penetrate the information-- once again - we constantly suggest tuning your inquiry and see that you are getting sufficient outcomes-- in any case-- Crystal Report is simply a great tool to reveal the outcomes of your question. 

 Delighted personalizing, querying and creating! if you desire us to do the task - offer us a call 1-866-528-0577! help@albaspectrum.com

[webmasterdunyasi-club]

Crystal Reports - Geterogeneus databases

Crystal Reports is the most versatile tool on the marketplace and it has market acknowledgment. Numerous software application suppliers use Crystal Reports as the primary reporting tool: Microsoft, Best Software, Oracle among others. 

 Often designers attempt to utilize Crystal straight to pull information from several database platforms - we comprehend the requirement to offer you extra pointers to increase Crystal report versatility. Typical case when Crystal report must pull information from several database platforms. Presuming that you are producing Crystal Reports on Windows platform - we advise you to utilize Microsoft SQL Server as a database gain access to tool. 

 1. Usage Crystal when you have your question tuned-- regardless how great Crystal Reports is - it is reporting tool - not querying environment. Tune your inquiry initially and make sure that you are pulling the best information. 

 2. Usage SQL Server Linked Servers. Usage expert tool, such as Microsoft SQL Server, where you can develop connected servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 and other platforms. 

 3. Usage Distributed Queries-- when it comes to SQL Server - research study the possibilities with OPENQUERY, OPENROWSET provisions to link to non-SQL Server connected servers. 

 4. Develop Stored Procedures - when you require criteria to be moved to the SQL declaration - you require kept treatment. You require saved proc if temperature tables are needed to do intermediate task in your SQL question. 

 5. Report Flexibility-- when you have actually report based upon the SQL view or saved proc - if you require to customize the information inquiry - you do not need to alter the report - you alter your SQL view/ kept treatment. 

 6. Develop SQL Query to penetrate the information-- once again - we constantly advise tuning your question and see that you are getting sufficient outcomes-- in any case-- Crystal Report is simply a good tool to reveal the outcomes of your inquiry. 

 Pleased personalizing, querying and developing! if you desire us to do the task - provide us a call 1-866-528-0577! help@albaspectrum.com

[sanchesbruninha543-blog]

Crystal Reports - Microsoft SQL Server

Microsoft SQL Server is the leader for economical and middle size SQL services. Numerous software application suppliers use Crystal Reports as the primary reporting tool: Microsoft, Best Software, Oracle to call a couple of. 

 Often designers attempt to utilize Crystal straight to pull information from numerous database platforms - we comprehend the requirement to offer you extra pointers to increase Crystal report versatility. Typical case when Crystal report need to pull information from several database platforms. Presuming that you are developing Crystal Reports on Windows platform - we advise you to utilize Microsoft SQL Server as a database gain access to tool. 

 1. Usage Crystal when you have your question tuned-- regardless how excellent Crystal Reports is - it is reporting tool - not querying environment. Tune your question initially and make certain that you are pulling the ideal information. 

 2. Usage SQL Server Linked Servers. Usage expert tool, such as Microsoft SQL Server, where you can produce connected servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 and other platforms. 

 3. Usage Distributed Queries-- when it comes to SQL Server - research study the possibilities with OPENQUERY, OPENROWSET stipulations to link to non-SQL Server connected servers. 

 4. Produce Stored Procedures - when you require criteria to be moved to the SQL declaration - you require saved treatment. You require kept proc if temperature tables are needed to do intermediate task in your SQL question. 

 5. Report Flexibility-- when you have actually report based upon the SQL view or saved proc - if you require to customize the information question - you do not need to alter the report - you alter your SQL view/ saved treatment. 

 6. Produce SQL Query to penetrate the information-- once again - we constantly suggest tuning your question and see that you are getting appropriate outcomes-- in any case-- Crystal Report is simply a good tool to reveal the outcomes of your question. 

 Delighted personalizing, querying and developing! if you desire us to do the task - provide us a call 1-866-528-0577! help@albaspectrum.com

[sqljoker]

Tweeted

OPENQUERY UPDATE problem with MySQL as linked server https://t.co/3sKFgAb4Gy via SSC

— SQL Joker (@sql_joker) May 10, 2018

[brendaasilva1324-blog]

Crystal Reports - Geterogeneus databases

Crystal Reports is the most versatile tool on the marketplace and it has market acknowledgment. Numerous software application suppliers use Crystal Reports as the primary reporting tool: Microsoft, Best Software, Oracle among others. 

 Often designers attempt to utilize Crystal straight to pull information from several database platforms - we comprehend the requirement to provide you extra pointers to increase Crystal report versatility. Typical case when Crystal report ought to pull information from numerous database platforms. Presuming that you are developing Crystal Reports on Windows platform - we advise you to utilize Microsoft SQL Server as a database gain access to tool. 

 1. Usage Crystal when you have your question tuned-- regardless how great Crystal Reports is - it is reporting tool - not querying environment. Tune your question initially and make certain that you are pulling the best information. 

 2. Usage SQL Server Linked Servers. Usage expert tool, such as Microsoft SQL Server, where you can produce connected servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 and other platforms. 

 3. Usage Distributed Queries-- when it comes to SQL Server - research study the possibilities with OPENQUERY, OPENROWSET provisions to link to non-SQL Server connected servers. 

 4. Produce Stored Procedures - when you require criteria to be moved to the SQL declaration - you require saved treatment. You require kept proc if temperature tables are needed to do intermediate task in your SQL question. 

 5. Report Flexibility-- when you have actually report based upon the SQL view or saved proc - if you require to customize the information inquiry - you do not need to alter the report - you alter your SQL view/ saved treatment. 

 6. Produce SQL Query to penetrate the information-- once again - we constantly advise tuning your question and see that you are getting sufficient outcomes-- in any case-- Crystal Report is simply a good tool to reveal the outcomes of your question. 

 Pleased personalizing, querying and developing! if you desire us to do the task - provide us a call 1-866-528-0577! help@albaspectrum.com

[sqljoker]

Tweeted

RT @pinaldave: Quickest Way to Add LoopBack Linked Server for OpenQuery https://t.co/FPSYLX0Uxe

— SQL Joker (@sql_joker) January 2, 2018

[sqljoker]

Tweeted

RT @pinaldave: Quickest Way to Add LoopBack Linked Server for OpenQuery https://t.co/lQWSlvUF2c

— SQL Joker (@sql_joker) July 26, 2017

[sqljoker]

Tweeted

#SqlServerHelp - SQL Server 2008 R2 slow query performance to Oracle 11g using Openquery https://t.co/VwTHGTZsKo via SSC

— SQL Joker (@sql_joker) June 18, 2017

[sqljoker]

Tweeted

RT @pinaldave: Quickest Way to Add LoopBack Linked Server for OpenQuery https://t.co/vtHOKaE7rO

— SQL Joker (@sql_joker) June 14, 2017

[sqljoker]

Tweeted

RT @pinaldave: #SQL SQL SERVER – Quickest Way to Add LoopBack Linked Server for OpenQuery https://t.co/L2UgFadYvQ

— SQL Joker (@sql_joker) June 10, 2017