Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) ?
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? ...
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.
Can you try
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.
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
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
Is there any update on this, I get the same issue.
I have tried all of the above, but nothing helps.