Wednesday, October 9, 2013

[SQL Server 2008 issues] Catch invalid object and rollback transaction

[SQL Server 2008 issues] Catch invalid object and rollback transaction

Catch invalid object and rollback transaction

Posted: 08 Oct 2013 02:22 PM PDT

I have some code with transaction logic in it. When an update fails, I want the transaction to rollback.The problem I'm having is if my transaction fails because of an invalid object name, it doesn't capture an error number and leaves my transaction open. Is there a better way to capture this update error and rollback the transaction?[code]BEGIN TRANSACTION print 'error1'print @@Error-- @@Error is 0 UPDATE a SET b = 1 FROM a,c WHERE a.t = c.t-- table c does not exist, so the update statement failsprint 'error2'print @@Error-- nothintg prints IF @@ERROR <> 0 BEGIN GOTO ErrorTrans ENDErrorTrans:print 'in errortrans' ROLLBACK TRANSACTION [/code]

user permissions to only for view

Posted: 08 Oct 2013 04:27 PM PDT

I need to give user 'dhii' select permissions only to a view not for all tables. The view has columns from two tables from the same database (ABC). 'dhii' should only see the columns in this view, not any underlying tables. what are the steps to follow?

Sql Server Help Online - ????

Posted: 08 Oct 2013 12:40 PM PDT

I find it difficult to navigate thru the Helpviewer Home (SQLServer Help online - local)I looked up data types, and it listed a bunch of types, but geospatial data types weren't even listed listed. Only when i searched for Geospatiol Data did the type and help display.. When it did display, it doesn't even provide any sort of "path" that would help me know where or in what section of the Help 'book' that info is categorized in. If I could figure out the big picture of where things are located, I might better be able to locate the resources for 'categories' of info, instead of just looking everything up word by word. It just seems like it's organized in pieces all over the place. The Contents display of Help itself, nowhere, even lists Datatypes that I can see.Does anyone have any helpful suggestions?thxf

Date range query problem

Posted: 08 Oct 2013 04:43 AM PDT

Does anyone know what is wrong with this query? I'm trying to make a date range 7 days before 'today' and 21 days after 'today':"SELECT tbl_pac2k_remarks.[Change Request] as ChangeRequest, tbl_pac2k_remarks.[Vetting Status] as VettingStatus, tbl_pac2k_remarks.[Clarification Remark] as ClarificationRemark, tbl_pac2k.[CHANGE REQUEST], tbl_pac2k.STATUS, tbl_pac2k.[Installation Start Date], tbl_pac2k.[Installation End Date], tbl_pac2k.[REQUESTER NAME], tbl_pac2k.DESCRIPTION, tbl_pac2k.[OUTAGE REQUIRED], tbl_pac2k.[BUSINESS IMPACT], tbl_pac2k.[Approval Type], tbl_pac2k.[Approval Group], tbl_pac2k.[OWNER GROUP], tbl_pac2k.[OWNER TECHNOLOGY GROUP], tbl_pac2k.CRL FROM tbl_pac2k_remarks INNER JOIN tbl_pac2k ON tbl_pac2k_remarks.[Change Request] = tbl_pac2k.[CHANGE REQUEST] [b]WHERE (tbl_pac2k.[Installation Start Date] > DATE_SUB([Installation Start Date],INTERVAL 7 DAY)) AND WHERE (tbl_pac2k.[Installation Start Date] < DATE_ADD ([Installation Start Date],INTERVAL 21 DAY))[/b] ORDER BY tbl_pac2k.[OWNER GROUP], tbl_pac2k.[Installation Start Date]"

ERROR IN SSIS

Posted: 08 Oct 2013 05:30 AM PDT

I have package i am running and i get the below once a week i am inserting data through lookup i (look up match output) in sql 2008 database..Error: Directing the row to the error output failed with error code 0x80070057.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Insert 1" (3223) failed with error code 0xC0209022 while processing input "OLE DB Destination Input" (3236). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join 3 1" (3039) failed with error code 0xC0047020 while processing input "Merge Join Left Input" (3044). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.Any ideaShaun

Slowdown After Moving to New Virtual Machine

Posted: 08 Oct 2013 05:23 AM PDT

Hi there! I'm having a bit of a conundrum with one of our servers currently. Last night, we moved our SQL box from its previous virtual machine to a new one, and provisioned some extra resources for it (more CPU, slightly faster disks).However, for some reason, everything's moving really sloooooowly on the server now. The SQL instance has grabbed up all the resources it usually does (28GB of RAM), and the CPU is spinning at about 25-40% usage, with occasional spikes up to 100% for a few seconds if things get heavy. All settings and so on are as they were previously, and these readings match what I usually see. However, one of our heavier procedures that usually takes about 5 minutes has now been running for around 40 minutes without finishing.Because of this, I'm not really sure what's slowing the server down; I'm not seeing any memory pressure from DMV queries, and I've checked to make sure the power-saving CPU limiter is off, so I don't have much of an idea as to what else I could tune. Is there something special that needs to be done when moving to a new virtual machine? I wasn't involved with the move, so I'm not entirely certain what transpired during it, but I don't think it should've shredded our performance quite this badly.Thank you for any assistance you can provide!

query to concatenate/pivot? base on group by

Posted: 08 Oct 2013 04:35 AM PDT

Hi,Is it possible to achieve result that illustrated below in nice single query? I started doing pivot, but still missing this concatenation, other whay go with XML Path??bit lost here.THanks all to all[code="sql"];with t as (select * from (select 21 id1, 200 id2, 'prod1' prodID unionselect 21 id1, 200 id2, 'prod22' prodID unionselect 21 id1, 200 id2, 'prod333' prodID unionselect 11 id1, 400 id2, 'prod11' prodID unionselect 11 id1, 400 id2, 'prod22' prodID unionselect 11 id1, 900 id2, 'prod01' prodID unionselect 33 id1, 300 id2, 'prod3' prodID ) a )-- select * From tid1 | id2 | ProdIDs |21 | 200 | prod1, prod22, prod33 |11 | 400 | prod11, prod22 |11 | 900 | prod01 |33 | 300 | prod3 |-- group by id1, id2[/code]

Replication issue - Never seen this before

Posted: 08 Oct 2013 04:06 AM PDT

When I tried to locate Xact Seqno for the article causing the issue . I get this error , can any one help me to explain what could be the cause . EXEC sp_browsereplcmds @xact_seqno_start = '0x00077C630003F4150005' , @xact_seqno_end = '0x00077C630003F4150005' -------------------------------------------------------------------------------------(0 row(s) affected)Message: Invalid attempt to read when no data is present.Call Stack: at System.Data.SqlClient.SqlDataReaderSmi.EnsureOnRow(String operationName) at System.Data.SqlClient.SqlDataReaderSmi.GetBytes(Int32 ordinal, Int64 fieldOffset, Byte[] buffer, Int32 bufferOffset, Int32 length) at Microsoft.SqlServer.Replication.ReplCmdsReader.convert_command(Boolean& fFirstChuck_in_cmd, Int32& iParamMarker, ParamToken& tokenReader, Object[]& rgobj, SqlDataReader& reader) at Microsoft.SqlServer.Replication.ReplCmdsReader.sp_printstatement(String strCmd)Debug dump: cbTSQL: 1702127982 wParam: 0 iParam: 0 oDatum: 0 oNextToken: 0<Reset />

sp_generate_inserts

Posted: 07 Oct 2013 11:45 PM PDT

I am still using sp_generate_inserts :[code="sql"] (Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.) Purpose: To generate INSERT statements from existing data. These INSERTS can be executed to regenerate the data at some other location. This procedure is also useful to create a database setup, where in you can script your data along with your table definitions.Written by: Narayana Vyas Kondreddi http://vyaskn.tripod.com[/code]This code has not changed for a number of years, it is still functioning, but is there a alternative for this within the SQL-server product ?I am aware of the 'database task generate scripts'.Thanks for your time and attention,Ben

DecryptByKey - returns me only 2

Posted: 07 Oct 2013 09:05 PM PDT

Hi all,I have a SQL Server which encrypt me / decrypts sensitive data in nvarchar column to binary one with insted of trigger.When I try to decode them with same key, the sensitive data looks like that '2 'Some ideas what happens ?Best regards,Krastio Kostov

Issues with certain domain users accessing a SQL server

Posted: 08 Oct 2013 01:52 AM PDT

I no longer can connect via SSMS to a specific server using my domain account. here are the facts and what I have checked. This is a sql 2008 r2 RTM machine. SP2 to be installed next month. --I can remote into the server and connect using my accoung to SSMS--TCP enabled on 1433--NP disabled--I can telnet into the ip 1433--Other domain users can connect just fine - there is one other users getting the same thing--ran xp_logininfo and my login is part of a a group with sysadmin rights--I can login from my laptop to SMSS using a sql user--Made sure sql browser was active--I have the maxtokensize set--It is not just on my laptop - I remoted into a 3rd server (not the sql) and I wasnt able to connect on that server to the other Sql server either. Errors I encounter:[b]SMSS connection error [/b]- A connection was established, but then an error occured during the login processs (provider: TCP provider, error: 0 - The specified network name is no longer available.) SQL error 64[b]ODBC connection [/b]- error 64 specified network name is no longer available[b]SQL error log message [/b]- The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. Error 17832 serverity 20 state 8. Again others can access just fine.

Update Based on Select with Multiple Returns

Posted: 08 Oct 2013 12:07 AM PDT

Good Morning!I'm trying to update a column based on a WHERE statement that uses a SELECT statement as the qualifier.My goal is to find duplicate records in my table, then have a value in one of the columns change so that the users can search for and view these duplicate records and decide how they'd like to deal with them.Here's the SELECT statement that does a nice job of finding my duplicates:select IDX_Invoice_Number, IDX_Vendor_Number, status from _obj_3group by IDX_Invoice_Number, IDX_Vendor_number, statushaving COUNT (*)>1Here's what I'd use to accomplish my goal, if it were possible:update _obj_3set status='7'where idx_invoice_number in(select IDX_Invoice_Number, IDX_Vendor_Number, status from _obj_3group by IDX_Invoice_Number, IDX_Vendor_number, statushaving COUNT (*)>1)But, I get an error because the SELECT statement yeilds multiple returns:'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'Which it will, because they're duplicates that it's finding.I've tried using an INNER JOIN back to itself with no luck, and just created a VIEW using my SELECT statement, hoping to be able to update the VIEW.No luck there, as the VIEW won't allow me to update it.Well, thanks taking a look, and I can't wait to see what comes back.Thanks!

.NET Framework 3.5 install fails when installing SQL 2008R2 on Windows Server 2003

Posted: 08 Oct 2013 12:52 AM PDT

Hello,I am having issues installing SQL 2008 R2. After launching the install file, the setup tries to install .NET framework 3.5. After a few prompts it fails with the following error: Microsoft .NET Framework 3.5 SP1 has encountered a problem during setup. Setup did not complete correctly.I have seen plenty of blogs with the same issue but nothing seems to work. I have a high priority project due and I cannot seem to get SQL 2008 installed. Please help.Here is what came out of my logs:[10/07/13,09:52:30] Microsoft .NET Framework 2.0a: [2] Error: Installation failed for component Microsoft .NET Framework 2.0a. MSI returned error code 1624[10/07/13,09:52:52] WapUI: [2] DepCheck indicates Microsoft .NET Framework 2.0a is not installed.

Export a table to a flat file. Can't get the quotes correctly.

Posted: 07 Oct 2013 10:35 PM PDT

While exporting a table using the export wizard, I tried using double quotes as a text qualifier and got "" Column value "" as output. How do I get a single set of quotes around the value?

Display columns as rows

Posted: 07 Oct 2013 09:02 PM PDT

Hi,I have one row in table like below.c1 c2 c3 c42 4 6 5now i want result like belowcolumn1 column2c1 2c2 4c3 6c4 5Thnak you in advance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog