Is there any sql script using which we can find out all local paths used in job steps and output_file_name path used in advanced settings in in each job step.
Example ) if i have a job which has job steps which out file logging and I need display those jobnames,step id, and command where local paths are used. The reason I wanted to find this output because as we are going for server migration and so we need to create those paths and folders on the new server. I don't want to visit each n every job and look for local paths. That's where i am in search of the script.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
Having some trouble getting my head around setting access to specificschemas- here's my problem:I've created a specific schema that I only want certain users tocontrolProblem: Even though I give them full access....the cannot createtables under that schema...my code is below (flyer is the schema,eflyerAdmin is the role, and eflyer is the user):GRANTALTER,CONTROL,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,TAKE OWNERSHIP,UPDATE,VIEW DEFINITIONON SCHEMA::flyerTO eflyerAdminGO-- Add an existing user to the roleEXEC sp_addrolemember N'eflyerAdmin', N'eflyer'
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have written an update, if i see the execution plan of that update, it is showing the Non Clustered index are updated. I am not able to understand the fact, that when there was no change in Leaf level root data how does Non clustered index got updated.
I'm running SQL Server 2008 R2 with latest patch. I'm performing all index maintenance online. How long the final phase of the index operation takes? Does the size of the index matter and if any blocking occurs, does the duration increase because the size of indexes is larger? I've been told by management, we can not have any downtime, its my understanding even with online index there's a chance blocking can occur in the final phase of the index operation. My database and index size is over 1.5 tb and the number of transaction per second are in the 100's.
SELECT CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '') ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
I have three sprocs and three tables. I was told to use a clustered index in the first table and a unique clustered index on the second table. I never asked about the third table and the person I need to ask is on vacation. Most of the contents of the first table will be joined with all of the contents of the second table into the third table. Do I need to have a unique clustered index on the third table too?
The clustered index in the first sproc is on a unique key that I had created using by concatenating several columns together.
CREATE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data (UNIQUE_KEY); CREATE NONCLUSTERED INDEX IX_DATE ON MRP.Margin_Optimization_Data (PERIOD); CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.Margin_Optimization_Data (GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
The second sproc with the unique clustered index is on the unique key from the first table and a date attribute.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.[MGN_OPT_KPI_SOURCE] (UNIQUE_KEY, PERIOD);
In the third sproc, I'll have a nonclusted index on the ODS_ID attributes, but I'm unsure of how to go about the clustered index situation.
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.MGN_OPT_KPI_VALUES (GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
/****** Object: Stored Procedure dbo.carr_summary_Datewise Script Date: 5/15/2008 10:20:37 AM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[carr_summary_Datewise]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[carr_summary_Datewise] GO
if @pMonth=1 begin set @tb1='ob_Jan08' end if @pMonth=2 begin set @tb1='ob_Feb08' end if @pMonth=3 begin set @tb1='ob_Mar08' end if @pMonth=4 begin set @tb1='ob_Apr08' end if @pMonth=5 begin set @tb1='ob_May08' end if @pMonth=6 begin set @tb1='ob_Jun08' end if @pMonth=7 begin set @tb1='ob_Jul08' end if @pMonth=8 begin set @tb1='ob_Aug08' end if @pMonth=9 begin set @tb1='ob_Sep08' end if @pMonth=10 begin set @tb1='ob_Oct08' end if @pMonth=11 begin set @tb1='ob_Nov08' end if @pMonth=12 begin set @tb1='ob_Dec08' end
select Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0) CallDate,Routepfx,Operatorout, cast(sum(Talktime/60.) as decimal(10,2)) Talktime,Cost from Report.dbo.'+@tb1+' (nolock) where Operatorout=''' + @Operator + ''' and Callyy=' + ltrim(str(@pYear)) + ' and Callmm=' + ltrim(str(@pMonth)) + ' and calldd between ' + ltrim(str(@sD)) + ' and ' + ltrim(str(@eD)) + ' group by Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0), Routepfx,Operatorout,Cost )x where CallDate between ''' + convert(varchar(20),@pD1,120) + ''' and ''' + convert(varchar(20),@pD2,120) + ''' ' --print(@sql) --exec (@sql)
set @sql= 'select Callyy,Callmm,Calldd,Routepfx Prefix,case when zone is not null then zone else ''--NOT FOUND--'' end zone, case when TimeCls is not null then TimeCls else ''--NOT FOUND--'' end TimeCls, ''' + @Operator+ ''' Operatorout,Sum(Talktime) Talktime,Cost from ('+@sql+') x group by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls,Cost order by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls ' --print (@sql) exec (@sql) end
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
this procedure takes more time to run
we need make it fastly
what should i need
if i create one index how it works
where should i create index please write one index for me
Recently we are having issues with one of the indexes during our maintenance. There is one index that which usually would have taken 10-15 mins continues to run even after 3 hrs. Other indexes on the same table finish nicely. Just this one gets stuck. Our index creation scripts drops existing index and creates new one..
CREATE INDEX ... ON ... WITH DROP_EXISTING, SORT_IN_TEMPDB ...etc
So last week when I got woken up because of this, I tried to drop the index explicitly and re create the index (by removing the DROP_EXISTING in above script). Even though it took about 45 mins it still finished in time during the maintenance window.
We had the same issue this week. I was looking at sp_who2 during the index creation, I noticed very low CPU activity and high DiskIO. example: CPU : 4k cycles, DiskIO 50000.
There is 20% of free space on the index file group. Generally index creation takes higher CPU and lower DiskIO.
How do I find out what the issue is with this index? The table has 40 million rows. this is a non-clustered index. There are 2 other indexes on the table - one clustered. they both finished properly in less than 10 mins.
yesterday i was trying to create Stored procedure but it fails i don't know why
CREATE proc GetBooksbyBorrowerID @Borrower_id INT AS BEGIN SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b WHERE a.borrower_id=@Borrower_id AND a.ISBN = b.ISBN END GO EXEC SP_Task1 10001
I have a client application written from C#/winforms app that needs access SQL DB on SQL server 2008 R2 developer. So I need to create a Login for this app with a user/password. But when i created it and put it on the client's connection string, the DB could not be accessed by the client windows forms app and the error was "Login failed for User App1".
Then I tried to login to SQL server from SSMS using the credentials of App1 but it also gave same error "Login failed for user App1". I have following the following steps of creation of the login for App1. i could login from "sa" account and other Admin accounts but not from "App1" account
1. created a server level Login 2. created a DB level user 3. created a Role (a DB role), i even tried creating an App Role 4. given permission to execute stored procedures that handle login
But didn't work. Maybe i made some mistakes in those step. So, how to create an account for user "App1" so it can be accessed by the client windows app and also from SSMS? i would prefer to use account creation from SSMS GUI.
How do i install SQL server 2008R2 in local machine with network accessibility features?
I am trying to create an index on a view that joins two tables.
I get the classic error of course: 'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'
Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.
Code is below and any help greatly appreciated.
CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS SELECTt_number FROM dbo.ticket_cancellations RIGHT OUTER JOIN dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number WHERE dbo.tickets.t_cancelled <> - 1 OR -- Add all cancellation codes that are to be excluded from the NET view below (dbo.ticket_cancellations.tc_cancellation_code <> 83943 AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)
GO -- Create a clustered index, it MUST be unique CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)
I was asked to import some data from excel into a table within a sqlserver 2000 db the import was complaining about an index so then I deleted the index imported the data succesfilly but I'm now unable to re create the index, please see the error that I'm getting below. Can someone please help. Thanks
- Unable to create index 'trainingGo'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 32. Most significant primary key is '439'. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Hi, all, I want to re-create an index on a production table. I got an error 644 "could not find index entry...". The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1 Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36. DBCC results for 'Mfg_DFSFNSF'. There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be: DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss) But that has to put the db under single user mode.