Hi everyone,
I am looking for sql monitoring tool. I have found tools from Idera and Quest but the price is a bit expensive.
The tool I am lloking for is able to show historical information such as CPU/memory/disk usage.
Any good suggestion? Thank you in advance.
We are in plan to build a Monitoring tool using PowerShell and Performance Monitor which could monitor 10 to 20 servers. Do you have any reference of any existing tool using Performance Monitor to monitor the SQL Server and available for free? I didn't want to put some effort, if something is available already.
View 2 Replies View RelatedI have been told that many ETL tools used to cost hundred of thousands of dollars. Is this true or were they referring to the ETL and Analysis tools, together? Can anyone provide some actual product names and estimated "costs" that hits these marks?
TIA,
barkingdog
Hi all,
I am new to the db world so please let me know if I don't make any since.
I was wondering if there is an open source SQL database monitoring tool out there that I can run on my desktop that would give me a log of what is going on on the database (or the server).
Thanks.
Hi guys!
I have built a website using asp.net 2005 that uses sql server 2005 database,
my question is,
which tool can I use to monitor the connections to the database,
because I feel that some of them are left open for a very long time.
Thanks in advance!
I am in the process of evaluating some SQL Performance Monitoring /DBAtool to purchase (For SQL Server 2000). I have the following list ofsoftware that I came across and have to finalize which one to recomendfor purchase by my company.Quest Central® for SQL ServerPerformance Center by Embarcadero (dbartisan)Performance analysis by BMC Software solutionsSQL Server Management by netIQI would greatly appreciate if anyone can give me a comparison/ anysuggestions on the above mentioned applications...!Has anyone used/currently using any of these applications? Pleaseadvise...Thank you in advance...!
View 7 Replies View RelatedOur shop is expanding use of SQL Server, both 2000 and 2005. We haveLitespeed on some boxes to handle the backup/recovery jobs. Can I askwhat are considered the best tools for monitoring SQL Server, in termsof things like performance monitoring, tuning and auditing if it ispossible to get all of this functionality in one?What do you use and like?Thanks in advance.Gerry
View 2 Replies View RelatedHi:
I removed the publication from the database but it is still showing in the monitoring tool. Does anyone know how to remove it from there?
Thanks
hi guyz i want to integrate Sql Server 2000 with Microsoft Monitoring tool , and wht I exactly want is , I need a report of each users and their internet history. i have an application on asp.net 1.1 in which i need to import data from some source it may be microsoft proxy server or any monitoring tool. 1 of my mate suggested me to use the tool which lies at : Control Panel -> Ad Remove programs -> Ad Remove windows components -> managment and monitoring tool -> Monitoring he was not sure either this tool can be integrate with sql server or not. but i need some data in to my tables, can any1 help me in this regard kindly ask me if there is any confusion
u guyz can email me as well ..... do let me know if know 1 find any solution
thnx
http://stripes.docmusic.com
SQL Stripes is an application that helps you, the SQL Server DBA, having complete control over your various SQL servers.
From the high-level of monitoring essential components - to the low-level of analyzing your various databases, tables etc…
This is *not* another Query-Analyzer like tool, nor does it intend to be. The idea is that each one of your servers gets a 'Stripe' which contains valuable real-time data on the selected server.
SQL Stripes is an intuitive and easy to use application, which is intended to make your DBA life a bit easier.
SQL Stripes is completely free. Please send your feedback/questions/suggestions
Thanks and enjoy!
I'm trying to create custom borders. I can't use the line tool in a text box nor can I add text to a rectangle. Any ideas how to draw a border that isn't the standard 4 lines around the edges king of thing?
View 2 Replies View RelatedHi folks
wld really appreciate it if anyone could guide me to a good third party tool for replication on sql server.
thanks in advance
achal
Hello,
I need a tool that will let me model a SQL Server 2005 database and then generate the tables, constraints, etc. from the model. I've never used a modeling tool so my knowledge is quite limited. I don't need to model or reverse engineer an application - my sole concern is on the SQL Server database side. I'm not concerned if the tool integrates with Visual Studio. And, of course, price is one consideration.
Are there any good tools that I should look at?
Thanks,
BCB
I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.
Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.
Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
[code]...
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
View 9 Replies View Relatedis it possible to change the settings on this tool. i want to have it so that it does not time out at all? or is that possible??
View 1 Replies View RelatedHi all,
I am a java developer having little bit knowledge in sql statements.
can any body guide me what is the effective way of using sql statement
to achieve the following goal.
here is the requirement.
1. There are 2 tables A & B.
2.The table 'B' may or maynot have an equivalent entry for the primary
key of table a.
3. select column1 (of A), column 2(of B) from table a and b.
4. now column2 should give a value, if there is a corresponding
entry in table b.other wise null.
5. at any time there may be 0 to 1 record in table b for the primary
of table a.
thanks in advance
krishna
I did a search of the forums and couldn't find any other threads on "Effective Dating" so please forgive me if this topic has already been covered.
Does anyone know of any support articles or tutorials covering the topic of "Effective dating" as a means for keeping a history for data?
I would like to keep a infinite history on certain tables and would like to avoid duplicating data structures if possible. Any suggestions?
Thanks,
JAndrews
My gut tells me this can't be done quite the way I'm hoping, but maybe someone has a little insight...
Is it possible to specify a specific timeframe for which triggers should be effective? I have some make-shift replication tasks which are currently done via pure insert/update queries and various times throughout the day. In the am our "target" table is completely wiped and repopulated from our "source" table which resides on a different server.
I'm hoping to setup update triggers throughout the day to catch changes to our "source" table. These triggers should only be active for a six-hour period throughout the day. Unfortunately true replication isn't an option here.
I can think of a couple ways to take care of this such as checking GETDATE() from the trigger itself and deciding whether or not to do anything, or perhaps scheduling a job to enable and disable triggers at the appropriate time. Is there a way to setup such a schedule for the trigger itself?
When I click on the properties of a 2005 sql server database and thenpermissions.If I select a group and then effective permission, I got an error,saying that "cannot execute as the principal server because the 'xxxgroup' does not exist, this type of principal cannot be impersonated,or you do not have permission (Microsoft SQL Server; Error: 15406)"I guess the error is valid, since the group does not exist in thedatabase as a user. If so, how do I get the effective permissions of agroup?
View 1 Replies View RelatedHi, I am looking for a sql statemnt that find the most related phrases, how to do that? For example, I want to find the related phrases bellow for the following phrase: "How to install the software"
1- "Installation does not work"
2- "installation"
3- "steps to install the software"
It's obvoius that phrase number 2 and 3 are related to that phrase, how to do that using sql?
Regards.
Dear SQL,
since I create some multi-language table - I want to allow finding unicode text
so I made the field: Key_Words" (ntext)
It will have a string that can include some words in different languages, so that I can find by using:
SELECT Key_Words FROM MyTable WHERE Key_Words LIKE '%" & MyVar & "%' "...
The problem is that I can not apply clustered index on ntext field (or *any* index...)
Any ideas how to deal with it ?
I have built my own functions in the past that involve a timestamp and record lock column in each table (to support some other DB).
I am currently using SQL 2000 and was wondering if there is a better (OK, easier) way to lock records and even prevent edits from taking place when a record is open.
Any input appreciated.
Can anyone provide me with some insight on tracking the history of my data using "Effective Dating"? I would like to keep track of an infinite history for many of the tables in my database and am trying to avoid duplicating data structures if possible.
Any advice on this topic would be appreciated, I am most interested in any links to tutorials or support articles on subject that you may know about. I have searched the web and the forums section and have not come across much.
Thanks,
JAndrews
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
I have a large SQL Database im building, in the database there is a table for each user with certain things listed. One problem is, sometimes in a few collums, there will be like 100things listed, instead of one line.
For Example:
Products in that some users will have 1 product like "apples" but others will have many more products.
What is the most effective way of listing the data? Create new tables? Seperate the products with comma's or spaces?
How do I do it, and keep the overall db size smaller?
Thanks
i have 3 tables having refrential integrity on id on column on each table
TABLEA contains 5 million rows
TABLEB contains 50 thousand rows
TABLEC contails 5 thousand rows
i have a proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.what is the optimized way to write query to get records on the basis of in parameters from these tables.
Hi,
Not sure if you could help or not, but I need to pull the most recent effective date for this report I am trying to run, but I am getting know where. If someone can take a look at this, it would be great.
Example….
pcs number 00004344 effective dates 5/1/2006 and 5/1/2007. I need it to be the most recent effective date which would be, 5/1/2007 date.
Can someone help me?
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES] Script Date: 03/10/2008 09:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES]
AS
BEGIN
SET NOCOUNT ON
DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME
SET @STARTTIME = GetDate()
PRINT 'SP started on: ' + CAST(@StartTIME as varchar)
PRINT ''
DECLARE @PKey varchar(16), @pcsi_id1 varchar(8), @pcsi_id2 varchar(4) /**@pcsi_id3 varchar(4),@Lpcsi_id3 varchar(4)**/
DECLARE @LplID varchar(12), @LTrm Datetime, @Eff Datetime, @Trm Datetime, @Gap int, @Corrected bit
DECLARE @CTrm DATETIME, @i varchar(8), @LastID varchar(8), @LEff Datetime, @FinalEff DATETIME
SET @i = 0
IF OBJECT_ID('tempdb..#pcsiData') IS NOT NULL
DROP TABLE #pcsiData
IF OBJECT_ID('tempdb..#HoldKey') IS NOT NULL
DROP TABLE #HoldKey
IF OBJECT_ID('tempdb..#HoldKey2') IS NOT NULL
DROP TABLE #HoldKey2
SET DATEFORMAT mdy;
SELECT pcsi_id1 + pcsi_id2 AS pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO #pcsiData FROM pcsi p
WHERE (SELECT COUNT(pcsi_id1 + pcsi_id2) FROM pcsi WHERE pcsi_id1 = p.pcsi_id1) > 1 --AND p.pcsi_prd = 'dgh'
ORDER BY p.pcsi_id1 + p.pcsi_id2 ASC, p.pcsi_eff1 ASC
--SET TRM DATES TO NULL WHERE DATE IS 1977-03-23 00:00:00.000
--(IMPACT XSQL process uses that date in place of null!)
UPDATE #pcsiData
SET pcsi_trm1 = null
WHERE pcsi_trm1 = '1977-03-23 00:00:00.000'
SELECT pcsi_id1 + pcsi_id2 as Pkey, (COUNT(pcsi_id1 + pcsi_id2)) AS DupCount --pcsi_eff1, pcsi_trm1, COUNT(pcsi_id1 + pcsi_id2) AS DupCount
INTO #holdkey
FROM #pcsiData
GROUP BY pcsi_id1 + pcsi_id2
HAVING count(*) = 1
IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[NonDuppcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE NonDuppcsiDataForMalPracticeReport
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO NonDuppcsiDataForMalPracticeReport
FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN(SELECT pkey from #Holdkey)
DELETE FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN (SELECT pkey from #HoldKey)
DROP TABLE #HoldKey
SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1, count(*) as NoofDup
INTO #HoldKey2
FROM #pcsiData
GROUP BY pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
HAVING count(*) > 1
SET NOCOUNT OFF
DELETE #pcsiData
FROM #pcsiData, #holdkey2
WHERE #pcsiData.pcsi_id1 = #holdkey2.pcsi_id1
AND #pcsiData.pcsi_id2 = #holdkey2.pcsi_id2
drop table #holdkey2
SET NOCOUNT ON
IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[pcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE pcsiDataForMalPracticeReport
--CREATE TABLE pcsiDataForMalPracticeReport (pcsi_pkey varchar(16) PRIMARY KEY, pcsi_id1 varchar(8), pcsi_id2 varchar(4), pcsi_id3 varchar(4), pcsi_eff1 varchar(8), pcsi_trm1 varchar(8), Corrected bit)
SELECT pcsi_id1 + pcsi_id2 as pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO pcsiDataForMalPracticeReport
FROM #pcsidata p
WHERE pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM #pcsidata WHERE pcsi_id1 = p.pcsi_id1 AND pcsi_id2 = p.pcsi_id2)
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM #pcsiData
--group by pcsi_pkey
Order By pcsi_id1 + pcsi_id2, pcsi_eff1 ASC
OPEN Cur
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = 2
DELETE FROM tmppcsiDatesWithGaps --Clear table used for debugging
WHILE @@FETCH_STATUS = 0
BEGIN --Begin While Loop
IF @pcsi_id1 + @pcsi_id2 = @lplID
BEGIN --If current record is for the same provider location as the last then...
SET @Gap = DATEDIFF(day, @Ltrm, @Eff)
IF @Gap > 2
BEGIN --If there is a gap greater than 1 day...
PRINT ''
PRINT 'GAP between fetch ' + str(@i - 1) + ' and ' + str(@i) + ' (' + @pcsi_id1 + ' ' + @pcsi_id2 + ' ' + '): ' + str(@gap) + ' days! '
PRINT 'Last Trm: ' + CAST(@LTrm AS varchar) + ' Eff: ' + CAST(@eff AS Varchar)
PRINT ''
--IF EXISTS (SELECT * FROM pcsiDataForMalPracticeReport WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2)
--IF @pcsi_id1 + @pcsi_id2 NOT IN (SELECT pcsi_id1 + pcsi_id2 FROM tmppcsiDatesWithGaps)
--BEGIN --Begin if effective date was not already updated
--IF @Leff > @Eff
UPDATE pcsiDataForMalPracticeReport
SET pcsi_eff1 = @Eff-- pcsi_Ltrm = @LTrm
WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_id3 = @lpcsi_id3, pcsi_eff1 = @LEff-- pcsi_Ltrm = @LTrm
--WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--INSERT INTO pcsiDataForMalPracticeReport (pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_id3, pcsi_eff1, pcsi_trm1)
--VALUES (@pcsi_id1 + @pcsi_id2 + @pcsi_id3, @pcsi_id1, @pcsi_id2, @lpcsi_id3, @LEff, @Ltrm)
INSERT INTO tmppcsiDatesWithGaps (pcsi_id1, pcsi_id2, lpcsiid, EffectiveDate) VALUES (@pcsi_id1, @pcsi_id2, @lplid, @Eff)
--END --End if effective date was not already updated
END --End if there is a gap greater than 1 day
END --End if the provider location is different than the last row
--Set current rows data in last rows variables...
SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm
--Get next row of data
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = @i + 1 --increment i
PRINT 'Iteration #' + str(@i) + ' -- ' + @pkey + ' Eff: ' + Cast(@Eff as varchar) + ' ' + ' Trm: ' + cast(@trm as varchar)
END --End While Loop
INSERT INTO pcsiDataForMalPracticeReport
SELECT distinct pcsi_id1 + pcsi_id2, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM NonDuppcsiDataForMalPracticeReport
/***
UPDATE #pcsidata
SET pcsi_trm1 = '20470101'
WHERE pcsi_trm1 is null OR pcsi_trm1 = ''
/**
SELECT p.pcsi_id1, p.pcsi_id2, MAX(p.pcsi_trm1)
INTO #HoldKey2
FROM #pcsidata p
group by p.pcsi_id1, p.pcsi_id2
ORDER BY p.pcsi_id1
UPDATE R SET pcsi_trm1 = I.pcsi_trm1 FROM pcsiDataForMalPracticeReport R
INNER JOIN #HoldKey I
ON r.pcsi_id1 = I.pcsi_id1
AND r.pcsi_id2 = I.pcsi_id2
**/
Print ''
Print ''
Print ''
Print 'SETTING MAX TERM VALUES NOW....(This may take a while)'
Print ''
Print ''
UPDATE pcsiDataForMalPracticeReport
set pcsi_trm1 = jp.MaxTrm
FROM pcsi p JOIN (SELECT pcsi_id1, pcsi_id2, MAX(pcsi_trm1) as maxtrm FROM pcsi p2
--WHERE p2.pcsi_id1 + p2.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2
GROUP BY p2.pcsi_id1, p2.pcsi_id2) jp ON (jp.pcsi_id1 + jp.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2)
DECLARE @NotTermed int
SET @NotTermed = (SELECT COUNT(*) FROM pcsiDataForMalPracticeReport WHERE pcsi_trm1 = '20470101')
PRINT''
PRINT 'Total non-duplicate records not termed: ' + str(@NotTermed)
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_trm1 = NULL
--WHERE pcsi_trm1 = '20470101'
**/
UPDATE pcsiDataForMalPracticeReport
SET pcsi_trm1 = NULL
PRINT ''
PRINT 'STEP TWO.......................'
PRINT 'Preparing the table names...tmpMalPracticeEffectiveDates'
PRINT''
--This step updates tmpMalPracticeEffectiveDates with the desired effective date and most recent termination date
--if there are no current records with a termination date = NULL
TRUNCATE TABLE tmpMalPracticeEffectiveDates
SET NOCOUNT OFF
PRINT 'Inserting new data into tmpMalPracticeEffectiveDates'
INSERT INTO tmpMalPracticeEffectiveDates
(pcsi_id1, pcsi_id2, pcsi_eff1)
SELECT DISTINCT pcsi_id1, pcsi_id2, pcsi_eff1
FROM pcsi p
WHERE p.pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM pcsi p2
WHERE p2.pcsi_id1 = p.pcsi_id1 AND p2.pcsi_id2 = p.pcsi_id2)
ORDER BY pcsi_id1, pcsi_id2
------------------------------
--Set temp bogus date to distinguis which records are current in
--subsequent statement
PRINT 'Setting bogus date to distinguish pcsi records that are not termed'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = '12/21/2049'
WHERE '03/23/1977' IN (SELECT pcsi_trm1 FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
-------------------------------
PRINT 'Setting most recent term date for pcsi records that are not currently active'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = (SELECT MAX(pcsi_trm1) FROM pcsi p
WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1
AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
WHERE tmpMalPracticeEffectiveDates.pcsi_trm1 is NULL
-------------------------------
PRINT 'Setting bogus dates back to NULL'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = NULL
WHERE pcsi_trm1 = '12/21/2049'
-------------------------------
--CORRECT EFFECTIVE DATES WITH GAPS...
PRINT 'Correcting Effective Dates for those records with gaps in credentialing records'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_eff1 = t.EffectiveDate
FROM tmppcsiDatesWithGaps t
WHERE tmpMalPracticeEffectiveDates.pcsi_id1 = t.pcsi_id1
AND tmpMalPracticeEffectiveDates.pcsi_id2 = t.pcsi_id2
----END OF SP---
DECLARE @Diff decimal
SET @ENDTIME = getdate()
PRINT ''
PRINT ''
DECLARE @GapCount int
SET @GapCount = (SELECT COUNT(*) FROM tmppcsiDatesWithGaps)
PRINT 'Total number of non-distinct provider locations: ' + Str(@i) + '.'
PRINT 'Total number of gaps found: ' + Str(@GapCount) + '.'
PRINT 'FINISHED ON: ' + cast(@ENDTIME as varchar)
SET @Diff = CAST(DATEDIFF(second, @StartTime, @EndTIME) AS varchar)
PRINT ''
PRINT 'Time elapsed: ' + str(@Diff) + ' seconds.'
PRINT ' = ' + str(@Diff/60) + ' Minutes!'
END
As our customers demand that we tighten our IT security in the company,I've been asked to prepare a report quarterly showing, for each user inActive directory, what his effective permissions are for every table inevery database that he has permission for on our SQL Server 2000 server. Isearched a bit for a tool to do this, but all I found was the PERMISSIONS()function for showing effective permissions of the current user. Is thereany way to do it for an arbitrary user, without logging in as them?
View 5 Replies View RelatedReading about SQL Injection attacks I came across this example:
SSN="172-32-9999';DROP DATABASE pubs --"
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"
One remedy given was a parameterized query as follows:
Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)
Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.
Thanks
Mike Thomas
In SSMSE, if I open Server Properties and then Permissions, I can highlight a user or user group and click the 'Effective Permissions' button.
For entries like 'BUILTINUsers' or 'BUILTINAdminsitrators' I get the following error message. I don't understand what it's telling me - can anyone explain?
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot execute as the server principal because the principal "BUILTINUsers" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15406)
Notes: as fara as I know, BUILTINUsers does exist, and I do have permission.
Hi,
I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.
The caching SQL command now looks like this
select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]
and I've set up the parameters so that the business key is the first parameter and the source date is the second.
I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.
Can anyone suggest how I can improve the performance?
Thanks.