I have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
In our Production db we have all most all tables have the column created or createdate.
I need to find out all tables without the created or createdate column
SELECT t8.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c
ON t8.OBJECT_ID = c.OBJECT_ID
[code]....
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,
[Code] ...
Using MSSQL 2012
I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.
Below is my where clause.
WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'
---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
I am using the following views from two db's to find records that don't match.
My question is can I have output of fields from the second table
SELECT distinct T1.[last name],t1.[first name],ENum
FROM ECLINICIAN_Info T1
WHERE NOT EXISTS(SELECT *
FROM ACLINICIAN_Info T2
WHERE t1.Enum = t2.Anum
and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)
I would like to include t2.Anum in my select ...
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
Any ideas?
I am having problems with a lookup transformation. I have a row in my lookup table for blank ('') source data. If I test the join using SQL the match is made, but the Lookup transform doesn't consider it a match and sends it to error output. Is there a property that I don't have set correctly or something else I am forgetting?
View 5 Replies View RelatedLength specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xxx.xx.xxx.xx]
Client IP address is same as the server its producing the error on. I get these messages around 12pm everyday.
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs. Each object and permissions
View 6 Replies View RelatedI need to find a way to identify SPIDS where queries are connected to certain databases using the 4 dot notation instead of actually being connected to the database in question.
For instance, I connect and my default database is MASTER. In MASTER, instead of typing USE MyDb ahead of my query, I just type my query as:
SELECT Col1, Col2
FROM MyDB.dbo.MyTable
The above is the simple scenario. We usually have queries connecting 3 or 4 user dbs together. So database1 might have a query pointing to database2. What I need is a way to identify the SPIDS that are connecting to database2 despite being in database1.
My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.
What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.
Is there any way to find how much the backup file will be generate before we run the backup job?
i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db
currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
This question is a long shot because it requires familiarity with a specific application called ChefTec which is a program for the restaurant industry, but I'm giving it a try because I have nowhere else to go to get the information I need. I am the developer for a competitor of this program, and some ChefTec users want to switch to our product and have their data, which is in SQL Server 2005 or 2008, converted.
My plan is to create a utility that the users could run to convert the data (written in Delphi, though that doesn't really matter). The problem is that I can't determine the connection string that ChefTec uses. It doesn't seem to be stored anywhere obvious (an ini file or the registry). When ChefTec is installed it either installs SQL Server or gives the option of using an existing instance, but there's no way for me to know how it was set up on any given machine. The program must be using some internal logic to create the connection string but that's not accessible to me.
CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')
CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')
My task is to find matches between these two tables on "name."
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
I have an idea to use LIKEW opeartor (with te wildcards) to match large (>10Kb) text fields of 'text' and 'ntext' types. Are there known problems here?
Thanks
I am having trouble with a select statement where i try to use a variety of methods on two columns in different tables that, hypothetically, have exactly the same contents.
Below are the three Create Table statements, so you can see how everything is set up. The columns zone_monthly.specific and #temp_dec0607c.specifics come from almost the same original source, so I can't fathom how they could actually be different in their contents - I know some of the individual lines are the same.
Create Table category_specifics (ID integer, segment nvarchar(Max), specifics nvarchar(max))
BULK INSERT category_specifics
FROM 'c:USERSTEALDOCUMENTShonda_chartsmonthly_filescategory_list.txt'
Create Table zone_monthly (ID Integer, specific nvarchar(max), zone integer, dec06 money, dec07 money)
INSERT INTO zone_monthly (specific, zone)
SELECT specifics, '12' AS zone
From category_specifics
Create Table #temp_dec0607c (ID integer, specifics nvarchar(max), zone integer, dec07 money, dec06 money)
Bulk Insert #temp_dec0607c
FROM 'c:USERSTEALDOCUMENTShonda_chartsmonthly datazonedec0607.txt'
Now here is the SELECT statement that I am having trouble with - It returns no results, and, as I said before, I am rather certain it should return something. I have tried it with an inner join as well (code included), and again, no results.
SELECT #temp_dec0607c.zone, #temp_dec0607c.dec07, #temp_dec0607c.dec06
FROM #temp_dec0607c
WHERE
#temp_dec0607c.specifics=zone_monthly.specific
SELECT #temp_dec0607c.zone, #temp_dec0607c.dec07, #temp_dec0607c.dec06
FROM #temp_dec0607c
INNER JOIN zone_monthly ON
#temp_dec0607c.specifics=zone_monthly.specific
What on earth is going on?
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View 4 Replies View RelatedI'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.
Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
[Code] ....
I have tableX with columns colA, colB, colC, colD and there are 2256 rows in the tableX.
I would like to find out the percentages of colA, colB, colC, colD that hold data (where it is not an empty string or NULL value).
So out of 2256 rows in the table, the user has stored data in colA 1987 times, colB 2250 times, colC 2256 times and colD 17 times.
So the report would say:
colA: 88.07%
colB: 99.73%
colC: 100%
colD: 0.01%
We have an application that has a bunch of fields that we believe are not being used and would like to remove them, but we need to prove this by looking at the data.
I know I could run a query, one at a time and change the column name, but this would take a long time as there are a lot of columns in this table. I am hoping there is some way to do this in one query.
I have a SQL select syntax as below
0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "
I have data looks like below
23-4c-4f-3rd-2
23-5c-4ad-3-2
THis is all in one field. I need to use sql to take out the data between the 1st and third hyphens to return
4c-3rd
5c-4ad
SQL code for the following? (SQL Server 2008 R2 - SQL Server 2012).
I have Table1 Containing two fields with the below entries
VehicleType Name
Two Wheels Bicycle
Two Wheels Scooter
Two Wheels Motorcycle
Four Wheels Sedan
Four Wheels SUV
Four Wheels Pickup
Four Wheels Minivan
The result I'm looking for would be
Table2
Vehicle Type
Two Wheels Bicycle, Scooter, Motorcycle
Four Wheels Sedan, SUV, Pickup, Minivan
I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
Thanks,
Judith
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul
View 3 Replies View RelatedI have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.
I am not sure if SQL Server 2008 has this capabilities.
Hi, hope someone can help.
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees
1 Dave
2 Peter
3 John
Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter
Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
Thanks