Search Results

Search found 1208 results on 49 pages for 'tsql'.

Page 36/49 | < Previous Page | 32 33 34 35 36 37 38 39 40 41 42 43  | Next Page >

• is this a problem in the sp_rename function or sql server itself ?

  - by user81967

  While renaming the column name, the square bracket is included in the column name, which I think is a bug, Here is a sample code snippet, create table [TestTable] (TestColumnName nvarchar(30)) select TestColumnName from TestTable sp_rename '[TestTable].[TestColumnName]', '[RenamedColumnName]', 'Column' select [RenamedColumnName] from TestTable -- does not work "Invalid column name 'RenamedColumnName'." select RenamedColumnName from TestTable -- does not work "Invalid column name 'RenamedColumnName'." select * from [TestTable] -- works fine!!! The bug here is that the column rename includes the square brackets, I found this which says that the "first character must be one of the following", but "[" does not seem be included in the list, can there be a problem with sp_rename or sql server itself, as it allows alteration of column name to start with a square bracket.

  Read the article

• Sub Query making Query slow.

  - by Muhammad Kashif Nadeem

  Please copy and paste following script. DECLARE @MainTable TABLE(MainTablePkId int) INSERT INTO @MainTable SELECT 1 INSERT INTO @MainTable SELECT 2 DECLARE @SomeTable TABLE(SomeIdPk int, MainTablePkId int, ViewedTime1 datetime) INSERT INTO @SomeTable SELECT 1, 1, DATEADD(dd, -10, getdate()) INSERT INTO @SomeTable SELECT 2, 1, DATEADD(dd, -9, getdate()) INSERT INTO @SomeTable SELECT 3, 2, DATEADD(dd, -6, getdate()) DECLARE @SomeTableDetail TABLE(DetailIdPk int, SomeIdPk int, Viewed INT, ViewedTimeDetail datetime) INSERT INTO @SomeTableDetail SELECT 1, 1, 1, DATEADD(dd, -7, getdate()) INSERT INTO @SomeTableDetail SELECT 2, 2, NULL, DATEADD(dd, -6, getdate()) INSERT INTO @SomeTableDetail SELECT 3, 2, 2, DATEADD(dd, -8, getdate()) INSERT INTO @SomeTableDetail SELECT 4, 3, 1, DATEADD(dd, -6, getdate()) SELECT m.MainTablePkId, (SELECT COUNT(Viewed) FROM @SomeTableDetail), (SELECT TOP 1 s2.ViewedTimeDetail FROM @SomeTableDetail s2 INNER JOIN @SomeTable s1 ON s2.SomeIdPk = s1.SomeIdPk WHERE s1.MainTablePkId = m.MainTablePkId) FROM @MainTable m Above given script is just sample. I have long list of columns in SELECT and around 12+ columns in Sub Query. In my From clause there are around 8 tables. To fetch 2000 records full query take 21 seconds and if I remove Subquiries it just take 4 seconds. I have tried to optimize query using 'Database Engine Tuning Advisor' and on adding new advised indexes and statistics but these changes make query time even bad. Note: As I have mentioned that this is test data to explain my question the real data has lot of tables joins columns but without Sub-Query the results us fine. Any help thanks.

  Read the article

• Procedure Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

  - by Nick

  The stored proc is failing at below location,Thanks, for all your help. --Insert MSOrg Information DECLARE @PersonnelNumber int, @MSOrg varchar(255) DECLARE csr CURSOR FAST_FORWARD FOR SELECT PersonnelNumber FROM Person OPEN csr FETCH NEXT FROM csr INTO @PersonnelNumber WHILE @@FETCH_STATUS = 0 BEGIN EXEC GetMSOrg @PersonnelNumber, @MSOrg out INSERT INTO PersonSubject ( PersonnelNumber ,SubjectID ,SubjectValue ,Created ,Updated ) SELECT @PersonnelNumber ,SubjectID ,@MSOrg ,getDate() ,getDate() FROM Subject WHERE DisplayName = 'MS Org' FETCH NEXT FROM csr INTO @PersonnelNumber END CLOSE csr DEALLOCATE csr Below is the stored prc defination GetMSOrg and fails at third condition CREATE PROCEDURE [dbo].[GetMSOrg] ( @PersonnelNumber int ,@OrgTerm varchar(200) out ) AS DECLARE @MDRTermID int ,@ReportsToPersonnelNbr int --Check to see if we have reached the top of the chart SELECT @ReportsToPersonnelNbr = ReportsToPersonnelNbr FROM ReportsTo WHERE PersonnelNumber = @PersonnelNumber IF (@ReportsToPersonnelNbr IS NULL) --Reached the Top of the Org Ladder BEGIN SET @OrgTerm = 'Non-standard rollup' END ELSE IF (@PersonnelNumber IN (SELECT PersonnelNumber FROM OrgTermMap)) BEGIN SELECT @OrgTerm = s.Term FROM OrgTermMap tm JOIN Taxonomy..StaticHierarchy s ON tm.OrgTermID = s.TermID WHERE tm.PersonnelNumber = @PersonnelNumber END ELSE BEGIN SELECT @MDRTermID = tm.OrgTermID FROM ReportsTo r JOIN OrgTermMap tm ON r.ReportsToPersonnelNbr = tm.PersonnelNumber WHERE r.PersonnelNumber = @PersonnelNumber IF (@MDRTermID IS NULL) BEGIN EXEC GetMSOrg @ReportsToPersonnelNbr, @OrgTerm out END ELSE BEGIN SELECT @OrgTerm = Term FROM Taxonomy..StaticHierarchy WHERE VocabID = 118 AND TermID = @MDRTermID END END GO

  Read the article

• Simple calculation in SQL Server

  - by meep

  I got a distance field in my database that stores the distance traveled on a specific route. I need to select all the distance fields and plus them together, then returning the result from my stored procedure. How can this be done?

  Read the article

• How to do rolling balances in Linq2SQL

  - by David Liddle

  Given an account with a list of transactions I would like to output a query that shows each transaction with the rolling balance (just like you would see on an online banking account). TRANSACTIONS - ID - DATE - AMOUNT Here is what I created in T-SQL however was wondering if this can be translated to linq2sql code? select T.ID, convert(char(10), T.DATE, 101) as 'DATE', T.AMOUNT, (select sum(O.AMOUNT) from TRANSACTIONS O where O.DATE < T.DATE or (O.DATE = T.DATE and O.ID <= T.ID)) 'BALANCE' from TRANSACTIONS as T where T.DATE between @pStartDate and @pEndDate order by T.DATE, T.ID Alternatively I guess my other option is to just call a stored procedure for these kind of results. However, I have Services which call Repositories and didn't really want to put the sproc call in the Repository.

  Read the article

• Getting maximum value of float in SQL programatically

  - by Axarydax

  Is there an method for programatically (in T-SQL) retrieving the maximum (and minimum) value of a datatype? That it would act like float.MaxValue in C#. I would like to use it in some selection when the parameter does not equal any actual values in the database, so I would use something like declare @min float declare @max float /*fill @min and @max, can be null if undefined*/ select * from foo where bar between isnull(@min,0 ), isnull(@max,max(float)/*magic*/) Thanks

  Read the article

• Is this query safe in SQL Server?

  - by xaw

  I have this SQL update query: UPDATE table1 SET table1.field1 = 1 WHERE table1.id NOT IN (SELECT table2.table1id FROM table2); Other portions of the application can add records to table2 which use the field table1id to reference table1. The goal here is to remove records from table1 which aren't referenced by table2. Does SQL Server automatically lock table2 with this kind of query so that a new record can't be added to table2 while executing this query? I've also considered: UPDATE table1 SET field1 = 1 WHERE 0 = (SELECT COUNT(*) FROM table2 WHERE table1.id = table2.table1id); Which seems possibly safer, but much slower (because a SELECT would be called on each row of table1 instead of just one select for the NOT IN)

  Read the article

• Use database dynamically

  - by Redi

  This execution it is giving me an error! Any hints of what am I missing? declare @dbname varchar(500) set @dbname='master' Exec (' Use ' + @dbname + ' go create PROCEDURE [dbo].[krijo_database] @dbname nvarchar(2000), @Direktoria varchar(4000) AS BEGIN declare @stringu nvarchar(100) set @stringu = ''CREATE DATABASE '' + @dbname exec (@stringu) End ')

  Read the article

• Why calling a user defined function needs the owner name when calling a stored procedure doesn't ?

  - by Nam Gi VU

  Why calling a user defined function need the owner name when calling a stored procedure doesn't ? Please help!

  Read the article

• What short query will return an vector (a one-column column table) representing a set of integers fr

  - by Ivan

  Assuming we have no actual table to select the data from, what do we need to SELECT to return a table consisting of one integer column containing all integer numbers starting with x1 and finishing with x2 and sorted in ascending order?

  Read the article

• Is it possible to use CASE with IN?

  - by dkackman

  I'm trying to construct a T-SQL statement with a WHERE clause determined by an input parameter. Something like: SELECT * FROM table WHERE id IN CASE WHEN @param THEN (1,2,4,5,8) ELSE (9,7,3) END I've tried all combination of moving the IN, CASE etc around that I can think of. Is this (or something like it) possible?

  Read the article

• Order By Problem T-Sql

  - by Nick LaMarca

  I have a sql statement I am using a simple sort such as the following Select numbers From theTable Order By numbers What I get in return is the following 1 11 12 14 2 21 22 23 3 35 37 etc... I want it to be ordered in normal order 1 2 3 4 5 6 etc...

  Read the article

• What is the best way of inserting a datetime value using dynamic sql

  - by jaffa

  What is the best way of inserting a datetime value using a dynamic sql string, whilst at the same time being able to handle the possibility of the value being null? The current statement inserts into a table from a select statement built using a string. The datetime value is stored in a parameter and the parameter is used in the select. Like so: set @execsql = 'Insert into ( start_date ) SELECT ( ''' + CAST(start_date as VARCHAR) + ''' + ')'

  Read the article

• Extract words from sentence(s) using TSQL

  - by Newbie

  I have the following input. INPUT: TableA ID Sentences --- ---------- 1 I am a student 2 Have a nice time guys! What I need to do is to extract the words from the sentence(s) and insert each individual word in another table OUTPUT: SentenceID WordOccurance Word ---------- ------------ ----- 1 1 I 1 2 am 1 3 a 1 4 student 2 1 Have 2 2 a 2 3 nice 2 4 time 2 5 guys! I am using SQL Server 2005. My fruitless approach so far is ;With numCTE As ( Select rn = 1 Union all Select rn+1 from numCTE where rn<1000) , getWords As ( Select rn, ID, indiChars From numCTE Cross Apply(Select ID, indiChars = Substring(Sentences,1,rn) From inputTbl)x where indiChars <> '' ) Select Id, Word = stuff(select ',' + cast(indiChars) from getWords g1 where g1.Id = g2.Id for xml path(''),'',1,1)x from getWords g2 Group by g2.Id I am looking for a set based solution. Thanks

  Read the article

• Trying to send text message using sp_send_dbmail truncates url in body

  - by Dabas

  I'm using send_dbmail to send a text message to customers. This is the following sql: exec msdb.dbo.sp_send_dbmail @recipients='[email protected]', @body='check out this url https://www.someurl.com/directory/blah.aspx', @subject='I am the subject!' The body gets truncated to "check out this url https://www.someurl.com/directory/blah.as" (the "px" is removed from the end of the url). I've ruled out message length as I have tried sending just "www.google.com/test.aspx" and the "px" is removed as well. Another strange thing, when I try forwarding the text message to myself and add the "px" back on myself, it works. It also works if I send a email from outlook with the same body. Any ideas? Thanks.

  Read the article

• t-sql - find differences for pk's in a single table (Self join?)

  - by Pace

  Hi Experts, My situation is this. I have a table of products with a pk "Parent" which has "Components" The data looks something like this Parent(PK) Component Car1 Wheel Car1 Tyre Car1 Roof Car2 Alloy Car2 Tyre Car2 Roof Car3 Alloy Car3 Tyre Car3 Roof Car3 Leather Seats Now what I want to do is some query that I can feed two codes in and see the differences... IE If I feed in "Car1", "Car2" it would return something like; Parent Component Car1 Wheel Car2 Alloy As this is the difference between the two. If I said "Car1", "Car3" I would expect; Parent Component Car1 Wheel Car3 Alloy Car3 Leather Seats Your help with this matter would be greatly appreciated.

  Read the article

• With SQL can you use a sub-query in a WHERE LIKE clause?

  - by Jason

  I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN LIKE SELECT statement. As an example, if I wanted to find all user records with a hotmail.com email address, I could do something like: SELECT UserEmail FROM Users WHERE (UserEmail LIKE '%hotmail.com') But what if I wanted to use a subquery as the matching criteria? Something like this: SELECT UserEmail FROM Users WHERE (UserEmail LIKE (SELECT '%'+ Domain FROM Domains)) Is that even possible? If so, what's the right syntax?

  Read the article

• What's the best way to prepare for a technical interview focusing on T-SQL?

  - by stackunderflow

  I have a job interview coming that will focus on T-SQL and general SQL competency. Any suggestions on how to prepare for this?

  Read the article

• How to retain a row which is foreign key in another table and remove other duplicate rows?

  - by Mithril

  I have two table: A: id code 1 A1 2 A1 3 B1 4 B1 5 C1 6 C1 ===================== B: id Aid 1 1 2 4 (B doesn't contain the Aid which link to code C1) Let me explain the overall flow: I want to make each row in table A have different code(by delete duplicate),and I want to retain the Aid which I can find in table B.If Aid which not be saved in table B,I retain the id bigger one. so I can not just do something as below: DELETE FROM A WHERE id NOT IN (SELECT MAX(id) FROM A GROUP BY code, ) I can get each duplicate_code_groups by below sql statement: SELECT code FROM A GROUP BY code HAVING COUNT(*) > 1 Is there some code in sql like for (var ids in duplicate_code_groups){ for (var id in ids) { if (id in B){ return id } } return max(ids) } and put the return id into a idtable?? I just don't know how to write such code in sql. then I can do DELETE FROM A WHERE id NOT IN idtable

  Read the article

• T-SQL Subquery Question

  - by Nick

  Hi, i have two queries. For each tuple of query1 i want to run query2. i dont want to use cursors. i tried several approaches using subqueries. query1: select distinct category, Count(category) as CategoryCount from mytable group by category query2: select top 3 Text, Title, Category from mytable where Category = '1' Category = '1' is a sample. the value should come from query1

  Read the article

• Is there a way to prevent SQL Server silently truncating data in local variables and stored procedure parameters?

  - by Luke Woodward

  I recently encountered an issue while porting an app to SQL Server. It turned out that this issue was caused by a stored procedure parameter being declared too short for the data being passed to it: the parameter was declared as VARCHAR(100) but in one case was being passed more than 100 characters of data. What surprised me was that SQL Server didn't report any errors or warnings -- it just silently truncated the data to 100 characters. The following SQLCMD session demonstrates this: 1 create procedure WhereHasMyDataGone (@data varchar(5)) as 2 begin 3 print 'Your data is ''' + @data + '''.'; 4 end; 5 go 1 exec WhereHasMyDataGone '123456789'; 2 go Your data is '12345'. Local variables also exhibit the same behaviour: 1 declare @s varchar(5) = '123456789'; 2 print @s; 3 go 12345 Is there an option I can enable to have SQL Server report errors (or at least warnings) in such situations? Or should I just declare all local variables and stored procedure parameters as VARCHAR(MAX) or NVARCHAR(MAX)?

  Read the article

• SQL Server: String Manipulation, Unpivoting

  - by OMG Ponies

  I have a column called body, which contains body content for our CMS. The data looks like: ...{cloak:id=1.1.1}...{cloak}...{cloak:id=1.1.2}...{cloak}...{cloak:id=1.1.3}...{cloak}... A moderately tweaked for readability example: ## h5. A formal process for approving and testing all external network connections and changes to the firewall and router configurations? {toggle-cloak:id=1.1.1}{tree-plus-icon} *Compliance:* {color:red}{*}Partial{*}{color} (?) {cloak:id=1.1.1} || Date: | 2010-03-15 || || Owner: | Brian || || Researched by: | || || Narrative: | Jira tickets are normally used to approve and track network changes\\ || || Artifacts: | Jira.bccampus.ca\\ || || Recommendation: | Need to update policy that no Jira = no change\\ || || Proposed Remedy(ies): | || || Approved Remedy(ies): | || || Date: | || || Reviewed by: | || || Remarks/comments: | || {cloak}## h5. Current network diagrams with all connections to cardholder data, including any wireless networks? {toggle-cloak:id=1.1.2}{tree-plus-icon} *Compliance:* {color:red}{*}TBD{*}{color} (?) {cloak:id=1.1.2} I'd like to get the cloak values out in the following format: requirement_num ----------------- 1.1.1 1.1.2 1.1.3 I'm looking at using UNIONs - does anyone have a better recommendation? Forgot to mention: I can't use regex, because CLR isn't enabled on the database. The numbers aren't sequencial. The current record jumps from 1.1.6 to 1.2.1

  Read the article

• SQL Server - Select one random record not showing duplicates

  - by Lukes123

  I have two tables, events and photos, which relate together via the 'Event_ID' column. I wish to select ONE random photo from each event and display them. How can I do this? I have the following which displays all the photos which are associated. How can I limit it to one per event? SELECT Photos.Photo_Id, Photos.Photo_Path, Photos.Event_Id, Events.Event_Title, Events.Event_StartDate, Events.Event_EndDate FROM Photos, Events WHERE Photos.Event_Id = Events.Event_Id AND Events.Event_EndDate < GETDATE() AND Events.Event_EndDate IS NOT NULL AND Events.Event_StartDate IS NOT NULL ORDER BY NEWID() Thanks Luke Stratton

  Read the article

• Make a final call to the Database when user leaves website (ASPX)?

  - by Jisaak

  I have a system set up to lock certain content in a database table so only one user can edit that content at a time. Easy enough and that part is working fine. But now I'm at a road block of how to send a request to "unlock" the content. I have the stored procedure to unlock the content, but how/where would I call it when the user just closes their browser?

  Read the article

• Update just one field from backup

  - by justSteve

  I'm looking to restore one field from a backup and can't find the syntax for an update statement that can look at 2 different catalogs. Seems like it should be something fairly close to: update users set idUserCompany = (select idUserCompany from .myBackup.dbo.users uT) where uT.idUser = idUser

  Read the article

< Previous Page | 32 33 34 35 36 37 38 39 40 41 42 43  | Next Page >