Wednesday, August 21, 2013

[SQL Server 2008 issues] How to display two mail Id using database mail to recipient

[SQL Server 2008 issues] How to display two mail Id using database mail to recipient


How to display two mail Id using database mail to recipient

Posted: 20 Aug 2013 05:57 PM PDT

Hi, I had configured the database mail, using my Mail ID.So, when I send the mail to reciepient,it displays my mail ID.Now, My requirement is,I don't want only my mail ID to display. we have a Email Id for our department.But I don't want to configure a database mail account using my department email id.Is there any way ,so that we can display to the receipient's "from" as email-id on behalf of department email id.Please help me!!Thanks in Advance!!

Map SQL server Spid to PID in Task Manager

Posted: 20 Aug 2013 05:26 PM PDT

HelloI'm trying to map the SPID of a certain process to the PID under the processes tab in task manager. I can map the PID to the KPID through the sysprocesses table but this KPID does not appear in task manager under the PID's under the processes tab. How can this be done?

trying to match up two tables

Posted: 20 Aug 2013 04:54 PM PDT

Hi ProfessionalsI have two tablestable 1 which is dbo.newtable[code]software_manufacturer,productname, productversionmicrosoft,excel,11.2[/code]and table2 which is dbo.datalookuptable[code]software_manufacturer,productname, productversion, licensablemicrosoft,excel,11.2,licensable[/code]how do i query these two tables so I can find all the licensable information for dbo.newtable

Add PK CLUSTERED to existing table with dependencies

Posted: 20 Aug 2013 01:56 AM PDT

Hi,My database has a bunch of tables on it that have no indexes at all on them. I want to add indexes to these tables but I am having trouble doing it and am getting messages about dependencies... As an example:I have a table with this column:[code][ACC_Name] [nvarchar](10) NULL[/code](the table also has some other columns but I do not think they are relevant to this...)I want to add a primary key clustered in order to enforce the acc_name to always be unique (and to speed up query times). I have tested and as of right now the acc_name is indeed always unique (# distinct acc_name's = # rows in table).I first tried to create the table the way I wanted it (with the PK clustered), insert * from the existing table into my new table and then delete the existing table and re-name my new one, but it won't let me delete the existing table because there are a bunch of views that point at the existing table.Next I tried to add the primary key clustered to the existing table like this, figuring this would maybe have been a smarter way to do it anyhow:[code]ALTER TABLE [dbo].[ACC_STRUCTURE]alter column [ACC_Name] [nvarchar](10) NOT NULLALTER TABLE [dbo].[ACC_STRUCTURE]ADD CONSTRAINT PK_ACC_Name PRIMARY KEY CLUSTERED(ACC_Name)[/code]...but it won't complete the first statement because of the dependencies mentioned above. Is there a way around this?

Few tricky Questions about SQL Server 2008 that I came across...

Posted: 10 Jan 2012 06:31 AM PST

Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of a transaction log?[/b]A. Transaction logs are truncated when you use SQL Profiler.B. Transaction logs are truncated when you use the following syntax: truncate table AC. Transaction logs are truncated when you backup your database.D. You can not backup transaction log.[b]Q: Which of the following statements about unique Constraints are TRUE?[/b]A. You can have only one unique constraint applied to a table.B. Unique constraints can be used to establish a link between two tables.C. You can only have one column assigned to a unique constraint.D. If a primary key was already assigned to a table, the unique constraint would identify a special column used for indexing, [b]Q: Which of the following is an improved performance feature that uses bitmap filters for selecting minimal rows with queries?[/b]A. Table partitionsB. Star JoinC. Where clauseD. Outer Join[b]Q: Using a join in your select statement instead of a where clause is more efficient because the join pulls less data into your working memory.[/b]A. TrueB. False[b]Q: Full text indexes can be backed up and restored with the rest of the database due to the ...[/b]A. ...new index organization systemB. ...overall size of the index getting smaller.C. ...index becoming a part of the transaction logs.D. ...indexes no longer being stored outside of the database.[b]Q. Which of the following statements, if used for creating a new login id, will result in an error?[/b]A. CREATE LOGIN [HumanResources/JohnSmith] FROM WINDOWSB. CREATE LOGIN AndrewWalters WITH PASSWORD '!^%&7', DEFAULT DATABASE = HRC. CREATE LOGIN John WITH PASSWORD '*&!@*&'D. CREATE LOGIN [HumanResources/Jim] FROM WINDOWS, DEFAULT DATABASE=CRM[b]Q. Which of the following statements can be used to provide sorted result set based in the CustomerName column?[/b]A. SELECT ROW_NUMBER() AS RN, CustomerName from Customers order by CustomerNameB. SELECT ROW_NUMBER() OVER (ORDER BY CustomerName) AS RN, CustomerName FROM CustomersC. SELECT ROW_NUMBER() (ORDER BY CustomerName) AS RN, from CustomersD. SELECT ROW_NUMBER() AS RN, CustomerName from Customers

sql server 2008R2 - Memory is not releasing after big job run

Posted: 20 Aug 2013 12:43 PM PDT

Hello,We have migrated to sql server 2008R2 and I have set up my MAX Memory 32 GB and MIN left a default 0.When I was watching performance while running big batch job, it was done within 30 minutes but memory was showing almost 30 GB in task manager and after it's ran, it was still showing 30 GB memory.I couldn't understand why it's not releasing memory after job is done and still showing 30 GB memory into task manager?Thanks,

Send email in a tabular format using SQL Server database mail depending upon output row count

Posted: 20 Aug 2013 09:06 AM PDT

Hi Experts..Requirement:--If the following query resulted greater than zero rows, send the results in email in a tabular format using SQL Server database mail--if is is equal to zero ,don't send the mail.can someone help me in thisSELECT [DateTime] ,[Text] FROM [MicrosoftLog].[dbo].[Log] where [LogApplicationID] in (select [LogApplicationID] from [MicrosoftLog].[dbo].[LogApplication] where applicationname in ( 'VirtualMan', 'PhysicalMan')) and StartText<> 'count=0' and StartDateTime < GetDate() and StartDateTime > dateadd(minute, -30, GetDate())Any input is appreciatedThanks

SQL Server Jobs not being scheduled after restart

Posted: 20 Aug 2013 04:31 AM PDT

I failed over our Cluster (SQL 2008 R2) and the SQL Agent did not start (AgentXPsDisabled)... I ran this script which brings SQL Agent online, but ALL the jobs "Next Run" is set to "Not scheduled"EXEC sp_configure 'allow updates', 0RECONFIGUREgo-- then:sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Agent XPs', 1;GORECONFIGUREGOEXEC sp_configure 'allow updates', 0RECONFIGUREI tried restarting SQL Agent from the SQL Server Config Manager but to no avail. Thoughts?

SQL Server import export wizard vs bulk insert

Posted: 20 Aug 2013 01:42 PM PDT

Hi Professionals.I have a question regarding importing data from spreadsheets.I manually use SQL Servers Import and Export wizard to import a .xls spreadsheet and it is intelligent enough to match the columns correctly. But when I do this using a bulk insert I run into problems for instancewhen I have a column named softwaremanufacturer and I import data that reads "Adobe systems, Inc" the Import wizards populates the column like so[code]softwaremanufacturerAdobe Systems, Inc[/code]but when I do a bulk insert of my csv I run into the following problems as it comes to the comma and thinks it is a new table column shifting the "Inc" part into the next column along like so[code]computername, softwaremanufacturer,productname, productversion"ACTADMINPC002 g00228" "Adobe Systems Inc" "Acrobat Reader (redistributable)""ACTADMINPC002 g00228" "Adobe Systems Inc" "Air""ACTADMINPC002 g00228" "Adobe Systems Inc" "Collaboration synchronizer""ACTADMINPC002 g00228" "Adobe Systems Inc" "EULA""ACTADMINPC002 g00228" "Adobe Systems Inc" "Flash player"[/code]Is there a way to get around thishope this makes sense and thanks in advance

SQL Join Isuue

Posted: 20 Aug 2013 01:40 PM PDT

I have three tables Table AID NAME1 NameATable BID Coutry1 County A1 Country BTable CID Family1 Family A1 Family B1 Family CI use a outer joinI get the following result set1 County A Family A1 County A Family B1 County A Family C1 County B Family A1 County B Family B1 County B Family CBut I want to get the following1 Country A Family A1 Country B Family B1 NULL Family CWhich query should I use?Thanks

Difference of two consecutive datetime rows

Posted: 20 Aug 2013 02:39 AM PDT

HI,I have the following tableEntryID DateInserted InsertedBy1776285 2013-06-03 07:46:38.340 5921776286 2013-06-03 07:47:47.677 5921776287 2013-06-03 07:48:17.367 5921776288 2013-06-03 07:49:08.750 5921776289 2013-06-03 07:49:08.750 5921776290 2013-06-03 07:49:45.177 5921776291 2013-06-03 07:54:54.290 5921776292 2013-06-03 07:57:11.703 5921776293 2013-06-03 07:57:35.930 831776294 2013-06-03 07:58:33.843 831776295 2013-06-03 07:58:36.293 5921776296 2013-06-03 07:58:54.853 831776297 2013-06-03 07:59:06.523 5921776298 2013-06-03 07:59:27.633 831776299 2013-06-03 07:59:46.383 831776300 2013-06-03 08:00:02.020 5921776301 2013-06-03 08:00:04.390 831776302 2013-06-03 08:00:29.590 5921776303 2013-06-03 08:01:00.240 5921776304 2013-06-03 08:01:12.943 831776305 2013-06-03 08:01:31.150 831776306 2013-06-03 08:01:31.990 5921776307 2013-06-03 08:01:50.373 831776308 2013-06-03 08:02:10.840 831776309 2013-06-03 08:02:30.243 831776310 2013-06-03 08:03:03.737 831776311 2013-06-03 08:04:08.750 5921776312 2013-06-03 08:04:14.723 831776313 2013-06-03 08:04:27.033 5921776314 2013-06-03 08:05:26.617 831776315 2013-06-03 08:05:26.617 831776316 2013-06-03 08:05:38.527 83Now I want to get the difference between 2 consecutive dateinserted col in hours or mins into another column say Time diff grouped by on the inserted by col since i want total time taken by each person inserted.like as belowEntryID DateInserted InsertedBy TimeDiff1776285 2013-06-03 07:46:38.340 592 diff(2013-06-03 07:47:47.677-2013-06-03 07:46:38.340)-- should be the output of this col either in mins or hours.Also if the difference is > than 3 mins I want only 3 mins as the output.1776286 2013-06-03 07:47:47.677 5921776287 2013-06-03 07:48:17.367 5921776288 2013-06-03 07:49:08.750 5921776289 2013-06-03 07:49:08.750 5921776290 2013-06-03 07:49:45.177 5921776291 2013-06-03 07:54:54.290 5921776292 2013-06-03 07:57:11.703 5921776293 2013-06-03 07:57:35.930 831776294 2013-06-03 07:58:33.843 831776295 2013-06-03 07:58:36.293 5921776296 2013-06-03 07:58:54.853 831776297 2013-06-03 07:59:06.523 5921776298 2013-06-03 07:59:27.633 831776299 2013-06-03 07:59:46.383 831776300 2013-06-03 08:00:02.020 5921776301 2013-06-03 08:00:04.390 831776302 2013-06-03 08:00:29.590 5921776303 2013-06-03 08:01:00.240 5921776304 2013-06-03 08:01:12.943 831776305 2013-06-03 08:01:31.150 831776306 2013-06-03 08:01:31.990 5921776307 2013-06-03 08:01:50.373 831776308 2013-06-03 08:02:10.840 831776309 2013-06-03 08:02:30.243 831776310 2013-06-03 08:03:03.737 831776311 2013-06-03 08:04:08.750 5921776312 2013-06-03 08:04:14.723 831776313 2013-06-03 08:04:27.033 5921776314 2013-06-03 08:05:26.617 831776315 2013-06-03 08:05:26.617 831776316 2013-06-03 08:05:38.527 83Thanks in advance.

Index Rebuild

Posted: 20 Aug 2013 07:25 AM PDT

I have to kill Index Rebuild process due to space issues, does this cause any corruption in database? Thanks

Copy Default trace from a server to your local machine

Posted: 20 Aug 2013 01:43 AM PDT

If you wish to replay events captured by the default trace can you copy the files to your local machine from the server and run them locally in profiler ?Will the trace show what stored procs were running and who ran them ?

Setting up server to add a node to a cluster.

Posted: 20 Aug 2013 12:48 AM PDT

Ok a bit of a different situation that I am used to so I need a sanity check.Right now I have a one node cluster, it was a two node cluster but we had a hardware failure so we evicted the bad node from the cluster and are rebuilding windows on the bad node after doing a hardware fix.So, that's the setup here is the question.I am guessing we add the node back into windows clustering and make it aware of the storage then add the node to sql clustering via sql install. Is that basically it? Can we add the node back to windows clustering without taking down the active node (there is some question here about adding the new node and making it aware of the storage will cause an issue with the active node).Thanks, I am used to doing this when I have two ore more existing nodes already in the windows cluster.

Error: 17189, Severity: 16, State: 1.

Posted: 23 May 2012 07:29 AM PDT

We're running 2008 SP2, 8 core, 64 GB, Enterprise Edition with about 200-500 connections. have min/max Memory setting 0/60000 and lock pages in membery.Got this error once (haven't seen before):"Error: 17189, Severity: 16, State: 1.MessageSQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xxx.xxx.xx.xx]"Followed by a number of these errors:"Error: 18056, Severity: 20, State: 29.MessageThe client was unable to reuse a session with SPID 210, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message."Followed by a bunch of these:"Error: 10982, Severity: 16, State: 1.Failed to run resource governor classifier user-defined function. See previous errors in SQL Server error log from session ID 439 for details. Classifier elapsed time: 0 ms. "The instance did not restart but caused a number of application timeouts. Googling content of SQLDUMPER_ERRORLOG.log and ERRORLOG at the time of issue did not produce any meaningful results. What could be the issue?

No comments:

Post a Comment

Search This Blog