I was actually asked this myself a few weeks ago, whereas I know exactly how to do this with a SP or UDF but I was wondering if there was a quick and easy way of doing this without these methods, I'm assuming that there is and I just can't find it. A point I need to make is that although we know what characters are allowed (a-z, A-Z, 0...
I use RAD Studio 2010 including latest updates 4 and 5, my database is SQL Server Express. I set up a TSQLConnection but it won't connect, error message is "DBX-Error: the driver could not be initialized correctly. A client library may be missing, may not be installed correctly, or may have the wrong version" (error messagetranslated fr...
I have a SQL Server 2008 database with a composite key: ProjectID (GUID) and TaskID (int). ProjectID is a foreign key to a Projects table. I want to have TaskID Auto-Increment, but restart for every ProjectID (that is: every projectID should have 1,2,3,... as TaskID). To my knowledge, this is not possible in SQL Server out of the box, a...
I want to load a bunch of view definitions into SQL Server 2005 & 2008. I am using IF/ELSE logic to dynamically building Create or Alter statements that I then EXEC. This works fine. However, unless I get the order of the statements correct, I get errors if a view is dependent on another view that would be created in a later statement. I...
I know Scope_Identity(), Identity(), @@Identity, and Ident_Current all get the value of the identity column, but i would love to know the difference. Part of the controversy i'm having is what do they mean by scope as applied to these functions above? I would also love a simple example of different scenarios of using them? ...
hello everyone, i am using collection object. In some cases no of objects become large. in that case what i have to do either loop through the object collection or do a new hit? which gives more optimal to performance ? ...
I would like to be able to round a number to n significant figures in SQL. So: 123.456 rounded to 2sf would give 120 0.00123 rounded to 2sf would give 0.0012 I am aware of the ROUND() function, which rounds to n decimal places rather than significant figures. ...
I am using SQL Server 2005 for full text searching. I did try the same create statement (with the same DB server collation //edit: Latin1_General_CI_AS) on SQL Server 2008 but it does not work anymore for umlauts: create table "ArtistFullTextTest" ( "ID" int IDENTITY(1,1) not null CONSTRAINT ArtistFullTextTest_PK PRIMARY KEY, "...
I have a procedure like this: create procedure Checkout @Foo nvarchar(20), @cost float as begin transaction declare @Bar nvarchar(20); select @Bar = Bar from oFoo where Foo = @Foo; update Foo set gold = gold - @cost where name = @Foo; update Bar set gold = gold + @cost where name = @Bar; delete from oFoo where @Foo = Foo; commit tra...
If you have a stored procedure that deletes record in a table, do you have to put a return statement and why? I have always never put a return statement, but I just saw a snippet that has a return statement. Sample: DELETE FROM TableName WHERE TableId = @Id RETURN ...
Hello, I'm currently working on implementing a fulltext search engine for one of our sites, using the features that the CMS itself offers. It allows me to execute an SQL query without doing middle-tier programming. However, this also means that I can't use programming to filter or clean the search query data up. Works fine, until a use...
Dear ladies and sirs. First a little introduction. We have an SQL Server Express 2008 database, which schema is kept in the source control in the form of the scripts, originally created by opening the context menu on the database in the Management Studio and selecting Tasks|Generate Scripts .... Each developer has a local SQL Server ...
Hi, With the following quiry i get the list of top 10 most expensive queries with their SQL statements. select top 10 * from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) order by max_logical_reads desc But how can I extract their object names ? ...
Hi, I have a table that contains many rows of SQL commands that make up a single SQL statement (to which I am grateful for this answer, step 5 here) I have followed the example in this answer and now have a table of SQL - each row is a line of SQL that build a query. I can copy and paste the contents of this table into a new query win...
Trying to get the number of incidents in my database, in the format: date numberOfIncidents date numberOfIncidents date numberOfIncidents date numberOfIncidents I supply the start date / end date to my SQL stored procedure, but if there is no entry in the database for a particular date (because no incidents occured) then I won...
I am developing a windows desktop client/server application in .NET where the client application connects to SQL Server Express 2005 via the SQL native client and a connection string. The client then executes SQL over the connection on the database directly (no stored procedures). How can I configure SQL Server (or windows) security in...
I want to optimize this query WITH CTE AS ( SELECT KladrItemName _KladrItemName , WordPositionKladrItem _WordPositionKladrItem , WordPositionAddressString _WordPositionAddressString , StartPosition _StartPosition , EndPosition _EndPosition , Metric _Metric , IsConstruction ...
When you log LINQ-to-SQL's query output via the "Log" property on the DataContext object, you get output similar to: SELECT [t0].[fullaname], [t0].[Worker], [t0].[Office] FROM [dbo].[Workers] AS [t0] WHERE [t0].[OfficeID] = @p0 ORDER BY [t0].[Name] -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [412] -- Context: SqlProvider(Sql2005) ...
In SQL server 2008 I have a hierarchical field (hierarchyid). How do I change its value or insert new row when using SQL Server Management Studio "edit rows" command? Is there a textual representation that will be converted to an ID? Yes, I know I could do it using a query, but I would like to manually enter it as TEXT using the studio ...
Is there a dependable way to find dependencies among views and tables in SQL Server 2005? sys.sql_dependencies doesn't list all of my dependencies. (I thought I saw a similar thread here but can't find it now. Sorry if this is a dup). ...