|
Hi John,
IMO the relevant part of http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/6615124e-5501-4fb2-b498-638d3a7f01c6[^]
is this:
I encountered the same error message while trying to create a data connection from Visual Studio 2005 using the Server Explorer.
This is what I did:
# 1. Started Visual Studio 2005
# 2. Opened the Server Explorer (View | Server Explorer)
# 3. Right-clicked the Data Connections node
# 4. Selected Create New SQL Server Database... from the context menu
# 5. Selected my computer from the Server Name dropdown list
# 6. Selected authentication method (Use Windows Authentication)
# 7. Typed a name in the New Database Name field
# 8. Hit the OK button
That's when I received the following error message
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error:40 – Could not open a connection to SQL Server)
It made absolutely no sense. I wasn't trying to connect to a remote computer. I was simply trying to connect to the SQL Server 2005 Express Edition which was installed on my local computer during the Visual Studio 2005 installation.
After numerous attempts on several computers, I finally realized what I was doing wrong. By adding the database instance name to the server name (# 5), it actually worked. Instead of selecting <server name=""> from the dropdown list, I entered <server name="">\SQLExpress manually. That was obviously the problem.
SQL Server 2005 Express Edition is obviously installed as a named instance, since one has to provide the instance name in addition to the server name (even when there is only one instance running on the requested server).
|
|
|
|
|
We're not running sqlexpress...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
|
Does it relate to SQL Express? 
|
|
|
|
|
Luc Pattyn wrote: Does it relate to SQL Express?
|
|
|
|
|
|
CREATE PROCEDURE .InsertTwoTables
@UserName nvarchar(50),
@ShopName nvarchar(50),
@Email nvarchar(300) ,
@time1 datetime,
@CategoryNameE nvarchar(50),
@ItemKey nvarchar(50),
@ItemKeyNameE nvarchar(50),
@CurrentQty nvarchar(50),
@SalesPrice nvarchar(50),
@Quantity nvarchar(50),
@Total nvarchar(50)
AS
BEGIN TRANSACTION
Set Nocount On
DECLARE @OrderID int
//primary key table
INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1)
SELECT @OrderID=@@IDENTITY
//foreign key table
INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total)
VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total)
COMMIT
when iam inserting values with these procedure
it is saving ordersno also repeated
i want one orderid with multiple values in TblDetails
can you give me example which helps me
|
|
|
|
|
The way the stored procedure is currently there will only be a one to one relationship. I would suggest removing the time from TblOrder then before an insert check to see if the values UserName, ShopName, Email already exist.
Something like (not tested)
DECLARE @there INT
SELECT * FROM TblOrder
WHERE UserName = @username
AND ShopName = @shopname
AND Email = @email
SET @there = @@rowcount
IF @there = 0
BEGIN
-- Your insert
END
Hope this helps
djj
|
|
|
|
|
Try using SCOPE_IDENTITY rather than IDENTITY - check BOL for the (significant) differences.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi guys,
I'm trying to get 2 values from a stored procedure: one telling me the execution was successful and another for telling me what the newly inserted ROW ID is. I currently have the following:
<br />
<br />
USE [xyz]<br />
GO<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
ALTER PROCEDURE [dbo].[spName]<br />
(<br />
@vchName VarChar (50),<br />
@intSuccess Numeric(9) OUT<br />
)<br />
AS<br />
SET XACT_ABORT ON<br />
BEGIN TRANSACTION <br />
<br />
INSERT INTO Table<br />
(<br />
vchName <br />
)<br />
VALUES<br />
(<br />
@vchName<br />
)<br />
<br />
IF @@ERROR <> 0<br />
BEGIN<br />
SELECT @intSuccess = 0<br />
ROLLBACK<br />
END<br />
ELSE<br />
SELECT @intSuccess = 1 <br />
<br />
<br />
COMMIT TRANSACTION<br />
SET XACT_ABORT OFF<br />
<br />
RETURN @intSuccess<br />
<br />
Currently it returns the value "@intSuccess" ONLY. I want to add the line "SELECT @@IDENTITY AS inLatestID" right under the insert statement so I can get the latest ID value of the record that was just newly inserted.
I think I will need to return another value alongside @intSuccess, but I'm not sure how that works in the Stored Procedure
|
|
|
|
|
You can have multiple out parameters, but you do not need to return success/failure as an out parameter. You can use standard try/catch (sql 2005/8) or raiseerror for earlier versions to check for errors. Check out BOL for more details
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I suppose that makes sense. Normally I check if the return value is > 0, I suppose if a Stored Procedure executes successfully, then my parameter should be > 0 and I can just check that.
Thanks for your reply.
|
|
|
|
|
A stored proc (or any other sql query) returns zero for success and non-zero for an error
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm playing with a hypothetical company with several branch offices and one main office. Said company has limited IT resources (ie, no budget), but would like to keep local office sales data in SQL Server Express, then update nightly to the main office SQL Server Standard edition. Is this even possible? Can the Express edition participate in replication?
And yes, it's for a homework assignment I'm assisting with... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
we don't do homework assistance assistance. you want rentacoder.
|
|
|
|
|
So do we do looped assistance or is it like nested assistance and is below the assistance threshold.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Luc Pattyn wrote: you want rentacoder
Too expensive! 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
looks like Express does not support replication info[^]
Another aspect to consider is the records identity information, integer identity format no longer works. Look into uniqueidentifier/GUID. Horrible, ugly canankerious bastard things that they are. One gotcha is that a GUID is not a nullable type.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Maybe you could use integer keys locally, and apply an integer-to-GUID conversion table before exporting it all.
|
|
|
|
|
If you only need to update nightly, you should look into a simple application that creates xml files, and sends them by ftp or something like that, and an application on the other side that imports the data from these files.
It's a robust system that we use ourselves, and built without a budget 
My advice is free, and you may get what you paid for.
|
|
|
|
|
That's a good thought, and I may do it just for the learning experience. But I'm just looking for what's possible - only the design is required for homework, not the implementation.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I would maintain a transaction table. And send the unsent transactions periodically.
I would also include a field that indicates where the transaction came from.
|
|
|
|
|
That would make a lot of sense, as one of the requirements I made up is that the stores have to be autonomous during comm failures, then update the home server when a link is available.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Just a tip, you should include a status flag in that table while you are at it. This flag can have separate values for (for example) "Handled", "ImportError", "Corrupted", etc
The most common problems when transferring data are data corruption, transfer failures, and import failures (usually because some data is in the wrong format or missing entirely, say a date may have the year 1900 instead of 2010, which happens astoundingly often).
Based on the flag you can then have the import application take different actions (correct data with default values, alert someone, try to retrieve the data again from the source, etc.)
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi I have an Sql Table Like this
MatTable
type = 1 Is IN Qty ( Buy )
type = 0 Is Out Qty ( Sell )
mat qty type
-------------------------------------------------- ---------------------- -----
mat1 10 0
mat2 2 0
mat3 10 0
mat1 5 1
mat2 5 1
mat2 7 1
mat4 4 0
mat4 4 0
mat4 8 1
mat3 6 1
mat1 10 0
How I can Get a result Like
mat Current qty
-------------------------------------------------- ----------------------
mat1 15
mat2 -10
mat3 4
mat4 0
Thank you In Advance
I know nothing , I know nothing ...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
