Unable to connect-Qliksense to Local MS SQL

[Anonymous]

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) ?

[petter]

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? ...

[Anonymous]

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.

[Anonymous]

see:

https://help.qlik.com/en-US/sense/September2018/Subsystems/PlanningQlikSenseDeployments/Content/Sens...

[nsetty]

Can you try

[KKmorgen]

This is very simple to achieve:

1. Open CMD or PS
2. Run: sqllocaldb start instancename (if your instance is not running)
3. Run: sqllocaldb info instancename
3. Copy the pipename in bold below
Instance pipe name: np:\\.\pipe\LOCALDB#EEA6B16B\tsql\query

4. Select the OLE DB Data source
5. Select the SQL Server Native Client 11.0 Provider or the OLE DB provider for SQL Server
6. Into the Datasource field paste in the named pipe from step 3
7. Select  Windows Integrated Security
8. Press Test
9.  Hopefully rejoice

Hope this helps everyone that is having difficulty connecting to (localdb) databases. 

[cbrammer1219]

I have tried this and I'm getting nothing when I do the sqllocaldb info.  I've tried all combinations and it returns LocalDB instance does not exist.

ServerName                                    InstanceName             HostName                       PortNumber
MARPURBUYER01\MSSQLS     MSSQLSERVER01        MARPURBUYER01        53204

[QNP_LG]

Any update on the above issue which you were facing? I am getting the same error. Not able to connect to any local SQL Server database. Any suggestions will be greatly appreciated. Thanks

[RandomITGuy]

Is there any update on this, I get the same issue. 

I have tried all of the above, but nothing helps.