Thursday, May 23, 2013

[SQL Server 2008 issues] SQL Trigger on condition

[SQL Server 2008 issues] SQL Trigger on condition

SQL Trigger on condition

Posted: 22 May 2013 07:20 PM PDT

Hi Team,i need a trigger based on condition.CREATE TRIGGER [dbo].[Trigger_Base_line] on [dbo].[Base_Tab]AFTER INSERT,DELETE,UPDATEhere i want a condition logic in trigger,if a record is inserted into base_tabthen Execute Stored_procedure1if a record is deleted from base_tabthen Execute Stored_procedure2if a record is updated in base_tabthen Execute Stored_procedure3please help me...

snapshot agent is not running

Posted: 19 May 2013 04:41 AM PDT

Hi,Friends I am working on sql server 2008 r2 and I want to replicate data by using snapshot replication, when i create subscription and publication after being successful, my data didnot distributed to the table. when i check for problem, i found that my snapshot agent is not running on the subscription side, what should i do?

BufferTempStoragePath

Posted: 22 May 2013 06:04 PM PDT

Hi I have a semicolon separated list for this property on my Data Flow Tasks. For example 'F:\Folder; G:\Folder'What could cause one of the folders in my list not to be used?Thanks

Database Tuning Adviser

Posted: 22 May 2013 05:28 PM PDT

Good Day. On our SQL Server 2008R2 we identified an inefficient query . We ran it through the Database Tuning Adviser and advised us to add 2 indexes . The same columns were identified , but the only difference was the order of the columns . This makes no sense to me . Any ideas ?

Agent Job Links to Another SQL Server and Fails due to Service Account Privs

Posted: 22 May 2013 02:54 PM PDT

To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite repeated Google searches to figure out how to solve. Let me preface my explanation by saying that I am not a DBA and I have only limited access to the involved servers because they belong to a customer for which we are implementing an application.What is going to be needed is kind of a step-by-step, cookbook approach because I'm not sure at quite what level their DBA resources operate. Without further adieu:1. Our application runs on a virtualized server named SQL2. I do not have direct access to this server but I can access the database for our application using SSMS from the application tier's server.2. There is a SQL Server Agent job defined that runs on SQL2 but links to a different SQL Server (SQL1) containing another application from which our application needs to draw some data. I do have limited access directly to SQL1 but very limited privileges there.3. On both servers, there is a SQL account defined ('myaccount') and the password is the same on both. This account does have sufficient privs on SQL1 to access the customer's application's database, or at least the table I need to read from.On my application server, I can launch SSMS, connect to SQL2 and run the SP below and it runs just fine. It is but a small subset of the actual SP that needs to run to keep this somewhat simple. I have defined an agent job on SQL2 whose owner is 'myaccount.' When the agent job runs it gets an error message. It is set up to run the exact same EXEC as shown below.[code="sql"]-- From SSMS this runs without issues:EXEC [dbo].[TestConnectToLinkedServer];-- Above is defined as the job step in the agent job named "Test Linked Server Connection"-- When I run this, it failsEXEC msdb..sp_start_job N'Test Linked Server Connection';-- I can get the error message it fails on with this (top 2 rows):EXEC dbo.sp_help_jobhistory @job_name = N'Test Linked Server Connection',@mode='FULL' ;[/code]The error message returned by the last query is:[code="plain"]The job failed. The Job was invoked by User myaccount. The last step to run was step 1 (Run SP).Executed as user: NT AUTHORITY\SYSTEM. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.[/code]Clearly the account that is being used on SQL1 (AUTHORITY\ANONYMOUS LOGON) isn't the same as the one on SQL2 (NT AUTHORITY\SYSTEM). I'm thinking this must have something to do with the account mappings but I am not sure on which server the mapping needs to be changed (or for that matter what it needs to be changed to).Here is the SP that is run either manually or by the agent:[code="sql"]CREATE PROCEDURE [dbo].[TestConnectToLinkedServer]AS BEGIN DECLARE @CustAppDBname NVARCHAR(20) = N'CustApp' ,@LinkedServer NVARCHAR(20) = N'SQL1' ,@retval INT ,@ErrorCount INT = 0 ,@ErrorCount1 INT = 0 ,@SQL NVARCHAR(MAX); IF NOT EXISTS ( SELECT 1 FROM sys.servers WHERE name = @LinkedServer) EXEC sp_addlinkedserver @server=@LinkedServer, @srvproduct=N'SQL Server'; BEGIN TRY -- This will fail if there is no server to link to (i.e., account not recognized) EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@LinkedServer ,@useself='TRUE', @locallogin='myaccount'; SELECT @retval = 0; END TRY BEGIN CATCH SELECT -3, 'Problem linking to Customer'' app server: ' + @LinkedServer, @retval ,[SQL Error Number] = ERROR_NUMBER() ,[SQL Error Severity] = ERROR_SEVERITY() ,[SQL Error State] = ERROR_STATE() ,[SQL Error Procedure] = ERROR_PROCEDURE() ,[SQL Error Line] = ERROR_LINE() ,[SQL Error Message] = ERROR_MESSAGE();; SELECT @ErrorCount = @ErrorCount + 1, @retval = -1; SELECT @ErrorCount1 = -3 END CATCH IF @ErrorCount = 0 EXEC @retval = sys.sp_testlinkedserver @LinkedServer; IF @retval <> 0 BEGIN SELECT -4, 'Test of linked server failed' SELECT @ErrorCount = @ErrorCount + 1, @retval = -1; END SELECT @SQL = N' SELECT TOP 10 * FROM [' + @LinkedServer + N'].[' + @CustAppDBname + N'].dbo.AppTable' EXEC sp_executesql @SQL IF @LinkedServer IS NOT NULL AND EXISTS ( SELECT 1 FROM sys.servers WHERE name = @LinkedServer) BEGIN IF @ErrorCount1 <> -3 EXEC sp_droplinkedsrvlogin @rmtsrvname=@LinkedServer, @locallogin='myaccount'; EXEC sp_dropserver @LinkedServer, 'droplogins'; ENDEND[/code]Any help would be greatly appreciated. BTW. I am not sure there's sufficient information here to lead someone to identify and precisely resolve the issue. If there is not, please let me know what additional information you need to diagnose the issue and I'll try to get it. It may take a day or two to get it as I'll be relying on others arms and legs to get it done (most likely).

SQL Server can't connect to database

Posted: 22 May 2013 08:02 AM PDT

I downloaded file SQLEXPRADV_x86_ENU.exe (version 10.00.1600.22), and used it to install SQL Server 2008 Express with Advanced Services, which I think has capabilities comparable to SQL Server 2008 Enterprise. Upon install, all Prerequisite conditions were met, and installation was completed with no failures.This version of SQL Server includes SQL Server 2008 Management Studio Express. The Management Studio doesn't show in Add or Remove Programs, and there's no icon for it on my desktop, but it does appear at Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio.I then downloaded file SQL2008.AdventureWorks_All_Databases.x86.msi, and attempted to use ot to install AdventureWorks Sample Databases for SQL Server 2008.I got the following error:"PrepInstance() failed for MSSQL$SQLEXPRESS.The following features are missing: Full Text SearchFix the problems and re-run setup."When I ran the Management Studio, it said:Server type: Database EngineServer name: NULL\SQLEXPRESSAuthentication: Windows Authenticationand gave me the following error:"Could not connect to NULL\SQLEXPRESS. Server not found or not accessible."I guess this must be because NULL is not a valid directory.Now the Server name is just coming up blank.Any help?

Scripting & automating all SQL Server Jobs

Posted: 22 May 2013 06:55 AM PDT

Hello,I am looking to script out all the SQL Server agent jobs and automate it, for high availability purposes on StandBy Server.here is the script that I found.... http://www.nilkanth.com/2004/07/18/sql-jobs-auto-backup/but it does NOT return any value...hmmm? Please keep in mind that I am using SQL 2008R2 Enterprise version.Please help !Thanks

Issue with C# Script task in SSIS

Posted: 22 May 2013 12:44 AM PDT

Hi,I am trying to set the value of a variable in SSIS using a C# script task. This task takes ReadOnlyVariables: User::Counter,User::FileDate,User::FileExtension,User::CodeReadWriteVariables: User::FileNameThe script task does not return the value. Can someone guide towards the right approach or let me know what I am doing wrong. Thanks Here is the code to set the value of that variable:------------------------------------------------------------------------------------------------------------------------------------using System;using System.Data;using Microsoft.SqlServer.Dts.Runtime;using System.Windows.Forms;namespace ST_501dbe371426493eaa6ec55b247902ab.csproj{ [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public void Main() { String strCode = string.Empty; String strFileDate = string.Empty; String strFileExtension = string.Empty; String strFileName = string.Empty; Int32 intCounter = 0; strCode = Dts.Variables["User::Code"].Value.ToString(); strFileDate = Dts.Variables["User::FileDate"].Value.ToString(); strFileExtension = Dts.Variables["User::FileExtension"].Value.ToString(); intCounter = Int32.Parse(Dts.Variables["User::Counter"].Value.ToString()); strFileName = strCode + "_" + strFileDate + "_" + intCounter.ToString("D4") + strFileExtension; ///This message box actually print the value that I am looking for MessageBox.Show("The Filename is: " + strFileName); /// But when I assigned this value like this below. It does not set the variable to that value Dts.Variables["User::FileName"].Value = strFileName; Dts.TaskResult = (int)ScriptResults.Success; } }}------------------------------------------------------------------------------------------------------------------------------------I thought this would work But it does not. To verify it I have another script task following that one, that reads the variable value through a message box. The message box shows nothing.Thanks for your help.

SSIS format Excel worksheet with script task

Posted: 17 May 2013 05:31 AM PDT

I'm exporting to an Excel file and then formatting the spreadsheet. Using SQL2008R2. When I run the package from the designer it runs successfully and formats the spreadsheet correctly.But after execution, I still see Excel running in background.I have an application.Quit(). Am I doing something wrong?Here's my script Task code:[code="vb"] Public Sub Main() ' ' Add your code here Dim oBook As Object Dim oSheet As Object Dim oXLS As Object Dim oFile As Object oFile = "\\MyHouse\MyFilePath\MyFile.xls" oXLS = CreateObject("Excel.Application") With oXLS oBook = .Workbooks.Open(oFile) End With oSheet = oBook.Worksheets("KHS_Case_Master") With oSheet .Range("KHS_Case_Master").Font.Name = "Arial" .Range("KHS_Case_Master").Font.Size = 10 .Range("A1", "FZ1").Font.Bold = True End With oSheet.Activate() With oBook .save() End With oXLS.Quit() oXLS = Nothing oBook = Nothing oSheet = Nothing oFile = Nothing ' Dts.TaskResult = ScriptResults.Success End Sub[/code]

Finding users with particular roles

Posted: 22 May 2013 12:19 AM PDT

HelloI need to find all users that have DB_Owner or DB_SecurityAdmin on every database. I've got this script that will tell me on a per database level, but running it against each DB isn't particularly efficient!select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleNamefrom sys.database_principals membersinner join sys.database_role_members drmon members.principal_id = drm.member_principal_idinner join sys.database_principals roleson drm.role_principal_id = roles.principal_idWHERE members.name <> 'dbo' and Roles.Name = 'db_owner' or Roles.name ='db_securityadmin' ORDER BY members.name I have looked at a few scripts but can't find exactly what I'm looking for. Does anyone have anything that will do this?Thank you

my login is locked for 30 min and tehn unlocked automatically

Posted: 22 May 2013 01:19 AM PDT

Hi whenever I login to any of server with my login ID first in the mroning every day. It shows followign message "Unable to log on you on becasue your account has been locked out, Please contact your administrator"but after 30 min I can able to correctlywhat could be reason behind this?what step i should take to resolved thi sissues?Thank You nirav

Search Code in multiple Views

Posted: 22 May 2013 02:56 AM PDT

I am migrating a group of related databases to a different server, and am changing the name of one of the databases. There are many views in the other databases, some of which may reference tables & views in the database with the changed name. So I'll need to change the database name in the view queries when it occurs.Some of these DBs contain dozens of views. Is there any way to query ALL of views in a DB to identify references original DB name other than opening & searching or testing each view individual view?

Super script

Posted: 22 May 2013 06:21 AM PDT

Is there anyway we can store the super script in SQL sever table?

views, no lock and cursors oh my....

Posted: 22 May 2013 02:38 AM PDT

First.... let's just assume i know sets should be used instead of cursors in most cases, and skip all the comments on that.I'm more wondering what would actually happen in this case.Assume we have views created and on each of these views is a no-lock.Cursors lock the tables/views they are using if i'm not incorrect, (one of the reasons not to use a cursor)but what happens with no-lock views, and a cursor?is a lock obtained? or is it ignored?i couldn't find anything specific on this one.Thoughts other than, 'use sets and not cursors'?

Universal driver for database for visual studio

Posted: 22 May 2013 12:54 AM PDT

universal driver for database for visual studio. Database may be any thing it may be SQL server, Oracle,MYSQL etc..... what ever database what is the best driver suited for all the database.Say today i am using MYSQL database tomorrow i may switch over to SQL server or to some other database.i should not change code in VS just need to change the connection string and it should would with minimum changes at the code rather than changing the whole code.Is ODBC will suite ?Thanks!

Read mail using SQL Server 2008 r2

Posted: 21 May 2013 08:06 PM PDT

Hi,I have configured SQL Server database mail.Now i want to read mail of ID that i have configured in SQL Server 2008 r2 database mail.is it possible?How?

removing LDF2 from the database

Posted: 22 May 2013 01:28 AM PDT

Hi,What is best procedure to remove a log file from the database on Production database.Note: we have added one extra log file to Production database due to log filefull issue.Now we need to delete that log file.

Snapshot Replication Problems...

Posted: 22 May 2013 01:37 AM PDT

Hello all,I have a couple of issues while trying to implement snapshot replication. Basically, I just want a nightly copy of a production database on server 1 to a reporting database on server 2. Seems simple enough, right?First, every time I go into the article properties, the "[b]Action if name is in use[/b]" option is always set to "[b]Drop existing object and create a new one[/b]." If I change it, hit "OK" and exit out, then come back in, it will still show as "Drop existing object and create a new one." Does it just show that as the first option, regardless of what is actually being used? (I've tried setting it to "Delete data. If article has a row filter, delete only data that matches the filter."Second, the scheduled replication processes [i]seems [/i]to be working fine. The snapshot gets generated fine (without any errors), and then (later) the distribution job runs fine (without any errors).However, the data doesn't change on the reporting database.Can someone point me to some common reasons that this might be happening? Perhaps some options or settings that I have overlooked?Thanks.

BufferTempStoragePath SSIS

Posted: 22 May 2013 01:07 AM PDT

Hi,Can you set the BufferTempStoragePath of a data flow task to be a shared network folder for instance '\\server\folder' or does it have to be a physical drive like F:\Regards

CMS - Creating an admin db across all servers in sql2008

Posted: 22 May 2013 12:56 AM PDT

HiCan anybody advise me how i can create an admin database from my cms so it gets copied to all my instances.I found an article by Dave Pinal which uses the scripting method. Im not sure if this is how to do it via a cms though ? Is there an easy way ? I have created the db at the top level ( the cms ) Thanks

Find date gaps

Posted: 17 May 2013 03:32 AM PDT

Hello everyone,I need to find a way to fill gaps in case a week is missing, example each record has Begin_Date and End_Date, and some records can have multiple Begin_Date and End_Date but they should be continuation of the previous week. Example :Row_Nbd Account_Nbr Account_Name Account_Desc Begin_Date End_Date1 1 test1 chk 1/1/2012 3/1/20122 1 test1 chk 3/2/2012 6/30/20123 1 test chk 8/2/2012 12/31/2012as you can see there us a missing date range right above row_nbr 3 which (7/1/2012 to 8/1/2012) how can find the missing week?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog