Friday, March 8, 2013

[SQL Server 2008 issues] Extract text value from a column having alphanumeric value

[SQL Server 2008 issues] Extract text value from a column having alphanumeric value

Extract text value from a column having alphanumeric value

Posted: 07 Mar 2013 04:52 PM PST

Hi All,I am using SQL server 2008. In my database I have a table sat "Employee" having a column say "Height" now in that cocolumnaeave value along with the units e.g. 5ft or 65in like this. Now I want to write a query that will seseparateut the value on the basis of test and numeric value. Something like this:Select Height[text] as HeightValue, Height[numeric] as HeightUnit from Employeeand I should get the result as HeightValue HeightUnit5 ft65 inSomething like the above example.Can we have such query? Please suggest.Regards,Girish Nehte

Query optimization

Posted: 07 Mar 2013 04:09 PM PST

Hello friends,I have table contains millions of records.When i fired query to get record by id. Query get millions of rows for that it take 2-3 second time.How can i optimize query?My sql query is:SELECT * FROM Table1 WHERE intId = @intId AND bintTimeStamp >= @bintStartTimeStamp AND bintTimeStamp < @bintEndTimeStamp ORDER BY bintTimeStamp

Inserting currency symbols in money datatypes

Posted: 07 Mar 2013 03:21 PM PST

Hi,I heard we can insert currency Symbols like $ to Money and small money datatypes. But while i am trying i realized we can insert $ into money datatype but it will not display symbol while viewing table content. Am i missing anything or it is like that only? If so What is need of money datatype because we can use decimal or nvarchar only?Thank you

confused With View , Function and Store Procedure

Posted: 03 Mar 2013 06:08 PM PST

i get confused where to use function , store procedure and view.plz help me;-)

How to find all column dependencies including triggers?

Posted: 07 Mar 2013 10:03 AM PST

Hi,There was a situation when a column was removed from a table via some external web application, and after that a bunch of "invalid column name" sql errors generated in app's log.The reason was that due to apparently app's bug, it did not update table's trigger that was still using the deleted column name in its code... What would be a best way to see ALL column dependencies?Thanks!

modifying a SP

Posted: 07 Mar 2013 08:03 AM PST

We already have a SP which selects data from db.I need to modify the SP so that it selects one more column called supervisor.The code to get the supervisor data is SELECT * INTo #sStaffNamesFROM (select p.personid,p.LastName + N', ' + p.FirstName AS Supervisor, S.StaffID---from Staff as s inner join people as pon s.PersonID = p.personid)A select peoplelinkid ,staffassigned,Supervisorfrom clientinfo as ci LEFT JOIN #StaffNames as s ON Ci.StaffAssigned=S.StaffIDwhere ci.staffassigned<>0 drop table #sstaffnamesThis code works perfectly and I am able to get the desired result.Now the problem is I am not able to figure out how to put the above code in a SP which selects data.Thanks in advance

Maintenance Plan issue

Posted: 07 Mar 2013 03:14 PM PST

Hi all, I created a maintenance plan to perform backups for all the databases in my test box. but the job is failing every time.it is not able to take the backup of a one particular user created database and taking backups of all other databases. can any one has any idea on this issue.

Create File without xp_cmdShell

Posted: 07 Mar 2013 04:54 AM PST

Hello,Is there a way to create a text file in the file system (Win) without xp_cmdshell / sp_configure?RegardsNicole ;-)

AD groups and login permissions

Posted: 07 Mar 2013 08:24 AM PST

Here's the situation.I have some logs that seems to show someone executing a procedure I don't think they should have been able to execute. They shouldn't even be able to get to the database, much less execute this proc.The permissions are handled through AD groups, and this person doesn't belong to any of the groups that can access the database, from what I can tell. But I wonder if there's some kind of nesting (groups belonging to another group) thing going on.Anyway, my question, is given a person's domain login, can I find out what effective SQL permissions they have?

looping through databases

Posted: 07 Mar 2013 07:29 AM PST

I am working on a sql task where i have to loop through all the databases in the server and do operations on those databases.. For example: selecting db1 and executing some select statements, selecting db2 and executing some select statements so on.. here is the code [quote]DECLARE @Loop intDECLARE @DBName varchar(max)declare @maxRow intset @Loop = 1;SET @DBName = ''SELECT @maxRow=max(database_id) FROM sys.databases WHILE (@Loop <= @maxRow)BEGIN SELECT TOP 1 @DBName = d.Name FROM master.sys.databases d WHERE d.Name > @DBName AND d.database_id not in (1, 2, 3, 4) and d.state_desc = 'ONLINE' ORDER BY d.Name set @Loop = @Loop+1; PRINT @DBNAME END[/quote]But the result is[quote]db1db2db3db4db4db4[/quote]I know the culprit is here[quote]SELECT @maxRow=max(database_id) FROM sys.databases WHILE (@Loop <= @maxRow)[/quote]But I couldn't go further.. Confused how to approach

Archiving

Posted: 14 Jan 2013 02:13 AM PST

MSSQL2008 with 1 TB Harddisk spaceDatabases: DB1 - data for 2012.DB1_Arch - data for 2011.*** Inherited this server; looks like he idea was DB1 is the production; and DB1_Arch is the archive of DB1 [u]Question 1[/u]: When I issue the following query[quote]SELECT file_id, name, type_desc, physical_name, size /1024 / 1024 AS gbsize, max_size/1024/1024 AS gbmaxsizeFROM sys.database_files ;[/quote]I get [b]96 gbsize [/b]for Data file and [b]2gb[/b] for Log file for DB1. However, when I do Reports, Standard Reports, Disk Usage on DB1, I get 790 xxx.xx MB for data file size (which is about [b]770gb[/b]) and 18 xxx.xx MB (or about [b]17gb[/b]) in log file size. Which is right? [u]Question 2[/u]: There's a request to archive 2012 data to DB1_Archive; then make a backup of it to a different file server. Then delete data from the current DB1 database. Immediate issue I'm facing - if DB1 is in fact 770gb, and total disk is only 1 TB; how do I archive to DB1_Archive first before deleting from DB1?? The math for the space just isn't there. Any suggestions?Thanks

Slow Cascade Stored Procedure & Hang

Posted: 07 Mar 2013 07:29 AM PST

Hi All,So we have this stored procedure that runs fine most of the times, but we have occasionally encountered that it hangs infinitely and also is very slow. I have pasted the stored procedure code below. Let me know what you'll thinkSELECT @rowCounter = 1, @totalrows = @@ROWCOUNT WHILE @rowCounter <= @totalrows BEGIN SELECT @currentId = tempId FROM @temp WHERE row = @rowCounter SELECT @newModeledCost = case when not exists (select 1 from dbo.DIM_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model') then ISNULL(DriverValue1,0)*ISNULL(DriverValue2,0)*ISNULL(UnitA,0)*ISNULL(UnitB,0)+ISNULL(FixedCost,0) else (ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(NULLIF(DriverValue2,0),1))* ISNULL(UnitB,0))+ISNULL(FixedCost,0) end , @oldModeledCost = ISNULL(ModeledCost,0), @newOct = (ISNULL(@newModeledCost,0) * (ISNULL(Oct, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newNov = (ISNULL(@newModeledCost,0) * (ISNULL(Nov, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newDec = (ISNULL(@newModeledCost,0) * (ISNULL(Dec, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJan = (ISNULL(@newModeledCost,0) * (ISNULL(Jan, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newFeb = (ISNULL(@newModeledCost,0) * (ISNULL(Feb, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newMar = (ISNULL(@newModeledCost,0) * (ISNULL(Mar, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newApr = (ISNULL(@newModeledCost,0) * (ISNULL(Apr, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newMay = (ISNULL(@newModeledCost,0) * (ISNULL(May, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJun = (ISNULL(@newModeledCost,0) * (ISNULL(Jun, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newJul = (ISNULL(@newModeledCost,0) * (ISNULL(Jul, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newAug = (ISNULL(@newModeledCost,0) * (ISNULL(Aug, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))), @newSep = (ISNULL(@newModeledCost,0) * (ISNULL(Sep, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))) FROM dbo.Calculations WHERE BudgetId = @currentId UPDATE dbo.Calculations SET ModeledCost = @newModeledCost, Oct = @newOct, Nov = @newNov, Dec = @newDec, Jan = @newJan, Feb = @newFeb, Mar = @newMar, Apr = @newApr, May = @newMay, Jun = @newJun, Jul = @newJul, Aug = @newAug, Sep = @newSep, Username = 'Cascade', lastmodified = getdate() WHERE BudgetId = @currentId AND @oldModeledCost <> 0 SET @rowCounter = @rowCounter + 1 END

computed column or trigger

Posted: 07 Mar 2013 04:25 AM PST

[code="sql"]CREATE TABLE dbo.#score ( StudentID int IDENTITY (1,1) NOT NULL , Math int , science int ,History int ,Sports int ,Least_score int);-- Insert values into the table.INSERT INTO dbo.#score (Math, science,History,Sports)VALUES (5, 6,8,10), (3, 7,0,9)select * from #scoreI want to make a least_score as a computed column or by use of some trigger .So that the least_score column should get updated automatically with the least value out of Math,Science,History and sports.In my case least_score for student_id=1 should be 5 and for student_id=2 it should be 0.How we can do this?.update #scoreset Least_score=5where StudentID=1update #scoreset Least_score=0where StudentID=2select * from #score[/code]

Oracle to SQL Server Migration - Database Size Estimation in SQL Server

Posted: 07 Mar 2013 03:51 AM PST

Hi we are migrating Oracle database to SQL Server the database size in Oracle is 5 TB. what wil be the size of this Oracle database in SQL Server once it is migrated. There might be the concerns abt what datatypes i am using, indexes and all. Counting all this concerns in general can i have a rough estimate..Thanks a ton in advance! :)

How can I delete Un_installed servers from the sql server Config manager&gt;

Posted: 07 Mar 2013 04:28 AM PST

i have 12 instances that i have installed and Uninstalled from my laptops and sql services are still showing in the configuration manager? thanks

Multiple Transaction Log Files

Posted: 07 Mar 2013 04:47 AM PST

We have multiple transaction log files configured for some of our high volume clients; this is done for disaster prevention reasons. Should transaction log backups fail repeatedly during late night activities, and no tech notification go out in time, a secondary log file generally lets stuff keep going on a 'spillover' volume until backup and truncation issues are resolved.Our issue with this strategy is: once SQL Server has hit a threshold that requires utilization of this secondary log file, after backup issues are resolved, SQL Server appears to continue to use this secondary file, while leaving the primary log file alone. What is the reason for this? How do we ensure SQL Server will return to using this primary log file (perhaps it's on a SAN and resides on higher performance drives than the failover file does...)Thanks!

Table variable subquery as column

Posted: 07 Mar 2013 04:15 AM PST

Hi All,I'm trying to use a value from a table variable's column that matches w/a passed in variable as a subquery for one of the main query's column. I'm trying the below, but the values for @Region and @Queue are showing up as blank in a SSRS report field which are pulling from it:ALTER PROCEDURE [dbo].[udp_WorkQueuesReport] -- Add the parameters for the stored procedure here @FromDt as date, @ThruDt as date, @Region as varchar(max), @Queue as varchar(max)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Declare @RegionTbl as table(RegionName varchar(50)) Declare @QueueTbl as table(QueueName varchar(50)) Insert @RegionTbl select Value from hpexprpt.dbo.split(@Region,',') Insert @QueueTbl select Value from hpexprpt.dbo.split(@Queue,',') select users.last_name + ',' + users.first_name as [User ID] , (Select RegionName from @RegionTbl where RegionName = @Region) as Region , (Select QueueName from @QueueTbl where QueueName = @Queue) as Queue ... from hpexpprod.dbo.work_items join...where...Any pointers would be greatly appreciated...Thanks in advance!

User Cal Licensing Question

Posted: 07 Mar 2013 03:31 AM PST

If I buy SQL User CAL, does that allow the user to be able to access ANY SQL server, or would I have to have a second SQL User CAL for the user to access a 2nd instance?Thanks!

Failure to calculate super-latch promotion threshold

Posted: 18 Sep 2012 12:26 AM PDT

I saw this error message in the error log of one of my SQL Server 2008 R2 servers today (with SP2 installed, v10.50.4000).[b]"Warning: Failure to calculate super-latch promotion threshold."[/b]I've never seen that message, can't find anything online about it, and don't know what the impact is.The only other relevant information I can give is that there was a series of DBCC CHECKDB commands occurring at the time (our nightly database checks).Any additional information would be appreciated.Thanks,John

trace 1204, 1222 on

Posted: 06 Mar 2013 10:52 PM PST

I have these two traces on, TraceFlag Status Global Session1204 1 1 01222 1 1 0But all I am getting in the error log is[298] SQLServer Error: 1205, Transaction (Process ID 200) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (ConnExecuteCachableOp)

Msg 911, Level 16, State 1, Line 1 Database 'databasename' does not exist. Make sure that the name is entered correctly.

Posted: 30 Apr 2012 11:02 PM PDT

Dear All,Would anyone be able to let me know how to resolve the following error message please? Msg 911, Level 16, State 1, Line 1Database 'databasename' does not exist. Make sure that the name is entered correctly.Thank you in advance!

Memory leak on witness instance

Posted: 27 Feb 2013 06:25 AM PST

We have a high availability sql server environment with synchronous mirroring and witness which has been working mostly fine for more than one year.But suddenly, last monday, the witness server became unresponsiveness and the errorlog showed lots of errors about insufficient memory. Looking at the memory information dumped to the errorlog by sql server I found that most of the usable memory was taken up by sqlutilities:>MEMORYCLERK_SQLUTILITIES (node 0)> SinglePage Allocator 986240I do now know whether this is normal or not. This accounts for 94% of available memory because the witness instance has server memory setting configured 1GB. To correct the incidence I restarted mssqlserver service on the witness, but 22 hours after that the same issue happened again. I am pretty clueless about what went wrong so I restarted the witness once more time and then switched all our databases to asynchronous mode without witness. This seemed to work as the witness instance (now idle) ceased to consume memory.The only other thing that came to my attention is that one of the main database servers (the one acting as secondary) has a network interface down. This server has two network interfaces bridged by a "Broadcom Advanced Server Program Driver" to provide a failsafe link. So even with one interface down this server seems to be working normally.Any ideas?

Need this complicated query

Posted: 07 Mar 2013 01:02 AM PST

Hi,I need a query which will have an input parameter @SerNum1, the query will copy all the ParamNum, ParamVal, Lock and Id of that serial number. Now I will send in a new @SerNum2 and this new SerNum2 will be inserted into the same table, column and all that copied data of the first SerNum1 will be pasted next to the new SerNum2.Please look at the attached PNG file for the structure of the table and cloumns.

Event time from deadlock in system_health different from time in error log

Posted: 07 Mar 2013 01:44 AM PST

Can anyone explain why I would have an event time of 2013-03-07 05:39:21.027 for a deadlock in the ring buffer, but when I look at the event in the SQL Error Log (we have flag 1222 on) it shows a time of 2013-03-06 11:39:30 PM. This is quite a time difference and I thought the ring buffer was synchronous. Any thoughts?

sqlcode vs sp

Posted: 07 Mar 2013 12:44 AM PST

hi friends i have small doubt in sql plese tell me how to Determine when to use stored procedures vs. SQL in the code

Fixed column names for Dynamic PIVOT Result

Posted: 06 Mar 2013 08:26 PM PST

Hello everybody,I'm using two dynamic Pivot queries(which are basically the same except for the filter on date) to build a final result.Both dynamic Pivot produce a result with 12 columns where the first one is a nvarchar and the other 11 columns are values.My final result for the column names must be like the following: sta_rep,0,1,2,3....23See attachment for more details.Thank you.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog