Search Results

Search found 106523 results on 4261 pages for 'sql server agent print'.

Page 155/4261 | < Previous Page | 151 152 153 154 155 156 157 158 159 160 161 162  | Next Page >

• Working by group by for grouping data into string format

  - by Shantanu Gupta

  I have a table that contains some data given below. It uses a tree like structure i.e. Department SubD1, SubD2 ..... PreSubD1, PreSubD1... PreSubD2, PreSubD2... pk_map_id preferences ImmediateParent Department_Id -------------------- -------------------- -------------------- -------------------- 20 14 5 1 21 15 5 1 22 16 6 1 23 9 4 2 24 4 3 2 25 24 20 2 26 25 20 2 27 23 13 2 I want to group my records on behalf of department then immediate parent then preferences each seperated by ',' i.e. department Immediate Parent preferences 1 5,6 14,15,16 2 4,3,20,13 9,4,24,25,23 and this table also Immediate parent preferences 5 14,15 6 16 4 9 3 4 20 24,25 13 13 In actual scenario all these are my ids which are to be replaced by their string fields. I am using sql server 2k5

  Read the article

• SQL Drop Index on different Database

  - by Jim

  While trying to optimize SQL scripts, I was recommended to add indexes. What is the easiest way to specify what Database the index should be on? IF EXISTS (SELECT * FROM sysindexes WHERE NAME = 'idx_TableA') DROP INDEX TableA.idx_TableA IF EXISTS (SELECT * FROM sysindexes WHERE NAME = 'idx_TableB') DROP INDEX TableB.idx_TableB In the code aboce, TableA is in DB-A, and TableB is in DB-B. I get the following error when I change DROP INDEX TableA.idx_TableA to DROP INDEX DB-A.dbo.TableA.idx_TableA Msg 166, Level 15, State 1, Line 2 'DROP INDEX' does not allow specifying the database name as a prefix to the object name. Any thoughts are appreciated.

  Read the article

• SQL Server Express: Failed to add primary key after creating a database table

  - by Syd

  Scenario: I have created a database table. And when I tried to add a primary key (using the SQL Server Management Studiio) to the table, it failed with an error message "Saving changes is not permitted. The changes you have made requrie the following tables to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option Prevent savign changes that require the table to be re-created." The environment is Windows 7. I have earlier installed it on a Windows XP SP2 and it worked. And yes, my table is empty (freshly created). My current workaround is to run the "Drop and Create" script. The other alternative is to create the primary key before I save the table. Is this a bug or a limitation with the Express version?

  Read the article

• Getting age in years in a SQL query

  - by Earlz

  Hello I've been tasked with doing a few queries on a large SQL Server 2000 database. The query I'm having trouble with is "find the number of people between ages 20 and 40" How would I do this? My previous query to get a count of everyone looks like this: select count(rid) from people where ... (with the ... being irrelevant conditions). I've googled some but the only thing I've found for calculating age is so large that I don't see how to embed it into a query, or it is a stored procedure which I do not have the permissions to create. Can someone help me with this?

  Read the article

• Easy plugin or procedure for sqlserver Management Studio to script row inserts.

  - by Patrick Karcher

  I've never been able to find a good script or plugin for sql server Management Studio (2005 and or 2008) for a very common scripting need: specifying a few/all rows in a table and scripting their insert. You can guess my story: I've got some configuration data in my dev db and I need to script it for deployment to UAT and then production. I've found a few cludgy systems in the past, that were more trouble than they were worth. I need something free and unobtrusive. Once I find it I'll share it with the other 20 developers in my shop who are annoyed by this. Aren't we all annoyed by this by the way? What is the best, easiest, free, way to specify a few/all rows in a table and get a script their insert?

  Read the article

• SQL Server GROUP BY troubles!

  - by Lucas311

  I'm getting a frustrating error in one of my SQL Server 2008 queries. It parses fine, but crashes when I try to execute. The error I get is the following: Msg 8120, Level 16, State 1, Line 4 Column 'customertraffic_return.company' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SELECT * FROM (SELECT ctr.sp_id AS spid, Substring(ctr.company, 1, 20) AS company, cci.email_address AS tech_email, CASE WHEN rating IS NULL THEN 'unknown' ELSE rating END AS rating FROM customer_contactinfo cci INNER JOIN customertraffic_return ctr ON ctr.sp_id = cci.sp_id WHERE cci.email_address <> '' AND cci.email_address NOT LIKE '%hotmail%' AND cci.email_address IS NOT NULL AND ( region LIKE 'Europe%' OR region LIKE 'Asia%' ) AND SERVICE IN ( '1', '2' ) AND ( rating IN ( 'Premiere', 'Standard', 'unknown' ) OR rating IS NULL ) AND msgcount >= 5000 GROUP BY ctr.sp_id, cci.email_address) AS a WHERE spid NOT IN (SELECT spid FROM customer_exclude) GROUP BY spid, tech_email

  Read the article

• String update in SQL Server

  - by Thiyaneshwaran S

  Currently I have varchar field. The delimiter is "$P$P$". The delimiter will appear at least once and at most twice in the varchar data. Eg. Sample Heading$P$P$Sample description$P$P$Sample conclusion Sample Heading$P$P$Sample Description If the delimiter appears twice, I need to insert a text before the second occurance of the delimiter. Eg: Sample Heading$P$P$Sample DescriptionINSERT TEXT HERE$P$P$Sample Conclusion If the delimiter occurs only once, then I need to insert a text at the end of the field. Eg: Sample Heading$P$P$Sample DescriptionAPPEND TEXT HERE How this can be done in SQL query?

  Read the article

• MySQL "IS IN" equivalent?

  - by nute

  A while ago I worked on a MS-SQL project and I remember a "IS IN" thing. I tried it on a MySQL project and it did not work. Is there an equivalent? Workaround? Here is the full query I am trying to run: SELECT * FROM product_product, product_viewhistory, product_xref WHERE ( (product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id) OR (product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id) ) AND product_product.id IS IN (SELECT DISTINCT pvh.productId FROM product_viewhistory AS pvh WHERE pvh.cookieId = :cookieId ORDER BY pvh.viewTime DESC LIMIT 10) AND product_viewhistory.cookieId = :cookieId AND product_product.outofstock='N' ORDER BY product_xref.hits DESC LIMIT 10 It's pretty big ... but the part I am interested in is: AND product_product.id IS IN (SELECT DISTINCT pvh.productId FROM product_viewhistory AS pvh WHERE pvh.cookieId = :cookieId ORDER BY pvh.viewTime DESC LIMIT 10) Which basically says I want the products to be in the "top 10" of that sub-query. How would you achieve that with MySQL (while trying to be efficient)?

  Read the article

• SQL compare entire rows

  - by zmaster

  In SQL server 2008 I have some huge tables (200-300+ cols). Every day we run a batch job generating a new table with timestamp appended to the name of the table. The the tables have no PK. I would like a generic way to compare 2 rows from two tables. Showing which cols having different values is sufficient, but showing the values would be perfect. Thanks a lot Thanks for the answers. I ended up writing my own C# tool to do the job - as Im not allowed to install 3rd party software in my company.

  Read the article

• VS 2010 SQL Update for SQL Statement

  - by Mike Tucker

  Please bear with me as I'm just beginning to learn this stuff. I have a VS 2010 Web project up and I'm trying to understand how I can make a custom UpdateCommand (Because I chose to write my own SQL statement, I do not have the option for VS 2010 to auto generate an update command for me.) Problem is: I don't know what the UpdateCommand should look like. Here is my Select: SELECT * FROM Dbo.MainAsset, dbo.Model, dbo.Hardware WHERE MainAsset.device = Hardware.DeviceID AND MainAsset.model = Model.DeviceID Which, VS 2010 turns into: SELECT MainAsset.pk, MainAsset.img, MainAsset.device, MainAsset.model, MainAsset.os, MainAsset.asset, MainAsset.serial, MainAsset.inyear, MainAsset.expyear, MainAsset.site, MainAsset.room, MainAsset.teacher, MainAsset.FirstName, MainAsset.LastName, MainAsset.Notes, MainAsset.Dept, MainAsset.AccountingCode, Model.Model AS Hardware, Model.pk AS Model, Model.DeviceID, Hardware.Computer, Hardware.pk AS Expr3, Hardware.DeviceID AS Expr4 FROM MainAsset INNER JOIN Hardware ON MainAsset.device = Hardware.DeviceID INNER JOIN Model ON MainAsset.model = Model.DeviceID How would I approach updating one column, say "MainAsset.site" if that's changed in the Gridview DDL? Any help constructive help would be appreciated. Thank you.

  Read the article

• Finding records within a 5 min time interval in SQL

  - by Mellonjollie

  I have a table with over 100,000 rows that contain the following columns: ID, Time, and Boolean. The time column tracks time down to the second. I need a query that will find all instances of Boolean = 1 for every 5 minute interval of time from the start of the table to the end, then group the count by time interval. The table represents 4 hours of data, so I should get 48 rows of results. I'm using MS SQL Server. I've tried a few approaches, but the time interval logic is giving me a hard time.

  Read the article

• sql - duplicates

  - by Sebastjan

  Hey guys I'm putting data from website (json) to sql base. In db i have these rows. ID | PostId | Name | Message Id is auto-increment primary key. PostId also has a unique values. Name and Message are nothing special. When I run my script / click on the button in form / ... , the program saves all the values into database (lets say there are 25). Next time I'm going to press the button there will be added 25 more records (all duplicates), and so on... Is there a way that the program can check through 'PostIds' if the value already exists before adding it to the db? Thanks

  Read the article

• SQL Server: Find Values that don't exist in a table

  - by MacAnthony

  I have a list or set of values that I would like to know which ones do not currently exist in a table. I know I can find out which ones do exist with: SELECT * FROM Table WHERE column1 IN (x,x,x,x,x) The set is the values I am checking against. Is there a way to find out which values in that set do not exist in column1? Basically, I'm looking for the inverse of the sql statement above. This is for a report, so all I need is the values that don't exist to be returned back. I have and could do this with a left join and putting the values in another table, but the values I check are always different and was hoping to find a solution that didn't involve clearing a table and inserting data first. Trying to find a better solution for me if one exists.

  Read the article

• Select rows with same column A but different column B

  - by Flip Booth

  ID Zip Room ----------- ---------- ------ 317 94087 S105 318 94087 L603 1739 94404-1801 L603 1823 94401-2129 L603 1824 94401-2129 L603 2135 94404-1801 L603 2268 95136-1459 S604 2269 95136-1459 S604 3704 92673-6417 L402 4479 93454-9670 L402 4480 93454-9670 L402 4782 92395-4681 L402 4783 92395-4681 L402 4852 92886-4411 L402 4853 92886-4411 L402 4959 92673-6417 L402 5153 91773-4028 L402 5202 91773-4028 L402 5211 91765-2959 L402 5212 91765-2959 L402 5388 92336-0605 L402 5392 92336-0605 L402 5727 92870 L402 5728 92870 L402 5831 92557 L402 5916 92557 L402 How do I select ID's that has THE SAME zip but different Room ? In the table above, I want the result to be: ID Zip Room ----------- ---------- ------ 317 94087 S105 318 94087 L603 Using SQL Server 2008

  Read the article

• TSQL: Calculate the average of the rolling last 4 weeks

  - by user1917664

  I need your help. Database: SQL SERVER 2008R2 I want to calculte for a year and a week the average of value of the 4 last weeks. I have data a table like that: YEAR WEEKS VALUE 2012 1 3000 2012 2 5000 2012 3 6000 2012 4 7000 2012 5 8000 2012 6 9000 2012 7 1000 2012 8 6000 2012 9 9000 2012 10 4000 And I want that : YEAR WEEKS VALUE 2012 1 ( Average value for week 49, 50, 51, 52 for the year 2011) 2012 2 ( Average value for week 50, 51, 52 for the year 2011 and week 1 for the year 2012) 2012 3 ( Average value for week 51, 52 for the year 2011 and week 1, 2 for the year 2012) 2012 4 ( Average value for week 52 for the year 2011 and week 1, 2, 3 for the year 2012) 2012 5 5250 - ( Average value for week 1, 2, 3 , 4 for the year 2012) 2012 6 6500 - ( Average value for week 2, 3 , 4, 5 for the year 2012) Thank U for your help

  Read the article

• sql server - how to execute tje second half of or only when first one fails

  - by fn79

  Suppose I have a table with following records value text company/about about Us company company company/contactus company contact I have a very simple query in sql server as below. I am having problem with the 'or' condition. In below query, I am trying to find text for value 'company/about'. If it is not found, then only I want to run the other side of 'or'. The below query returns two records as below value text company/about about Us company company Query select * from tbl where value='company/about' or value=substring('company/about',0,charindex('/','company/about')) How can I modify the query so the result set looks like value text company/about about Us

  Read the article

• Creating audit triggers in SQL Server

  - by Mike C.

  I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but after poking around on Google I found that it was incredibly easy to do this incorrectly, and I wanted to avoid that on the get-go. Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure: ID LogDate TableName TransactionType (update/insert/delete) RecordID FieldName OldValue NewValue ... but I am open for suggestions. Thanks!

  Read the article

• Simplifying CASE WHEN SQL statement

  - by kateroh

  Im trying to improve the following CASE statement to calculate the difference only once. I do it to avoid negative numbers: SELECT (CASE WHEN ((SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers)) > 0) THEN (SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers)) ELSE (0) END) This not only looks stupid, but also is not thread-safe. I tried the following, but I get an error message "Invalid column name 'diff'." SELECT (CASE WHEN ((SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers) as diff) > 0) THEN (diff) ELSE (0) END) How can this be simplified? Is there an in-built SQL function that already does this job? EDIT: Sorry, forgot to mention that the select statement is inside of a view declaration, so I cant declare variables.

  Read the article

• How to find a between dates using sql?

  - by rajeeshmenoth

  How to reject without saving dates in database? Eg: the two columns in a database are from_date and to_date From date : 25/08/2014 To date : 29/08/2014 Problem: the above dates are saved in a two fields like from_date and to_date (room reservation booking), the next reservation details I don't want the date between 25/08/2014 to 29/08/2014. The between dates are not saved in database. Only the from date and to date are saving into the database. How to block between date using sql?

  Read the article

• T-SQL While Loop and concatenation

  - by JustinT

  I have a SQL query that is supposed to pull out a record and concat each to a string, then output that string. The important part of the query is below. DECLARE @counter int; SET @counter = 1; DECLARE @tempID varchar(50); SET @tempID = ''; DECLARE @tempCat varchar(255); SET @tempCat = ''; DECLARE @tempCatString varchar(5000); SET @tempCatString = ''; WHILE @counter <= @tempCount BEGIN SET @tempID = ( SELECT [Val] FROM #vals WHERE [ID] = @counter); SET @tempCat = (SELECT [Description] FROM [Categories] WHERE [ID] = @tempID); print @tempCat; SET @tempCatString = @tempCatString + '<br/>' + @tempCat; SET @counter = @counter + 1; END When the script runs, @tempCatString outputs as null while @tempCat always outputs correctly. Is there some reason that concatenation won't work inside a While loop? That seems wrong, since incrementing @counter works perfectly. So is there something else I'm missing?

  Read the article

• How to do OrderBY on XML column in SQL SERVER 2008

  - by Rohit

  I am creating a comma seperated values of columns specified in dbName attribute of below xml. Now i want to concatenate those columns on the basic of Position attribute. DECLARE @varXML AS XML = '<gridFormat> <column property="FacilityInternalID" dbName="Pname" HeaderText="TAT Health" IsVisible="1" Position="1" /> <column property="FacilityInternalID" dbName="Priority" HeaderText="Priority" IsVisible="1" Position="2" /> <column property="FacilityInternalID" dbName="JobID" HeaderText="Job Number" IsVisible="1" Position="3" /> <column property="FacilityInternalID" dbName="Status" HeaderText="Status" IsVisible="1" Position="6" /> <column property="FacilityInternalID" dbName="name" HeaderText="Customer" IsVisible="1" Position="4" /> <column property="FacilityInternalID" dbName="sname" HeaderText="Facility " IsVisible="1" Position="5" /> </gridFormat>' PRINT @varXML This is the query by which I am generating CSV of columns. I have to use it is select list. SELECT @ColumnsToDisplay = LEFT(MyCsvList, LEN(MyCsvList) - 1) FROM ( SELECT ( SELECT row.value('@property', 'varchar(200)') + ', ' AS [text()] FROM @varXML.nodes('gridFormat/column') AS d ( row ) FOR XML PATH('') ) AS MyCsvList ) AS MyCsvListFinal SET @SQL = 'SELECT ' + @ColumnsToDisplay + ' FROM JobListingDetails' Result should be select Pname,Priority,JobID,name,sname,status FROM JobListingDetails. Please help.

  Read the article

• Repeatedly execute a stored procedure

  - by manivineet

  I have a situation where I need to repeatedly execute a stored procedure Now this procedure (spMAIN) has a cursor inside which looks for a value from a table as T1,which has the following structure ID Status ---- -------- 1 New 2 New 3 success 4 Error now the cursor looks for all rows with a status of 'New' Now while processing , if that instance of the cursor encounters an error, another SP say spError needs to be called, the 'Status' column in T1 needs to be updated to 'Error' and spMAIN needs to be called again which again repeats the process, looking for rows with 'new' how do I do it? Also, also, while we are at it, what if an SP has other SPs inside it and if any of those SP raises an error, same thing needs to be done, the T1 table needs to be updated ('Error') and spMAIN needs to be called again. can you also recommend something ? here's some code ALTER PROC zzSpMain AS BEGIN DECLARE @id INT BEGIN TRY IF EXISTS ( SELECT * FROM dbo.zzTest WHERE istatus = 'new' ) BEGIN DECLARE c CURSOR FOR SELECT id FROM zztest WHERE istatus = 'new' OPEN c FETCH NEXT FROM c INTO @id WHILE @@FETCH_STATUS = 0 BEGIN PRINT @id IF @id = 2 BEGIN UPDATE zztest SET istatus = 'error' WHERE id = @id RAISERROR ( 'Error occured', 16, 1 ) END UPDATE zztest SET istatus = 'processed' WHERE id = @id FETCH NEXT FROM c INTO @id END CLOSE c DEALLOCATE c END END TRY begin CATCH EXEC zzSpError END CATCH END

  Read the article

• Optimzing TSQL code

  - by adopilot

  My job is the maintain one application which heavy use SQL server (MSSQL2005). Until now middle server stores TSQL codes in XML and send dynamic TSQL queries without using stored procs. As I am able change those XML queries I want to migrate most of my queries to stored procs. Question is folowing: Most of my queries have same Where conditions against one table Sample: Select ..... from .... where .... and (a.vrsta_id = @vrsta_id or @vrsta_id = 0) and (a.podvrsta_id = @podvrsta_id or @podvrsta_id = 0) and (a.podgrupa_2 = @podgrupa2_id or @podgrupa2_id = 0) and ( (a.id in (select art_id from osobina_veze where podosobina_id in (select ado from dbo.fn_ado_param_int(@podosobina)) group by art_id having count(art_id)= @podosobina_count )) or ('0' = @podosobina) ) They also have same where conditions on other table. How I should organize my code ? What is proper way ? Should I make table valued function that I will use in all queries or use #Temp tables and simple inner join my query to that each time when proc executing? or use #temp filed by table valued function ? or leave all queries with this large where clause and hope that index is going to do their jobs. or use WITH(statement)

  Read the article

• SQL Server 2005 - Building a WHERE clause

  - by user336786

  Hello, I have a stored procedure that is dynamically building a query. The where clause associated with this query is based on filter values selected by a user. No matter what I do though, the where clause does not seem to get set. -- Dynamically build the WHERE clause based on the filters DECLARE @whereClause as nvarchar(1024) IF (@hasSpouse > -1) BEGIN IF (@hasSpouse = 0) SET @whereClause='p.[HasSpouse]=0' ELSE SET @whereClause='(p.[HasSpouse]=1 OR p.[HasSpouse] IS NULL)' END -- Dynamically add the next filter if necessary IF (@isVegan > -1) BEGIN IF (LEN(@whereClause) > 0) BEGIN SET @whereClause = @whereClause + ' AND ' END IF (@isVegan = 0) SET @whereClause = @whereClause + 'c.[IsVegan]=0' ELSE SET @whereClause = @whereClause + '(c.[IsVegan]=1 OR c.[IsVegan] IS NULL)' END PRINT @whereClause The @whereClause never prints anything. In turn, the LEN(@whereClause) is always NULL. The @isVegan and @hasSpouse values are passed into the stored procedure. The values are what I expected. What am I doing wrong? Why is the @whereClause never being set? Thank you for your help! Thank you!

  Read the article

• Shrinking the transaction log of a mirrored SQL Server 2005 database

  - by Peter Di Cecco

  I've been looking all over the internet and I can't find an acceptable solution to my problem, I'm wondering if there even is a solution without a compromise... I'm not a DBA, but I'm a one man team working on a huge web site with no extra funding for extra bodies, so I'm doing the best I can. Our backup plan sucks, and I'm having a really hard time improving it. Currently, there are two servers running SQL Server 2005. I have a mirrored database (no witness) that seems to be working well. I do a full backup at noon and at midnight. These get backed up to tape by our service provider nightly, and I burn the backup files to dvd weekly to keep old records on hand. Eventually I'd like to switch to log shipping, since mirroring seems kinda pointless without a witness server. The issue is that the transaction log is growing non-stop. From the research I've done, it seems that I can't truncate a log file of a mirrored database. So how do I stop the file from growing!? Based on this web page, I tried this: USE dbname GO CHECKPOINT GO BACKUP LOG dbname TO DISK='NULL' WITH NOFORMAT, INIT, NAME = N'dbnameLog Backup', SKIP, NOREWIND, NOUNLOAD GO DBCC SHRINKFILE('dbname_Log', 2048) GO But that didn't work. Everything else I've found says I need to disable the mirror before running the backup log command in order for it to work. My Question (TL;DR) How can I shrink my transaction log file without disabling the mirror?

  Read the article

< Previous Page | 151 152 153 154 155 156 157 158 159 160 161 162  | Next Page >