I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT * FROM ( select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type ,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo ,n.note FROM ih_cml_log_entry cml
When adding/removing tables in SSMS 2016 CTP2, I keep tripping into:
Index was outside the bounds of the Array
When editing a SQL Server 2014 Database.
If I use the SSMS for SQL 2014, the error is not happening. Possibly a reintroduced bug. Tried to use the Connect site to submit, but it says I am not enabled to use this area.
Am just on the lookout to find out whether there is a way to put a prefix at the beginning of the transaction log backup file that is created by SQL Server logshipping.
So, instead of having <DatabaseName>_<DateTime>.BAK (which is what SQL does), I would like to have TL_<DatabaseName>_<DateTime>.BAK.
I have looked into a parameter for the sqllogship.exe command, but cannot find any & have looked at fields in the logshipping tables, & can't see anything there either.
below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01
SELECT @@SERVERNAME as ServerName,DB_NAME() AS DatabaseName, SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013, SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012, SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010, SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009 FROM
[code]...
I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.
Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix? For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.** Any help would be appreciated.
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
I have this t-sql code which will get some table stats on one database at a time, I was wondering how I would get it to loop through all databases so it will pull the stats from all tables in all databases. Here is my code:
Select object_schema_name(UStat.object_id) + '.' + object_name(UStat.object_id) As [Object Name] ,Case When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null Else Cast(Sum(User_Seeks + User_Scans + User_Lookups) As Decimal) / Cast(Sum(User_Updates
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
Doing a data migration from one CRM to another and need to get a listing of all entities in current CRM, together with fields and field types. OK, I got the XRMToolBox which gives me that, but I'm hoping there is a sql tool out there that will do the same, plus give me a count on each field of the number of entries in that field in the database.
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4 View2, Column4 View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/ -- Create 2 test views IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]')) DROP VIEW dbo.[View1] GO CREATE VIEW View1 AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
Hi all, i encountered this problem.. whenever i tried to create publications it always prompt me this error msg "sql server replications does not support nicknames,such "." or "(local)". i know i need to delete the registration and register a new server with a different name.. i just want to double confirm whether deleting the registration will affect the databases or not ? :
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
I have decided to use Wufoo online forms so collect evaluation data from my clients. Each form will have different type of data.
For example
Form 1 might have: Client id, name, user id, comments
Form 2 might have: Client id, name, user id, address, comments, telephone number
Form 3 might have: Client id, name, user id, comments, date of birth, email address
Each form can have over 20+ data types which can be all different to other forms.My question is: What is the best way to store all the data into sql server without creating new columns of new data types everytime a new form is created in wufoo.
I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
I am using SQL server 2012. An user tried to export tables from GIS application to the SQL database.
After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.
He was added as a login and user as in a windows group. Meaning he is a member of the windows group.
I assume when export, the default schema should be dbo. but apparently not. I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables. just wondering why is this?
I am trying to clean up security. When I check tables in a specific database I see a list of users with select access. There are 1000+ tables in the database. I know I can do 'revoke select on table_name to user_name' ....
In last week my database was crashed and some how i managed to restore it back on SQL2K12 but after restoration all the relationships are removed and sql server is showing below message when i open diagram of the database.Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.how to get back all the relationships of the tables.
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated FROM item I CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated FROM Orders O WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O PartNumberOrderNoO.DateCreated 1A1XXX 1CHXX1 1/8/2014 2A2XXX 1CHXX3 1/20/2014 3A3XXX NULL NULL 1B1XXX 2CHXX1 2/10/2014 2B2XXX 2CHXX3 2/22/2014 3B3XXX NULL NULL
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...
DBCC Shrinkfile (DB_Name_log2, Emptyfile); go alter database [Db_Name] remove file DB_Name_log2; go
I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?
Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***