Transact SQL :: How To Get Unknown Dropped Indexes

Jun 15, 2015

I created dropped all indexes in a database and run in one database instead of actual databse. How to recreate again dropped indexes ...

View 4 Replies


ADVERTISEMENT

Transact SQL :: Convert Unknown Number Of Questions From Rows Into Columns

Jun 17, 2015

Using the following tables and data---

CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')

[Code] ...

And currently outputs;

Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015

How complex is the structure?

Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million

Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)

[Code] ....

However, I would like it to output;

Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?

[Code] ....

The number of questions are unknown for each RiskReviewID and they can be added to in the future.

View 7 Replies View Related

Unknown Ammount Of Parameters, Parameterized Statement/split Strings In Transact Sql

Oct 26, 2007

Hello,
Is there any way to run a parameterized statement against SQL when you have an unknown ammount of inputs.
Instead of doing the following:
FOR i as integer = 0 to topValuestrWhere &= " OR myColName='"& myArr(i) &"'"
NEXT
wash up the string and submit this to SQL, I want to do it with parameters. I'm calling a sproc in SQL and I would prefer just to send in a string like "1,4,42',45" in a parameter split that in SQL and run my query.The query would be something like "...WHERE myColname=1 OR myColname=2 OR myColName=3" I only want to declare one parameter and not use string concatenation as described above.It doesn't matter if there's a split function in SQL or not, as long as it solves my problem in an efficient manner. Cheers!/Eskil 

View 3 Replies View Related

Transact SQL :: Parse Unknown Number Of Data Elements To Multiple Lines

Jun 11, 2015

We are using a table that may give 1 to and unknown number of data elements (ie. years) .   How can we break this to show only three years in each row.  Since we don't know the number years we really won't know the number of rows needed.  Years are stored in their own table by line.  
 
car make year1 year2 year3
A   volare 1995 1996 1997
a   volare 1997   1998   1999
b toyat  1965    1966   1968

We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.

View 8 Replies View Related

Unknown Members In Report Parameter Causes CONSTRAINED Flag Error In STRTOSET Function When NullProcessing Unknown Member

May 1, 2007

Hi,



I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).



When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:

STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).



When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.



How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?



Thanks



View 10 Replies View Related

Drop Tables With Unknown Names And Unknown Quantity

Jul 20, 2005

This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!

View 2 Replies View Related

Transact SQL :: How To Get Indexes Of Capital Letters In A String

Sep 15, 2015

Is it possible to pull the index(s) for capital words in a string.  For example, if I have a string that says, GetTableName, would it be possible to output, 0, 3, and 8?

View 5 Replies View Related

Transact SQL :: Create Table Indexes In SSMS2012

May 26, 2015

I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..

-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees

[code]....

Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012. 

View 3 Replies View Related

Transact SQL :: Data Compression And Rebuilding Indexes

Aug 31, 2015

If I'm doing data compression(page level) does it rebuild indexes too? and how about stats, does it update stats too?

View 4 Replies View Related

Transact SQL :: Error Object Doesn't Exist While Rebuild Indexes

Sep 16, 2015

We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.

/*
Script to handle index maintenance
Tuning constants are set in-line current values are;
SET @MinFragmentation
SET @MaxFragmentation
SET @TrivialPageCount

[code]....

View 19 Replies View Related

Transact SQL :: Find Column ADRSCODE Used In Indexes Or Primary Keys?

Oct 12, 2015

I wanted to find all occurrences of ADRSCODE in a Database where ADRSCODE is in either an Index or a Primary Key.

I know how to get all of the occurences of ADRSCODE in a database and the table associated with it, I just want to tack on the Index and/or primary key.

SELECTOBJECT_NAME(object_id)FROMsys.columns
WHEREname
='foo'

How can I get the other bit of information ?

View 2 Replies View Related

Transact SQL :: Fragmentation Levels Of All The Clustered Column-store Indexes In A Database?

Sep 18, 2015

I have a  database in which I have  some tables in which I have implemented Clustered columnstore Index. How to find the fragmentation levels of all these indexes via  a single T-SQl script

View 3 Replies View Related

Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)

Oct 4, 2015

I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.

Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based 
column b: is the source filename of the data load (varchar 256)
....

a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)

b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?

c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.

-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?

View 4 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

Can SA Be Dropped

Mar 24, 2004

Is it possible at all to remove the SA login or deny "System Administrator" from it by loggin as another user who has sa previledges

thanks

View 1 Replies View Related

Dropped Table

Jan 25, 2001

I am helping another division with a problem of a dropped table. They have no backup and after dropping the table, the server has been re-booted 3 times. Is there a way to recover the table with no backup?

Help anyone,
Thank you.

View 2 Replies View Related

Dropped Connections

Apr 6, 1999

Here's a poser.
I run a VB application to import data from a text file into a SQL 6.5 database. Typically, there'll be 300,000 records, all of which have to be processed. Time taken will be in excess of 2 hours.

When the application finishes, I try to go into Enterprise Manager and run a Select Count(*) to verify that the data's been imported. Invariably, I get an error reading:
"A connection could not be established to <server> - [SQL Server] Could not allocate a new object descriptor for required system catalog in database '1'. Another database must be closed or objects in another database dropped in order to open this database."
I get the same error whether I try to view Current Activity, open the Query Tool or simply expand the server in the Server Manager area.
Usually, it means I have to re-boot the server, which is not popular with other users. Does anybody know what's causing this and what might be the solution?
Thanks.

View 1 Replies View Related

Dropped Job Failed

Apr 17, 2007

I believe I deleted a maintenance plan that was attached to a job and therefore have received the following message:



Drop failed for Job 'Shrink DSS Database.Subplan_1'. (Microsoft.SqlServer.Smo)

An execption occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlSserver.connectionInfo)

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error: 547)



Is there a T-SQL or other means I can use to remove this constraint and delete the job show it does not show up in SQL Server Management Studio any longer?

View 5 Replies View Related

Who Has Dropped SQL Table?

Jan 16, 2007

Somebody has dropped my SQL table in (multiuser environment),now I want
to know who has done that. Is there any way by which I can retrieve
that(user name or machine name)?
I am using SQL 2005.

View 3 Replies View Related

The Dropped Table Does Not Have A Primary Key And Cannot Be Used

Feb 25, 2004

I'm trying to drop a table onto the design view in Web Matrix and the following message appears: "dropped table does not have a primary key and cannot be used".

I'm using a SQL 2000 database that was previously an MSDE 2000 database. Is there anyway that I can define a column as a primary key?

Thanks.

View 5 Replies View Related

Is It Possible To Recover A Dropped Table?

Jun 3, 2004

Hi All,
Accidentally I have dropped a table and that is the main table for my application.
I dont have a backup of it. Is there any way to recover it.Please help me guys.
Thanks,
Kumar.

View 1 Replies View Related

Stored Procedure Getting Dropped In SQL 7.0

Apr 2, 2001

Hi
ONLY ONE of our many stored procedure is getting dropped in SQL 7.0. We were not able to find thru SQL Profiler. Let me know any alternate methods to find it or the possible reasons why/how the sp is getting dropped.


Thanks

View 1 Replies View Related

User Being Dropped From Dbases

Nov 30, 2000

Hello,

I have the following problem that I need to find a solution. I have created a generic user id and login in the model datbase on SQL 7. However the genericuser does not show in the explorer window for some databases, does not work on some databases. I have to go into TSQL and use dropuser to get rid off them and re-link them again.

Does any1 have a clue about why this happening? How it can be solved?

TIA Gurmi

View 1 Replies View Related

HELP!!! I Dropped A Few Tables By Accident.

Aug 5, 2002

I was doing a DTS import and accidently imported into the wrong database, dropping very critical tables. Please let me know how I can restore these transaction back. My last backup was 24 hours ago, and I just need to restore a few hours back.

Thanks,
KJ

View 1 Replies View Related

Database User Cannot Be Dropped

Mar 2, 2005

Hi..I just restored a databae from backup. After restoring, as usual, from the Enterprises manager, I want to delete the database user and readd again from the DB login name. But when I tried to delete the DB user, it prompts me the following.

"the selected user cannot be dropped because the user owns objects"

why? I never encountered this b4 ? I restored DB again but problem still the same. Any ideas?

View 2 Replies View Related

How To Recover A Dropped Table

Dec 9, 2011

Unfortunately I dropped my table in sqlserver. How to I recover that table?

View 3 Replies View Related

Dropped Table But Have PHP Script

Mar 19, 2015

After installing a Zen Cart plugin I was getting errors that there was a duplicate entry for the mysql table so I went in and dropped the table assuming that it would come back when I re - installed everything, anyways I have the PHP code that created the table but not sure how to recreate it or if that's even possible. Also my main way to get to change anything is phpMyAdmin, I tried importing the code there but that didn't work.

View 2 Replies View Related

Recovering Dropped SQL Database

Sep 21, 2006

I am in a big trouble ,

accidently i have issued a DROP DATABASE XXX command in the sql client, thinking its a local server....... The whole 4 months of database database is now dropped.





Please help me , how can i recover the database ....



thanks in advance

View 1 Replies View Related

Most Logins Dropped-No Permissions

Mar 28, 2007

Hi,



In my local instance of SQL Server at work (which I use for testing), somehow all of the logins except 2 were dropped. The administrators group no longer exists as a login (nor do any other windows users or groups)-the only 2 remaining are sa (which is disabled) and a SQL login I had created earlier which has no permissions except read permission on master. I can login as this SQL user, but I do not have permissions to create logins or enable the sa account. Do I have any options other than uninstalling and reinstalling my local instance?



Thanks,



-Dave

View 5 Replies View Related

How To Recover From Dropped Tempdb?

Feb 7, 2007



I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:eepmssql emp empdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.



Thank you for your comments.



View 9 Replies View Related

Restoring A Dropped Table - No Backup - HELP!!

Nov 24, 2000

Hello,

I'm in trouble, I have just dropped a table from a sql dbase and now need to recreate it. I have no backup and I am panicking!!! I need to know if I can recrreate them .

Any and all help appreciated

DVNC

View 1 Replies View Related

Replication Problem - Subscriber Not Dropped

Apr 30, 2001

Hi there,

I installed 2 SQL2000 servers at a client site, the first acting as a publisher and distributor for transactional replication. The second is a push subscription to the first. The client wanted to change the database name, and some other stuff, so I removed the replication and set it up again for the new database name.

All's fine at the publisher / distributor but the subscriber is showing as still having active push subscription to the old (and new) databases

Any idea how to fix this

View 2 Replies View Related

Database Connection Timeout / Dropped

Jan 25, 2013

My sql server is setup to use windows authentication. From time to time one of the applications on that server would have issues connecting to that server. I changed it to use sql authentication and all of a sudden all the problems went away.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved