I have a stored procedure that builds a dynamic where clause and executes the SQL statement. It looks similar to this: declare @customerId int declare @sql varchar(100) set @customerId = 12 set @sql = Replace('select customerName from customer where customerId = @customerId', '@customerId', @customerId) exec @sql I know the above do...
Our database has a bunch of records that have the same invoice number, but have different dates and different notes. so you might have something like invoice date notes 3622 1/3/2010 some notes 3622 9/12/2010 some different notes 3622 9/29/1010 Some more notes 4212 9/1/2009 notes 4212 ...
Given that I'm using TSQL, what's the name of this style of naming? \\servername\instance.database.schema.table and what other items can be inserted in place of .dbo. in the previous naming instance? how does one go about creating those alternatives? links to answers welcome Also, how would one refer to a job on the server instead ...
I have the following update statement : UPDATE D SET D.Xtra1 = CASE when ExtendedProperties.PropertyName="Transmittal Number" then ExtendedProperties.PropertyValue else NULL END, D.Xtra2 = CASE when (ExtendedProperties.PropertyName="File Identificati...
I've gotten the "Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source" error in my classic asp page a couple of times, usually after I've just changed a stored procedure. In the past, uninstalling and reinstalling the COM+ applications and a reset of IIS has seemed to fix this proble...
select datepart(month,s1.Timeperiod) as monthofaum, datepart(YEAR,s1.Timeperiod) as Yearofaum, ISNULL(s2.endingAum,0) as Starting_Aum, s1.endingAum as Ending_Aum from #temp_1 s1 left outer join (select * from #temp_1)s2 on month(s1.Timeperiod) = dateadd(D,1,month(s2.Timeperiod)) This w...
What would be a typical situation under which the following error would occur, and what does it mean? Also note that I find myself disconnected from the server when the query fails (but I have not myself observed that). Location: "xact.cpp":4253 Expression: !m_parNestedXactCnt SPID: 56 Process ID: 2208 Description: Try...
I have a CHAR(250) column being used as a foreign key to a varchar(24) column. In MySQL I recall that I could create an index specifying column(24) in order to create an index on the leftmost 24 characters. This doesn't appear to be possible on MS SQL Server. My question is this: Is it possible to use an indexed view on SQL Server 20...
Hi, I am running a simple statment below which gives the output as follow: select '''' + name + '''' + ',' as Emp_Names from dbo.employee Emp_Names 'Jason', 'Robert', 'Celia', 'Linda', 'David', 'James', 'Alison', 'Chris', 'Mary', Is there a way in SQL that can show my desired output as: Emp_Names 'Jason', 'Robert','Celia'...
Hello, I am currently looking for a Select statement which will do this.. |------Apples------| |--id--| - 1 - 16 - 23 - 42 |------Oranges------| |--id--| - a - b - c *SELECT STATEMENT* |------Fruit Cocktail------| |--AppleID--|--OrangeID--| 1 a 1 b 1 c 16 ...
which is better, is there any performance difference? setting datetime column's default value to getdate() or using getdate() with the insert t-sql script. ...
I've inherited some database creation scripts for a SQL SERVER 2005 database. One thing I've noticed is that all primary keys are created as NON CLUSTERED indexes as opposed to clustured. I know that you can only have one clustered index per table and that you may want to have it on a non primary key column for query perfoamce of searc...
In SSMS 2008 R2 I create the table aTest(Albnian varchar(10), Dflt varchar(10)) In SSMS table designer both column properties have Collation: < database default > (under Column Properties--> "Table designer") I change collation of column Albnian to a non-default, for ex., to Albanian_CI_AS. If I script the table in SSMS (right-cli...
Dear all, I am currently upgrading a database server from SQL Server 2000 to SQL Server 2008 R2. One of my queries used to take under a second to run and now takes in excess of 3 minutes (running on faster a faster machine). I think I have located where it is going wrong but not why it is going wrong. Could somebody explain what the ...
I have multiple views fueling entity framework, and some of the views don't return all the fields as their brethren. for EF's sake, i need to ensure that all views return the same signature. So in the views that have less fields, i simply add missing columns: ,Column1 ,Column2 ,null Column3 since Column3 is not in the table returne...
Hi All, I wanted to know if in SQL Server there is an equivalent to the Oracle INSTR function? I know that there is CHARINDEX and PATINDEX, but with the oracle version I can also specify the Nth appearance of the character(s) I am looking for. Oracle INSTR: instr( string1, string2 [, start_position [, nth_appearance ] ] ) The CHARINDE...
Using SQL Server 2008, I'd like to create a UDF that gives me the create date of an object. This is the code: create function dbo.GetObjCreateDate(@objName sysname) returns datetime as begin declare @result datetime select @result = create_date from sys.objects where name = @objname return @result end go I'd like to put t...
Setting the 32nd and 64th bits is tricky. 32-bit Solution: I got it to work for 32-bit fields. The trick is to cast the return value of the POWER function to binary(4) before casting it to int. If you try to cast directly to int, without first casting to binary(4), you will get an arithmetic overflow exception when operating on the 3...
I need to convert some data stored as varchar in a SQL Server database that I want to change from CamelCase to delimiter-separated. I know how to do this trivially in C# but not T-SQL. Any ideas? ...
So I want to put the files up on my local server, for reference purposes and aside from my regular DVCS because this will turn into training material later. But I want to be able to pretty format the SQL file. (Would be nice if it extended to C# code as well.) I'm not sure what libraries do this already, and I'm willing to bet there's a...