Thursday, March 14, 2013

[SQL Server 2008 issues] new to calling procedures or scripts

[SQL Server 2008 issues] new to calling procedures or scripts

new to calling procedures or scripts

Posted: 13 Mar 2013 10:54 AM PDT

WHat is the best way to go around this. I know I can just add the code all in one page but I would rather break it upso I have 2 .sql files one called createtable.sql and one called cleantheversion.sqlhere are the scriptscreatetable.sqlif exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable')) drop table BigTable; --Drop SOURCE--Create a tableCREATE TABLE BigTable(software_name_raw VARCHAR(255),software_version_raw VARCHAR(255))GO--Insert records into Source tableINSERT INTO BigTableVALUES('Microsoft Office 2003','11.0.2.1'),('Microsoft 2003 Office','11.0.3.1'),('Microsoft Office 2003 Professional','12.1.2.1'),('Sun Microsystems','11.0.0.1')GOSELECT * FROM BigTable as Source_TABLEGO??can i call the other .sql file somehow-----EG Call cleantheversion.sql???????????---------------------cleantheversion.sqlBEGIN TRANSACTION Inner3;GOupdate dbo.BigTable set BigTable.Software_Version_Raw =case when CHARINDEX('.',Software_Version_Raw,0) >0 then substring(Software_Version_Raw,0, CHARINDEX('.',Software_Version_Raw,0) )+ '.x' when isnumeric(Software_Version_Raw)=1 then Software_Version_Raw + '.x' else Software_Version_Raw endgoCOMMIT TRANSACTION Inner3;

How can I get these values with a match field that has different file extensions.

Posted: 13 Mar 2013 05:57 PM PDT

Hello and thank you for the help.I am trying to write a select statement where I can view 3 columns by a filename with a join statement. Unfortunately the value in both filenames match but their file extensions do not. Here is an example:6024500036687.pst.txt = 6024500036687.pst.msg.htmAs you see the first piece of the filename match but the file extensions do not. Any suggestion will help. I am fairly green and would greatly appreciate the help.

update still difficult??

Posted: 13 Mar 2013 03:05 PM PDT

I have a table which has three columns [code]create table employees (ID int NOT NULL PRIMARY KEYNAME varchar(10) NULLEXTENDEDID int NOT NULL IDENTITY(1,1))INSERT INTO employees ( ID, NAME, EXTENDEDID) VALUES( 1, Sean, 1), ( 3, Jason, 2), ( 2, Gail, 3), ( 5, Jeff, 4)[/code] I have to Identify those columns where ID is not equal to EXTENDEDIDSo I did [quote]Select * from employees where ID <> EXTENDEDID[/quote]Now I have to make the two columns equal.. I know that EXTENDEDID is an identity column and i cant change it..So trying to make the columns equal comparing with EXTENDEDID So the required output shd be[quote]ID NAME EXTENDEDID1 Sean 12 Jason 23 Gail 34 Jeff 4[/quote]Now I used the following statement/statements to change get the O/P[quote]Update employees set ID= (select EXTENDEDID from employees where ID<> EXTENDEDID) where ID<> EXTENDEDIDupdate employees set ID= EXTENDEDIDUpdate employees SET ID= EXTENDEDID WHERE employees.ID <> employees.EXTENDEDID[quote]All of them failMy next requirement is to Print all those records that have been updated accordingly...Any Suggestions are appreciated :)

Reg Backup Stratergy

Posted: 13 Mar 2013 04:07 PM PDT

Is it best practice to take backups of system databases and user created databases in separate through maintenance plans and jobs. Else we should take them together.

Why is "instead of delete" trigger not fired by delete inside "after update" trigger

Posted: 13 Mar 2013 02:40 PM PDT

One of my tables has both an "after update" and an "instead of delete" trigger. When I perform a delete within the after update trigger, the instead of delete trigger is not fired. Although I don't actually want the instead of delete trigger to fire in this particular case, I'm troubled by the fact that I haven't seen anything in the documentation that indicates it shouldn't. If I understand correctly, the recursion is indirect, and hence, should not be prevented by RECURSIVE_TRIGGERS disabled (the setting in my database).The only thing I found in the docs that seemed close to explaining what I'm seeing is the following from the SQL Server 2008 R2 documentation:[i]If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.[/i]This statement, however, applies only to the case in which the statement triggering the recursive call to the instead of delete trigger is itself within the instead of delete trigger. In my case, the delete statement in question is in an after update trigger, so the statement from the docs seems not to apply.I noticed that at least one other user has been puzzled by this behavior:http://www.sqlservercentral.com/Forums/Topic1387252-23-1.aspxStill no response to his post...Does anyone know what's going on here? I could explicitly disable triggers before executing the delete statement within the after update trigger, but I don't want to do this unnecessarily...Thanks,Brett S.

"Truncation may occur due to inserting data from data flow column...." in SSIS 2008

Posted: 13 Mar 2013 05:18 PM PDT

HI All,Am getting that error in SSIS 2008 even i changed the column length in flat file source in advanced editor..i guess its not saved so that it is giving the same error again.So colud any body tell me how that meta data property is to be refreshed or solve this issue ?Regards,Ravindra

How to add below complex conditions in SQL script..

Posted: 13 Mar 2013 04:29 PM PDT

[size="2"][font="Courier New"]Hi All,Below is my written queryDECLARE @FromDate DATETIMEDECLARE @EndDate DATETIMESET @FromDate = '2013-01-01 00:00:00.000'SET @EndDate = '2013-02-13 00:00:00.000'SELECT DISTINCT year(sd.FKDAT) As YEARWISE_DATA, so.vkbur As MARKET, so.bezei As NAME, sd.kunrg As PARTY, cm.NAME1 As PARTY_NAME, --za.FGCODE As ITEM, --za.FGDESC As ITEM_DESCRIPTION, --za.EANNUM As USACODE, im.MATNR As ITEM, im.MAKTX As ITEM_DESCRIPTION, im.EAN11 As USACODE, SUM(sd.FKIMG) As QUANTITY, SUM(vb.NETWR) As VALUE_IN_FC, SUM(sd.NTGEW) As WEIGHT -- (SUM(vb.KWMENG) - SUM(sd.FKIMG)) As PENDING_QUANTITY, -- (SUM(vb.NETWR) - SUM(sd.NETWR)) As PENDING_VALUE, -- (SUM(vb.NTGEW) - SUM(sd.NTGEW)) As PENDING_WEIGHT FROM sales_office soLEFT JOIN SALES_DATA sd ON so.VKBUR = sd.VKBURINNER JOIN Item_Mas im ON sd.MATNR = im.MATNR INNER JOIN Cust_Mas cm ON sd.KUNRG = cm.KUNNRINNER JOIN VBAP vb ON sd.AUBEL = vb.VBELN AND sd.AUPOS = vb.POSNR and sd.MATNR = vb.MATNRWHERE sd.FKDAT >= @FromDate AND sd.FKDAT <= @EndDate AND im.EAN11 != ' 'GROUP BY YEAR(sd.FKDAT), so.vkbur, so.bezei, sd.kunrg, cm.NAME1, --za.FGCODE As ITEM, --za.FGDESC As ITEM_DESCRIPTION, --za.EANNUM As USACODE, im.MATNR, im.MAKTX, im.EAN11ORDER BY 1, so.VKBUR, sd.kunrg, im.EAN11 ASCFor PENDING_QUANTITY, PENDING_VALUE and PENDING_WEIGHT calculations I have to place two conditionsI will check in sales_data table with condition (sd.FKDAT >= @FromDate AND sd.FKDAT <= @EndDate AND sd.KUNRG = 'HA127', AND sd.MATNR = 'FG151765162081222') [b]Condition-1[/b] IF I found no entries in result set i.e. 0 Rows Affected Then I want this result setQUANTITY = 0, VALUE_IN_FC = 0, WEIGHT = 0, PENDING_QUANTITY = SUM(vb.KWMENG) PENDING_VALUE = SUM(vb.NETWR) and PENDING_WEIGHT = SUM(vb.NTGEW) From VBAP table with one more filter only for Pending Attributes calculation i.e. (vb.AUDAT >= @FromDate AND vb.AUDAT <= @EndDate AND vb.KUNNR = 'HA127', AND vb.MATNR = 'FG151765162081222') alongwith other select query output[b]Condition-2[/b] IF I found entries in sales_data table based on parameters provided above ThenQUANTITY = SUM(sd.FKIMG), VALUE_IN_FC = SUM(vb.NETWR), WEIGHT = SUM(sd.NTGEW)PENDING_QUANTITY = 0, PENDING_VALUE = 0, PENDING_WEIGHT = 0My concern is how to add these conditions in above Select query and in Inner Join of Sales_data & VBAP statements..?[/font][/size]

Errors for sql server connection in asp server

Posted: 13 Mar 2013 04:03 PM PDT

In an asp server we are getting error for SQL server connection like below[highlight="#FAFAD2"]System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at AutoRenReg.ConnectToSQLDB()[/highlight]Also after the ExecuteScalar, ExecuteReader and ExecuteNonQuery i am getting error like[highlight="#FAFAD2 "]System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.WriteSni() at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode) at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush() at System.Data.SqlClient.TdsParser.TdsExecuteSQLBatch(String text, Int32 timeout, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at WinMsg.Page_Load(Object sender, EventArgs e)[/highlight]Both errors not seen always.But it happens sometime.So what may be reason for these error?How to solve it?

need assistance with query

Posted: 13 Mar 2013 03:42 PM PDT

Hello all!How could I return the top ten highest values in a column? WOuld it be somewhere along the lines of Select Top(10) StudentScore from dbo.Employees?The output would be along the lines of this:10099665433etc...Does anyone know how? thanks

How to Get The Most Filled Records from Similar Records

Posted: 13 Mar 2013 09:29 AM PDT

How to Get The Most Filled Records from Similar RecordsI have table called "Locations" and it contains 4 Columns "Neighborhood,City,Governorate,Country"[b]Neighborhood, City, Governorate, Country, , , Egypt , , Alexandria, Egypt, East, Alexandria, Egypt, West, Alexandria, EgyptMoharemBek, West, Alexandria, EgyptMoharemBek, North, Alexandria, Egypt, , Cairo, Egypt, , , Morocco[/b]Here is the first Record filled only with Country Value and nothing else ...The Next Record Contains 2 filled Columns Governorate and Country and the country value the same as The First Record ...so here the winner will be The Second Record and we shall ignore First Record.The Same On the Third Record ..Contains 3 filled Columns City, Governorate and Country ... and Governorate the same as the second record and Country the same as First and Second ...so here the winner will be the third record and we shall ignore the first 2 records .The Output I wish to get it :[b] , East, Alexandria, EgyptMoharemBek, West, Alexandria, EgyptMoharemBek, North, Alexandria, Egypt, , Cairo, Egypt, , , Morocco[/b]

ProcessAddress times out every time - result set size, time of day seem irrelevant

Posted: 13 Mar 2013 02:16 PM PDT

I am using the ProcessAddress geocoding function found [url=http://yaddressudf.codeplex.com/]here[/url] on CodePlex, but no matter what, it always times out on me. Is this just an issue with Yahoo's geocoding, or am I doing something wrong?Here's my stored procedure:BEGIN UPDATE [Account_Master_2013] SET [County] = pa.County, [Long] = pa.Longitude, [Lat] = pa.Latitude FROM [Account_Master_2013] CROSS APPLY ProcessAddress([Account_Master_2013].[Address], [Account_Master_2013].CITY + ', ' + [Account_Master_2013].[STATE], null) pa WHERE [Account_Master_2013].[state]=@StateRegion AND [Account_Master_2013].County IS NULL AND ACM IN (SELECT TOP (@Top_N_Records) ACM FROM [Account_Master_2013] WHERE lat=0 OR lat IS NULL);ENDAt first I tried limiting the records processed to a really small set, but that didn't seem to matter. Nor did the time of day that I ran the stored procedure (because I was wondering if the server were just busy). The code compiles fine, and runs, but times out after about two minutes, even if I set the @Top_N_Records = 5 .What am I doing wrong? Or should I use something else to do geocoding? I am pretty sure the syntax for the update is right - Vedran Kesegic sorted that part out a long time ago... now it just plain fails though.Where do I start to try to fix this? Thanks!Pieter

Fuzzy Matching Barcodes

Posted: 13 Mar 2013 06:01 AM PDT

I have a list of 1 million+ products with numeric barcode values stored as VARCHAR(50) that can range in length in a table. I'm trying to find a way to do a fuzzy search query when a user enters a barcode to find a product. Let's say I have a barcode table like:Barcodes----------98425103984251049942510399564312If the user entered something like 98425102, I would expect it to return 98425103. However, if the user entered 97425103, I'd expect both 98425103 and 99425103 to be returned. I'm basically looking for the closest matches to the entered barcode, while factoring misspellings/mistypes as well. Would I implement this via RegEx CLR or is there a way to do this through the standard LIKE operator?

how smart is the SS2008R2 query optimizer?

Posted: 13 Mar 2013 05:56 AM PDT

Hello - How smart is the SS2008R2 query optimizer? For example, consider the following query:select top 10 * from ActivityPointer where Subject like '%new registrant%' andOwningUser = 'F9841D46-5904-E011-949A-005056BC3615'A guid search is going to be faster than a text search. And a like search (%%) makes text searches even slower. So is SS2008R2 smart enough to optimize this query before it executes it?For example, will SS2008R2 determine on its own that OwningUser is a guid search that provides better performance than a text like search so therefore apply the guid search first in order to reduce the number of rows that the text like search is applied to?

Diagnosing SQL response time issues

Posted: 13 Mar 2013 05:46 AM PDT

We moved our server racks in our colo facility over the weekend, upgraded to a new firewall, and installed new switches.I've got one SQL server (SQL 2008 R2 SP2, Windows 2008 R2 Enterprise) in that facility. Before the move, it was behaving just fine. After the move, I'm seeing blocking and deadlocks on a regular basis. Note that this SQL server is very underutilized - far more hardware than necessary for the load it has.The interesting part of this is that if I compare SQL server stats from before the move and after, the only stat that's changed is wait times have started to creep up. But I can't spot any good reason for that on the server itself.What we *are* seeing is data getting *to* the SQL server fine, but a huge delay in the data get back from the server to the calling application. My favorite test case - I fire off a very small update from one application (it updates four records in a table) against a SQL server that isn't in the colo facility, and response time is the same as prior to the move. If I set that same application to look at the SQL server in the colo facility and send the exact same update, it takes 42+ seconds to finish.But you don't see that 42+ seconds on the SQL server. And if the application times out, the data is still getting written to the server.We've run hardware diagnostics on the SQL server - nothing reporting an issue.We've swapped out network cables, and have tested changing switches and even moving back to the old switches that were in place prior to the move. No change in behavior.I'm seeing far more ASYNC_NETWORK_IO waits than I did prior to the move, but nowhere near enough to account for the sheer lag we're experiencing.I've checked baseline SQL Server configuration from before and after - no changes to the config.We did deploy quite a few windows updates - this server hadn't been offline for maintenance in several months. My network guys are telling me they don't see anything unusual, and me and my SQL folks aren't finding anything pointing to the SQL server directly - everything I'm seeing is pointing to dropped connections over the network.I'm perfectly willing to hire a consultant to come in and look at the situation - but I'm not even sure what type of consultant to call at this point - a network one or a SQL one. Any suggestions for where to hunt next would be appreciated. Short of physically removing the server from the colo facility and racking it in another office, I'm kind of out of things to test.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog