|
I am executing a call to "sp_helptext" from a web service, in order to obtain the text of a stored procedure. The call itself seems to be ok but eventually it throws an exception saying that the specified object does not exist in the database - which is a false statement, i've checked carefully.
I probably do not have enough rights to access something from the ASPNET account. Do i have to login as "sa"? Is this the only solution available?
"though nothing
will keep us together
we can beat them
for ever and ever"
rechi
|
|
|
|
|
You don't give enough information. An exception gives information about what went wrong, What does it say?
Bogdan Rechi wrote:
Do i have to login as "sa"? Is this the only solution available?
Never ever ever under any circumstances run SQL Server through ASP.NET an application with the sa (or any user that has the sysadmin fixed server role). I cannot emphasise enough the large wide gaping security hole you will open in your application if you do.
Also, the SQL Server documentation have this to say about sp_helptext: "Execute permissions default to the public role." That means everyone who can log in to the SQL Server and has access to that database can call the stored procedure. So, you have absolutely no reason what-so-ever for using sa.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
An exception gives information about what went wrong, What does it say?
It's an SqlException saying that 'dbo.a_procedure' does not exist in database 'a_database', where a_procedure is a stored procedure and a_database is the database.
If i call sp_helptext 'a_procedure' from the Query Analizer then it works just fine and prints the text as expected. That's why i was so confused that i even thought about using 'sa'.
Thank you for the reply.
"though nothing
will keep us together
we can beat them
for ever and ever"
rechi
|
|
|
|
|
I believe sp_helptext is in the master database so it needs the full namespace to the stored procedure. Try executing it with the prefix "master.dbo.sp_helptext" like so.
EXEC master.dbo.sp_helptext yourstoredprocedure
If this doesn't work then step two is to type in the servername before "master".
If that doesn't work, then also try
EXEC master.dbo.sp_helptext yourdatabase.yourdatabaseowner.yourstoredprocedure
The first one should be all that's needed, but if it doesn't then try the other two.
|
|
|
|
|
I'm not exactly sure whether what tojamismis said is enough. sp_helptext reads syscomments table of the particular database.
AFAIK, first you need to give ASPNET permission to execute the stored procedure itself. If it's not enough, try giving it read access to the syscomments table of the database.
HTH!
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi,
In my c# application, I have two separate layers for db and ui i.e. two dlls. I want to make generalized insert, update, delete functions that support these operations for any table and any column types...
To accomplish this, first of all how can we generate an insert/update/delete sql statement if the table name is given ??
Cheers
CNU
|
|
|
|
|
NOTE: You don't specify what your backend database is, so I will assume SQL Server.
Cnu wrote:
To accomplish this, first of all how can we generate an insert/update/delete sql statement if the table name is given ??
The SqlDataAdapter can do this. By supplying a SELECT statement it can figure out the INSERT/DELETE/UPDATE for the table. However, it does not work with joins.
Anyway, I am not sure that this is the best way to proceed. A better way would be to create appropriate stored procedures and have your DAL (Data Access Layer) call the stored procedures. This adds a level of security and performance is improved over calling ad hoc SQL queries against a database.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
I have a server app that opens a connection to the database, does some SQL queries, then closes the connection.
I'm wondering if simply keeping the connection open for the duration of the server's lifetime is a better idea. Any opinions?
My thinking behind this: opening and closing the connection in a multithreaded server with an asynchronous MSDE connection is difficult to do; it'd be easier just to keep the connection open at all times. What do you guys think?
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Bad bad!
Always close the connection. There is no point in tying up server resources. Besides, framework does connection pooling for you. 
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
Thanks for the reply. As I mentioned in the first post, my current code opens and closes on demand, just as you suggest. However, once I've got simultaneous SQL commands being executed/queried, what then?
For example, consider the following:
void DoTwoThingsAtOnce()
{
Thread reader = ReadSomeStuff();
Thread doer = DoSomeStuff();
reader.Start();
doer.Start();
}
SqlConnection connection;
void ReadSomeStuff()
{
connection.Open();
...
connection.Close();
}
void DoSomeStuff()
{
connection.Open();
connection.Close();
}
The above code will begin executing ReadSomeStuff and DoSomeStuff both on different threads. When ReadSomeStuff finishes, it will close the connection -- but alas, DoSomeStuff is not finished with the connection, so closing it will cause DoSomeStuff to fail.
Should I instead be creating connections objects for each operation?
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Yes, you should have separate connection objects. A connection is normally associated with some resource - for example, in SQL Server a connection is associated with a 'server process', which is itself tied to a User Mode Scheduler thread, which can only run on a single CPU.
Your concurrent operation here is a really bad idea as SqlConnection objects are not thread-safe. You're likely to corrupt the state of the object entirely if you call Open simultaneously on both threads. Even if you passed an opened connection to both threads, they're likely to stomp all over each others' internal TDS buffers. The SQL Server Managed Provider does all the high-level command formatting in managed code, then uses the Net-Library stack to submit the command to SQL Server.
Use SqlConnection's connection pooling. The cost of retrieving an existing connection from the pool is minimal.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, that's exactly the information I needed. Yeah, as far as the concurrent operations go, I was simply locking the connection object, and doing synchronous reads and writes.
So to retrieve an existing connection from the connection pool, do I just call connection.Open again? Or is there some other specific way of doing this?
*edit* nevermind, MSDN says yes, connection.Open does in fact use pooling as long as the connection string is the same. Thanks for your help Mike.
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
That's the worst possible thing you could do... Open the connection only for as long as you need it, then close as soon as possible. There is no excuse or reason to ever hold a connection open if your not using it.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Right, and my previous code was in fact opening/closing as needed; I guess I was asking for options in the face of asynchronous operations more than anything. It looks like connection pooling gives me what I want, and that is what I'll be using. Thanks for your reply.
Any remotely useful information on my blog will be removed immediately. There are 10 kinds of people in the world. Those who have heard of the ubiquitous, overused, worn-out-like-an-old-shoe binary "joke" and those who haven't.
Judah Himango
|
|
|
|
|
Is it possible to use a Stored Procedure with a Disconnected Recordset? The code below gives me the error: <b>Operation is not allowed when the object is open.</b>
I have been trying to figure this out for a few hours now... and am hoping someone can give me some guidance.
<code><% @Language = "VBScript" %>
<%
Option Explicit
Response.Buffer = true
Response.Expires = 0
Dim objConn
Dim objCmd
Dim objParams
Dim objRs
Dim item
Dim rsFirstName
Dim rsLastName
%>
<!-- #include file="_inc/adovbs.inc" -->
<%
Set objConn = Server.CreateObject("ADODB.CONNECTION")
objConn.Open(Application("StoredProcedures_ConnectionString"))
Set objCmd = Server.CreateObject("ADODB.COMMAND")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "usp_authors_perm"
objCmd.CommandType = adCmdStoredProc
Set objParams = objCmd.CreateParameter "au_lname",advarChar,adParamInput,40)
objCmd.Parameters.Append objParams
objParams.value = "m"
Set objRs = Server.CreateObject("ADODB.RECORDSET")
objRs.CursorLocation = adUseClient
Set objRs = objCmd.Execute
Set objRs.ActiveConnection = Nothing 'Problem occurs here: Operation is not allowed when the object is open.
objRs.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set rsFirstName = objRs("au_fname")
Set rsLastName = objRs("au_lname")
objRs.CacheSize = 10
while NOT objRs.eof
Response.Write rsLastName & "<br/>"
objRs.MoveNext
wend
%></code>
|
|
|
|
|
Try specifying your cursor as forward only, and use Batch Optimistic locking. Not sure if it'll work, but it's worth a shot.
|
|
|
|
|
Hi,
Does any body know how to take SQL Sever 2000 or Access Database backup in c#.net? Mean what program we write in c#.net to take whole database [Table,Relation etc] backup.
Please help me...
Muhammad Waqas Butt
waqasb4all@yahoo.com
Muhammad Waqas Butt
|
|
|
|
|
Hi,
I am a newbie to SQL and all I know is insert, delete and update 
I have to upload some resources (images and files) to the database. For this purpose I have created a column with datatype varbinary and length 8000.
But I have some problems in this process :
1. Is there any way that we can upload files of size more then 8000 ?
2. I tried uploading a file of size 2393 KB on disk. But I get the following error:
System.Data.SqlClient.SqlException: Cannot create a row of size 8077 which is greater than the allowable maximum of 8060.
This is how I create the byte array :
FileStream fs = new FileStream(aFilePath, FileMode.Open);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, (int)fs.Length );
fs.Close();
this is how I upload to db :
SqlParameter sqlParam = new SqlParameter("@" + fieldName, sqlDbType.VarBinary, 8000);
sqlParam.Value = buffer;
mySqlCommand.Add(sqlParam);
mySqlCommand.ExecuteNonQuery();
Sorry, the above code is in bits and pieces since I cannot put all here...
Please help me...
Cheers
CNU
|
|
|
|
|
|
Thanks a lot...
I changed the sqlparameter staement by removing the size. It is working now.
But I have another problem....
This is how I am displaying the downloaded image on a lable.
System.IO.MemoryStream ms = new MemoryStream();
ms.Write(byteArray, 0, byteArray.Length);
ms.Flush();
this.lblImage.Image = System.Drawing.Image.FromStream(ms);
ms.Close();
The exception is as follows:
System.ArgumentException: Invalid parameter used.
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement)
at System.Drawing.Image.FromStream(Stream stream)
at InitBasicInfo() in d:\projects\tc project\tcstudio\frmservermodel.cs:line 769
Thanks.
Cheers
CNU
|
|
|
|
|
You are trying to hard to construct your MemoryStream! This works just fine:
System.IO.MemoryStream ms = new MemoryStream(byteArray);
As a sidenote, ms.Flush() is overriden to do nothing at all.
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
|
I have an expereince similar to you. But what I have don't is working with access database. I go the tip from my teacher that don't store an object (if you want to store an image/picture) in your database. Because it would increase a lot of space when your record increase. The best way to do this just create a field with text string and hold the path that point to the picture file. When you load the record on the form just load the picture in the picture box base on the path that you store in the database. This will decrease a lot of space from your database and improve a lot of database performance. I also test it and it is work very well.
But remember! if you use the method from me, becareful when you image is change it file name or file path. You have to handle the RUN TIME error in your application to do that.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi all,
Why wont my sql query work.
It returns with no records.
SELECT pkey, description
FROM Test
WHERE (((test.description) Like 'DEL%'))
Yet there is a record with 'DELIVER' in the description col of the Test table.
Tnx
|
|
|
|
|
Try testing it with the SQL builder
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Test your query in Query Analyzer.
By the way those parenthesis are extra 
This posting is provided "AS IS" with no warranties, and confers no rights.
Alex Korchemniy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
