Thursday, September 19, 2013

[SQL Server 2008 issues] trying to send database mail of sql server using gmail account

[SQL Server 2008 issues] trying to send database mail of sql server using gmail account

trying to send database mail of sql server using gmail account

Posted: 18 Sep 2013 06:17 PM PDT

Hi i am trying to send database mail and using gmail as an smtp server.i have used my emailid,and password for credentials.port use is either 587 or 456.but i am getting following error.i got all this from netThe mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-09-19T11:50:46). Exception Message: Could not connect to mail server. (No such host is known). ) can anybody tell what could be reason , why it is giving error "No such host is known"

Adding time intervals in minutes and display as csv

Posted: 17 Sep 2013 10:57 PM PDT

i have a table with three columns ascol1 col2 col311:30 13:30 1500:10 01:40 5the out put should be as follows11.30,11.45,12.00,12.15,12.30,12.45,13.00,13.15,13.3000.10,00.15,00.20,00.25.....01.40earlier help is highly appreciated

String Split

Posted: 17 Sep 2013 09:29 PM PDT

I want to write query which procures me result like below.General rule is if any word starts from number then it should split as word.Input-----Ørstedsvej 7BVolvo 25D104ND NokiaResult------Ørstedsvej 7 BVolvo 25 D104 ND NokiaOr the simplest way to do.

csv to rows

Posted: 18 Sep 2013 06:07 AM PDT

I have a table as followscol1 col2 col3 col4 col5A 11:30 13:30 15 11.30,11.45,12.00,12.15,12.30,12.45,13.00,13.15,13.30i need the following outputA 11.30A 11.45A 12.00A 12.15A 12.30A 12.45A 13.00A 13.15A 13.30earlier help is highly appreciated

Export data from corrupted table

Posted: 18 Sep 2013 06:58 AM PDT

I have a table that has inconsistency errors.There are around 13 million rows of data in it.1. If I truncate this table and run a dbcc, would the errors disappear?2. Is there a way I can copy the latest 1 million or half a million rows of data through export data wizard or through select * into?

dynamic filename in SSIS flat file source

Posted: 18 Sep 2013 07:14 AM PDT

How to specify for dynamic filename in SSIS flat file sourcefor file 20110818_abc_def.csvas the name abc_def does not changes only the date part changes. so how to make it dynamicaly and also the file source location does not change.Any clue on it.Shaun.

SQL profiler

Posted: 18 Sep 2013 01:52 AM PDT

Hello all, We are running profiler for one of our systems and the profiler shows that one specific query is executed 8000 times could this be the sql server or the packet are getting dropped by the network and it is trying again ? see belowTextData ApplicationName NTUserName LoginName CPU Reads Writes Duration ClientProcessID SPID StartTime EndTime BinaryDataexec sp_executesql selectXXXXXX .Net SqlClient Data Provider NULL DBName 0 2 0 132 2652 81 2013-09-18 10:43:57.973 2013-09-18 10:43:57.973

Create link server for Microsoft Access2000

Posted: 28 Aug 2013 06:00 AM PDT

Hello,Can some one help to create link server for Microsoft Access2000, here is what I did but it is not working:EXEC sp_addlinkedserver @server = N'fx', @provider = N'Microsoft.ACE.OLEDB.12.0', @srvproduct = N'OLE DB Provider for ACE', @datasrc = N'\\us-balt-san-1\inhouse$\shipping\FX\DATA\fx.mdb';GO

Backing up a Mirror

Posted: 18 Sep 2013 04:56 AM PDT

If you are mirroring a database, should you still run backups on the Mirror?

html file to sql server table

Posted: 18 Sep 2013 02:26 AM PDT

How can I get data out of this HTML table into a new SQL server table.This is a sample HTML code of a table, I have some HTML files with 400000 rows with 48 columns.I have never worked on a HTML file before.<html><head><META content="text/html;charset=Cp1252"></head><body><table border><tr><th>RESPrimaryId</th><th>ResultCode</th><th>Description</th><th>Abbreviation</th><th>Notes</th><th>LastUpdateTime</th><th>LastUpdateUser</th><th>CreatedTime</th><th>CreatedUser</th></tr><tr><td>1000000001</td><td>1</td><td>Cleaned / Lubricated</td><td>1</td><td></td><td>2013-09-09 19:00:00.139</td><td>DBA</td><td>2006-11-03 15:03:41.593</td><td>Installer</td></tr><tr><td>1000000002</td><td>2</td><td>Performance Test</td><td>2</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.640</td><td>Installer</td></tr><tr><td>1000000003</td><td>3</td><td>Adjusted / Calibrated</td><td>3</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.656</td><td>Installer</td></tr><tr><td>1000000004</td><td>4</td><td>Removed from Service</td><td>4</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.656</td><td>Installer</td></tr><tr><td>1000000005</td><td>5</td><td>Repair/Tested/Return Svc</td><td>5</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000006</td><td>6</td><td>Performed Inservice</td><td>6</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000007</td><td>7</td><td>Completed as Requested</td><td>7</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000008</td><td>8</td><td>Serviced by Vendor</td><td>8</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.671</td><td>Installer</td></tr><tr><td>1000000009</td><td>9</td><td>Transferred Job</td><td>9</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.687</td><td>Installer</td></tr><tr><td>1000000010</td><td>10</td><td>Not Found/Unavailable</td><td>10</td><td></td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2006-11-03 15:03:41.687</td><td>Installer</td></tr><tr><td>1000000072</td><td>102</td><td>REPAIR FROM PM</td><td>RFP</td><td>REPAIR THAT IS COMPLETED FROM A FAULT FOUND DURING PM</td><td>2013-09-09 19:00:00.154</td><td>DBA</td><td>2007-03-16 09:35:48.588</td><td>DBA</td></tr></table></body></html>

Anyone used SQL Trace (server side) as a permanent auditing solution?

Posted: 18 Sep 2013 03:21 AM PDT

I have some very strict auditing requirements on my current project, and SQL Server Audit has some serious flaws. CDC won't work in entirety, either. I'm considering setting up server side traces, as the data that SQL Trace is able to gather would be sufficient. I know that it has a much smaller performance impact than Profiler, but I'm still unsure about how it would hold up over the long term. Has anyone tried to use this as a permanent auditing solution? If so, what were your results? Pros/cons?

Check File exists and generate File

Posted: 18 Sep 2013 02:09 AM PDT

i need to create package,if file exists then copy the file to different location but with different name such as using today date.Shaun

Error message when changing location of share drive in SSIS package

Posted: 18 Sep 2013 03:56 AM PDT

Hi all,At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS package delivering the data in a flat file.This week i had to change the location of the sharedrive in the package, after doing the changes the package is not working and the following message appears (see below), does anyone knows how to solve this one?Executed as user: (Database Name)\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4279.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:46:26 AM Error: 2013-09-18 09:46:27.42 Code: 0xC020200E Source: Data Flow Task Flat File Destination [111] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.42 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (111) failed the pre-execute phase and returned error code 0xC020200E. End Error Error: 2013-09-18 09:46:27.42 Code: 0xC020200E Source: Data Flow Task Flat File Destination [16] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.42 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (16) failed the pre-execute phase and returned error code 0xC020200E. End Error Error: 2013-09-18 09:46:27.44 Code: 0xC020200E Source: Data Flow Task Flat File Destination [166] Description: Cannot open the datafile "ShareLocation\Filename.txt". End Error Error: 2013-09-18 09:46:27.44 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "Flat File Destination" (166) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:46:26 AM Finished: 9:46:27 AM Elapsed: 0.672 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,009/18/2013 09:00:00,Aurum - Backup to share,Error,0,(Database Name),Aurum - Backup to share,(Job outcome),,The job failed. The Job was invoked by Schedule 14 (daily). The last step to run was step 1 (*****).,00:00:00,0,0,,,,009/18/2013 09:00:00,Aurum - Backup to share,Error,1,(Database Name),Aurum - Backup to share,Aurum Share Backup,,Best regards,Daniel

Stored Procedure Execution Error Message

Posted: 17 Sep 2013 11:04 PM PDT

We have this stored procedure create that runs when i add any of the parameters to the section of the code to prompt me to enter values at the time of execution but ideally i want to run this for all sets of data and when i do that, i receive an error message of "Command text was not set for the command object" If under the Add parameters section, I add the following list of parameters @PCN_Key AS INT, @CostSetKey as INT, @Part_Type_MP AS VARCHAR(100) and then when I go to execute the sproc with the values of 156371 for the PCN_Key, 1609 for the CostSetKey and Bottle for the Part_Type_MP, the table gets populated. Since there are more then 1 PCN_Key and Part_Type_MP, i would like the SPROC to pull in all data. So when I remove the parameters section from the code and execute it, no parameters appear on the Execute Procedure box and when I click OK, the message then appears. USE [ConstarOLAP_PROPHIX_FactDb] GO /****** Object: StoredProcedure [dbo].[ProphixStdCostExport] Script Date: 09/16/2013 16:38:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: <Peter Pearce, Baker Tilly> -- Create date: <September 9, 2013> -- Description: <Executes Remote Plex Sproc for Exporting Standard Cost> -- ALTER PROCEDURE [dbo].[ProphixStdCostExport] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @PCN_Key AS INT = '', @CostSetKey AS INT = '1609', @Part_Type_MP AS VARCHAR(100), @Part_No AS VARCHAR(100) = '', @Cost_Date AS VARCHAR(20), @Building_No_MP AS VARCHAR(1000)='', @qq AS CHAR(1) = '''', @sproc AS VARCHAR(1000) SET @Cost_Date = CONVERT(VARCHAR,DATEADD(d,0,DATEDIFF(d,0,GETDATE() -1)), 120) SET @sproc = 'sproc166848_58075_1335485(' @qq @Part_No @qq ',' CAST(@PCN_KEY AS VARCHAR(20)) ',' @qq CONVERT(VARCHAR, @Cost_Date,120) @qq ',' @qq @Part_Type_MP @qq ',' @qq @Building_No_MP @qq ',' CAST(@CostSetKey AS VARCHAR(20)) ')' DELETE FROM [tblStdCost] INSERT INTO [tblStdCost] ( Part, Plant, Building, Date, Account, Currency, Version, Measure ) EXEC(@sproc) AT [PLEXREPORTSERVER] END

Convert date to a format of dd/mm/yyyy HH:mm:ss

Posted: 18 Sep 2013 01:56 AM PDT

Hi,I have the following sql[code="other"]Select '2013-09-17 21:01:00.000'[/code]I want this the date to show as:17/09/2013 21:01:00I have tried the following sql but does not change to do the format I require[code="other"]Select CONVERT(varchar,'2013-09-17 21:01:00.000',103)[/code]Thanks

Multiple splits

Posted: 17 Sep 2013 10:09 PM PDT

I have string of format with two delimiters '|' pipe and ',' comma '1,100,12345|2,345,433|3,23423,123|4,33,55'And have to insert into table columns as belowseq invoice amount1 100 123452 345 4333 23423 1234 33 55Please helpThanks & Regards,Prathibha

How to add date stamp and set expression or variable on ftp task?

Posted: 18 Sep 2013 01:56 AM PDT

Hi All,I have a file on local server say C:\fOLDER\Data.txtNow I need to move this file onto a FTP Server with date stamp attached to it and archive it on a daily basis.Example--/root/history/Data_09_18_2013.txt---remote Ftp serverThe next day again new file should be created in ftp server history directory as /root/history/Data_09_19_2013.txtI need to do this using SSIS nad run this package everyday as a job.Kindly help me with this issue.Thanks all in advance!

Unable to remove old transaction log backups.

Posted: 17 Sep 2013 09:32 PM PDT

Hi All,We have a DR server configured with logshipping. The transaction logs are taken from Production and copied to the DR server and restore job happens from there for logshipping. Our Production DB'ss full and txn logs are saved to tape drives on a timely basis, hence i need not worry about the txn logs copied to DR server. Now these txn logs on DR are occupying too much space as there are txn log files from past 3-4months. I want to all files older than 1 week for which i tried maintenance job but with no result.Could somebody help me out with this issue.Thanks,Hari Mindi

Help in Where Clause

Posted: 18 Sep 2013 12:13 AM PDT

Hi All, Im stuck and need some help, I have 3 out of 4 variables working in my Where clause and need help getting the 4th one to work. My variables are @startdate, @enddate, @class, @STdrp. The date range works fine and when I filter for @class that works as well, but the @STdrp when I choose something there it returns those results, but everything else as well. How can I just get back what was passed as @STdrp?Im sure the way I wrote this is pretty messy, my apologies Im not as good on the back end as I am the front end. :)[code="sql"] WHERE ( ct.AlertedDate between @startdate and @enddate OR ct.[Dispo] = @STdrp OR ct.[AcceptStatus] = @STdrp --OR -- @STdrp = '%' ) AND isnull(QC.Class,'BB') like @class AND @startdate <= ct.AlertedDate ORDER BY ct.[AlertedDate][/code]Thanks for your advice.

Read-Commited Snaphsot isolation causing deadlocks?

Posted: 18 Sep 2013 12:36 AM PDT

Our DBA has some type of process setup to monitor deadlocks and sends out an emails when a deadlocks occurs. We have been testing read committed snapshot isolation on our application for a couple months because the performance problems we have been having are definitely related to locking. Everything went very smoothly on our dev & test servers but when we went to apply it to our production database this morning we got about 1300 deadlock graph emails very quickly. Here is the contents of one of the emails:<EVENT_INSTANCE><EventType>DEADLOCK_GRAPH</EventType><PostTime>2013-09-18T06:59:19.993</PostTime><SPID>19</SPID><TextData><deadlock-list><deadlock victim="process6a1288"><process-list><process id="process75e748" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="200" schedulerid="4" kpid="1984" status="background" spid="20" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> <process id="processae94c8" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="301" schedulerid="5" kpid="7244" status="background" spid="34" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> <process id="processb1db88" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="99" schedulerid="7" kpid="6428" status="background" spid="35" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> <process id="process6a1288" taskpriority="0" logused="10000" waitresource="DATABASE: 8 " waittime="402" schedulerid="1" kpid="912" status="background" spid="22" sbid="0" ecid="0" priority="0" trancount="0"> <executionStack/> <inputbuf/> </process> </process-list> <resource-list> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="process6a1288" mode="S"/> </owner-list> <waiter-list> <waiter id="process75e748" mode="X" requestType="wait"/> </waiter-list> </databaselock> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="process6a1288" mode="S"/> </owner-list> <waiter-list> <waiter id="processae94c8" mode="X" requestType="wait"/> </waiter-list> </databaselock> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="process6a1288" mode="S"/> </owner-list> <waiter-list> <waiter id="processb1db88" mode="X" requestType="wait"/> </waiter-list> </databaselock> <databaselock subresource="FULL" dbid="8" dbname="unknown" id="lock12137cf80" mode="U"> <owner-list> <owner id="processae94c8" mode="S"/> </owner-list> <waiter-list> <waiter id="process6a1288" mode="X" requestType="wait"/> </waiter-list> </databaselock> </resource-list> </deadlock> </deadlock-list> </TextData> <TransactionID/> <LoginName>sa</LoginName> <StartTime>2013-09-18T06:59:19.993</StartTime><ServerName>PROD-SQL01</ServerName><LoginSid>AQ==</LoginSid><EventSequence>462976693</EventSequence><IsSystem>1</IsSystem><SessionLoginName/></EVENT_INSTANCE>Anyone have any idea what could be going on here? We applied this early in the morning when we have very little database traffic. FYI: We do use idera software for auditing though.

How to port no on Sql server

Posted: 17 Sep 2013 07:59 PM PDT

Can any one know where to find port id in sql server.I am new to sql server dba ...please help.Thanks & Regardsshiva
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog