How to check that there is transaction not yet commit in SQL server 2005?
anyone know what is the command to check un-commited transaction? ...
sql-server
Is it possible to easily detect or predict a Primary Key violation in SQL?
My issue is that I want to run a query to check if the data I am using will be a duplicate of something already in the database, for example I have a Product Code and want to stop the same item being listed multiple times, and this Product Code is the Primary Key. Can I avoid doing this: SELECT ProductCode FROM tblProducts WHERE Product...
asp.net session using sql server mode
Hi I am using a ASP.net session in SQL Server mode. I have created the necessary tables and stored procs in a custome db My question is: Can I use this database to serve more than one application / web site ? Is there anything to take into consideration having multiple websites use the same db for their session store cheers ...
Does it make sense to put an index on tiny tables
I have a few tables with 4 to 10 rows. We don't anticipate that these tables will ever grow much more that a few more rows. Does it make sense to put an index on their primary keys. ...
.net Compact Edition 2.0, SQL Server Compact Edition and replication
Hi Guys, I have been tasked with creating a mobile application for a Windows 6.0 CE device which is running .NET 2.0.7045.0 Compact Framework. I have SQL Server 2005 Express running on my local machine and I want to let the Mobile Application have a replication or snapshot of my database, so the users can update the data in the field. ...
Can I create a One-Time-Use Function in a Script or Stored Procedure?
In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I'd like to abstract away some complexity in a script I'm writing, but it would require being able to declare a function. Just curious. Thanks!! ...
NOLOCK on Views, does it propagate to the tables?
If you include a NOLOCK on a select from a VIEW, does that NOLOCK propagate down into the tables joined within the view? If not, what's the purpose of locking a VIEW? Sample View Code: CREATE VIEW [my_custom_view] AS SELECT a1.[column_a], a1.[column_b], a1.[column_c], a1.[column_d] FROM [table_a] a1 JOIN [table_b] b1 O...
SQL server management from visual studio
I need to make some permission changes on a MS SQL server (2005) database. Some tables read only for all but dbo, some tables read-write for all etc. In the past I used the management program that came on the SQL server disk. That is not an option for me right now. I cannot find a place in visual studio to alter table permissions. D...
What is your preferred syntax for SQL aliases?
I have seen many different syntax styles for aliases in SQL. Are there any advantages/disadvantages to any of these syntax styles? What is your preference? Alias only: SELECT patient.nm_first patient_firstname, patient.nm_last patient_lastname, physician.nm_first physician_firstname, physician.nm_last phys...
Database connections work fine when application is run from localhost. Login fails from dev server.
I have an application which connects to a database, retrieves a username from a user's table and matches it against the username retrieved with System.Security.Principal.WindowsIdentity.GetCurrent.Name On my localhost, everything works fine. The database exists on the development server but the application lies on my localhost. All of...
tsql string concat with select and order by does not work with function in order by clause?
Consider the following tsql... create function dbo.wtfunc(@s varchar(50)) returns varchar(10) begin return left(@s, 2); end GO select t.* into #test from ( select 'blah' as s union select 'foo' union select 'bar' ) t select * from #test; declare @s varchar(100); set @s = ''; select @s = @s + s from #test order by s; selec...
Can't Convert unicode Data into XML column in sql server 2008
Dear All, I already have a table with 3 column (Id bigint, Title nvarchar(450), Description nvarchar(MAX)) in sql 2008 I decide convert Title and Description column into one XML column. but when trying to update get many error like "illegal name character" or "illegal qualified name character" and etc. to solve this problem i just cre...
What is the best way to auto-generate INSERT statements for a SQL Server table?
We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables. Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query. The first ...
the multi-part identifier could not be bound
select (case when t.freeplayabandoned != f.freeplayabandoned then 'freeplayabandoned' when t.freeplaydownloaded != f.freeplaydownloaded then 'freeplaydownloaded' end) from testtable t where not exists (select * from freeplay.egmfreeplay f where f.freeplaydownloaded = t.freeplaydownloaded) I was trying to get the col...
SQL Server 2000 > EXECUTE AS
Hey all. Could someone tell me how to elevate the execution of a stored procedure in SQL Server 2000? I've tried using EXECUTE AS, but that was added in SQL SERVER 2005. ...
SQL query doubt :removing prefix characters
In SQL, how can I remove the first 4 characters of values of a specific column in a table. Coulmn name is Student Code and an example value id ABCD123Stu1231. I wan to remove first 4 chars from my table for all records Please guide me ...
Connection to Oracle in Reporting Services won't work after sp3 and cu1
Hi, Last night I installed sp3 and cu1 on a 64 bit SQL 2005 failover cluster. Everything went well. Reporting services is installed on the primary node. It now cannot connect to Oracle. The initial errors seemed to be related to this: http://support.microsoft.com/default.aspx?scid=KB;en-us;870668 I granted network service access, b...
Tool to convert T-SQL to graphical model?
Sometimes I am looking at complex SQL Server SQL statements and wondered if there's a tool which can represent the query into a graphical model. For example: You have a select query which joins (could be inner + left and right joins) with 10 tables. Is there a tool to take this query, represent the 10 tables graphically and draw the di...
SQL update query using joins
I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number ,mm.ManufacturerID from item_master im, group_master gm, Manufacturer_Master mm where im.mf_...