I am in middle of upgrading from a poorly designed legacy database to a new database. In the old database there is tableA with fields Id and Commodities. Id is the primary key and contains an int and Commodities contains a comma delimited list. TableA: id | commodities 1135 | fish,eggs,meat 1127 | flour,oil In the new datab...
Hey all, I know I can get a list of tables from a given database with the following query: select * from information_schema.tables How do I go about excluding system tables though? Thanks, Sonny ...
So,please help me to split value from table like below field A has value below 13974;14098;14237;14269;14317;14319;14392;14393;13 257;13983;13820 and i need to split and down value in row like below file B 13974 14098 14237 14269 .... please help me to supports many thanks ...
Hi, How can I find out what other views/stored procedures are using a specific view/stored procedure? Thanks Lennie ...
Dear all, Please help me to find a solution. I have data in table like ID Code 1 123,456,789,12 2 456,073 3 69,76,56 I need to list of code in row ID Code Ref 1 123,456,789,12 123 1 123,456,789,12 456 1 123,456,789,12 789 1 123,456,789,12 ...
I have a table that I need to normalize with many fields In SQL-Server 2000. It contains 2 fields which I'm using to come up with distinct combination as defined by the specs. ID and Rate: there are multiple rows of same IDs and Rates I first created a temp table by grouping the IDs and Rates combination. SELECT ID, Count(*) AS IDCou...
Hey everyone, I'm sure this is discussed somewhere, but after searching for a while I can't find it. I'm building a stored procedure for SQL 2000 and I need to build a list of numbers that I can use in an IN function. The query looks like this SELECT DISTINCT type FROM dbo.table WHERE building = @currentBuilding This will return ...
I am still fairly new to SQL so I wanted to know if I am doing this the most optimized way. SELECT DISTINCT ACCOUNTID, ACCOUNT_NAME (SELECT COUNT(*) FROM TICKET WHERE (ACCOUNTID = OPPORTUNITY.ACCOUNTID)) AS [Number Of Tickets], (SELECT COUNT(*) FROM TICKET WHERE (ACCOUNTID =...
I am using Linq-to-SQL (C# 3.5) to read two tables, house and county, from my database (SQL Server 2000). House has a CountyID column that matches an ID column in the county table. The County table has ID and CountyName columns.There is no association in the database - I added it in the dbml file. (Parent class = County, Child class = Ho...
I am trying to populate a date table which contains every date for the next 35 years with information regarding each day. My ERP system has an accounting years table (GLRULE) which specifies the accounting periods for each specific year since many companies do not work on Calendar Months. The GLRule table contains one record for each...
Hi, I want to know the growth type (%age or MB) for database files on SQL server 2000. I Used sys.database_files files on SQl 2005 to get this information. I tried using sysfiles on SQl 2000 for this, but it wasn't good enough. Can anyone help please? Regards Manjot ...
Is there a way to find the unused tables which are nothing else but rubbish in database ? ...
SQL Server 2000. Is there any SP can list what databases are owned by a specific login(role)? Like: EXEC sp_xxxx 'myloginname' I want to see a set of database names that's owned by myloginname. Thanks. ...
This is my logic. I have an articles table and a matching images tables. The images are stored in binary format. Each image table has 2 instances of each image, this is because I have 2 sizes. 300 x 200 and 500 x 400 with their rows separated by ImageSize I want to write a stored procedure that checks if ImageSize=3 is available and if...
I am desiging a new table that will potentially have 200K rows. I would like to make sure that querys to this table are efficiant. in the past I had always given a row a unique id in the assumption that this would result in an index: CREATE TABLE [dbo].[Equipment]( [EquipID] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ...
i have a contact us table containing name emailid phoneno message repliedmessage as fields, by default the replied message field is null after replying to a particular message i am updating that field but at the same time i also want to retrieve the other values also like name and emailid using select statements ...
Hey all, I'm curently working on a project where I need to transfer data (with some minor transformations) from one database to another. Everything was going just fine when I hit my first Timestamp column... It seems SQL Server doesn't want me to use explicit values for this type of column and it, instead, wants to generate its own. ...
I recently became involved with a new software project which uses SQL Server 2000 for its data storage. In reviewing the project, I discovered that one of the main tables uses a clustered index on its primary key which consists of four columns: Sequence numeric(18, 0) Date datetime Client varchar(9) Hash tinyint This ta...
Hi, I have a SQL 2000 server with a database (ITinventory) and a table which is also called ITinventory. I would like to create a query which will look at the field 'Status', if the status is 'disposed' then I would like to set a 'location' field to 'disposed'. Many thanks ...
Please help me with this: This code works fine except for cases in which the control goes inside the IF block of "NOT EXSISTS", then any query run after the execution of this block causes the sql connection to forcibly get closed, although the results from the running of this code block are correct. I can not run other queries after run...