I know you can use sql profiler to see what sqlcode actually executed when you run a sproc, but is there any way toget this information in asp.net? After executing a sproc, I'd like to send the sqlcode that was sent, to my Audit class. Is there any wayto retrieve this in asp.net itself?cheers!
View 1 RepliesI am wondering what the advantages of using CRL Sprocs over T-SQL sprocs and what not.
Looking for such comparison and articles on websites resulted in only "how to create CRL sprocs" but none of them were talking about what they are used for in what situations.
I would really appreciate it if you guys can post comments, links and external articles.
Thank you in advance.
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I find the replication put many sprocs with sp_ prefix in our database. Do you think that should be changed? I have been told not to use sp_. See http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html.
View 3 Replies View Relatedi have a question. how do i protect my website from sql injection.right now most of my queries are in the form of: Public Sub updateCredits(ByVal deduct As Int16, ByVal userid As Guid) Dim cmd As New SqlCommand Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer2").ConnectionString) cmd.Connection = con cmd.CommandType = Data.CommandType.Text cmd.CommandText = "Update [userprofile] SET credits = credits - @c WHERE userID= @id" cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = userid cmd.Parameters.Add("@c", SqlDbType.Int).Value = deduct Using con con.Open() cmd.ExecuteNonQuery() con.Close() End Using cmd.Dispose() End Sub is that a safe way to do it? using parameters and stuff? or should i completely switch over to stored procedures as i hear they are safer.
View 12 Replies View RelatedWhile trying to assign a variable a table name then later use the variable name in a select statement (ie select sys_id from @table_name) it fails and says incorrect syntax.
How can I use a variable for a table name to later use within the sproc?
is it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?
i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.
I know that stored procedures(sql server) caches stored procedures in memory where it keeps the compiled execution plan in memory, how does it work with the views does sql server store /cache the views. Just wondering Thanks
View 2 Replies View RelatedCan someone explain the generated sprocs of VS2005 if one column can be nullableDependentOfSeqID = @Original_DependentOfSeqID OR ((@IsNull_DependentOfSeqID = 1) AND (DependentOfSeqID IS NULL))In VS2003 the generated sprocs would beDependentOfSeqID = @Original_DependentOfSeqID OR ((@Original_DependentOfSeqID IS NULL) AND (DependentOfSeqID IS NULL))Which is the best?
View 1 Replies View RelatedHello,
I have 3 stored procedures.
A, B, C
sproc B has input variable of int
In sproc A, I want to execute B, then C and UNION them together
Can some one possible help me out with syntax?
Thanks a lot.
Due to a business rule change, I had to take what was 1 column in a table and split it off into a new table. Now I need to find every time that column is used in a SPROC and change those sprocs. Is there a way to sift through the sprocs to search for a "phrase" (the column name) -- other than reading through every one manually?
Thanks
Mark
there's a concept named cyclomatic complexity in software dev which measures the complexity of code by its number of decision points. This would be measured by # of if statements, nested if statements, etc in a method.
Do SQL queries have any type of equivalent? For example, # of joins, # of conditions, etc. Factors into a complexity metric which indicate how complex, risky or error-prone a sproc might be based on certain factors?
I've been using EM for writing stored procedures. I am ready to upgrade to something that works like an IDE. What do you use?
View 7 Replies View RelatedAnyone have the code that would allow me to see if any of my sprocs contain references to a function? I imagine it would someting like select name from sysobjecst where charindex(whatevertextis, 'ufnName') > 0
Thanks
Hi,
Is it possible to rollback changes made to the DB when debugging a t-sql sproc in VS2005? i.e. step through the sproc, then hit rollback and be able to step through it again in the same state
Thanks, moff.
We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.
We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.
Hello.
I'm using what looks to be a popular script to grant execute privileges to stored procedures, and it works great as long as the user account that you want to grant to is not a domain account.
For example, I need to grant execute to myDomaindbUsers, but get a syntax error when the script tries to execute this statement:
SET @SQL = 'GRANT EXECUTE ON [' + @Owner
+ '].[' + @StoredProcedure
+ '] TO myDomaindbUsers'
Incorrect syntax near ''.
The script works fine if a non-concatenated user account is given.
We use Active Directory to manage our access, thus the domaingroup.
Has anyone found a way around this?
Thanks in advance.
Tess
Here's the entire script for anyone who's interested:
USE whateverDatabase
GO
DECLARE @SQL nvarchar(4000),
@Owner sysname,
@StoredProcedure sysname,
@RETURN int
-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name)), 'IsMSShipped') = 0
AND name LIKE 'p%'
OPEN cursStoredProcedures
-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure
-- Set the return code to 0
SET @RETURN = 0
-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN
-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@RETURN = 0))
BEGIN
-- Create the SQL Statement. Since we€™re giving
-- access to all stored procedures, we have to
-- use a two-part naming convention to get the owner.
SET @SQL = 'GRANT EXECUTE ON [' + @Owner
+ '].[' + @StoredProcedure
+ '] TO myDomaindbUsers'
-- Execute the SQL statement
EXEC @RETURN = sp_executesql @SQL
-- Get the next row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure
END
-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures
-- Check to see if the WHILE loop exited with an error.
IF (@RETURN = 0)
BEGIN
-- Exited fine, commit the permissions
COMMIT TRAN
END
ELSE
BEGIN
-- Exited with an error, rollback any changes
ROLLBACK TRAN
-- Report the error
SET @SQL = 'Error granting permission to ['
+ @Owner + '].[' + @StoredProcedure + ']'
RAISERROR(@SQL, 16, 1)
END
GO
Hi,
I really confused , I wanna get an rowid on sql 2000 table so I have created a sproc and it's syntax is OK
How can I check it on sql query analyzer? this sql server 2000
Also How can I use that in select statement?
thanks..
here is my select statement which I have to use sproc inside
select custid,ordernum,sku,amount,
dbo.get_rownums (custid,ordernum,sku ) ???
from tp_cod cod
here is my sproc:
CREATE PROCEDURE [dbo].[get_rownums] @custid as varchar(10),@ordernum as varchar(5),@sku as varchar(10) , @i as int output
AS
BEGIN
DECLARE @SkuID as varchar(10)
--DECLARE @i as int
DECLARE got_sku CURSOR FOR
Select sku from tp_cod where custid=@custid and ordernum=@ordernum
set nocount on
set @i=0
OPEN got_sku
FETCH NEXT FROM got_sku INTO @SkuID
WHILE @@FETCH_STATUS = 0
BEGIN
Set @i =@i + 1
if @SkuID=@sku
begin
return @i
end
else
begin FETCH NEXT FROM got_sku INTO @SkuID end
END
CLOSE got_sku
DEALLOCATE got_sku
END
GO
Hi,
Just a general question here.. I'm designing a web application that might have 50 million - 100 million rows plus. Basically its a simple logging table each row probably only 24 bytes wide, however I can see it taking quite awhile to execute.
The query is basically a group by, showing the amount of "hits" per day.
Are there any special types of strategies I should implement ? Or is a properly designed structure with indexes likely sufficient (on the right hardware of course)
Thanks for any advice!,
Mike
Are there any issues calling SQL 2005 CLR bases stored procedures and functions from a web application which uses the dotnet 1.1 framework?
I assume not, but would like advice from those who've been there...
Thanks,
Marie
I have been attempting to create a managed stored procedure which calls a web service using WSE 3.0 for security.
It appears that the WSE-generated config file (or possibly the app.config file) is not accessible to the .Net code.
Is there a method for using config files with CLR managed sprocs?
Thanks,
Max
In Enterprise Manager one can select several SPROCS/VIEWS using the CONTROL key and then Right-Click to script out those objects. Alternativly, pressing CONTROL-C copies, to the clipboard, the T-SQL to create the selected objects.
SQL Management Studio seems to only allow you to script one object at a time.
Is there a way in SQL Management Studio to select multiple objects and generate create or modify scripts?
There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )
View 1 Replies View RelatedWe have migrated a database (myDb2014)Â to a SQL Server 2014 instance. We have another on a 2008R2 instance (myDb2008). Both on the same server.
I have setup a linked server to the 2014 instance from the 2008R2 one.
I have a number of sprocs on myDb2008 that call TVFs and sprocs on myDb2014. This worked fine when they were both on the same instance.
Now however, I have an issue where I would have to update all the references in each calling query.
I looked into creating Synonyms but this only works for Tables/Views.
Is there a workaround to querying myDb2014 TVFs/sprocs without having to update each calling query in myDb2008?
I need just the names of tables, views and sprocs within a SQL Serverdatabase. What's the easiest way to do this?
View 3 Replies View RelatedHi everyone, I've got a question. How can I Know when was the last time that a Stored Procedure was executed?
I'm thanking for your help.
Oscar.
Hi,
this code inserts twice the same record. I thing it is due to the "Selet Scope_Identity" in the sqlcommand.If i remove the Select part, the inserts occurs only once. If i remove the line "comd.ExecuteNonQuery()", then the inserts also occurs once.
Is there something wrong in my code?
ThanksT.
Dim connection As SqlConnection Dim comd As SqlCommand Dim connectionstr, sql As String Dim iden As Integer connectionstr = ConfigurationManager.ConnectionStrings("econn").ConnectionString.ToString() connection = New SqlConnection(connectionstr) comd = New SqlCommand() comd.Connection = connection sql = "INSERT INTO table(field,...) VALUES (@fld,...); SELECT SCOPE_IDENTITY()" comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).Value = txtvnm.Text ... connection.Open() iden = Convert.ToInt32(comd.ExecuteScalar()) comd.ExecuteNonQuery() connection.Close()
I created a simple DTS which executes a VB standard exe that simply writes a string to an ascii file opened as append. SQL Server, the exe, and the ascii file are all on the same NT box (mine). If I execute the DTS myself the process works with no problems. When I attempt to execute the DTS via a job the job hangs with no apparent indications as to what may be the cause of the hangup. The SQL Server Agent is up and running and set to run under the system account. I have applied SQL Server SP3. The same problem was occuring prior to applying SP3.
Does anybody have any idea? All sugestions are appreciated.
Thanks in advance.
Hi folks
Is there any (easy!) way in which I can see the SQL that has been executed. I'm using stored procs that create & execute other stored procs(via sp_executesql). At the early stages there are often trivial errors in the created procs that cause rather general exception messages that do not give much of a clue as to where the error is. (It's tedious to find these in the debugger) ... and was wondering if there's any trace output type option I can turn on to see what sql has been presented for execution.
Thanks.
Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks
View 3 Replies View Relatedhello, I have a big problem with a script.. some instructions seems to be not executed. I don't understand. If I execute line perline it's ok - but the entire block no.
Explain me and find the solution:
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ACS_ACL'
AND COLUMN_NAME = 'ZoneUId' )
BEGIN
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
UPDATE dbo.ACS_ACL SET ZoneUId = '1' WHERE ZoneUId IS NULL;
print 'end of script'
END
result:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'ZoneUId'.
the error is on line:
UPDATE dbo.ACS_ACL SET ZoneUId = '000000001' WHERE ZoneUId IS NULL;
how is is possible because the preceding line is
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
Hi All,Is there way that commands executed in the query analyzer get loggedautomatically?TIAJoriz
View 3 Replies View Related