Using REPLACE in an UPDATE statement - SQLTeam.com
www.sqlteam.com/article/using-replace-in-an-update-statement - Similar31 Mar 2010 ... This article covers using the REPLACE function to selectively replace text inside
a string in SQL Server. The REPLACE function is easy to use ...sql - Oracle UPDATE statement using the `REPLACE` function ...
stackoverflow.com/.../oracle-update-statement-using-the-replace-function - CachedI am trying to remember how form a proper REPLACE statement in ... UPDATE
TABLENAME SET COLUMN1 = ' ', SET COLUMN2 = ' ' WHERE ...error while using replace in an update statement in sql server 2008 ...
stackoverflow.com/.../error-while-using-replace-in-an-update-statement-in- sql-server-2008 - CachedWhat error are you getting ... Major Error 0x80040E14, Minor Error 25501 >
update table US14-HSS-SQUARE_AISC14-HSS-SQUARE set ...SQL Update Replace statement - Stack Overflow
Using REPLACE in a UPDATE statement - Dev Shed
using a sql statement to do a string replace - Velocity Reviews
Query to use update statement with Replace function in SQL Server ...
www.aspdotnet-suresh.com/.../query-to-use-update-statement-with.html - Cached12 Jun 2012 ... In previous posts I explained Replace function example and Substring function
example in SQL Server and many more articles relating to SQL ...Replacing part of field data of table with another string using SQL
update using replace function - Dbasupport.com
www.dbasupport.com/.../showthread.php?26372-update-using-replace... - CachedData was entered using dashes. eg 617-123-4567 I want to get rid of the dashes
... dashes but how can I use this function in an update statement.SQL statement for replace/update a stringvalue in an Oracle table ...
ask.sqlservercentral.com/.../sql-statement-for-replaceupdate-a-stringvalue-in-a .html - Cached0. Please use replace in Oracle, can be done in one update statement. ... we can
help you better if you are using SQL Server. Any chance of ...replace considerations for SQL
pic.dhe.ibm.com/infocenter/rbdhelp/.../regl_data_sql_replace.html - CachedUse a get statement (with the forUpdate option) to retrieve the row. ...
sqlStatement: An embedded SQL UPDATE statement to replace the implicit SQL
UPDATE ...SQL UPDATE Statement - W3Schools
How to use REPLACE() within NTEXT columns in SQL Server ...
OraFAQ Forum: SQL & PL/SQL ? update using replace function
www.orafaq.com/forum/t/170809/0/ - CachedI have a table called email that contains a field called email. I have a few records
in the table that contain example@hotmial.com and want to ...Changing part of a string in SQL Server using REPLACE | Victoria ...
victoriayudin.com/.../changing-part-of-a-string-in-sql-server-using-replace/ - Cached11 Mar 2013 ... Using REPLACE in SQL Server, you can quickly update the account names to
reflect the correct department name. If you want to preview what ...Using REPLACE in an UPDATE statement - Ashish's Blog
www.ashishblog.com/blog/using-replace-in-an-update-statement/30 Nov 2011 ... doing a search and replace in SQL is not radically difficult. You basically do an
update using the replace() function.SQL injection: Replace SELECT query with INSERT/UPDATE statement ...
www.backtrack-linux.org/forums/showthread.php?t=62612 - CachedHello, Does anybody know if it is possible to use sqlmap to inject into a SQL
SELECT query, which takes in POST variables from web form - to ...Running a SQL query to replace certain characters in a table ...
www.webmasterworld.com/databases_sql_mysql/3216875.htm - Similarrunning a sql query to replace certain characters in a table. ... have a database
that contains many characters and symbols we no longer want (or that we need
to replace with something else). ... UPDATE Product_Descriptionsmysql - Why would an UPDATE SET REPLACE() statement match ...
dba.stackexchange.com/.../why-would-an-update-set-replace-statement-match -rows-but-change-none-and-give - CachedWhy would an UPDATE SET REPLACE() statement match rows, but change ... I'
m seeking a certain string in a field and want to replace it with a ...SAP HANA Reference: REPLACE | UPSERT - SAP Help Portal
help.sap.com/hana/html/sql_replace_upsert.html - CachedThe UPSERT or REPLACE statement with a subquery works like the INSERT ...
clause is false or update the current row values if WHERE evaluates to true.
Wednesday, September 25, 2013
[SQL Server] Using REPLACE in an UPDATE statement
[SQL Server] Handling SQL Server Errors
TRY...CATCH (Transact-SQL) - TechNet - Microsoft
technet.microsoft.com/en-us/library/ms175976.aspx - CachedImplements error handling for Transact-SQL that is similar to the exception ...
Errors that have a severity of 20 or higher that stop the SQL Server Database ...Error Handling in T-SQL: From Casual to Religious - TechNet
technet.microsoft.com/en-us/library/aa175920(v=sql.80).aspx - CachedVB and C/C++ programmers are so spoiled by the error-handling tools in ... That's
because SQL Server sets the value of @@Error variable after each statement.Error Handling in SQL Server ? a Background
www.sommarskog.se/error-handling-I.html - CachedError Handling in SQL 2000 ? a Background. An SQL text by Erland Sommarskog
, SQL Server MVP. Last revision 2009-11-29.Using TRY?CATCH to Handle SQL Server Errors - About Databases
New Course: SQL Server 2012: Transact-SQL Error Handling | the ...
blog.pluralsight.com/.../new-course-sql-server-2012-transact-sql-error- handling/ - Cached21 Aug 2013 ... Joe Sack of SQLskills has just published a new course: SQL Server 2012:
Transact-SQL Error Handling Robust Transact-SQL coding practices ...SQL Server 2012: Transact-SQL Error Handling - Online Training ...
pluralsight.com/training/.../sqlserver-transact-sql-error-handling - Cached21 Aug 2013 ... SQL Server 2012: Transact-SQL Error Handling. Learn how to write Transact-
SQL code that deals correctly with anticipated and unanticipated ...Error Handling in SQL Server Stored Procedures - Stack Overflow
stackoverflow.com/.../error-handling-in-sql-server-stored-procedures - CachedI have a fairly complex SP (logic wise) with nested IF BEGIN END ... In order to be
able to do a rollback , you need to do a begin transaction at the ...What is the best practice use of SQL Server T-SQL error handling ...
Handling SQL Server Errors in Nested Procedures - CODE Magazine
Error handling in sql server Part 56 - YouTube
www.youtube.com/watch?v=VLDirfx_OQg6 Oct 2012 - 18 min - Uploaded by kudvenkat
Link for csharp, asp.net, ado.net, dotnet basics, mvc and sql server video tutorial playlists ...Handling SQL Server Errors - SQLTeam.com
www.sqlteam.com/article/handling-sql-server-errors - Similar5 Apr 2010 ... This article covers the basics of TRY CATCH error handling in T-SQL introduced
in SQL Server 2005. It includes the usage of common ...ODBC Sql Server error handling - Perl Monks
www.perlmonks.org/?node_id=1049307 - CachedBeing fairly new to Perl I'm not sure how to change the error handler to be able to
ignore this particular error. So something like if nativerr ...Exception Handling in SQL Server - CodeProject
www.codeproject.com/Articles/.../Exception-Handling-in-SQL-Server - Cached17 Jul 2009 ... How to handle the errors effectively in SQL Server?; Author: Erode ... Handling
errors in SQL Server became easy with the number of different ...SQL Server Error Handling Tips
www.mssqltips.com/sql-server-tip-category/79/error-handling/ - CachedSolve your SQL Server Error Handling issues with these valuable tips, tutorials,
how-to's, scripts, and more for SQL Server Developers.Exception Handling in SQL Server Stored Procedure with TRY ...
New Course: ?SQL Server 2012: Transact-SQL Error Handling? - Joe ...
www.sqlskills.com/.../new-course-sql-server-2012-transact-sql-error-handling / - Cached21 Aug 2013 ... Today Pluralsight published my new course, ?SQL Server 2012: Transaction-SQL
Error Handling?. As the title suggests, this course steps ...SQL Server Exception Handling by TRY?CATCH - Dot Net Tricks
www.dotnet-tricks.com/.../sqlserver/O3P3120412-SQL-Server-Exception- Handling-by-TRY?CATCH.html - Cached25 Sep 2012 ... Like C#, SQL Server also has an exception model to handle exceptions and
errors that occurs in T-SQL statements. To handle exception in Sql ...Handling Errors in SQL Server 2012 - Simple Talk
https://www.simple-talk.com/sql/.../handling-errors-in-sql-server-2012/ - Cached3 Jan 2013 ... The error handling of SQL Server has always been somewhat mysterious. Now at
last, the THROW statement has been included in SQL Server ...SQL Server 2008 error handling best practice - Anthony Bloesch's ...
SQL Server Error Handling - BlackWasp
www.blackwasp.co.uk/SQLErrorHandling.aspx - Cached3 Oct 2010 ... Microsoft SQL Server 2005 introduced new error handling capabilities for scripts
and stored procedures. This article describes the use of the try ...
[SQL Server] Using Dynamic SQL in Stored Procedures
Building Dynamic SQL In a Stored Procedure - CodeProject
www.codeproject.com/.../Building-Dynamic-SQL-In-a-Stored-Procedure - Cached9 Oct 2007 ... A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set
of statements stored in a variable and executed using a SQL ...c# - Using dynamic SQL in stored procedure? - Stack Overflow
stackoverflow.com/questions/.../using-dynamic-sql-in-stored-procedure - CachedI'm creating a stored procedure for MySQL in a C# app. The stored ... If it makes
any difference, I'm using MySQL Connector Net v6.5.5, MySQL ...Using Dynamic SQL in Stored Procedures - SQLTeam.com
www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures7 Mar 2011 ... Dynamic SQL allows stored procedures to ?write? or dynamically generate their
SQL statements. The most common use case for dynamic SQL ...Coding Dynamic SQL Statements - Oracle Documentation
docs.oracle.com/cd/B10501_01/appdev.920/.../adg09dyn.htm - CachedFor example, dynamic SQL lets you create a procedure that operates on a table
... "A Dynamic SQL Scenario Using Native Dynamic SQL"; "Choosing Between
Native ..... to native dynamic SQL inside PL/SQL stored procedures and functions.Dynamic SQL Stored procedure - MSDN - Microsoft
social.msdn.microsoft.com/.../sqlserver/.../action?...dynamic-sql-stored- procedure - Cached13 Oct 2012 ... I am trying to write a dynamic SQL stored procedure. I am not successful in doing
so. I am new to this so please kindly help. Below is my Stored ...SQL injection: Dynamic SQL within stored procedures - The What ...
Stored Procedures - Executing & Dynamic SQL - SQL Server - SQL ...
www.toadworld.com/.../sql.../10260.stored-procedures-executing-dynamic- sql.aspx - CachedStored Procedures - Executing & Dynamic SQL. See Also: ... SQL Server will
inform you that you must declare @qty variable before using it. Similarly, any ...Using dynamic sql inside Oracle stored procedure - Database ...
dba.stackexchange.com/.../using-dynamic-sql-inside-oracle-stored-procedure - CachedAssuming I have the following procedure. CREATE PROCEDURE foo ... The only
reason why I might do that is if I needed to address an object ...Why you want to avoid Dynamic SQL in stored procedure?
dba.stackexchange.com/.../why-you-want-to-avoid-dynamic-sql-in-stored- procedure - Cachedup vote 4 down vote. There is nothing wrong with using dynamic SQL if you must.
... Conditional compilation of SQL Server stored procedure.Using Dynamic SQL Statements in Stored Procedures ...
The curse and blessings of dynamic SQL
Execute Dynamic SQL commands in SQL Server - MSSQLTips.com
Dynamic SQL
www.benkotips.com/pages/DynamicSQL.aspx - CachedDynamic SQL vs. Stored Procedures. The purist DBA view will point out that
using stored procedures would be better because of the advantages they provide
.Stored procedure - Wikipedia, the free encyclopedia
Database - Advanced Stored Procedures Cursors and Dynamic SQL ...
www.youtube.com/watch?v=B9zKi8H_IUs5 Feb 2013 - 13 min - Uploaded by Ron Eaglin
A demonstration of using cursors and dynamic SQL to solve more complex reporting and ...Generate dynamic SQL statements in SQL Server - TechRepublic
www.techrepublic.com/.../generate-dynamic-sql-statements-in-sql-server/ - Cached10 Mar 2008 ... Since sp_executesql is a stored procedure, passing SQL strings to it results ...
Although I am not a huge fan of using dynamic SQL statements, ...Permissions With Dynamic SQL Within Stored Procedure : The ...
forums.asp.net/t/next/1017556 - CachedI have a CLR stored procedure which does some dynamic SQL building based
on values sent in via XML. It's a CLR stored procedure using ...Dynamic SQL Examples - SQLUSA
sqlusa.com/bestpractices/training/scripts/dynamicsql/ - CachedStored procedure with dynamic SQL WHILE loop. 4. Dynamic SQL with using
parent's #temptable. 5. Dynamic SQL for dynamic PIVOT query. 6. Dynamic
stored ...Speed Test: Dynamic SQL vs Stored Procedures - BlackWasp
Dynamic SQL versus stored procedures - Sybase
[T-SQL] Help required to extra image from varbinary column and create image on file system
[T-SQL] Help required to extra image from varbinary column and create image on file system |
- Help required to extra image from varbinary column and create image on file system
- Query Needed
- Top Randomized Results with Multiple Qualifying Conditions
- Advice on complex logic with embedded functions.
- Convert a single column into multiple rows
- Query help - summarize data with some complexities - TSQL gurus needed
- Ignore Error in T-SQL
| Help required to extra image from varbinary column and create image on file system Posted: 21 Sep 2011 04:41 AM PDT SQLServer 2008.Problem: I have a table populated by an external system which stores image data in a varbinary column. I need to create a trigger on the table so that on an insert to the table, the trigger fires and picks up the newly inserted record and extracts the varbinary data and outputs it to a physical image file with a name composed of the recordID + '.JPG' (or whatever graphic format is used) on the hard disk then I can write a row to another table with the filename. I don't mind if the generated file is a JPG or GIF or PNG although I would prefer it not to be a BMP due to the size of these.Creating the trigger is no problem - the issue I am having is extracting the data from varbinary column and creating the graphic file.I've tried googling for this and I can't find a complete solution anywhere. Ideally this needs to run on the SQLServer within a trigger so that it is fully automated.Can anyone help me with this?Many thanksCharlotte CB |
| Posted: 24 Sep 2013 09:33 PM PDT Dear All Currently I am getting following result sets Registerno SubjectCode SubjectName Qno Marks12402223 171906 Quality And Reliability Engineering 1 412402223 171906 Quality And Reliability Engineering 2 1012402223 171906 Quality And Reliability Engineering 3 1112402223 171906 Quality And Reliability Engineering 4 512402223 171906 Quality And Reliability Engineering 5 914124223 170903 Power System Protection 1 314124223 170903 Power System Protection 2 414124223 170903 Power System Protection 3 614124223 170903 Power System Protection 4 314124223 170903 Power System Protection 5 1But I want following result sets Registerno SubjectCode SubjectName Qno1 Qno2 Qno3 Qno4 Qno5 12402223 171906 Quality And Reliability Engineering 4 10 11 5 914124223 170903 Power System Protection 3 4 6 3 1Please help me |
| Top Randomized Results with Multiple Qualifying Conditions Posted: 24 Sep 2013 06:58 AM PDT I found this to be an interesting exercise for the day. I've been assigned the task of picking the "winners" in a lottery, which must be random, but the aggregate results need to meet multiple criteria.It would be easy if there was just a "single" criteria .. you could just pick the top "n" lottery results until the criteria was met. But I need to consider multiple factors.And yes I can easily do this with what is described as "RBAR" ...The table:[code="sql"]CREATE TABLE #Ticket (TicketID NUMERIC(18,0) PRIMARY KEY, sRace CHAR(1), iAge NUMERIC(18,0), sParty CHAR(1))[/code]Some data (I have thousands of records for each lottery)[code="sql"]INSERT INTO #TicketSELECT '18986', 'B', '40', 'D' UNION ALLSELECT '39189', 'W', '63', 'R' UNION ALLSELECT '42223', 'B', '46', 'D' UNION ALLSELECT '44106', 'W', '82', 'R' UNION ALLSELECT '44365', 'B', '52', 'D' UNION ALLSELECT '45726', 'W', '42', 'D' UNION ALLSELECT '53323', 'W', '49', 'D' UNION ALLSELECT '58770', 'W', '70', 'R' UNION ALLSELECT '59624', 'B', '33', 'D' UNION ALLSELECT '59788', 'B', '36', 'D' UNION ALLSELECT '77297', 'W', '72', 'R' UNION ALLSELECT '81772', 'W', '66', 'R' UNION ALLSELECT '90966', 'W', '76', 'R' UNION ALLSELECT '95401', 'W', '56', 'R' UNION ALLSELECT '96760', 'B', '39', 'D' UNION ALLSELECT '101073', 'W', '68', 'R' UNION ALLSELECT '107167', 'B', '44', 'R' UNION ALLSELECT '108750', 'W', '57', 'R' UNION ALLSELECT '123544', 'A', '69', 'R' UNION ALLSELECT '124403', 'A', '44', 'R' UNION ALLSELECT '124937', 'W', '49', 'R' UNION ALLSELECT '126040', 'A', '56', 'R' UNION ALLSELECT '127882', 'A', '75', 'R' UNION ALLSELECT '128238', 'W', '26', 'R' UNION ALLSELECT '132748', 'W', '77', 'R' UNION ALLSELECT '133906', 'W', '35', 'D' UNION ALLSELECT '134248', 'B', '37', 'R' UNION ALLSELECT '136046', 'H', '43', 'D' UNION ALLSELECT '136253', 'W', '55', 'R' UNION ALLSELECT '138220', 'W', '52', 'D' UNION ALLSELECT '140297', 'B', '76', 'R' UNION ALLSELECT '140457', 'W', '36', 'D' UNION ALLSELECT '148863', 'B', '62', 'R' UNION ALLSELECT '148943', 'W', '69', 'R' UNION ALLSELECT '148959', 'W', '57', 'R' UNION ALLSELECT '151948', 'B', '64', 'R' UNION ALLSELECT '152141', 'B', '46', 'D' UNION ALLSELECT '153106', 'W', '53', 'R' UNION ALLSELECT '156206', 'W', '52', 'R' UNION ALLSELECT '160553', 'W', '60', 'R' UNION ALLSELECT '161406', 'B', '27', 'D' UNION ALLSELECT '161663', 'B', '25', 'D' UNION ALLSELECT '161987', 'W', '36', 'R' UNION ALLSELECT '162127', 'W', '61', 'R' UNION ALLSELECT '181421', 'B', '27', 'D' UNION ALLSELECT '181818', 'B', '42', 'D' UNION ALLSELECT '181954', 'W', '57', 'R' UNION ALLSELECT '182168', 'W', '66', 'R' UNION ALLSELECT '182292', 'I', '47', 'R' UNION ALLSELECT '182379', 'W', '44', 'R'[/code]So I can simply "lotterize" the winners with a CTE like this:[code="sql"]WITH lottery AS (SELECT TicketID, sRace, sParty, iAge, ABS(CHECKSUM(NEWID()))%100000+1 AS iRandom FROM #ticket )SELECT TOP 5 * FROM lottery ORDER BY iRandom[/code]**BUT**, I need to be assured that at least 80% of the "winners" have sRace = "W" **AND** 80% of the winners have sParty = "R"So considering this sample result set (it will of course vary each time you run the code) TicketID sRace sParty iAge iRandom181954 W R 57 282124937 W R 49 586696760 B D 39 7212133906 W D 35 9853140457 W D 36 1568459788 B D 36 19464108750 W R 57 20181101073 W R 68 20440161663 B D 25 20612123544 A R 69 2110080%+ (or 4+) must be sRace = "W" and 80%+ (or 4+) must be sParty = "R"Or logicially (and more importantly from a coding perspective) no more than 1 of each can be other than that.So the winners would have to beTicketID sRace sParty iAge iRandom181954 W R 57 282124937 W R 49 586696760 B D 39 7212108750 W R 57 20181101073 W R 68 20440 |
| Advice on complex logic with embedded functions. Posted: 16 Sep 2013 09:22 AM PDT Hi,I have a scenario were under certain circumstances I need to use the earliest start date and then the latest finish date then work out days between these dates for records that need to be grouped together by a typeLatest finish date - earliest start date.In other circumstances, but using data from the same table I need to just use the start and finish date working out the days between without grouping by type.Finish date - start date.This determines the number of days another calculation needs to be greater than in order to qualify for my query. If the days between falls between certain ranges this gets me the days to be greater than called a qualifying period. The other calculation takes the start date, same as number 2 above and measures the days between.Actual finish - start dateI then check to see if this number of days is > the qualifying days.To determine if I should use just the start / finish date or the latest start / finish date I have a function that analysis about 10 parameters to work out what the rows type are first and then I can work out which method to use.All the information for parameters comes from three tables. All joined with inner joins.At the moment I feel I have functions embedded with functions and its not clean but messy.Just woundering how other people tacke complex scenarios like these. One option is I can pass in the rows unique id, have a select statement in a function get everything it needs and perform the calculation, but this feels I would be selecting from the same tables twice when I can use things like Max(date) over (Id) type logic.I could have a view with all the complex logic and self join using the Id mentioned above but this feels it would perfom not as well.Because the data is from 3rd party database, we cannot add indexes to improve performance or add application logic. I can select data only.Thank youEliza |
| Convert a single column into multiple rows Posted: 24 Sep 2013 02:03 AM PDT Helloi have a big task in front of mei have something like this in the text field. in a tableI have two columns Author ID and Descriptionthe description field is as followsDate: 09/04/2013 12:36:24 ReportName Person: Jack Jilll!!!!!!DD Name D/U R F PN!!!----------------------------------------------------------------------------------------------------------------------------------------!!!BookName1 100Pages Written Studymaterial!!!BookName2 200Pages Written Studymaterial!!!The Above is the whole text . To get this i had to concatenate 2 fields and replace Char(13) with !!!.All the other text is in the column value1 and columnvalue2.Now from this i need to put Starting with BookName1 to end of !!! in one row and then BookName2 in second row. for that Author IDSo it should it beAuthorID Description1234 BookName1 100Pages Written Studymaterial1234 BookName2 200Pages Written Studymaterial I used !!! to seperate which text gets into next row.I dont need anything that is before the BookName1Can you please suggest a way to do this. |
| Query help - summarize data with some complexities - TSQL gurus needed Posted: 24 Sep 2013 01:45 AM PDT I'm not exactly sure how best to tackle this. Below is the setup. I removed columns that are inconsequential to the problem and changed the column names and values, but are otherwise indicative of the actual data. Basically, need to generate dynamic descriptions based on a summary of data within a table called InvoiceLine[code="sql"]--CREATE TABLECREATE TABLE InvoiceLine (InvoiceNumber INTEGER, FranchiseLoc VARCHAR(20), Distribution VARCHAR(3))[/code][code="sql"]--INSERT SAMPLE DATAINSERT INTO InvoiceLine VALUES (259,'Coastal Carolina','MJB'),(259,'Coastal Carolina','MMA'),(259,'Coastal Carolina','NEA') ,(259,'Coastal Carolina','PVA'),(259,'Lexington','CC'),(259,'Lexington','HB'),(259,'Lexington','SS'),(259,'Lexington','WB') ,(248,'Ottawa','HA'),(248,'Ottawa','HB'),(248,'Lexington','500'),(248,'Lexington','501'),(248,'Lexington','AB') ,(248,'Lexington','AK'),(248,'Lexington','BP'),(248,'Lexington','CC'),(248,'Lexington','HB'),(248,'Lexington','ILX') ,(248,'Lexington','MF'),(248,'Lexington','SS'),(248,'Lexington','WB')[/code][code="sql"]--Pulling everything from the table show 21 rows--259 and 248 are distinct values for InvoiceLineNumber--from which a summary needs pulledSELECT * FROM InvoiceLine[/code]Given above data, this is what they want the resultset to look like:[code="sql"]SELECT 259 AS InvoiceLineNumber,'Coastal Carolina | Lexington' AS [Description], 'Coastal Carolina: MJB, MMA, NEA, PVA | Lexington: CC, HB, SS, WB' AS DescriptionDetailUNIONSELECT 248,'Lexington | Ottawa','Lexington: 500, 501, AB, AK, BP, CC, HB, ILX, MF, SS, WB | Ottawa: HA, HB'[/code]Basically, put unique FranchiseLoc values for a given InvoiceLineNumber into a single column called Description in a row separate by a vertical pipe. That alone isn't too difficult, but for DescriptionDetail, it gets a little crazier. Put unique FrancechiseLoc values for a InvoiceLineNumber into a single column called DescriptionDetail, but add unique distributions per FrancechiseLoc after a colon. This is where I'm getting a bit lost.Any help/thoughts appreciated. Let me know if any questions. |
| Posted: 24 Sep 2013 01:12 AM PDT Hi,How do we ignore an error in T-SQL? For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.[code="sql"]DECLARE @lCounter SMALLINTDECLARE @lError SMALLINTSET @lCounter = 0WHILE (@lCounter <= 1000 )BEGIN SELECT CONVERT(VARCHAR, GETDATE(), @lCounter) SET @lCounter = @lCounter + 1END[/code]Thanks in advance |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
[SQL Server 2008 issues] Indexes missing after restoring a database to a different server
[SQL Server 2008 issues] Indexes missing after restoring a database to a different server |
- Indexes missing after restoring a database to a different server
- TempDB - same files
- Urgent Help Guys for this Scenario!!!
- SQL Server downtimes
- An INSERT EXEC statement cannot be nested
- Max Memory
- 32 BIt ODBC SQL driver
- how to move ssis packages from one server to another
- Combine CASE for Boolean (is NULL) and Expression
- Creating a recordset from 1 table without a cursor
- Need to fill the Gaps with previous value
- Works in Management Studio but not from C#
- sql server replace query?
- Shift week to Wed - Tues
- SQL cluster installation failed with below error
- Monitoring the SQL Server Agent
- select query not working
- Query Execution Slow and Fast find the difference
- Building Oracle forms frontend application with a sqlserver backend (bypassing oracle database)
- SQL 2008 Developer Edition
- SSMS closes without warning during query run
- Script to find all filestream databases with list of Tables (contain filestream column)
- Security restriction to a SP
| Indexes missing after restoring a database to a different server Posted: 24 Sep 2013 07:20 PM PDT Hello,I am a bit befuddled by a problem I have encountered.The scenario is, On Server A, database is backed up (full backup) , then transaction log backups every 15 minutes. The backup file is then copied to Server B and restored.The result is the database is apparently successfully restored to Server B, yet when digging a little deeper, it is apparent that some of the indexes that exist in User Tables in the original database (On Server A) do not exist in the restored version of the database (On Server B)If the same backup is restored to a new database on Server A, there iis no problem and all the indexes exist.Both servers are Microsoft SQL Server Standard Edition (64-bit), version 10.0.5500.0 |
| Posted: 24 Sep 2013 09:18 AM PDT Friends,I want to know if my datafiles (mdf and ldf) should be the same disk or disks distinct.ex:Example: 1 DISK T tempdb.mdf tempdbdev2.ndf tempdbdev3.ndf ... tempdb.ldfORExample 2: DISK T tempdb.mdf tempdbdev2.ndf tempdbdev3.ndf ... ANOTER DISK DISK K tempdb.ldfThanks. |
| Urgent Help Guys for this Scenario!!! Posted: 24 Sep 2013 12:27 AM PDT Hi, I have a table say emp which has two columns empid and empskills.I need a query to retrieve the empid which has both C and CPP.Input:Empid Empskills1 C1 CPP1 VB2 C2 CPP3 C4 CPPFrom the above input ,I need output like this(otherwise only empid)Empid Empskills1 C,CPP,VB2 C,CPPIf I use the below query,i ll get all the emp id.But I need only the empid which has both the given skills .Select distinct empid from empwhere empskills in ('C','CPP')Thanks in advanceThanks,Gopinath. |
| Posted: 24 Sep 2013 11:59 AM PDT Hello team,Sorry if duplicated, is it possible to get ride of how many times an instance has been restarted? I need this for an Up-time report, so in a month I need to validate how many days or hours an instance was up.I know is very easy to get the time when the instance was last restarted, I can also check with the Xp_readerrorlog the details when an instance was shutdown, but I can't figure out how to get the report, is probably tricky.Any help or comments will be really appreciated.Thanks, |
| An INSERT EXEC statement cannot be nested Posted: 24 Sep 2013 07:04 PM PDT Hi All,i am trying to post sp result to temp table then am getting this error, How can i resolve this issue?.[b]Msg 8164, Level 16, State 1, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 461An INSERT EXEC statement cannot be nested.Msg 213, Level 16, State 7, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 793Column name or number of supplied values does not match table definition.[/b]Ex:insert into #Result(ID,Name,Count)exec [Rep_Accuracy_Achievement_Analysis_Report] |
| Posted: 11 Sep 2013 10:12 AM PDT I have set Max Memory to 6 GB. But SQL Server is using only 2.9 GBQuery used:SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (kB)'So why SQL Server is not using all allocated Max memory i.e 6GB? Is this normal? SQL Server should use all allocated Max memory right?Thanks,Gary |
| Posted: 24 Sep 2013 05:42 PM PDT I need to insatll 32 bit SQL odbc driver in Linux environment , can any one pls help on this of providing the link |
| how to move ssis packages from one server to another Posted: 24 Sep 2013 04:07 AM PDT server name: AServer name :bssis project to be moved ..I am try ing getting lot of errors in the package. |
| Combine CASE for Boolean (is NULL) and Expression Posted: 24 Sep 2013 03:13 PM PDT Hi,I just thinking how to make my code more readable, I need to check C1 for is NULL and for nothing '', I learned that syntax is differentFor now I came up with nested CASE[code="sql"]CASE when C1 is NULL then 'Alpha' else CASE C1 when '' then 'Bravo' C1 when 'c' then 'Charlie' else 'Whiskey' end end[/code]Is there any other way to make it more structural.Tx and BestMario |
| Creating a recordset from 1 table without a cursor Posted: 24 Sep 2013 08:52 AM PDT Hello, I have a questionUsing TSQL without a cursor how do I separate this table into a recordset Director, Managers, Sales Reps and trainees based on the contents ? Sean Murray Is director, the next 4 are managers , next 4 are sales reps and the last 2 are trainees. [b]RepID SalesForceName ReportsToID[/b]1 Sean Murray 12 Johnny Smith 13 Pete Rodin 14 Josh Turner 15 Harry Sykes 16 Jimmy Rud 37 Paddy Giles 38 Jimmy Young 39 Paul Connell 310 Noleen Yates 711 Trish Gates 7 |
| Need to fill the Gaps with previous value Posted: 06 Oct 2012 03:22 PM PDT Hi experts,I have a scenario to fill in the GAPS between the dates with previousdate+1 day.here is the table DDL ,sample data and expected output CREATE TABLE #SAMPLETABLE(DATECOL DATETIME,WEIGHTS float)INSERT INTO #SAMPLETABLESELECT '08/09/2012',8.2 UNION ALLSELECT '08/10/2012',9.4 UNION ALLSELECT '08/14/2012',10 UNION ALLSELECT '08/15/2012',9.6 UNION ALLSELECT '08/16/2012',9.3 UNION ALLSELECT '08/19/2012',9.7 SELECT * FROM #SAMPLETABLEORDER BY DATECOLDATECOL WEIGHTS2012-08-09 00:00:00.000 8.22012-08-10 00:00:00.000 9.42012-08-14 00:00:00.000 102012-08-15 00:00:00.000 9.62012-08-16 00:00:00.000 9.32012-08-19 00:00:00.000 9.7What i need is to fill in the GAPS between the dates with previousdate+1 day and weights is same value as previous record values. -- Expected OutPut2012-08-09 00:00:00.000 8.22012-08-10 00:00:00.000 [b]9.4[/b][b]2012-08-11 00:00:00.000 9.42012-08-12 00:00:00.000 9.42012-08-13 00:00:00.000 9.4[/b]2012-08-14 00:00:00.000 102012-08-15 00:00:00.000 9.62012-08-16 00:00:00.000 [b]9.3[/b][b]2012-08-17 00:00:00.000 9.32012-08-18 00:00:00.000 9.3[/b]2012-08-19 00:00:00.000 9.7Please help me.Thanks, |
| Works in Management Studio but not from C# Posted: 24 Sep 2013 01:11 AM PDT I have a simple stored procedure that takes one nvarchar parameter as input. The parm is compared to a column defined as uniqueidentifier (column SubjectId, below). When I run the sproc in Management Studio it works. When I call it from c# it does not fail but it returns 0 rows.I have checked the spelling of the sproc and of the parm (and their case). All aok. I have converted the sproc call to Command.Text. No change. I have hardcoded the parameter value. No change. I have put a breakpoint in just before filling the dataset and grabbed the query and executed it in Mangement Studio and it works, then I let C# continue execution but no rows are returned. I have changed the dataadapter to a datareader, no change.Here's my code:[code="plain"][dbo].[GetBySubjectId] (@SubjectIdString nvarchar(550) = null) asbeginset nocount onSelect GroupId, GroupName from dbo.MyTable where filetypename = 'Enrollment' and SubjectId = @SubjectIdString[/code]Here's an example of the sproc call with the parm hardcoded in as an experiment:[code="plain"]sqlCommand.Parameters.Add("@SubjectIdString", SqlDbType.NVarChar).Value = "FBBE20B1-55F1-E111-BF73-00155D062F00";[/code]Anybody see the problem? |
| Posted: 24 Sep 2013 07:42 AM PDT I have a column in sql server table in which there are integer values, now i want to replace a range e.g values> 50 with a string 'good' and values<50 replace with 'bad' in only one column in that table, I have tried thisSELECT t.ColA, CASE WHEN t.ColA >= 50 THEN 'Normal value' WHEN t.ColA < 50 THEN 'abnormal value' ENDFROM MySchema.MyTable AS tbut it only selects, not replaced the column values>50 with 'normal value' string. any help please? |
| Posted: 24 Sep 2013 03:26 AM PDT Hello. I need to be able to group data based on not only the date, but also the "week of". However, the "week" is defined as Wed - Tues. Basically, I think what I need is for the code to convert a date to the preceding Wednesday (not the Wed of last week). For example, 9/23/2013 would convert to 9/18/2013, but 9/27/2013 would convert to 9/25/2013. I can find some code to find a date in the previous week, but nothing like this (so far).I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.Thanks,PK |
| SQL cluster installation failed with below error Posted: 24 Sep 2013 03:42 AM PDT Hi,SQl cluster 2008 r2 failed with below error.Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068119551 Exit facility code: 1211 Exit error code: 1 Exit message: Failed: see details below Start time: 2013-09-24 18:02:40 End time: 2013-09-24 18:34:10 Requested action: InstallFailoverCluster Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0xA60E3551%400xD3BEBD98%401211%401Machine Properties: Machine name: xxxxxxxxxxxA Machine processor count: 24 OS version: Windows Server 2008 R2 OS service pack: Service Pack 1 OS region: United States OS language: English (United States) OS architecture: x64 Process architecture: 64 Bit OS clustered: YesProduct features discovered: Product Instance Instance ID Feature Language Edition Version Clustered Configuration file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\ConfigurationFile.iniDetailed results: Feature: Database Engine Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: SQL Client Connectivity SDK Status: Passed MSI status: Passed Configuration status: Passed Feature: SQL Server Replication Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Full-Text Search Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Integration Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Client Tools Connectivity Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Management Tools - Complete Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Management Tools - Basic Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Client Tools SDK Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txt Feature: Client Tools Backwards Compatibility Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0xD3BEBD98@1211@1 Configuration error description: The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\Detail.txtRules with failures:Global rules:There are no scenario-specific rules.Rules report file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130924_175452\SystemConfigurationCheck_Report.htmKindly help me to find out the error......... |
| Monitoring the SQL Server Agent Posted: 24 Sep 2013 02:57 AM PDT We had an issue recently, where after patching the OS and rebooting, the SQL Agent hung:To clarify: All the jobs in the agent would start, and then run continuously without finishing.Is there a way to monitor the SQL Agent and send out an alert of there is hanging activity (for instance long running jobs - which would normally be monitoring by a job IN the Agent).I'm brainstorming here guys...Thanks,Rude Dog |
| Posted: 24 Sep 2013 12:11 AM PDT Here is my table script:USE [MyDB]GO/****** Object: Table [dbo].[USER_MAC_MAPPING] Script Date: 09/24/2013 18:34:41 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_MAC_MAPPING]') AND type in (N'U'))DROP TABLE [dbo].[USER_MAC_MAPPING]GOUSE [MyDB]GO/****** Object: Table [dbo].[USER_MAC_MAPPING] Script Date: 09/24/2013 18:34:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[USER_MAC_MAPPING]( [MAPPINGID] [int] IDENTITY(1,1) NOT NULL, [userid] [int] NOT NULL, [mac_id] [nvarchar](50) NOT NULL, [created_by] [int] NULL, [created_on] [datetime] NULL, [modified_by] [int] NULL, [modified_on] [datetime] NULL, CONSTRAINT [PK_USER_MAC_MAPPING] PRIMARY KEY CLUSTERED ( [MAPPINGID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [constraint_name] UNIQUE NONCLUSTERED ( [userid] ASC, [mac_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO[b]my issue is : when I run select query it keeps on showing message "Executing Query" and never ends.This is a newly created table .....there are no data .....or may be maximum 1 record I tried also select count to print number of records .....this also does not work....it keeps on showing message "Executing Query" and never ends..what is the issue ? how to fix it ?[/b] |
| Query Execution Slow and Fast find the difference Posted: 23 Sep 2013 11:15 PM PDT Hi, I'm desesperated and I need some help.I've a problem with a simple query. If I put a where clause to a particular field it takes a long but only depending the type of where. Let's say if I put: where field is not null it runs fastBut If I put: where field > 1 it runs so slow.This is the query (the most isolated version with the problem) with the > 1 clause.--------------------------------------------------------------------SELECT cabe.CodigoEpisodioFROM dbo.CabecerasFacturas AS cabe RIGHT OUTER JOIN dbo.Historias AS hist INNER JOIN dbo.Episodios AS epis ON hist.CodigoHistoria = epis.CodigoHistoria INNER JOIN dbo.Centros ON epis.CodigoCentro = dbo.Centros.CodigoCentro INNER JOIN dbo.Empresas AS emp ON dbo.Centros.CodigoEmpresa = emp.CodigoEmpresa AND hist.CodigoEmpresa = emp.CodigoEmpresa ON cabe.CodigoCentro = dbo.Centros.CodigoCentro AND cabe.CodigoCentro = epis.CodigoCentro AND cabe.CodigoEpisodio = epis.CodigoEpisodioWHERE (cabe.SwEstadoCabeceras > 1)---------------------------------------------I include the execution plan for the slow and fast versions. Please, help me I really need to fix this as soon as possible.A lot of thanks in advance.http://www.comoflipas.com/slowPlan.xmlhttp://www.comoflipas.com/fastPlan.xml |
| Building Oracle forms frontend application with a sqlserver backend (bypassing oracle database) Posted: 24 Sep 2013 12:55 AM PDT We are trying to migrate from an oracle database to sqlserver. We can use the Sqlserver's Migration Assistant to migrate the data. However, we do not have enough time to convert/rewrite the code. Is it possible to have the existing oracle forms frontend to work with a sqlserver database backend (without an oracle database)? and how? |
| Posted: 23 Sep 2013 11:03 PM PDT Help! I need to obtain a copy of SQL 2008 Dev edition, but Amazon and Microsoft and Provantage don't have any copies available. I already have an R2 version, but I need the non-R2 version for some testing.Anyone have any ideas where a copy of this is still for sale somewhere? (I have both 32bit & 64bit machines). |
| SSMS closes without warning during query run Posted: 19 Sep 2013 01:01 AM PDT Does anyone know why SSMS would close without warning during the middle of a query run?It's done this to me twice this morning. I do have Statistics_IO and Statistics_Time and Include Actual Execution Plan on (which I usually have off), but it seems odd to me that that would be the only reason why SSMS shuts down.No error BTW. The window just vanishes. I haven't checked the logs yet. Doing that after I finish posting. Would love to know if anyone else has seen this problem before. |
| Script to find all filestream databases with list of Tables (contain filestream column) Posted: 18 Sep 2013 10:56 PM PDT Hello,Do you have a script to find all filestream databases with list of tables containing Filestream Column ?I would like a result like this :Name of database, Name of TableThanks,Eric |
| Posted: 23 Sep 2013 08:54 PM PDT I have a little application which allows a user to execute a store procedure with a database restore command in it. The user has to enter a user name and password but I have to give them higher rights than I would like too. Can I restrict this user to only being able to execute this SP?Many thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
Subscribe to:
Posts (Atom)
Danh sách blog
1. Thông Tin Địa Điểm Trường Học Thuộc TpHCM
2. Tìm địa điểm ** Chính xác** Bản đồ,số ĐT,website
3. ATM Agribank, bản đồ, địa chỉ, đường đi
4. ATM Vietcombank, bản đồ, hướng dẫn đường đi đến Địa điểm
5. ATM Sacombank, bản đồ, hướng dẫn đường đi đến Địa điểm
6. ❤ ❤ Dịch vụ chọn lọc cho bạn ❤ ❤
7. Sửa lỗi
8. Đầm bầu
9. Top nice templates for you
10. Top answers for your issues - collect information from Google Search
11. Top Clips Vui Nhộn
12. AngularJS Tutorials
13. Lời bài hát