I need to run sql statements from the application itself. i.e. the user can go into the asp.net applciation, gets a box and can run sql statements from there I am already doing something like this http://stackoverflow.com/questions/1858329/can-i-rollback-dynamic-sql-in-mssql-tsql/1860334#1860334 That is running dynamic sql is there a ...
I am using SQL server MSDE 2000. I have a field called notes of type nvarchar(65). The content is 'Something ' with an extra space after the content (quotes for clarity) in all the records. I used the following command. UPDATE TABLE1 SET notes = RTRIM(LTRIM(notes)) But it does not work. Is there any alternate way to do it? ...
what's the correct approach in t-sql in the following? : sum(rounded(fractions,2)) or round(sum(of fractions),2) ta! I've tried both approaches and got my total still end up with a different result for each don't know if I need to truncate somwhere I want x, y * @v = z so that sum(x) * @v = sum(z) Thanks ...
hello, I have stored procedure getList(@date datetime) how programmatically execute stored procedure for differend datetime values. datetime each month for 3 years. Any idea, please. ...
Given an email address column, I need to find the position of the @ sign for substringing. What is the indexof function, for strings in t-sql. Looking for something that returns the position of a substring within a string. in c# var s = "abcde"; s.IndexOf('c'); // yields 2 ...
Possible Duplicate: T-SQL WHERE col IN () What is the maximum size for a SQL Server query? (# of characters) Max size for an IN clause? I think I saw something about Oracle having a 1000 item limit but you could get around this with ANDing 2 INs together. Similar issue in SQL Server? UPDATE So what would be the best approach i...
My client is using Advantage Database Server and wants to move to SQL Server but obviously wants to move all his clients data as part of the upgrade over to SQL Server. I've thought about writing an app to do the transfer but thinking it might be more trouble than its worth. What would you recommend? ...
I have a simple data logging table with two data columns A and B for which I want to generate the sum of A+B. However either or both A and B could be null. The table also has a timestamp column which is the primary key I can see several ways to skin this cat, but I was curious to know what the preferred method might be to sum the dat...
I've written a stored procedure as following: CREATE PROC spSoNguoiThan @SNT int AS begin IF not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'NhanVien' and column_name = 'SoNguoiThan') ALTER TABLE NhanVien ADD SoNguoiThan int else ...
I have two tables, a patient table an and appointment table. I'm attempting to retrieve the information from both tables depending on what doctor is logged in at the time. My stored procedure is as follows: ALTER PROCEDURE dbo.sprocGetAllAppointmentsForUser @UserID varchar(50) AS SELECT Appts.appt_id, Appts.patient_id, Appts.dr_id, A...
i got error when i run exec sp_changeobjectowner 'testtable','dbo' 'testtable' table - Unable to modify table. The object with name "testtable" already exists. ...
I need create full index search on table with multi columns as pk ...
How to Work Around COM+ System Configuration Check Failure in SQL Server Setup? ...
SQL Server 2005: 240 million row table. Column requirements changing from NOT NULL to NULL. Generally bad practice (and often impossible) to use ALTER statements when dealing with tables this big, however, trying to avoid rebuilding the table, if possible. Tested ALTER statement against a dev table containing 20m rows, and the statem...
Hello All, I couldn't solve that mistery question in SQL SERVER. Here an example that I tried to do and it didn't work. DECLARE @Total int; SET @Total = (SELECT COUNT(*)-10 FROM MYTABLE) SELECT TOP @Total IdColumn FROM MYTABLE How can I use the following query SELECT COUNT(*)-10 FROM MYTABLE as an integer variable, somewhere el...
I am trying to pull together some data for a report and need to concatenate the row values of one of the tables. Here is the basic table structure: Reviews ReviewID ReviewDate Reviewers ReviewerID ReviewID UserID Users UserID FName LName This is a M:M relationship. Each Review can have many Reviewers; eac...
I have a legacy DB with: firstname, lastname, address1, address2, address3, address4, zipcode The data is scattered between the different columns with no consistency eg the actual zipcode could be in any column and there are plenty of typos. Is there a way I could use something like SOUNDEX / DIFFERENCE in a SP to loop through everythin...
"[" is not classed a unicode character http://en.wikipedia.org/wiki/List%5Fof%5FUnicode%5Fcharacters (my guess) as to why this wouldn't work: declare @v nvarchar(255) set @v = '[x]825' select 1 where @v like '[x]825' Ta! ...
I want to call a SP from another SP. I know I can easily call it. But Problem is, if an error occurs in SP2 then I want to ROLLBACK SP1. SP1 BEGIN Tran [Some Code] Call to SP2 [Some Code] SP2 BEGIN TRAN [Some Code] [Error Comes] ROLLBACK TRAN This would rollback Tran in sp2 only. I...
hi We want all members who selected category 1 as their favorite category to also have category 9 added as one of their favorite categories. I assume the following three queries will ALWAYS produce exactly the same results ( assuming FavCategory.CategoryID and FavCategory.MemberID form a primary key ): SELECT 9, md1.MemberId FROM M...