|
Usually there is only one listener on a server, but you may have many database instances each with their own database service. When you connect to those services each connection initially is to the listener which then hands off the connection to the correct database service. If you list the services of your server, you should see a service name OraListener or something like that. There should also be services for each database instance that runs there. The names of these will correspond to the database instance name.
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
GOOD DAY FOR: Moonlighting, as porn star Savanna Samson has launched her own wine label. Her Sogno Uno, an Italian red, received an "outstanding" 90 to 91 score from influential wine critic Robert Parker. "I wanted to do something my parents could be proud of," she said. (Reuters in CNNMoney.com)
|
|
|
|
|
I have a stored procedure that bomb's out with an error right at the start.
The error doesn't make any sense to me so I want to debug, but when I hit debug, Query analyser runs all the way to the error withoutout breaking code-execution once.
Obliviously something is wrong in my code, how do I find it?
Inserting a print command after every single line of code doesn't work either, i think sql doesn't handle the print command in a timely fashion.
Im running Win XP Pro SP2, SQL2000 SP4
-- modified at 10:07 Tuesday 4th April, 2006
I found the problem in my code afterall, but my Query Analyser still wont debug, I now realise that it's been like this since I installed Visual Studio 2005.
Can anyone help?
you can't forget something you never knew...
|
|
|
|
|
Hi
I am trying to connect to a CSV file and import the data from the file to MSSQL database.
I am using OleDbConnection to connect to the file.
My code works fine if I run the web page on my local server. However, if I copy the project to my hosted server and then try to connect to the CSV file, I get error message: "Cannot find table ".
I believe I am unable to connect to the CSV file because of security issues. I have following code:
<br />
ConnCSV = @"\\173.0.0.5\csv\"<br />
string cs = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ConnCSV+";Extended Properties='text;HDR=No;FMT=Delimited'";<br />
<br />
string sqlSelect;<br />
OleDbConnection objOleDBConn;<br />
OleDbDataAdapter objOleDBDa;<br />
objOleDBConn = new OleDbConnection(cs);<br />
objOleDBConn.Open();<br />
sqlSelect = "select * from ["+ File.CSV +"]";<br />
objOleDBDa = new OleDbDataAdapter(sqlSelect,objOleDBConn);<br />
objOleDBDa.Fill(ds);<br />
objOleDBConn.Close();<br />
173.0.0.5 is the IP address of the network drive
So does anyone know how to connect to CSV file from a hosted environment??
regards
-- modified at 10:51 Tuesday 4th April, 2006
|
|
|
|
|
I need to create tables by calling a storage procedure. The name of table must be a parameter.
I am using Visual c++ 6.0 with sql server, can you show me how to execute it from my program also.
I learn my self
|
|
|
|
|
Hi, I have never done a Visual C++ Program so I cannot give you a lift with this part...
but I have used stored procedures to create tables and sp
alter Procedure SPCreateTable
@TableName nvarchar ( 50 )
As
declare @CreateString nvarchar ( 4000 )
Set @CreateString =
'CREATE TABLE ' + @TableName + '
(
CategoryID int IDENTITY (1, 1) NOT NULL ,
CategoryName nvarchar (15),
) '
Exec ( @CreateString )
Go
Exec SPCreateTable 't3'
This is a sample that works, you only have to paste it in the Query Analizer...
Good Luck
Ricardo Casquete
|
|
|
|
|
Could you show me some theory about managing database for multi user program? Thanks so much. 
|
|
|
|
|
As I remember that was a whole semester's worth of database lectures from my uni' days. How much information do you really want?
The shortest summary would be:
* Use transactions
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi
I come from MSAccess programming and using access is easy to create a view of data coming from 2 different tables linked with a join.
I'm trying to port my application under VS2003 C# and I don't know how create the same thing with my dataset.
The brute-force mode I found is to create a new table copying the fist one, add a column and fill it with the data retrived from the second one with GetParentRow or GetChildRow methods. Is this the right way or there is a easiest way?
Thank in advance
Best regards
Massimo Nicoletti
|
|
|
|
|
Hi man...
You only have to create the typed dataSet from the View.
I mean, as well you add a table to a dataset, you can add a view.
If the View is well done with the Primary Keys and the Not Null columns you can do updates and Insert aganist the View and you will find the both tables will be updated...
Ricardo Casquete
|
|
|
|
|
Hello Ricardo,
my dataset is a Typed dataset so You think that I have all the info I need?
I can "reach" the joined column of a table using the info in the dataset?
How I can do this?
Thank you for your help
Thank you for your help
Massimo Nicoletti
|
|
|
|
|
OK Man,
If you have a view in Access or SqlServer, you should be able to see it from the Server Explorer of the Visual Studio.
Instead of the Tables Node, From the View Node.
So now, to create a Typed DataSet from the View the only thing you have to do is to Drag and drop it over a dataSet or over a form ( creating also the OdbcDataAdapter ).
Doing so, when the DataAdapter has been created if you have done the View Properly ( I mean with all the PrimaryKeys of the Tables, and all the Columns that cannot be empty ) the DataAdapter willk generate the Update and DeleteCommand.
If you don't have all this information, you won't be able to delete or update.
Now the only thing you have to do is to work with the Data of the DataSet.
I mean
foreach ( System.Data.DataColumn col in this.dst.Tables [ 0 ].Columns )
{
col [ 0 ]....
}
foreach ( System.Data.DataRow row in this.dst.Tables [ 0 ].Rows )
{
row [ 0 ].ToString()....
}
to add a row in the tables of the View, you only have to add a row to the DataSet and call Update to the DataAdapter...
and more or less thats all
Ricardo Casquete
|
|
|
|
|
Ok tested and work fine.
The last question is: if I'm working off-line, I mean with the dataset can be non syncronized with the source (access database) because I modify a record and I have not yet updated the source, before creating dataset I must update the database with my dataset or I can "start" with the data stored in the dataset?
Thank you
Massimo Nicoletti
|
|
|
|
|
No, The dataSet is designed to work offline, you don't need to do a update foreach change you do in your dataSet...
Do all the changes you need and then Update All.
After that call the dataSet.AcceptChanges() Method and that's all
Regards
Ricardo Casquete
|
|
|
|
|
Excuse my ignorance, Ricado,
work-dataset is the dataset I use in winform
view-dataset is the dataset I create vith access view (I use view-dataset with the .writexml method to save xml on disk)
Every time I call the .fill methot of dataadapter to fill the view-dataset with data stored in the access database, this must be syncronized with my work-dataset.
So, I think, every time I need data from view, first I must check if my work-dataset is syncronized with access, and If not Syncronize it, and after call the . fill method to populate the view-dataset.
Is it correct?
Thank for your big big patience
Massimo Nicoletti
|
|
|
|
|
Yes thats it...
You can ask your work dataSet.HasChanges(), that returns a bool value....
This value will gave you must syncronize the DataSet or not...
Also have a look, maybe could be interested for you to use the DataSet1.Merge( dataSet2 ) method that joins two dataSet with the changes...
hope it works...
and don't worry for my patience... I have a lot...
Ricardo Casquete
|
|
|
|
|
Hi Ricardo,
It works very well !!!
Thank you very, very much.
Best regards
Massimo Nicoletti
|
|
|
|
|
Aloha,
We have a lot of data with textstrings in English, Russian and other languages. The data exists as a lot of files with SQL statements.
The statements looks fine in Query Analyzer (Server Mgmt. Studio) on my local machine. I have SQL Server 2005 on my local machine.
It's when I upload my statements to the production server that things start to go wrong. The statements still look fine in e.g. Notepad, but when I open them in Query Analyzer all the Russian texts are changed to garbage strings. The product server have SQL Server 2000, so I access the statements using an older version of Query Analyzer than on my local machine.
Any idea on how I get Query Analyzer to show my Russian texts correctly and thereby be able to execute the statements correctly? An other way of executing the statements could also be useful as long as the Russian texts are preserved.
Thanks and do remember that today (April 4) is National Workplace Napping Day,
phi
phimix.com 
|
|
|
|
|
Can someone please help me to create a new user that can access a database? I want to add this in my connection string in my web.config file. I don't want to supply the sa password for this. I'm struggling to get this done in SQL Server 2005, not sure where to go.
Please can someone help.
Regards,
ma se 
|
|
|
|
|
Hi all!!
I need some advise please. I have a stored procedure that adds a member to tblMember, and in the same stored procedure I add the member ID and role ID to the tblMemberInRoles table. Now what happens if the member, for example, has more than one role? How do I pass this "array of roles" to the stored procedure?
How would the C# code look like when passing these parameters? I am using framework 2.0 and SQL Server 2005.
Is this the correct waty to do it? Or is there a different way that you guys would do it? Please let me know!!
Any help or advise is appreciated!!
Cheers.
ma se
|
|
|
|
|
Two ways I can think of... [1] Call the stored procedure multiple times for each role (or create a separate SP for adding the roles) or [2] pass the role names in as a comma separated string and parse the string in the stored procedure.
There may be a third possibility but I've not had a chance to really look at it and that is passing an XML fragment into the stored procedure. SQL Server 2005 has better XML processing capabilities than SQL Server 2000 and that may be a possibility. However, I've not really looked at the XML capabilities of SQL Server 2005 yet, so I cannot say for certain.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Thanks for the reply..
What I would've liked to have done was to add the member, return the new member ID, and loop through the roles array and just add the role ID and member ID to the MemberInRoles tables (by calling another stored procedure to add the roles), but I don't think you can loop in a stored procedure.
Can you maybe help to write some code to parse the comma-separated string, loop through the contents and call the sp_MemberInRoles_InsertMemberRole stored. But there can be 1 role per member, 5 roles, etc, you can't have a fixed loop, it differs.
Regards,
ma se
|
|
|
|
|
ma se wrote: but I don't think you can loop in a stored procedure
Of course you can loop in a stored procedure. You can use WHILE[^]
DECLARE @remainingRoles NVARCHAR(4000);
DECLARE @commaIndex INT;
DECLARE @currentRole SYSNAME;
SET @remainingRoles = @roles; -- @roles == the value sent into the stored proc
SET @commaIndex = CHARINDEX(',',@remainingRoles);
WHILE(@commaIndex <> 0)
BEGIN
SET @currentRole = LEFT(@remainingRoles, @commaIndex);
-- Do stuff with the current role here!
SET @remainingRoles = RIGHT(@remainingRoles, @commaIndex + 1);
END
IF LEN(@remainingRoles) <> 0
BEGIN
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles
END
DISCLAIMER: The above has not been tested or optimised. E&:OE
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi Colin,
I managed to work through your code, and struggled a bit. Your RIGHT statement is incorrect. The input @Role value is "AnotherTestAdmin,Member,SystemAdmin,TestAdminn".
My changed code is (PLEASE FEEL FREE TO COMMENT AND CHANGE AS PLEASED), not sure if I'm using the variables in the right places??
ALTER PROCEDURE [dbo].[sp_Member_InsertMember]
(
@Roles nvarchar(200)
)
AS
BEGIN
DECLARE @RemainingRoles nvarchar(4000);
SET @RemainingRoles = @Roles;
DECLARE @CommaIndex int;
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
DECLARE @CurrentRole sysname;
WHILE(@CommaIndex <> 0)
BEGIN
SET @CurrentRole = LEFT(@RemainingRoles, @CommaIndex - 1);
PRINT @CurrentRole;
SET @RemainingRoles = RIGHT(@RemainingRoles, LEN(@RemainingRoles) - @CommaIndex);
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
END
IF LEN(@RemainingRoles) <> 0
PRINT @RemainingRoles
END
Not sure what you mean by:
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles
And why do you have the following declaration?
DECLARE @currentRole SYSNAME;
Hope you can shead some more light??
Thanks
ma se
|
|
|
|
|
ma se wrote: Not sure what you mean by:
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles
Where you have put the PRINT statements is where you need to put your code that deals with the roles - e.g. insert them into your table. In the loop the variable that contains the role name is @CurrentRole, after the loop the variable that contains the last role is @RemainingRoles.
ma se wrote: And why do you have the following declaration?
DECLARE @currentRole SYSNAME;
Force of habit. SQL Server defines a role name as sysname when it is stored internally: sysusers table[^]
SYSNAME is a synonym for NVARCHAR(128)
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Just getting back to the this stored procedure. Just a little confused.
The stored procedure is called sp_InsertMember.
What it does is to add the member details, like first and last name, DOB, username and password to the tblMember table. If it @@ERROR is equal to 0, then it loops through the roles string passed as a parameter. The stored procedure used to add the member role is called sp_MemberInRoles_InsertMemberRole, and this I call using the EXECUTE function.
Now my questions are:
In sp_InsertMember I start off with a BEGIN TRANSACTION. If the member was added successfully, where do I add the COMMIT TRANSACTION? Before or after looping through the string of roles?
In the sp_MemberInRoles_InsertMemberRole I also have a BEGIN TRANSACTION and a COMMIT TRANSACTION. Lets say the member was added succesully, then it starts to loop through the string of roles. And lets say the member ahs 3 different roles. After the inserting the first role, it encounters an error, so in sp_MemberInRoles_InsertMemberRole it does a ROLLBACK TRANSACTION. Hoe far does it do this rollback? Will it remove the first role it added? What I want it to do is if the member was inserted successfully, and there is an error with inserting roles, that it removes all roles added, plus the member details.
Here is a modified copy of sp_Member_InsertMember:
ALTER PROCEDURE [dbo].[sp_Member_InsertMember]
(
-- input parameters here
)
AS
BEGIN
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
DECLARE @RemainingRoles nvarchar(4000);
SET @RemainingRoles = @Roles;
DECLARE @CommaIndex int;
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
DECLARE @CurrentRole sysname
IF (@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
IF (EXISTS(SELECT MemberUsername
FROM dbo.Members
WHERE MemberUsername = @Username))
BEGIN
SET @ErrorCode = -2 -- Username already exists
GOTO CleanUp
END
INSERT INTO Members
(
-- column names
)
VALUES
(
-- input parameters
)
IF @@ERROR <> 0
BEGIN
SET @ErrorCode = -1
GOTO CleanUp
END
-- Loop thru roles string
WHILE(@CommaIndex <> 0)
BEGIN
SET @CurrentRole = LEFT(@RemainingRoles, @CommaIndex - 1);
EXECUTE dbo.sp_MemberInRoles_InsertMemberRole @@IDENTITY, @CurrentRole
SET @RemainingRoles = RIGHT(@RemainingRoles, LEN(@RemainingRoles) - @CommaIndex);
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
END
-- If all is cool
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
CleanUp:
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
My sp_MemberInRoles_InsertMemberRole has the exact same structure, but just different input values and column names. Also, please comment on the wat that I have doe things here.
Regards,
ma se
South Africa
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
