|
Hello,
thanks for your reply.
But i think the ip address would be used for connecting to a remote server on the internet. Local would be the local server on my machine not a remote one.
How do i config client-network information with sql server? Not sure about this.
Thanks in advance,
Steve
|
|
|
|
|
Data Source="SQLServerIP\InstanceName";Initial Catalog="DataBase";Persist Security Info=True;User ID="UserID";Password="Password"
Obviously the " are just to separate the required values and what you will modifiy.
|
|
|
|
|
Hello private_void,
Thanks for your reply.
I have noticed you are using that connection string with a sql security. Currently I have my sql server setup for Windows authenication. That is why I never had the pwd or username.
Just wondering, can you connect remotely with windows authentication? If so, have I missed something about windows user account? security login?
Please if you have any idea about windows authentication.
thanks very much,
Steve
|
|
|
|
|
try to config an alias for "DEVSQL" with sql server client network utility.
ps: DEVSQL <=> IP on Lan
i have only use SSPI in same machine,not lan or internet yet.:p
|
|
|
|
|
Thanks for your reply yahao,
But I am sure what you mean by:
yahao wrote: config an alias for "DEVSQL" with sql server client network utility
How will this help solve the current problem?
I have connected to sql server many times. This the first time remotely.
The server I am using is 2005 developer edition.
Many thanks again,
Steve
|
|
|
|
|
or try to directly connect remote server use IP rather than use machine name.
this is the fun of resolve problem.
|
|
|
|
|
Well I think you should use the IP address. This should be a static IP. If it is not that could be why you are having problems. When you enable the connections via Pipes you should reboot the Server. I don't see anything wrong with your connection string and yes SQL 2005 does allow Windows Authentication, but you still have to set up a Login under Security\Logins. The user name should be Domain\WindowsUserAccount. If you have any questions regarding connections strings there is a great website; http://www.connectionstrings.com.
Hope this helps.
|
|
|
|
|
I have a question that I was hoping someone could help me with. I am using VB.net 2005 express edition with SQL Server 2005 Express edition.
I have a database that has a table in it called Folder. It consists of two fields. One is called folderId and folderName.
I want to add a new row to this folder by calling a second form from my main form. For example, say I open up a second form from my main form. This second form consists of a text box and an button called OK. The user will enter the new folder name into the text box. How do I make it so the name the user enters into the text box is added to the folder table.
|
|
|
|
|
Please stick to your original thread[^].
Posting the same question in different forums is rude. Be patient and someone will give you an answer to your question.
|
|
|
|
|
He's a very naughty boy
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
|
I'm designing a database (at which I do not have much knowledge) and I have a question. I want to have a table that has properties, such as title, author, length, etc for books and one of the properties I want is genre (fiction, non-fiction, thriller, etc). Here's my problem, each book could be have than one category so how would I set that up? I want to have the Books table with a Genre field which would be a FK to the Genre table. What I'm not sure about is storing the values because of normalization ie. if I just stored the GenreID's as a comma seperated list in the Genre field wouldn't that break normalization not to mention make it harder to parse...I'm just a little confused with how to design this and any help would be appreciated.
Thanks.
- Aaron
|
|
|
|
|
Don't store the data as a a comma separted list. You need a Book_Genre table that stores the book_id and the genre_id, is covered by a unique index and links to the Book and genre tables on the key fields.
|
|
|
|
|
This is a many-to-many join that you are describing:
One book can have many genres. One genre can contain many books.
What you need is an intermediate table. These are necessary to model many-to-many joins. The intermediate tables contains a compound primary key made up of the primary keys of the tables that are being joined together. In this case a Book table and a Genre table.
The intermediate table BookGenre will look like this:
BookId int not null PK
GenreId int not null PK To join a book to a genre you create a new row in the BookGenre table with the corresponding primary keys. The act of making the compound primary key in the intermediate table ensures that you don't have duplicate joins. e.g. Terry Prattchet's Thump listed as Fantasy twice, or vice versa.
Does this help?
|
|
|
|
|
This is exactly what I'm looking for, thanks very much for the help. It makes a lot of sense, but it's not something I would have thought of.
Thanks again!
- Aaron
|
|
|
|
|
hi
can anyone plz help me debug this procedure and tel me if there is any other software that helps us write and debug stored procs in a much convinient way
I'm using SQL server 2000 by the way.
and this is the error...
--------------------------------------------------
Error 156: Incorrect syntax near the keyword 'SET'.
---------------------------------------------------
CREATE PROCEDURE dbo.BranchBillDetailInsertCombo
(
@BillID numeric(18),
@Quantity int,
@Serial varchar(30),
@prodLineID numeric(18),
@ManID numeric(18),
@ModelID numeric(18),
@PresentAT numeric(18),
@Error_code INT OUTPUT
)
AS
DECLARE @counter int
DECLARE @availableQty int
DECLARE @PurchID numeric(18)
DECLARE purch_cursor CURSOR FOR
SELECT purchid FROM vu_unsoldproducts
WHERE prodLineID = @prodLineID
AND Manid = @Manid
AND ModelID = @ModelID
AND Upper(Serial) = Upper(Serial)
AND presentAT = @presentAT
Select @avialbleQty = (select count(*) AvailableQty FROM vu_unsoldproducts
Group by serial, prodlineid, manid, modelid HAVING prodlineid = @prodLineID AND manid = @manid AND modelid = @modelID AND UPPER(serial) = UPPER(@Serial)
SET @Error_code = 0 -- means there is no error in executing the stored procedure
IF @Quantity >@availableQty
BEGIN
SET @ERROR_CODE = -1
--return -- dubious statement... I dont know the syntax for that.
END
ELSE
BEGIN
OPEN purch_cursor
FETCH NEXT FROM purch_cursor
INTO @PurchID
Insert INTO branchBillDetail(BillID, purchID)
Values (@BillID, @purchID)
SET @counter = 1
WHILE (@@FETCH_STATUS = 0) OR (@Counter < @Quantity)
BEGIN
SET @counter = @counter + 1
-- @counter++
FETCH NEXT FROM purch_cursor
INTO @PurchID
Insert InTo branchBillDetail(BillID, purchID)
Values (@BillID, @purchID)
END
CLOSE purch_cursor
DEALLOCATE purch_Cursor
END
Rocky
|
|
|
|
|
there has two mistake in your code:
1.missing a right bracket at the end of "Select @avialbleQty = (..."
2.the var @availableQty is not equal definition
The code after DEBUG:
CREATE PROCEDURE dbo.BranchBillDetailInsertCombo
(
@BillID numeric(18),
@Quantity int,
@Serial varchar(30),
@prodLineID numeric(18),
@ManID numeric(18),
@ModelID numeric(18),
@PresentAT numeric(18),
@Error_code INT OUTPUT
)
AS
DECLARE @counter int
DECLARE @availableQty int
DECLARE @PurchID numeric(18)
DECLARE purch_cursor CURSOR FOR
SELECT purchid FROM vu_unsoldproducts
WHERE prodLineID = @prodLineID
AND Manid = @Manid
AND ModelID = @ModelID
AND Upper(Serial) = Upper(Serial)
AND presentAT = @presentAT
Select @availableQty = (select count(*) AvailableQty FROM vu_unsoldproducts
Group by serial, prodlineid, manid, modelid
HAVING
prodlineid = @prodLineID
AND
manid = @manid
AND
modelid = @modelID
AND
UPPER(serial) = UPPER(@Serial))
SET @Error_code = 0 -- means there is no error in executing the stored procedure
IF @Quantity >@availableQty
BEGIN
SET @ERROR_CODE = -1
--return -- dubious statement... I dont know the syntax for that.
END
ELSE
BEGIN
OPEN purch_cursor
FETCH NEXT FROM purch_cursor
INTO @PurchID
Insert INTO branchBillDetail(BillID, purchID)
Values (@BillID, @purchID)
SET @counter = 1
WHILE (@@FETCH_STATUS = 0) OR (@Counter < @Quantity)
BEGIN
SET @counter = @counter + 1
-- @counter++
FETCH NEXT FROM purch_cursor
INTO @PurchID
Insert InTo branchBillDetail(BillID, purchID)
Values (@BillID, @purchID)
END
CLOSE purch_cursor
DEALLOCATE purch_Cursor
END
|
|
|
|
|
Hey thanks a lot man I really appreciate it, well its just that at the moment I'm not feeling at home with this syntax u know
but is there any other tool in which we can easily see our mistakes like for example .net environment helps alot in that regard.
thanks again
Rocky
|
|
|
|
|
hi!
I wrote sql server stored procedure since 2002,as a matter of experience,the best way to reduce bugs and errors is keep good habit for coding.
for example,code with EditPlus text-editor rather than sql server query analyzer,and use the syntax file & auto-completion for *.sql file type.
of course,one important factor is has clear structure of application,and then,produce high-quality code.
there is some code of mine:
/*
'##############################################################################
'## Copyright (C) 1998-2006 Yahao SoftWorks
'##
'## PROJ： FFS
'## SUBJ： get common present data
'##
'## Code by Yahao
'##############################################################################
'## C/U-Date: 2006-10-09
*/
-- Remove the existing Stored Procedure --
if (exists (select * from sysobjects where name = 'usp_m_getcPresentDetailData' and type = 'P'))
drop proc usp_m_getcPresentDetailData
go
CREATE PROCEDURE usp_m_getcPresentDetailData
(
---- In: op id ----
@inCmdID smallint,
---- In: obj id ----
@inOpItemID int,
---- In: obj name ----
@inOpItemName varchar(60),
---- Out: record amount ----
@otcPresentAmount int OUTPUT,
---- Ret ----
@ReturnValue int OUTPUT
)
WITH ENCRYPTION
AS
SET NOCOUNT ON
DECLARE @intRetValue As int,@intErrCode As int
DECLARE @intcPresentAmount As int
IF @@NESTLEVEL = 1
BEGIN
SET @ReturnValue = 99
RAISERROR ('illegal call',16, 1)
RETURN
END
---- var init ----
SET @intErrCode = 0
SET @intcPresentAmount = 0
---- RS1: present data ----
SELECT
PresentName,
StockAmount,
AvgStockPrice,
OptionSet,
CatID,
StockStatusTID,
Remark,
CreateOn,
Status
FROM onenb_shop.dbo.CommonPresentData
WHERE PresentID = @inOpItemID
---- return data ----
SET @otcPresentAmount = @intcPresentAmount
SET @ReturnValue = 0
SET NOCOUNT OFF
|
|
|
|
|
Good style:
SELECT
@availableQty = (
SELECT COUNT(*) As AvailableQty
FROM vu_unsoldproducts
GROUP BY
serial,
prodlineid,
manid,
modelid
HAVING
prodlineid = @prodLineID
AND
manid = @manid
AND
modelid = @modelID
AND
UPPER(serial) = UPPER(@Serial)
)
Bad style:
Select @availableQty = (select count(*) AvailableQty FROM vu_unsoldproducts
Group by serial, prodlineid, manid, modelid
HAVING
prodlineid = @prodLineID
AND
manid = @manid
AND
modelid = @modelID
AND
UPPER(serial) = UPPER(@Serial))
|
|
|
|
|
hi
well I just need to build a stored proc to make a lot of insertions at one place for the user, the thing is that user can give the required quantity and the data would get inserting by matching the number of rows equal to taht of this quantity (it ould already be chked that the number is less or equal to existing Quantity or not). so the data gets inserted into this other table.
I think may be I'd need to have cursors here coz I need to access each row of the extracted data indivually. But I'm not too fond of using them though.
but even if I have to use them can u plz give me a sample code for that in TSQL
the tables are as follows
BranchBillDetail(purchID, billid) <--insertion in this
vu_unsoldproducts(purchID, modelid, prodlineid,manid, etc...) searching done on this
thanks in advance
Rocky
|
|
|
|
|
--This should solve your problems if I understand your request.
--How to do it w/o a cursor
DECLARE @RowCount INT
DECLARE @DataTable TABLE(
RowNum INT IDENTITY(1,1)
DataColumn1
,DataColumn2
,DataColumn3
ETC
)
SELECT @RowCount = Count(*) FROM [Data Source]
WHILE @RowCount > 0
BEGIN
INSERT YADAYADA
WHERE RowNum = @RowCount
SET @RowCount = @RowCount - 1
END
--Let me know if this helps
|
|
|
|
|
yea it has given me some idea to move on u know. I'm a bit out of touch with that project for a few days now and I'll certainly look into it again to get the job done..
thanks a lot
I really appreciate it!
Rocky
|
|
|
|
|
Is it possible to write an INSERT statement to add a row to a table with just a single column which is an identity column ?
e.g
Create Table Amit (Id int Identity(1,1))
Then what would be my insert statement.
Note:
Please note i don't want to use set auto_identity amit On 
Plase reply....
Amit
|
|
|
|
|
INSERT INTO Amit DEFAULT VALUES
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
