Thursday, February 28, 2013

[SQL Server 2008 issues] Detect 32 or 64 bit SQL from registry.

[SQL Server 2008 issues] Detect 32 or 64 bit SQL from registry.

Detect 32 or 64 bit SQL from registry.

Posted: 27 Feb 2013 05:51 AM PST

I am trying to write up a script to detect whether the installed SQL Server 2008 instance is 32 or 64 bit. Here's the catch - SQL Server would be disabled when I run this scirpt. So I cant use xp_msver or @@version to get the info.Is there any way to get the same info from the registry?Thanks,Akshay.

Question and answer CPSM exam

Posted: 27 Feb 2013 06:06 PM PST

When you have no idea about the CPSM Exam while studying, we will be your study guide. exam1pass products can help you master the Institute for Supply Management(ISM) Other Certification knowledge by clearly showing you the key points required in the exam. With the help of our CCENT CPSM training materials you will learn better without attending other expensive courses.Once there is some changes on CPSM Exam (Interconnecting Institute for Supply Management(ISM) Other Certification Networking Devices Part 1) exam, we will update the study materials timely to make them be consistent with the current exam. We devote to giving our customers the best and latest Institute for Supply Management(ISM) Other Certification CPSM dumps. Besides, the product you buy will be updated in time within 120 days for free.http://www.exam1pass.com/CPSM-exam.html

Publish SSRS 2008 in LAN

Posted: 27 Feb 2013 03:51 PM PST

How to publish SSRS for internal alone.I have a project which needs to be circulated only to my team members using my hostname.What are the steps to deploy them.now i am able to do with http://localhost/Reportshttp://myhost-name/Reportswhere as if i see in another machine [b]http://myhost-name/Reports[/b] i am unable to get the details.Thanks

Optimizing query

Posted: 27 Feb 2013 12:28 PM PST

Hi,Can someone help me in optimizing below query, is there any other way where I can improve this querySELECT A.Col1, COUNT(B.POST_DT) AS OD_NO FROM (SELECT Col1, col3, Col2, SUM(CASE WHEN Col2> 0 THEN GROSS_AMOUNT ELSE DUEAMNT END) AS DUEAMNT, SUM(CRAMNT) AS CRAMNT FROM myTbl WHERE POST_DT< someDate AND Col2=0 GROUP BY Col1, col3, Col2 HAVING SUM(DUEAMNT) > SUM(CRAMNT) ORDER BY Col1) A, myTbl B WHERE A.col3=B.col3 AND (CASE WHEN B.Col2> 0 THEN B.GROSS_AMOUNT ELSE B.DUEAMNT END)>0 GROUP BY A.Col1TIA

MCTS 70-448 expiring in July 2013

Posted: 27 Feb 2013 05:39 PM PST

hi,I want to gain a certification of entry level. I'm working as a BI developer for around 5 years and found 70-448 BI development and maintainance SQL2008 to be the best one to start.However, this certification is expiring in July 2013, which makes me think whether it is a wise decision to go for a cerftification which is valid only for few months.I may be missing on few other important aspects and hence need some enlightment.Please help

Extended Properties for adding descriptions

Posted: 27 Feb 2013 04:15 PM PST

Hi,I want to use extended properties for adding description to our database. I tried it and I am able to do that, my problem here is; The database which we have is very huge in size. Can we use extended properties on these kind of DB's. If no, what will the problems of doing so? If Yes, are there any other advantages doing so?Please give as much as information on extended properties related to performance affect on DB.Thanks in advance!!!

error while installing sql server 2008 r2 express in a windows 8 pc

Posted: 18 Feb 2013 02:56 PM PST

I was trying to install sql server 2008 R2 in windows 8 pc.First i installed sql server 2008 r2 by using UI mode in windows 8 PC without enabling .net 3.5. While installing i got an warning like shown below.[img]http://social.msdn.microsoft.com/Forums/getfile/236708[/img]I clicked on "Run the program without getting help" and then it installed properly.But when i do the install using configuration file in silent mode i got same warning and i clicked on "Run the program without getting help" and after that at the middle of installation i got the error likeMicrosoft (R) SQL Server 2008 R2 Setup 10.50.1600.01Copyright (c) Microsoft Corporation. All rights reserved.Unhandled Exception: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInfrastructureException: The file or directory is corrupted and unreadable. ---> System.IO.IOException: The file or directory is corrupted and unreadable. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access,FileShare share, Int32 bufferSize, FileOptions options, String msgPath, BooleanbFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost) at System.IO.StreamWriter..ctor(String path) at Microsoft.SqlServer.Chainer.Infrastructure.LogProviderFile..ctor(String targetFile) --- End of inner exception stack trace --- at Microsoft.SqlServer.Chainer.Infrastructure.LogProviderFile..ctor(String targetFile) at Microsoft.SqlServer.Chainer.Infrastructure.StatusLogs.WriteSummaryLog(Int32 resultCode, Boolean finalResult, Exception exception) at Microsoft.SqlServer.Chainer.Setup.Setup.WriteStatusLogs(Int32 resultCode,Exception e) at Microsoft.SqlServer.Chainer.Setup.Setup.Start() at Microsoft.SqlServer.Chainer.Setup.Setup.Main()So my doubt is without enabling .net 3.5 also we can install sql server 2008 r2.But why i got the error while installing using configuration file?

database security

Posted: 20 Feb 2013 06:28 PM PST

I am developing an application which uses sql server express.so there are so many database which need to have security.So any customer who installs our application will get those database in his PC.So he can see the data present the database. So how can i make it safer so that data present in the database will not be seen to anyone.

ISO 8601 - The correct way to represent a date

Posted: 27 Feb 2013 03:32 PM PST

[url]http://xkcd.com/1179/[/url][img]http://imgs.xkcd.com/comics/iso_8601.png[/img] 

How to use uniquename function in my mdx script?

Posted: 27 Feb 2013 03:32 PM PST

Hi All,I really need your help.For a SSRS report, I have this mdx script:select {[Geographie].[Commune].[AHUY], [Geographie].[Commune].[BRETENIERE]} on columns,{[Activite].[Branche].&[B], [Activite].[Branche].&[C]} on rowsfrom [ACSEL2]where ([Measures].[CATTC], [Perimetre].[Perimetre].&[2], [Temps].[Annee].&[2006])Please, I need to have the uniquename for the members that I have in columns ({[Geographie].[Commune].[AHUY], [Geographie].[Commune].[BRETENIERE]})Please Can U help me to write this mdx script ?Lidou

SET ROWCOUNT

Posted: 27 Feb 2013 10:06 AM PST

So I set the rowcount to 1 in a query session.....but now I can't get more than 1 row to return. How can I turn it back off?I ran SET ROWCOUNT 1and to reverse I triedSET ROWCOUNTSET ROWCOUNT OFFSET ROWCOUNT = OFFNone of those commands are valid

Is my Create Index Online finished?

Posted: 27 Feb 2013 12:35 PM PST

If I create an INDEX like the following[code]CREATE NONCLUSTERED INDEX [IX_table_col1_col2] ON [dbo].[table] ( [col1] ASC, [col2] ASC)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON) ON [PRIMARY][/code]How do I check up on it to see when it's 100% completed

how to get Mac Address in SSRS

Posted: 27 Feb 2013 02:45 PM PST

Hi,I am having a requirement of taking the MAC Address using SSRS is it is possible to take?My Requirement: To take and keep the mac address of each user and make a note that he does not make double entry of comments ,there is no log restriction like using userid and password , just to access the page and user will give some comments.Other than MAC Address can we do this?If so how to find the uniqueness on the users.Notes:A user should not have double entry.No user is provided Userid and password.Thanks!

effective way to break a string into rows basing on lenght of string

Posted: 27 Feb 2013 01:47 AM PST

/*Current table that has a Primary key and a text column, need to break this text into rows if the len > 15*/select '1' as PK,'Physical inspection Check line cord & plug Perform calibration procedure ESI #102 SCOPE' text unionselect '2','test1' /*This is what I want to achive*//*Break the text when the length of text > 15 into seprate rows */select '1' as PK,'1' as SequenceOrder,'Physical inspec' as text unionselect '1','2','tion Check lin' unionselect '1','3','e cord & plug ' unionselect '1','4','Perform calibra' unionselect '1','5','tion procedure ' unionselect '1','6',' ESI #102 SCOP' unionselect '1','7','E' unionselect '2','1','test1' the table in the db is huge with a lot of records more than 200000. [b]Is there a way to do this keeping in mind an effective run time[/b]

Incorrect data type in Float column

Posted: 21 Feb 2013 01:13 AM PST

Somehow, I have a table with non-numbers in my floating column. I can find them... select isnumeric(Column) as IS_Numeric , convert(nvarchar (20),Column) as IS_Value from TABLE order by IS_Numericwhat i find is that there are values of " -1.#IND " in my column when the column is defined as FLOAT.When I try to run a update where IS_Numeric = 0, i get the message "Possible index corruption detected. Run DBCC CHECKDB." update TABLE set COLUMN= 0 where (select isnumeric(Column) as IS_Numeric from TABLE )= '0'Would appreciate the advice! Cheers, Mark

Select top 2 records

Posted: 27 Feb 2013 06:40 AM PST

One table as below.How to code to select top 2 records?ID SEQUECE NOTE1001 1 AAA1001 2 BBB1001 3 CCC1001 4 DDD1001 5 EEE2002 1 GGG2002 2 HHH2002 3 KKK2002 4 MMM2002 5 NNN2002 6 PPP2002 7 QQQ2002 8 RRRI need to select top 2 records:ID SEQUECE NOTE1001 4 DDD1001 5 EEE2002 7 QQQ2002 8 RRR

Is SQL Server 2 tier architecture or 3 tier architecture

Posted: 25 Feb 2013 05:15 PM PST

Hi Team,Can anybody please let me know Is SQL Server 2 tier architecture or 3 tier architecture ?

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?

T-SQL Logic Help

Posted: 27 Feb 2013 04:57 AM PST

I am having, for some reason, a hard time figuring out how to tackle a problem that I have. We have a field in our CRM that has a persons Current Position and Desired Position. The format for the data is "DIR, ADM; DIR, VP, MGR". The semicolon is what determines the split. I have a list of what these values should be "AA", "CEO", "DIR", etc....They are 2-4 characters in length. The database field is nvarchar(50). I need to pull all records that don't have one of the 34 valid values. So for example, if a record has the following "Director" in it. The query should return it. I should be able to do this, but am blanking. Help?!? TIA.Tim

(Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'. - sql 2005 - &gt; 2008 R2

Posted: 29 Aug 2011 09:12 AM PDT

I have a package on 2008r2 and job on 2005 pointing to the package on 2008r2. When i run the job i get the following message 'Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E14 (Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'.). Source"..Does anyone know how to fix this?

Websphere to MSSQL via JDBC Good idea?

Posted: 27 Feb 2013 01:43 AM PST

Hello,My employer is considering an architecture with Websphere app servers connecting to SQL Server via JDBC. I am asked to give some advice about that, specifically around performance and scalability. I find a lot of how-to information but not much analysis of how it works.Can anyone comment with experience about this or maybe point me to white papers?This will be a high-volume enterprise app with a tablet UI.Thanks much!

Index Consolidation

Posted: 26 Feb 2013 11:51 PM PST

The scenario.Missing Index 1Column A,BMissing Index 2Column A,CMissing Index 3Column A,D Include ECan these be consolidate asColumn A,B,C,D include EIndexes ordered 1-3 in terms of Impact value.

adding an extra tempdb file sql2008

Posted: 27 Feb 2013 02:00 AM PST

We have a sever with 2 physical processors and 8 logical ones. We are seeing quite a lot of blocking as the tempdb seems to be getting used quite heavily. From running sp_whoisactive i can see in the 'wait info' column that there are 'PAGELATCH_UP:Tempdb:1(GAM)' as the main reason for the waits whenever there is any blocking going on.As we only have one Tempdb.mdf file on the D drive (raid 5), I am thinking of adding a secondary Tempdb.ndf file on the E drive (raid 5).My main question is regarding the sizing of this new file. When i view the database properties and view the files i can see the intial size was 8mb (which is the defualt as far as i understand) and it is set to grow by 10% unrestricted. When i browse to the actual Tempdb file it is approx 1.5GB.Do i set the new secondary .ndf file as initial size 8MB or 1.5GB as i understand the files have to be sized the same.ThanksStevenps I am also aware of the 1/4 - 1/2 number of tempdb files per logical processor which in my case is 8 logical processors - so 2 files should be ok for starters ! (maybe i'll go to 4 if it doesnt fix the contention issues)

DB Mail - Controling What apears in the FROM setcion of an email

Posted: 27 Feb 2013 12:52 AM PST

We recently upgraded from SQL 2005 to SQL 200R2. I used DB Mail on the SQL 2005 Box and am doing the same on the 2008R2 box. I have created a profile and account on DB Mail on the 2008R2 server that is identical to the profile & account on the 2005 server with the exception that anywhere I had 'SS2005' on the acount or profile on my 2005 box I now have set to 2008R2 on the 2008R2 box. I am using the exact same (unchanged) email address on each and am using teh same mail server IP and port. I have taken screenshots and checked and double checked and I can find nothing different between the 2 and yet an email from each comes into OUtlook with very different FROM values. Using the exact same Call msdb.dbo.sp_send_dbmail on each box (with the diference being the subject line )...On my 2005 box the FROM is shown as SQL04\2005.DBA and the same call to this SP on the 2008R2 server produces an email with my name (format is FirstName Lastname) in the FROM field.Does anyone knwo if something changed with DB Mail between 2005 & 2008R2 that coudl cause this? Its important because this tag line in the FROM field lets me easily detremine which serevrt an email is coming from as we have a number of SQL Servers that use the sp_send_dbmail SP to send info throughout the day.night. Thoughts?If a screenshot is necessary I can do it but my IT guy tells me I have to use fake values for the vairous fields for security reasons and thats why I haven;t included it in the post. If its necessary I can do it though.

SQL HELP... derived/calculated column issue

Posted: 27 Feb 2013 12:02 AM PST

I need to be able to do the followingYear 2012 2013Month 11 12 1 2 3 Beginning 0 2 4 18 40Opened 5 11 16 65 98 Completed 3 9 2 43 76Ending 2 4 18 40 62 Ok, Beginning = ending of previous monthOpened = tablefieldCompleted = tablefieldEnding = beginning + opened - completedI am struggling with getting the beginning formula/calculation correct, any help would be GREATLY appreciated.

sp_add_jobstep TSQL Command exceeds 8000 characters

Posted: 26 Feb 2013 11:32 PM PST

Hello,I'm hoping this might help someone in the future.I'm writing a script to create an sql server agent job on many different servers.There is a TSQL step which I'm paspassingscript to, now I need to pass the name of a target server to it, so I have a couple of variables.DECLARE @commandtext varchar(max),@targetserver varchar(100)I populate the @targetserver then construct the @commandtext, something like 'Select * from ['+@targetserver+'].dbo.Contacts' although oviousobviouslylonger.When I pass it into sp_add_jobstep exec sp_add_jobstep @job_ID=@_ID,@step_id=3,@step_name='Populate',@subsystem = 'TSQL',@database_name=@DatabaseName,@on_success_action = 3,@on_fail_action =2, @command = @commandtextIt truncates it to 8000 characters. If I check the Len(@commandtext) before it was showing 23109.I've resolved this by defining @targetserver as varchar(max) too.DECLARE @commandtext varchar(max),@targetserver varchar(max)after all the help I've had from this forum I hope this helps someone elseGiles
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog