While 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?
I 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 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 RelatedI 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 Replies View Relatedis 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 Related Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
What is @@variables means in sql server?
View 3 Replies View RelatedIs there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies View RelatedHi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)
Hi
I think this is an easy question.
Is it possible to use a variable in a create database statement
i.e.
declare @db_name varchar(20)
select @db_name='new_db'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name
CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).
Thanks,
Jim