In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me? ...
I've been testing my app on a SQL Server 2005 database, and am trying to establish a preliminary picture of the query performance using sys.dm_exec_query_stats. Problem: there's a particular query that I'm interested in, because total_elapsed_time and last_elapsed_time are both large numbers. When I tickle my app to invoke that query (...
I have a stored procedure on a SQL Server 2005 database which has a statement like this: IF @Condition = 0 BEGIN RAISERROR('some error message',16,1) RETURN END and it's called from a C# client like so: try { SomeVariable = SqlHelper.ExecuteScalar(GetConnectionString(), "MySP", new obje...
Hi What is the best way to delete a large, binary column from a SQL Server 2005 database yet minimise the size of the resulting .mdf and .ldf files? Our app used to allow users to upload their documents. These were stored as BLOBs in a column in a documents table. Over time, this table grew to be >90% of the overall database size. W...
My organisation uses Microsoft CRM 3.0, and I am attempting to backup the database. The following error is preventing me from doing so, does anyone know how to resolve this issue? Error: System.Data.SqlClient.SqlError: The backup of full-text catalog 'ftcat_documentindex' is not permitted because it is not online. Check errorlog file f...
I am afraid that i am unable to locate the absolute path of the SQL SERVER data files. I have tried to so do by doing the following. foreach( Database db in srv.Databases) string filepath=db.PrimaryFilepath; string name=db.Name; abspth=filepath+"//"+name+".mdf"; Like this i have workaround.But is there is any alter...
Hello everyone, I have a table, schema is very simple, an ID column as unique primary key (uniqueidentifier type) and some other nvarchar columns. My current goal is, for 5000 inputs, I need to calculate what ones are already contained in the table and what are not. Tht inputs are string and I have a C# function which converts string in...
I have two clients A and B. Both clients have identical, synced local data caches. If client A makes an offline edit to record X and then client B also offline edits record X and syncs with the server, when client A syncs with the server, the change that client B made is not reflected and no amount of bidirectional syncing makes the tw...
This is similar to a previous question MSSQL: Change type of a column with numbers from varchar to int, except in this case not all of the original data can be successfully converted from varchar to int. In these cases, I want the output to just be NULL. For example, the incoming set of values in a varchar column will look like: {'123...
I have the following situation: I have a stored procedure that takes data from a bunch of tables and creates a record into a single table. After that the end user can get some graphics based on the data from the row in the resulting table. The problem is that the collecting of data from multiple tables into the resulting one can have a v...
Hello everyone, I searched but cannot find a good temp table usage tutorial for a newbie in SQL Server 2005/2008. I want to learn pros and cons of temp table compared with normal table, its life time and how temp table is shared (in the same session, cross sessions)? thanks in advance, George ...
How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log? Let's say I have the following scenario. I have a Delete operation that's too big for the current transaction log. I wan't to: Increase the transaction log (can I detect the current size?, can I tell how large I need th...
Is there any way I could find out programmaticaly what databases exist on a server? It's because I would like to show user all available databases and allow him to choose the one he's interested in. ...
Hi, I have a question about mirroring and cross-db views. We have a view in database X which references database Y on the same server S1. The databases X & Y are both mirrored onto another server S2. The problem is: if db X fails over to S2, the view which references db Y is now looking on S2, but db Y may not have failed over. What is ...
Hi, Currenly I am using Oracle 9i Lite Client to connect my VB 6.0 application to Oracle database. The Oracle 9i Lite Client is installed on all the user's machine. (around 200) The VB 6.0 application also needs to talk to SQL Server 2000 database I am trying to think for replacement of Oracle 9i Lite client so that I can bring down...
I've got a grails 1.1 web app running great in development but when I try and run it in production with an sqlserver database it crashes in a weird way. The relevant part of my datasource.groovy is as follows: environments { development { dataSource { dbCreate = "create-drop" // one of 'create', 'create-drop','update' ...
In SQL Server 2005 how can you use a variable for the username to GRANT or DENY permissions to objects withing the database? I have tried: DECLARE @username varchar(30) SET @username = 'DOMAIN\UserName' GRANT SELECT ON [mytable] TO @username GRANT UPDATE([field one], [field two], [field three]) ON [mytable] TO @username I get Incorrec...
I have a strong database background but very little practical experience with ms sql server. I want to learn the tools, the architecture, profiling and tuning. Besides browsing around for little bits and pieces of information, I'm actually looking for larger guides. Books, training, series of articles, etc. How do you recommend I start? ...
I'm looking for the best way to go about adding a constraint to a table that is effectively a unique index on the relationship between the record and the rest of the records in that table. Imagine the following table describing the patrols of various guards (from the previous watchman scenario) PK PatrolID Integer FK GuardID Integer...
I currently have a 'Filter' object which corresponds to a business object. This object has properties that relate to the different ways that I want to be able to filter/search a list of such business objects. Currently these Filter objects have a method that builds the contents of a where-clause that is then passed to a SQL Server 2000...