Poor import support Re: Connect vb express to SQL2005 Developer ed?

Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?

have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.

Selecting sql2005 as the server thru the add new database connection in advanced area yields the error message

surely there is away.

So far still no joy,

While Scotty indicates that the connection to SQL2005 is intentionally hampered other posters indicate that the user instance problem can be solved by running the .net config tool to add the asp database to sql server, which I have done.

However this does not yet work.

I cannot get the code to spark yet.

Again my configuration cannot be any simpler. sql express sp1 and sqldeveloper on the same machine using windows security (all installed with default cofigurations)

In the absence of other ideas I may try to get sql express downloaded and going, I'm not sure what issues that will present re: what I'm trying to develop.

I'm trying to develop the skills to replace some fairly involved reporting and some user table interface that I had going in access2003, I hate to pruchace a big piece of sw when I don't know if it will remove the current roadblocks...

Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)

Hi,

1). Are you able to Log In using Windows User Login credential on your SQL Server machine?

2). Have you cross check connection string?! Refer http://www.connectionstrings.com/

3). Is remote access , tcp/ip,named pipe are enabled and running properly?

4). SQL Server Browser service is running ?

5). Have check for any dependency service has problem?

6). RPC Service is running?

7). Have you check with Firewall settings?

8). Can both machine (Server/Client) ping each other?

9). What error message you got ?

Hemantgiri S. Goswami

|||

The following answers and eliminates many of the questions you pose...

I guess that MS doesn't want this envionment option to exist?

If you could answer I think it would solve a number of hanging queries.

Thank you for responding, I have temporarily given up on this quest since I have to get some real work done! ;') I went ahead and downloaded sql express and fidled a bit and now have it running with vb express and sql developer but it appears that the barriers are fairly high between the two enviornments...

Assume I want to start from scratch, one machine.....

Not answereing your questions precisely but asking a new one. With everthing operating on one machine under windows authentication using the default installation options for each product, what would be the correct (best) way to get the vb express to talk to sql developer?

Also it would be great to have the sql management studio be able to move the tables between the two environments...

All I'm trying to do is get the "best" developoment environment I can at a low cost.

|||

hi,

tomdart wrote:

Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?

have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.

...

Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)

Visual Studio Express editions designers are "designed" to use only "file based" connections and thus User Instances features... you can connect to SQL Server engine (Developer edition as well), but only by code and not via the designers..

regards

|||

Andrea Montanari wrote:

hi,

tomdart wrote:

Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?

have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.

...

Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)

Visual Studio Express editions designers are "designed" to use only "file based" connections and thus User Instances features... you can connect to SQL Server engine (Developer edition as well), but only by code and not via the designers as all editions but SQLExpress do not support User Instances..

regards

|||

Just to give "official" confirmation to what Andrea has already indicate, and provide more detail...

VS Express Editions are designed to work exclusively with SQL Express in terms of creating database through the UI and including them into your project. To this end, the following is true:

When you create a database using the VS Express UI, VS verifies the version of SQL being accessed is SQL Express. Any other version will fail.|||Thanks for the confirmation, I'm only doing development, learning and testing, but the signifigant restrictions of express and developer seem to make it pretty difficult. E.G. no effective import to express and no language (VB) in developer. Hence the need to use both to develop.|||

Hi tomdart,

If you want to import from Access I suggest you check out the Migration Assistant for Access that was recently released. This will get your Access data into SQL.

Mike

|||

Thank you for your reply, the following presented in the spirit of constructive feedback.

What I was hoping to do was simply import a text file, csv which is no problem with access, but simply cannot be done in sql express. I have managed , after hours to get the import going in sql developer since I can see and modify the package, but the lousy import and poor design of defaults makes the text import of express vitually a deal killer for my use. I may resort to import to access then import to express, but that seems like a sub optimal solution! I would add that native ssis in sql-developer is no champ to work with either. I don't get why the sophisticated folks at msft could not have either ssis or text import of SQL work as intellegently as Access. It seems that the defaults and error handeling just arn't as well set up, perhaps a "default mode" (which could be more intellegent or forgiving) of some sort could be added? As it now stands I regard this area as the "worst" from an everyday use perspective.

As to SSIS in sql developer, (and hence the big sql) I can't even find good doccumentation on how to modify a wizzard generated ssis package, The generated XML appears to be fairly inscrutable, and undoccumented.

|||

Hi tomdart,

Thanks for the feedback. I wasn't here when they made the decision to remove Import/Export from Express and I'm working to having at least in import solution in the next version. Your comments will help support my claim.

As another alternative I can suggest the BCP utility which can handle import and export of text based data.

Mike

Poor import support Re: Connect vb express to SQL2005 Developer ed?

Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?

have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.

Selecting sql2005 as the server thru the add new database connection in advanced area yields the error message

surely there is away.

So far still no joy,

While Scotty indicates that the connection to SQL2005 is intentionally hampered other posters indicate that the user instance problem can be solved by running the .net config tool to add the asp database to sql server, which I have done.

However this does not yet work.

I cannot get the code to spark yet.

Again my configuration cannot be any simpler. sql express sp1 and sqldeveloper on the same machine using windows security (all installed with default cofigurations)

In the absence of other ideas I may try to get sql express downloaded and going, I'm not sure what issues that will present re: what I'm trying to develop.

I'm trying to develop the skills to replace some fairly involved reporting and some user table interface that I had going in access2003, I hate to pruchace a big piece of sw when I don't know if it will remove the current roadblocks...

Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)

Hi,

1). Are you able to Log In using Windows User Login credential on your SQL Server machine?

2). Have you cross check connection string?! Refer http://www.connectionstrings.com/

3). Is remote access , tcp/ip,named pipe are enabled and running properly?

4). SQL Server Browser service is running ?

5). Have check for any dependency service has problem?

6). RPC Service is running?

7). Have you check with Firewall settings?

8). Can both machine (Server/Client) ping each other?

9). What error message you got ?

Hemantgiri S. Goswami

|||

The following answers and eliminates many of the questions you pose...

I guess that MS doesn't want this envionment option to exist?

If you could answer I think it would solve a number of hanging queries.

Thank you for responding, I have temporarily given up on this quest since I have to get some real work done! ;') I went ahead and downloaded sql express and fidled a bit and now have it running with vb express and sql developer but it appears that the barriers are fairly high between the two enviornments...

Assume I want to start from scratch, one machine.....

Not answereing your questions precisely but asking a new one. With everthing operating on one machine under windows authentication using the default installation options for each product, what would be the correct (best) way to get the vb express to talk to sql developer?

Also it would be great to have the sql management studio be able to move the tables between the two environments...

All I'm trying to do is get the "best" developoment environment I can at a low cost.

|||

hi,

tomdart wrote:

Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?

have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.

...

Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)

Visual Studio Express editions designers are "designed" to use only "file based" connections and thus User Instances features... you can connect to SQL Server engine (Developer edition as well), but only by code and not via the designers..

regards

|||

Andrea Montanari wrote:

hi,

tomdart wrote:

Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?

have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.

...

Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)

Visual Studio Express editions designers are "designed" to use only "file based" connections and thus User Instances features... you can connect to SQL Server engine (Developer edition as well), but only by code and not via the designers as all editions but SQLExpress do not support User Instances..

regards

|||

Just to give "official" confirmation to what Andrea has already indicate, and provide more detail...

VS Express Editions are designed to work exclusively with SQL Express in terms of creating database through the UI and including them into your project. To this end, the following is true:

When you create a database using the VS Express UI, VS verifies the version of SQL being accessed is SQL Express. Any other version will fail.|||Thanks for the confirmation, I'm only doing development, learning and testing, but the signifigant restrictions of express and developer seem to make it pretty difficult. E.G. no effective import to express and no language (VB) in developer. Hence the need to use both to develop.|||

Hi tomdart,

If you want to import from Access I suggest you check out the Migration Assistant for Access that was recently released. This will get your Access data into SQL.

Mike

|||

Thank you for your reply, the following presented in the spirit of constructive feedback.

What I was hoping to do was simply import a text file, csv which is no problem with access, but simply cannot be done in sql express. I have managed , after hours to get the import going in sql developer since I can see and modify the package, but the lousy import and poor design of defaults makes the text import of express vitually a deal killer for my use. I may resort to import to access then import to express, but that seems like a sub optimal solution! I would add that native ssis in sql-developer is no champ to work with either. I don't get why the sophisticated folks at msft could not have either ssis or text import of SQL work as intellegently as Access. It seems that the defaults and error handeling just arn't as well set up, perhaps a "default mode" (which could be more intellegent or forgiving) of some sort could be added? As it now stands I regard this area as the "worst" from an everyday use perspective.

As to SSIS in sql developer, (and hence the big sql) I can't even find good doccumentation on how to modify a wizzard generated ssis package, The generated XML appears to be fairly inscrutable, and undoccumented.

|||

Hi tomdart,

Thanks for the feedback. I wasn't here when they made the decision to remove Import/Export from Express and I'm working to having at least in import solution in the next version. Your comments will help support my claim.

As another alternative I can suggest the BCP utility which can handle import and export of text based data.

Mike

Poison Message. The conversation endpoint is not in a valid state for SEND.

Hi all, i searched everywhere but couldn't find any info on the following error that i'm currently receiving:

"The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'DI'."

I understand that this is due to some problem in my send/receive protocol but how do i fix this problem so that i can continue with my testing? Right now i'm forced to drop my entire test database and reinstall everytime this message shows up because i can't send/receive any messages at that point. Is there anyway to get rid of it?

Thanks in advance. This is driving me nuts.

DI stands for DISCONNECTED INBOUND and the conversation goes into this state when the peer has ended the conversation. If you try to send on a conversation in this state you'll get this error. You need to establish a clear protocol on who'w ending the conversation and how, because what s likely happening one side (initiator?) issues the END CONVERSATION verb and ends the conversation while the target is still trying to respond.

|||I think i found a solution to my own problem.

This is from the Microsoft online book:
http://download.microsoft.com/download/f/8/5/f8520d64-f109-4111-b0b0-51f1f6d2d220/Rational_Press_Service_Broker_Sample.pdf

If you look at the example in Chapter 1, you will notice that only one end of the conversation

was ended. In Figure 2.3, you can see one conversation with a state of “DI” (meaning

“disconnected inbound”), indicating that the opposite endpoint was ended with an END

CONVERSATION command but this endpoint of the conversation hasn’t been ended yet.

The other conversation state is “ER” (or “error”) because it has been open long enough

that the timeout has expired and the conversation ended with an error. If you execute an

END CONVERSATION command specifying the conversation_handle for the row in

the sys.conversation_endpoints table, it will go away.

Remembering to end all conversations is a very important programming practice. If you

forget to do this, you will fi nd thousands of rows in the sys.conversation_endpoints table

after your application has run for awhile.

If anyone has other ideas then please let me know.

Cheers!!
|||Thanks Remus for the reply, here is my sending protocol:

declare @.dh uniqueidentifier;
declare @.Message nvarchar(500);

SET @.Message = N'<MessageTypes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"><TransactionID>12345</TransactionID><MessageType>Swap</MessageType><Source>OMS</Source><IsCancel>false</IsCancel></MessageTypes>';

BEGIN DIALOG CONVERSATION @.dh
FROM SERVICE ClientServiceOMS
TO SERVICE 'TranProcessorService'
ON CONTRACT TradeCon
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.dh
MESSAGE TYPE MessageTypes(@.Message)

END CONVERSATION @.dh;

And here is my receiving protocol (I removed all error handling for simplicity):

CREATE PROCEDURE ServiceProc
AS
DECLARE @.MsgXml xml, @.MsgTypeName nvarchar(128),
@.dh uniqueidentifier, @.Message nvarchar(500);

SET @.ErrNS = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
SET @.EndDlgNS = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN TRAN;

WAITFOR (RECEIVE TOP(1)
@.MsgXml = CAST(message_body as xml),
@.MsgTypeName = message_type_name,
@.dh = conversation_handle
FROM ServiceQue) timeout 1000;

-- Send reply message
SET @.Message = N'<ResponseMessage><Message>Received</Message></ResponseMessage>';
SEND ON CONVERSATION @.dh
MESSAGE TYPE ResponseMessage(@.Message);

END CONVERSATION @.dh;
PRINT '(ServiceProc) Reply Message Sent Successfully.'

COMMIT TRAN

So are my "END CONVERSATION" statements in the correct place?

Thanks a lot.
|||Hello again, I just wanted to clairfy that the receiving procedure is called automatically by the receiving queue to process the messages. So i guess i'm not understanding exactly when to end the conversation.

The above receiving procedure is incorrect, here is the correct version:

CREATE PROCEDURE ServiceProc
AS
DECLARE @.MsgXml xml, @.MsgTypeName nvarchar(128),
@.dh uniqueidentifier, @.Message nvarchar(500);

BEGIN TRAN;

WAITFOR (RECEIVE TOP(1)
@.MsgXml = CAST(message_body as xml),
@.MsgTypeName = message_type_name,
@.dh = conversation_handle
FROM ServiceQue), TIMEOUT 1000;

-- Send reply message
SET @.Message = N'<ResponseMessage><Message>Received</Message></ResponseMessage>';
SEND ON CONVERSATION @.dh
MESSAGE TYPE ResponseMessage(@.Message);

END CONVERSATION @.dh;
PRINT '(ServiceProc) Reply Message Sent Successfully.'

COMMIT TRAN

I really appreciate all the help.

Thanks
|||

Scorpio8 wrote:

BEGIN DIALOG CONVERSATION @.dh
FROM SERVICE ClientServiceOMS
TO SERVICE 'TranProcessorService'
ON CONTRACT TradeCon
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.dh
MESSAGE TYPE MessageTypes(@.Message)

END CONVERSATION @.dh;

For one, you are doing fire-and-forget and that has some problems, see here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx. Second, the target is trying to respond on a conversation that was already ended by the initiator (hence the error you're getting).

Remove the END CONVERSATION on the sending side, have the initiator wait for the response from the target and end it's side of the conversation after it received a response.

|||Thank you very much Remus, that article and your other articles were very helpful. I got everything working now. But even though all the conversations end up gracefully i still end up with an entry in the sys.conversation_endpoints queue on both database instances:

select state, state_desc from sys.conversation_endpoints

state state_desc
CD CLOSED

Do i need to clear this queue every now and then or should it automatically clear up when the conversations end?

|||

Hi, i found an answer to my question in the book "Pro SQL Server 2005 Service Broker" which i got over the weekend, great book. The answer is on page 59:

"As soon as you process the EndDialog message on the initiator side, the conversation will end on both sides and then discarded from memory. However, for security reasons, it takes about 30 minutes until the initiator's conversation endpoint is deleted from sys.conversation_enpoints catalog view."

Just how in the heck were we mortals supposed to figure this out by ourselves!! lol