This a microsoft SQL 2000 server. I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID. Here is my problem: Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name FROM dbo.sysobjects WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
I have a column that is VARCHAR(30) this column is supposed to contain values that "look" like a date in the form mm/dd/yyyy - however because it is a free-text character field often times data is entered other then a date -- "text" -
How do I return only the data that is in the format of mm/dd/yyyy
This should be trivial but I'm ignorant so I'm hoping someone can assist. I can find lots of code snippets for removing duplicates, but I can't find a variation that works for my case.
Overview: I'm doing a name look-up, combining first & last names and if it matches against an employee table, getting the employeeID for that person. I need to only return the unique matches, and exclude any names that happen more than once in the employee table.
I have 3 possible results from my select (snippet below): 1) Single match 2) No match 3) Multiple matches (2 or more people, same first and last name)
My simple code below does exactly what I need for case 1 & 2. If there is a single match on the name, it's returns the res_ID and emp_ID as expected, if no match, no record and that works for me.
The problem I can't solve is if there are two John Smith employees, both records are returned, which is what my query requests, but not what I need. I want ONLY return data which has ONE exclusive match, and exclude all others.
Code for case 1 & 2, doesn't handle 3rd case:
Select distinct ot.res_ID, e.emp_ID From employee e, @OutputTable ot Where (e.fName + ' ' + e.lName) = ot.empFullNameText
Employee table has first & list names, plus emp_ID @OutputTable is a table variable from my proc which has "John Smith" type text names as one string.
This has to be simple, but I'm over my head on this one. All ideas, reference links or other assistance appreciated.
I want to write a query that will cycle through the results and if it comes across another record that has a matching Table.ID I want to exclude that row from the result set.
I am not all too familiar with how to use either a Case or If..Else Statement within a Sql statement that would accomplish this.
I have a list of numbers that I get from a query to my database. how could I make the query so that I can exclude a couple of numbers. For example the numbers 1234 and 8888. My list is in the range of 1 - 99999.
I have 16,000 rows in tblClient and 3000 rows in NewTable.SELECT tblClient.*FROM tblClient INNERJOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossierif I use = (equal) instead of <> (exclude), the query returns 3000 rowswhen I use <> it returns 160000 rows,if I try group by, the query bugswhat is my problem
Select AVG(AVG_Back), AVG(AVG_Yield) FROM tblUser WHERE Date Between '3/1/2008' AND '3/31/2008'
I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc. I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc. What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has. Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions? Here's the query I have: SELECT MasterSkillsList.SkillFROM ClassSkills INNER JOIN MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE (MasterSkillsList.Skill <> ClassSkill.Skill) Edit: I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong? SELECT SkillFROM MasterSkillsListWHERE (NOT EXISTS (SELECT Skill FROM ClassSkills WHERE (ClassName = @ClassName)))
I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.
Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.
Product Location Time Sales A X 1/04 200 B Y 1/04 100
A X 2/04 300
In the cube, if you were to look at product by location for just 2/04, you would see:
Product Location Sales All Loc 300 A X 300 Y
All Loc B X Y
How do you get rid of the zero's or combinations that don't exist?
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
Based on a table like below I have created a report so that I can compare number of items in the main warehouse (LOCATION1) and the outlets (LOCATION2 and LOCATION3).
Now the issue starts when I add a parameter to my report for user to choose which outlets (LOCATIONs) he wants in the equation. I know how to make a column disappear based on parameter value but how to take it out of equation? At the moment when user selects only LOCATION2 and not LOCATION3 then data is not filtered correctly:
Ideally I would like a user to select random outlets (warehouse would be static on the report) and compare one or multiple and only show records that are 0 in the outlets.
Hi All,I am trying to delete rows from a table using a SQL statement similarto this:DELETE FROM TableA where ID1 IN(Select ID1 From TableB where ID2>= @min and ID2<=@max)Basically I want to delete all rows from TableA that have an ID in arange in TableB. This is done in a stored proc.When I look at the execution plan, it is using the indexes as I wouldhope for. The problem is that it is doing a sort which accounts for73% of the cost. I do not need to sort the results. I don't care whatorder they are deleted in.How can I prevent the sort from occuring? I need this delete to occuras fast as possible.Thanks In Advance
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
Hi all, I have a stored procedure that generates the following SQL WHERE clause UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND It is good except that it i can not remove the last AND which is not neccessary at the end of the clause. I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .
I've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
Help
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection") Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
I have lot of SQL logins which do not have access to any databases . They do not show any database access when i check their properties . My server is SQL 7.0 with SP 3 .
I want to clean up those logins with the help of script instead of deleting every login manually which is time consuming as i have to check the database access before deleting the login.
Is there any where script available or any other way to delete these unnecessary logins ? Also will it be a problem on the SQL Server if i delete those logins at one command ?
Here is a simplified version of my problem: I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully? Marcha x
Hello, I have a report that is based on a dynamic column. The column is specified by the user, and the column tyep may be text, datetime, or bigint. The user specifies the column name, and there is also a parameter that specifies the type. I allow my users to format the date via passing in a date format string, so my code looks something like:
=Switch
(
Parameters!primarycolumntype.Value = "text",
Fields(Parameters!primaryitemcolumn.Value).Value,
Parameters!primarycolumntype.Value = "date",
[DO DATE FUNCTIONS HERE, e.g. Year(Fields(Parameters!primaryitemcolumn.Value).Value)],
Parameters!primarycolumntype.Value = "numeric",
Fields(Parameters!primaryitemcolumn.Value).Value
)
The trouble I am running into is that I am getting invalid date conversion errors whenever a text value is being supplied - in other words, the code in the "DO DATE FUNCTIONS HERE" is being executed regardless of whether my primary column type parameter is text OR date. It would appear that all conversion fuctions are calculated at runtime prior to running other logic. If so, this is a bad thing for me, as that means I cannot mix data types in the same report and figure it out all dynamically. Is this true? I had always thought that the date code (in the example above, the call to Year()) would not be executed unless that statement in the switch statement was true. I've had the same luck with If statements, and Choose statements, and I've even tried using custom code.
I think my whole problem relates to a misunderstanding of how the functions are processed. Can anyone shed some light?
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .
Now there are some entries in the subscription list at the subscriber which need to be deleted . The list shows the name of the publication , the database name and the date .
I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .
Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....
Here is the code...
SELECT * FROM TABLE
order by case when isnumeric(fieldname)=1 then right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10) else fieldnameend --10 is the length of the field
The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....
I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1
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?
