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
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?
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.
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 ? )
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.
i 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.
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!
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?
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.
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
Can 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?
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?
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?
Anyone 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
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
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.
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
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
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)
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
I've been using stored procedures to do queries including inner joins etc. I have always known about views but don't understand why they are used, what is there purpose, they seem the same as sql queries, is that true? Is it more effiicient to use views rather than stored procedures to do your queries?
Really I just want to know why I would ever want to use views instead of stored procedures to do my queries?
I would like to know if its possible to create a view of a table residing in a SQL server which is not the one in which we r currently working in Desc: 1) Say i have 2 SQL servers( different Boxes) A & B I am working in ' B ', can i have a view of a table residing in SQL server 'A' in my system( Server 'B') 2) Leaving out the network uncertainities, will such a setup give any performance issue when the view is for a table that captures daily transactions running into several thousands of records 3) Is ther any other Alternative to access tables in another SQL sever without going for Views?
Hi there,First thing off, I'd like to know what Views are useful for? I thing it can speeds select query containing multiple join, but I'm not even sure if that's true.Second, assuming that Views speeds thing up for SELECT query, can it be used by NHibernate when restoring object from their persistence store (in this case, SQL 2k5) ? If not, can they be (views) of any use if we use NHibernate?Third, would it be better to create our crystal report using Views to improve performance?Thanks for anyone who participates in this thread! :-)
Is it possible to make views which takes Data from Multiple Servers? NOTE: The view is very complex because it uses FOUR Diff. statements joined together with UNION clause.
One more Q. If my Query Analizer Displays following on execution of query. " Internal Query Processor Error: The query processor could not produce a query plan." What should be my approach to rectify the problem.
I have two databases on my Server - database1 and database2. Database1 has a view on tables on database2 : create view myview as select * from database2.dbo.tablename (because all tables in database2 are owned by dbo) and myview on database1 is owned by dbo. when a user on database1 - lets say user1 tries to select rows from myview, we get an error saying that user1 is not a valid user on database2. it only allows user1 to select rows from myview if i make user1 a user on database2 as well, which is not what i want. Can someone please point me in the right direction. i would like to be able to select rows from a view which is based on tables in another database. thanks simran