Thursday, July 11, 2013

[SQL Server 2008 issues] is there a way to tell when a login was created or revoked?

[SQL Server 2008 issues] is there a way to tell when a login was created or revoked?

is there a way to tell when a login was created or revoked?

Posted: 10 Jul 2013 12:56 PM PDT

Hi sqlcentral colleagues - is there a way to tell when a login was created or revoked? I haven't created any triggers for this purpose. I was wondering if there's default login history info I can pull. Any hope of that? Thanks in advance.

Does anyone have an explanation for this behavior? or another reason to always schema qualify your tables

Posted: 10 Jul 2013 07:29 AM PDT

I was faced this week with a problem. One job was behaving in a odd manner and we could not replicate the issue.In the end the problem was that the job was accessing the wrong table, I just don't know why it was doing that.So here is the scenario:We have a job that runs against a database. That database has a table named DBO.SITE_MASTER and a synonym for the object RPT.SITE_MASTER that points to data on another database.The job in question has a statement like (please note the absence of schema in the table name):SELECT @ID=SITEID from SITE_MASTER where SITE_NAME=@SITE_NAMEAfter trying everything I could think of I noticed that whenever I executed that statement (with a sysadmin like account) I would access the DBO table. Whenever the SQL Agent job (running as "sa") executed that statement, it would access the synonym and return different results.The fix was easy, and should be in place form the start (just fully qualify the table with the schema), but does anyone know WHY this is happening? I thought that SQL would check for the schema with the same user name, and if that was not available would always default to DBO.Why is SQL in this case defaulting to a non-default schema, and only for the SQL Agent user?Thanks in advance

Extract String between Special characters

Posted: 10 Jul 2013 09:23 AM PDT

Hi All, I have a string like this Building Management - Ex. Existing Facility\Keys & locks\Project Co to ensure that 400 complete sets of keys are available at all times.now my requirement is to extract string before first \ ,second \ and third \ . So my results would be like this Building Management - Ex. Existing FacilityKeys & locksProject Co to ensure that 400 complete sets of keys are available at all times.what is best way of achieving this in sql server 2008.

Schema Diagram

Posted: 09 Jul 2013 11:53 PM PDT

Hi Has anyone ever had to reverse engineer a schema diagram from a database before? If so, what tool did you use? I have Toad for SQL Server which I'm currently trying to figure out if it will do as I want.Thanks.

Insert bulk failed due to a schema change of the target table.

Posted: 10 Jul 2013 07:21 AM PDT

Hello Expert.First time I used sqlbulkcopy its performance is impressive, however my application threw a SQL exception after processed about 500,000 records. The error stated "Insert bulk failed due to a schema change of the target table." which I don't understand why, data being inserted into the same table, and why it processed 500,000 records with out issue, but not the rest which I cant understand.Here is the chunk of VB.Net code that does the bulk copy. Notice that MyDestTable has a couple more fields that I didn't want them to be mapped. all fields in the MySourceDataTable table are specified in the mapping.Dim sqlBulk As New SqlBulkCopy(_connectionString) sqlBulk.DestinationTableName = MyDestTable sqlBulk.ColumnMappings.Add("DataOwnerID", "DataOwnerID") sqlBulk.ColumnMappings.Add("ObjectID", "ObjectID") sqlBulk.ColumnMappings.Add("ObjectTypeID", "ObjectTypeID") sqlBulk.ColumnMappings.Add("PropertyName", "PropertyName") sqlBulk.ColumnMappings.Add("Pvalue", "Pvalue") sqlBulk.ColumnMappings.Add("UpdateIDRefreshed", "UpdateIDRefreshed") sqlBulk.ColumnMappings.Add("UpdateIDModified", "UpdateIDModified") sqlBulk.ColumnMappings.Add("Attribute", "Attribute") sqlBulk.ColumnMappings.Add("POrder", "POrder") sqlBulk.WriteToServer(MySourceDataTable) sqlBulk.Close()Anyone run into this type of error while using SQLBulk copy? I did lot of googling but didn't find anything points to the right direction.Thanks in advanced.

Data center migration

Posted: 10 Jul 2013 07:35 AM PDT

I hope this forum is "general" enough for this question. I figure most people here have experienced this issue in one way or another.We are migrating our data center and we are concerned about our SQL servers with relative data.Our goal is to move the server as smooth as possible to the new location, which is only about 200 yards around the corner, but there are bumps, door entries, rough sidewalks, etc.Does anybody on here have some best practices regarding server transportation he or she would be willing to share?Thanks in advance.

how to modify sql job to remove extra information coming in text file

Posted: 10 Jul 2013 07:26 AM PDT

I am exporting the sql query result set to a text file and its writing without the issue, i want to eliminate lot of extra information it is writing For example Job 'myNIC_PROD_ActiveUsers_In_Last5Minutes' : Step 1, 'Active_Users' : Began Executing 2013-07-04 18:25:47 [b]NumberRows Time_stamp ----------- ------------------------------------------------------ 39 2013-07-04 18:25:47.630[/b] (1 rows(s) affected) So i only need to see Bold text in this post

linked server issue

Posted: 10 Jul 2013 01:04 AM PDT

HiCan anybody advise on the following error :[quote]OLE DB provider "SQLNCLI10" for linked server "server1" returned message "The partner transaction manager has disabled its support for remote/network transactions.".Msg 7391, Level 16, State 2, Line 2The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server1" was unable to begin a distributed transaction.[/quote]Any pointers would be great. Thanks

Importing Text file madness

Posted: 10 Jul 2013 06:09 AM PDT

OK. This one has driven me mad for the past couple of hours. I am working on a new & improved web site for this State Agency and converting some old Informix text-file based databases to SQL Sever and .NET.The task is simple. Import a text file into a SQL Server 2008 Table.I am trying to import this file into a SQL Server 2008 Table:http://www.mvdb.virginia.gov/Downloads.txtAnd I am getting these errors:* Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Description" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) * Error 0xc020902a: Data Flow Task 1: The "Source - Downloads_txt.Outputs[Flat File Source Output].Columns[Description]" failed because truncation occurred, and the truncation row disposition on "Source - Downloads_txt.Outputs[Flat File Source Output].Columns[Description]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.(SQL Server Import and Export Wizard) * Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\.....\Downloads.txt" on data row 2. (SQL Server Import and Export Wizard) * Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Downloads_txt returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)From what I can gather, my "Desciption" field is being truncated. Here's the weird part. The txt file comes another SQL Server (test machine) 2008 table. Description is defined as being varchar(200). So it the table/field I'm trying to import INTO. In fact, I doubled it to 400 just to see what it would happen. No luck. Same errors.Any ideas? Going nuts here.

find session

Posted: 10 Jul 2013 01:22 AM PDT

Hi friends, Our database server(windows 2008R2 4GB 64-bit) goes up to 98% physical memory and about 3.5G from sqlservr.exe. Max server memory is set to 2GB for sql server. Users are facing performance issues.. Is there a way to find out which process is using up most memory from sql server? Also, are there any options to resolve this other than increasing RAM?Thanks a lot

Total Beginner Question

Posted: 10 Jul 2013 06:33 AM PDT

Believe me when I say that I have googled this and searched this forum up and down and either don't understand or can't find the answer I am looking for. I have a simple SQL 2008 database that receives names, companies and the date when the company representatives visit request expires. I am trying to write a query that will return rows of reps whose visit request expires in 30 days. What I have written is SELECT vrExpdate - 30 DAYSFROM mydatabaseIt returns an error stating Operand type clash: date is incompatible with int. The data type for vrExpdate is "Date". I understand that an integer isn't compatible with a date but what I don't understand is how to write this query. I have tried unsuccessfully using every "DATE" function I can find...from DATEADD to DATEDIFF and everything in between.Any help will be greatly appreciated.

Raid 1 or raid 10

Posted: 10 Jul 2013 03:12 AM PDT

Hello.What is the best, Tempdb and Ldf's users databases in a Raid 10 with two volumes or one Raid 1 for each?Thanks.

Red lines under third occurance of a table name

Posted: 10 Jul 2013 02:38 AM PDT

Hello. I have an annoying problem and I can't find anything about it.In scripts I have, that are templates created by my co-workers, the first occurance of a table name, ex: dbo.Exception_Ibank, is fine.The third occurance has a red squiggly line under it as do all following occurances. If I move my mouse over the table name, it says that it can't find it. The script runs properly, though. Also, all of the field names of the columns in that table have red lines under them, too.I've tried to clear the cache, which didn't do anything.This isn't a performance issue. It's just annoying.Any help would be greatly appreciated.Bert

Updating SQL table using SSRS

Posted: 10 Jul 2013 01:00 AM PDT

Hi everyoneI have the following table in sql server:CREATE TABLE [DBO].[SHIFTS]( [WEEK_ENDING] DATETIME NULL,[FORENAME] VARCHAR(50) NOT NULL ,[SURNAME] VARCHAR(50) NOT NULL,[EMP_ID] VARCHAR(10) NOT NULL ,[DEPARTMENT] VARCHAR(50) NOT NULL,[SHIFT] VARCHAR(20) NOT NULL,[HOURS] INT NULL)I was wondering whether it was possible to create a front end utility in SSRS that could alter the [DEPARTMENT] ,[SHIFT] and [HOURS] fields back in the base sql table. Ideally I'd like the ability for the user to type in the requisite changes which would then update the table. The report will be viewed by Managers and the purpose of this utility will be for them to check, alter and approve the hours worked by their staff. I know its possible to update tables using SSRS but not sure whether it can be done by direct entry via SSRS. Thanks in advance,BO

Import data from multiple Excel sheets to multiple Tables using SSIS

Posted: 09 Jul 2013 08:33 PM PDT

Hi,I have a excel file that has multiple sheets and I need to import data from each seperate sheet to a seperate table using SSIS. E.g. Sheet A data should go to Table A and Sheet B data should go to Table B. We are doing some data unpivoting also before sending it to table.Can somebody help me to achieve this?

Sql server Mirroring without witness Server

Posted: 10 Jul 2013 02:20 AM PDT

HI,I need to start mirroring the databases without witness server,Can i please have instructions from scratch to end kind of step by step.Regards

Replication DB - Dead lock issue

Posted: 29 Jun 2013 03:32 PM PDT

Hi,We have One Database which is a Replication Db, sometimes during beginning of the month, lot of user activities going and we having Dead lock issue.Any one has idea how to prevent the dead lock in Replication DB?We have to manually kill the user session one by one but if someone having same kind of issue and have any idea?Thanks,

How to update Flag for maximum amount in a a group

Posted: 10 Jul 2013 12:27 AM PDT

Create Table #Temp(Pk_Id Int Identity(1,1),Lnno Int,Amount Int,Flag Char(1))Insert Into #Temp(Lnno,Amount)Values (1,5),(1,10),(2,20),(2,25)Select * from #TempHi, My requirement is,I want to update Flag as 'Y' for the Lnno which has maximum amount. Please help this is urgent to me. Thanks in Advance!

Unable to open Alerts in SQL Server Agent / Cannot view properties

Posted: 15 Nov 2012 05:53 PM PST

I am unable to open Alerts in SQL Server Agent and Cannot view the properties using GUI. Although I am able to create and modify alerts via ScriptIt gives me this exception. Anybody has any idea ?===================================Cannot show requested dialog.===================================Cannot show requested dialog. (SqlMgmt)------------------------------Program Location: at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc) at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider) at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control) at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolMenuItemHelper.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc) at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()===================================Failed to create/initialize Agent Alert dialog. (SqlManagerUI)------------------------------Program Location: at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlert..ctor(CDataContainer dataContainer)===================================Cannot create/initialize Response page. (SqlManagerUI)------------------------------Program Location: at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlert.AddResponsePage() at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlert..ctor(CDataContainer dataContainer)===================================Object reference not set to an instance of an object. (SqlManagerUI)------------------------------Program Location: at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlertResponse.RefreshJobComboBoxAndSelectJob(Job currentJob) at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlertResponse.RefreshJobComboBox() at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlertResponse..ctor(CDataContainer dataContainer, String agentAlertName) at Microsoft.SqlServer.Management.SqlManagerUI.AgentAlert.AddResponsePage()

script

Posted: 09 Jul 2013 11:49 PM PDT

i would like to find the above details DB names, last backup finished date , backup size of all the databases , free space in data files in all the databases, is any script to achieve this . .

Rounding issue

Posted: 30 May 2013 01:21 PM PDT

Hi everyone,I have problem with rounding. There is a table[code="plain"]CREATE TABLE [dbo].[Fin3PaymentDisb]( [PaymentID] [bigint] NOT NULL, [Currency] [char](3) NOT NULL, [Amount] [money] NOT NULL, [Rate] [float] NOT NULL, [CurrencyPay] [char](3) NULL, [RatePay] [float] NOT NULL, [AmountPay] AS ([Amount]*[Rate]), CONSTRAINT [PK_Fin3PaymentDisb] PRIMARY KEY CLUSTERED [/code]with valuesAMOUNT = 3875.0000RATE = 0.17836AMOUNTPAY = 691.145 And this query returns record[code="plain"]SELECT P.*FROM Fin3Payment P INNER JOIN ( SELECT PaymentID, SUM(AmountPay) AS Amount FROM Fin3PaymentDisb GROUP BY PaymentID ) AS L ON L.PaymentID = P.PaymentID AND ROUND(Round(L.Amount, 2) - ROUND(P.Amount - P.Offset - P.SetDisc, 2), 2) <> 0[/code]where PaymentAmount = 691, Offset = -0.15 and SetDisc = 0, i.e. 691.145 is rounded to 691.14. But in the query[code="plain"]SELECT ROUND(691.145, 2)[/code]result is 691.15.What could be the problem here? It runs on MS SQL 2005 Express SP2.Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog