Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ishan_das
New Contributor

Unable to connect-Qliksense to Local MS SQL

I'm trying to fetch data from my local MS sql server and put it on Qliksense (on web, enterprise edition) via data load editor. But no go,

Attempted to create a new connection using OLE DB,

used provider "MS OLE DB provider for MS SQL",

data source as "Local Server Name"....test fails...

have tried windows integrated security as well as specific user name and password options.

Question?

is it possible to connect Qliksense on web to local MS SQL(desktop) ?

4 Replies
MVP
MVP

Re: Unable to connect-Qliksense to Local MS SQL

Yes definitely.

Did you try with only a single period - a dot as the Data Source? MSSQLSERVER is often the default name.

Did the MS SQL Server install as a named instance? Then the instance would have to be specified as <servername>\<instancename> or (local)\<instancename> or .\<instancename>

Here is a SQL script you can run in Microsoft SQL Server Managment Studio to determine some of this information:

set nocount on
Declare @key Varchar(100), @PortNumber varchar(20)

if charindex('\',CONVERT(char(20), SERVERPROPERTY('servername')),0) <>0 begin
  set @key = '
SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
end else begin
  set @key = '
SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
end

EXEC master..xp_regread @rootkey = '
HKEY_LOCAL_MACHINE', @key = @key,
  @value_name = '
Tcpport', @value = @PortNumber OUTPUT
SELECT CONVERT(char(20), SERVERPROPERTY('
servername')) ServerName,
  CONVERT(char(20), SERVERPROPERTY('
InstanceName')) InstanceName,
  CONVERT(char(20), SERVERPROPERTY('
MachineName')) as HostName,
  
convert(varchar(10), @PortNumber) PortNumber




origin of script: How do I connect to the default instance of SQL Server if a "named instance" parameter is required? ...




fhoebechan
New Contributor

Re: Unable to connect-Qliksense to Local MS SQL

Hi Peter

I was trying to connect to mssql - (localdb)\V11.0 and i tried running the sql code you provided with the server name and instance but the connection failed.

i tried

1. Servername\Instancename - 12345\LOCALDB\LOCALDB#12345

2. .\Instancename - .\LOCALDB#12345

3. (localdb)\Instancename - (localdb)\LOCALDB#12345

with windows integrated security and also specific username and password(WindowsNT)

but none of the connection successfully connected.

Hope that you are able to help.

Thanks.

Capture.JPG

Capture1.JPG

marterqlik
New Contributor III

Re: Unable to connect-Qliksense to Local MS SQL

nsetty
Contributor II

Re: Unable to connect-Qliksense to Local MS SQL

Can you try

oledb-connection.PNG

Community Browser