I need a query that gets all of the databases on a server and determines if a backup has been taken. I would normally use the jobs query I currently have, but it wouldn't tell me when the last backup was taken for a new database. I think I should be able to use sp_helpdb or some table from the msdb database. Also, this has to work on SQL Server 2000/2005. If anyone has anything I'd be very grateful. Thanks in advance.
-Kyle
I am trying to determine which of three stored procedure designs arefastest in the Query Analyzer:One query is a straight SELECT query with all desired rows and a dozen(tblName.RowName = @param or @param = Null) filters in the WHEREstatement.One query populates a #Temp table with the UniqueIDs from the resultsof the SELECT query in the above example, then joins that #Temp tableto get the desired rows.One query users EXEC sp_executesql @sql, @paramlist, @paramin which the @param has the dozen filters.What I'm trying to determine is which is the fastest.Each time I run the query in Query Analyzer it returns the samerecordset (duh!) but with much different Time Statistics.Are the Time Statisticts THE HOLY QRAIL as far as determining which isfastest, and what so I want to look at, the Vale or the Average? Inotice there are different numbers of bytse sen and bytes received foreach of the three queries.Any illumination on this is appreciated.lq
View 4 Replies View Related
Hi, I'm sorry if this is the wrong place to ask this question. These forums are overwhelming and I couldn't find the appropriate place to post.
I am wondering if there is some way to determine when a query has completed. What I want to do is use a PleaseWaitButton in asp.net when the user clicks to run a query and have the button reactivate after the query has completed. I know how to set a specific wait time but I do not want the user to have to wait a default time for the query to complete. Any help you may provide would be greatly appreciated.
Thanks!
Hi all,
I am looking for a query to determine the exact size of a large table.
Thanks
JJ
Hi.
I have a VB.NET function that returns an array of Integers.
Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54]
One characteristic of the array is that no two numbers
repeat - it reflects the IDs of my Users table. And, that
it is not ascending or descending.
What I would like to know is how do I sort my query in
the order of the integers in this array? Ideally, I
would like to use ORDER BY for this query.
thanks in advance.
Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle
Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access. The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!
Given the data below, I have a couple needs:
1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)
2) Query to determine if any date ranges of the same category overlap
declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'
[code]....
I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.
I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...
exec sp_QueryInvalidForNotification "exec myTestSproc 99"
Does such a sproc exist? Or does anyone know how to write one?
Hello,
Can anyone tell me the SQL statement to restore an existing backup to an existing database?
Thanks in advance.
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedHi, This is a diffcult issue to explain. I hope to make my problemclear to you.SITUATIONI'm building A SLA Query for a customer. This customer has an awkwardway to determine the SLA results ;-) Depending on a category which isstored in a headertable (Requests) a field and logic is determined howto get a proper Close_Date. This Close_date can be the closedate ofthe request. It is also possible that the close_date is a certaindetail record (Request_lines). Also It is possible that thisclose_date is the ordered_date of a certain line_item.DONE SO FARI have created a metatable with rules per category. With this rule iwould like to determine a close_date. This close_date will be used asparameter in a function from which i determine the total minutes howlong this request has endured.GOALI want to create something like this:SELECT id, getdiffSLA(@StartDate, GetCloseDate(some parameters))FROM...I've created the function 'GetCloseDate()' in which i want todetermine the closedate. In this function i want to determine therule. Execute the proper logic and get the date.This is what i'm trying to do in a function (this is one rule of many):......ELSE IF @iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATEBEGIN............SET @vcSQL = N' SELECT @max = MAX(Close_date)FROM samis.dbo.SAD_A_REQUEST_LINESWHERE Parent_Quote = ''' + @vcNumberPRGN + ''' AND Part_No In ('+@vcIN_String + ')'Exec sp_executesql @vcsql, N'@Max datetime OUTPUT', @max OUTPUTSET @dCloseDate = @maxELSE IF.........THE PROBLEMThis doesn't work and yeah i know i can't use exec /sp_executesql in afunction. But if i try this in a stored procedure i can't use this ina Query.SELECT id, storedprocedure FROM ... Doesn't work, also.An option is that i could create a cursor, loop every record and callthe stored procedure, get the close_date, execute my SLA calculationfunction, store the result in a temptable, use this temptable in thequery <pffff>.But the table consists of 200000 records (with a detailtable) andperformance is a issue. It's used for loading a datawarehouse and notin a OLTP system so a bit slow performance is allowed but not to much.SO how do i do this without using a cursor???GreetzHennie
View 5 Replies View RelatedHi all,
How to determine the value for the bar in Neural Network Viewer? I know the value is displaying in the tooltips when we pointing to the bar in the table but i don't know how to get them. So where can i get those of calculation or data of score, probability of value1 and 2, and lift for value1 and 2 ? Is it get from the Microsoft Neural Network Content Viewer? which column and how to calculate? If not, please advise.
Hope my question is clear.
I am looking forward to hearing from you shortly and thanks a lot in advance.
Thanks!
With best regards,
Yours sincerely,
xuenly
I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If
View 1 Replies View RelatedGood day,
We have a web application (ASP) running on SQL Server 7.0.
Recently, the users are getting quite a lot of timeouts on
the database:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
The database is not supposed to be doing too much work, so I can't
understand why these timeouts are occuring. How can I determine
the cause of the timeouts?
The cause could probably anything from a trigger that's taking
too long, a query that's taking too long, or simply bad database design.
I've looked at SQL Server's Profiler, but could not yet use it
successfully to give me any hints of what could cause the timeouts.
Any ideas of how I can use Profiler, Performance Monitor, or any other
tool(s) to see what is happening in the background in the database,
i.e. how much processing a trigger is using, etc.
Thanks very much!
---
Gert Lombard
OSI Airport Systems
South Africa
I'm new to full text catalogs and we have a vendor who's code utilizes them. The database server is SQL 2005 and I am noticing the following message in the SQL log every minute.
Changing the status to MERGE for full-text catalog "ResearchCatalog" (5) in database "DBA_Test" (11). This is an informational message only. No user action is required.
A SQL job is running the following command every minute.
exec sp_fulltext_catalog 'ResearchCatalog', 'start_incremental'
What is the typical frequency for running an incremental?
Can the messages be suppressed?
Thanks, Dave
for sql servers how can i determine who [what accounts] are the dbas?
View 1 Replies View RelatedI've been doing a lot of reading on this and my head is starting tohurt! It seems to be quite a feat to work out how much memory isactually being used by our server.I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of4GB and a max of 6GB is assigned to SQL server.I'm trying to work out whether we've assigned enough or toomuch/little memory to SQL server. My first thought was to let SQLdymanically manage its own memory and see how much it uses, of coursewhen AWE (/3GB /PAE) is enabled it will just use all that isavailable.In perfmon "target server memory" = 6.1GB, "total server memory" =6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).My second thought was to use "total pages" - the average "free pages"= average mem used, therefore giving me the average amount of memoryused by SQL. I found out that SQL uses a min of 4GB (the min weassigned) and the max of all the memory, 6GB.Is there an easier way of finding out how much memory is actually usedin this situation or is going by the above average the best way?What i'm unsure about is will SQL just use all memory assigned to ituntil it has the whole DB in memory? 20GB including indexes etc....Any help would be greatly apprechiated.
View 2 Replies View RelatedI need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?
Thanks
Hi all... This is the definition on the M/S site:
"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."
Good... straight forward, right? Ok.... try entering thses command seperately:
Create Table Readings (ReadingDate DateTime Not Null);
Create Function [dbo].[funct_SameDate](@datReadingDate Datetime)
Returns DateTime
As
Begin
return @datReadingDate;
End
Alter table Readings add
[TempColumn] as (dbo.[funct_SameDate](ReadingDate)) PERSISTED NOT NULL;
Error on last command returns:
Computed column 'TempColumn' in table 'Readings' cannot be persisted because the column is non-deterministic.
Can someone please explain this to me? The same value is always being returned.
This does work:
Create Table Readings (ReadingDate DateTime Not Null);
Alter table Readings add [TempColumn] as (ReadingDate) PERSISTED NOT NULL;
I obviously want to do more things inside the function, but I can't get by the first step.
Any suggestions?
Thanks!
Forch
Hi,
I have a report that has a table with detail grouping. This table shows the sales by day for each product. The users only want to see the date field for the first item in the group. After that, they do not want to display this field (to reduce the data on the report). However, when the data wraps to a second page, they want the date to appear on the first row of the new page.
Is there any way to determine if a row is the first row on a page?
I tried using the RowCount, but that continues from the previous page.
Any thoughts?
Thanks!
David.
hi,
since I am kind o'new with SQL, I preffer get an advice fro you pro's: I created an application which performs access to a database on an SQL server. the application will be used by a few different users, each on a different computer. the application calls stored procedures, updatesinserts records in tables on the SQL and delete rows. what would be the best role to define the users activity ? How do I limit their activity ONLY to the specified actions ?
I'm working on a sproc that determines the next order id for a specified customer. The table has
custid int,
ordernum varchar(10)
Data is:
1000, 1000-001
1000, 1000-002
1001, 1001-001
1000, 1000-003
I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).
I honestly don't know where to begin.
Can someone please help?
Hi All,
i am working with database mirroring and i need to be able to determine which database will be principal.
for example if server A is principle and server B is mirrored and server A is down, then server B take owner and become principle, after fixing server A i want it to be principle again,
is it possible ??
What is the most reliable way to determine the last LSN of a database? I've looked in sys.database_files to no avail. I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.
-joe
With an INSERT statement I add a record to a table.
Then I want to get the (autonumber) ID of the newly created record.
What is the fastest and best way to do this?
I need to determine which service pack we are running on our sql servers. I run SELECT @@VERSION and get it tells me that we are running 7.00.1020. I have a listing from google that tells me the value for each service pack, but my version doesn't match anything on the list.
Can you tell which service pack I am running based on the results of my query?
Thanks!
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Hi,
I am trying to determine what service pack I am running on a SQL 7 server. Can somebody tell me how to do this?
I have an article from technet showing me how to do it for SQL 6.5 but not 7. I assume it is the same method but I need to know which product version number relates to which service pack.
Thanks
Dear all,
Please tell me how to determne the editiion of mssql installed. I can only know the version but cannot know the edition.
Regards,
Simon
How do I determine the Login name from the user name, in SQL?
For example, I have a Login called Accounting with users Bob and Sue. How do I know from Bob or Sue's user name that they are members of the Accounting Login?
I am using NT Authentication.
Thanks!