Cannot Remotely Access SQL Server Standard Named Instance

Good Day,

I am creating a SQL Named instance as a testing environment. This instance is on the same physical box as my Development environment, both are SQL 2005 standard edition. From the server in Management Studio, I can load, and interact with both instances. From a remote connection (e.g., my pc) I cannot access the named instance. I am getting the following:

Connect to Server

X Cannot connect to <server>\<named instance>

Additional Information

An error has occured while establishing a connection to the server. When connecting to SQL Sever 2006, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I have checked, and rechecked the server settings for this named instance, and remote connections are set to "allow" I have enabled TCPIP and Named Pipes prototocals, and have ensured that my firewall is allowing the "listenting port" for the named instance, and have even tried turning off my XP firewall during testing.

I am sure that I have probably missed something, and have searched the community but only have been able to find resolutions that I have already tried. Is there more?

Thanks in advance for any help and guidance you can provide.

hi mt,

please be guided by this article

http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=807492&SiteID=1

regards,

joey

|||" error: 26 - Error

Locating Server/Instance Specified", it means that it's trying to

locate the SQL Server using SSRP but could not get contact with SQL

Browser. So, please make sure your SQL Browser is running.

|||Yes, that is absolutely it. I did not set up the Browser Severvice to run. Thank you.

Cannot process the object "SET FMTONLY ON EXEC DBName..StoredProc". The OLE DB provide

I am getting an error when creating MyView:
Cannot process the object "SET FMTONLY ON EXEC DBName..StoredProc". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

MyView is accessing a stored procedure as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create VIEW [dbo].[MyView]
AS
SELECT * FROM
OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=Yes',
'SET FMTONLY ON EXEC DBName..StoredProc' )
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

StoredProc has a dynamically created synonym to avoid conflicts as follows:

declare @.DB as varchar(25)
SET @.DB = db_name()
DECLARE @.TblName as varchar(50)
DECLARE @.cx as varchar(100)
SET @.TblName = 'MyFunction'
declare @.DySynName as varchar(200)
declare @.intI as int

SELECT @.DySynName = 'dsicx' + @.TableName + '_' + system_user + '_' + CONVERT(char(12), GETDATE(), 14)

- The following while loop strips off the colon
SELECT @.intI = charindex(':',@.DySynName)
WHILE @.intI > 1
BEGIN
SELECT @.DySynName = substring(@.DySynName,1,@.intI - 1) +
substring(@.DySynName,@.intI + 1, LEN(@.DySynName)-@.intI )
SELECT @.intI = charindex(':',@.DySynName)
END

SET @.cx= substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

EXEC ('CREATE SYNONYM '+@.DySynName +' FOR '+@.cx+'..'+@.TblName+'

select *
from '+@.DySynName +'('''+@.DB+''', 5, 0)

drop synonym '+@.DySynName )

The StoredProc procedure works fine on it's own, and the View works fine if I use a static name instead of @.DySynName when creating, using and dropping the synonym.
MyView will work with the StoredProc procedure if I use @.DySynName for the syonym but I have to hard code the login and password in the OPENROWSET function instead of using Trusted_Connection=Yes, since multiple users will be accessing MyView a hard coded login and password will not suffice.

Is there something I can use other than Trusted_Connection=Yes that will allow me to use the @.DySynName variable for my synonym? Any ideas would be greatly appreciated, please keep in mind that I cannot exclude the dynamic synonym name and I cannot use temporary tables or create and delete the view upon use because of the load on the system.

Use following statements, when you create View:

Code Snippet

CREATE VIEW [dbo].[MyView]

AS

SELECT * FROM

OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=Yes',

'SET FMTONLY ON; EXEC DBName..StoredProc' )

GO

I just add semicolon and SQL Server execute "SET FMTONLY ON; EXEC DBName..StoredProc" as two statements

|||Still no luck, thanks for trying though.

Cannot Open Database. Login Failed.

I am a newbie in sql server and i am creating a web application using VB.NET

I have a button that when i click it will open the database. Heres the code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnect As New SqlConnection("integrated security=SSPI;data source=(local)\SQLEXPRESS;persist security info=False;initial catalog = CorporateDB;")

sqlConnect.Open()

End Sub

the problem is when i clicked the button an error will show:

Cannot open database "CorporateDB" requested by the login. The login failed. Login failed for user 'TRANSLTR\ASPNET'.

The CorporateDB is the database. And i don't know where did i got this user 'TRANSLTR/ASPNET' My computer name is Transltr but not 'TRANSLTR/ASPNET'.

I tried using this connection string in creating windows application in VS.NET. It works fine. What did i missed here. Please i need your help.

ASPNET = Windows Authentication. You need to configure a generic SQL Authentication or use your own. Either way you have to change your connection string to the following format:

Data Source=ServerName;Initial Catalog=DataBaseName3;UId=UserLoginName;Password=Password;Trusted_Connection=False"

You can also use impersonation in the your web.config file. It's up to you.

Adamus

|||Can you show me the steps on:

1. How can i configure a generic SQL Authentication

2. using inpersonation web.config

I apologize for all this trouble. I'm still a newbie in ms sql. Thanks for replying.
|||

Just use the connection string I've posted and put the login and password you use to log into SQL. If you use Windows Authentication, add this line to your web.config file:

<system.web>
<identity impersonate="true" userName="WindowsDomain\YourUserName" password="YourPassword" />
</system.web>

Adamus

sql

Cannot have two flat file destinations in a package (possible bug)?

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

You can only have one data flow per data flow task.|||You can have more than one Data Flow Task per package and multiple Data Connections. Try that way. Two Data Connections and two Data Flows Task.|||

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Are you using the same Flat File Connection Manager for both Flat File Destinations? It sounds as though you are.

-Jamie

Cannot have two flat file destinations in a package (possible bug)?

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

You can only have one data flow per data flow task.|||You can have more than one Data Flow Task per package and multiple Data Connections. Try that way. Two Data Connections and two Data Flows Task.|||

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Are you using the same Flat File Connection Manager for both Flat File Destinations? It sounds as though you are.

-Jamie