Is it possible to use SQL Server 2008 CROSS APPLY with LINQ-2-SQL? Example SQL: select d.dateCol, tvf.descr, tvf.value from dateTable d cross apply tvFunction(d.dt, 'anotherParam') tvf where d.category='someCat' CROSS APPLY enables using values from a table (dateTable in the example) as parameters to a tablevalue function. This is v...
I have some data with messed-up accented characters. For example in the data we have things like ClΘmentine that should should read Clémentine I'd like to clean it up with a script, but when I do this for example Select Replace('ClΘmentine', 'Θ', 'é') this is what I get: Clémenéine Apparently Θ matches both Θ and t. Any ideas ...
I have backed up an encrypted DB (symmetric key/certificate) and restored it on a different server. Unfortuantely we're having problems with the decryption... hoping someone can help. In the restored db, I can see the Symmetric Key and the Certificate in SSMS, but when I try to Open the key using the cert ( open symmetric key Key...
There is a store procedure that uses FREETEXTTABLE twice on two tables and then merges the results and returns the top 50. The problem is if I do a search on "Women of Brewster", the results returns "Confession of an ex doofus motha" with a rank of 143 from table A and second "Women of Brewster Place" with a rank of 102 from table B. ...
I guess the real question is: If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of: the current SELECT statement other transactions against the given table Example: Select * from aTable with (NOLOCK) ...
I continue to get this error: Object '%s' cannot be renamed because the object participates in enforced dependencies I need to find a script that will help me to find all the dependencies that there is with this table. I will need to drop them, rename, then bring them back. Thanks. SQL Server 2005 ...
I am working with some tables where I want the C# class to have a different property name than the underlying table column. However, when I use the Translate method to read the results, the properties that don't match the source name never get populated. Even when I use Linq to generate the SQL. For instance, my table is defined in the ...
I have a database for a web application that is home to some personal information about my users. What should I do to make sure the data is secure? Encrypting the data makes sense, of course... but what about stopping somebody from getting on the machine to begin with? What do I do about the developers that need access to the data, to...
The reports I can churn out in SQL Server 2005 Reporting Services using the MS Business Intelligence Development Studio is rather limited for what I want to do. I have had a look at a similiar stackoverflow question and have had a browse through the MSDN website. I have managed to add some additional functionality from examples I've foun...
A while ago I had a query that I ran quite a lot for one of my users. It was still being evolved and tweaked but eventually it stablised and ran quite quickly, so we created a stored procedure from it. So far, so normal. The stored procedure, though, was dog slow. No material difference between the query and the proc, but the speed c...
We develop websites using SQL Server and ASP.NET. Development: web on developer PC, database on DEV server Testing: both on TEST server Live: both on LIVE server At the moment, DEV and TEST servers are separate physical servers. I'm considering making them virtual servers on the same physical servers. It is important that is easy...
In my database, in one of the table I have a GUID column with allow nulls. I have a method with a Guid? parameter that inserts a new data row in the table. However when I say myNewRow.myGuidColumn = myGuid I get the following error: "Cannot implicitly convert type 'System.Guid?' to 'System.Guid'." ...
Hi We use Guid's for primary key, which you know is clustered by default. When inserting a new row into a table it is inserted at a random page in the table (because Guid's are random). This has a measurable performance impact because the DB will split data pages all the time (fragmentation). But the main reason I what a sequential Gui...
Hi, I have a sproc that returns a single line and column with a text, I need to set this text to a variable, something like: declare @bla varchar(100) select @bla = sp_Name 9999, 99989999, 'A', 'S', null but of course, this code doesn't work... thanks! ...
Hi, Are there any performance benefits of using SQL Server 2008 over SQL Server 2005? ...
(sql server 2005) A person uses their cell phone multiple times per day, and the length of their calls vary. I am tracking the length of the calls in a table: Table: Calls [callID, memberID, startTime, duration] I need to a query to return the average call length for users PER DAY. Per day means, if a user used the phone 3 times, fi...
I'm currently doing some GUI testing on a ASP.net 2.0 application. The RDBMS is SQL Server 2005. The host is Win Server 2003 / IIS 6.0. I do not have the source code of the application because it was programmed by an external company who's not releasing the code. I've noticed that the application performs well when I restart IIS but a...
I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days. Here's what I've got that's not working: DECLARE @DateString CHAR(8) SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1) EXECUTE master.dbo.xp_delete_file 0, N'e:\Database Backups',N'7z', @DateString, 1 I've ...
Within a stored procedure, another stored procedure is being called within a cursor. For every call, the SQL Management Studio results window is showing a result. The cursor loops over 100 times and at that point the results window gives up with an error. Is there a way I can stop the stored procedure within the cursor from outputting an...
Hi All, I have a SQL Server 2000 database with around a couple of hundred tables. There are several SQL user accounts that can access this database but each one has different permissions granted on tables in the DB. How do I create a script to give me a report of the permissions granted to a particular user. i.e. to generate something...