sql

SQL Server: Extract Table Meta-Data (description, fields and their data types)

I am trying to find a way to extract information about my tables in SQL Server (2008). The data I need needs to include the description of the table (filled from the Description property in the Properties Window), a list of fields of that table and their respective data types. Is there any way I can extract such meta-data? I presume I ...

DB (SQL) automated stress/load tools?

I want to measure the performance and scalability of my DB application. I am looking for a tool that would allow me to run many SQL statements against my DB, taking the DB and script (SQL) file as arguments (+necessary details, e.g. host name, port, login...). Ideally it should let me control parameters such as number of simulated clien...

connection string of ADO.Net of SQL Server

Hello everyone, I am confused about the connection string format of ADO.Net when connect to a non-default instance of SQL Cluster. I did search but find various samples, could anyone let me know what is the correct format please (if I know the virtual server name and instance name)? I am using trusted connection. thanks in advance, Geo...

Normalizing data

I need to update the managerid field with the related employeeid. CREATE TABLE Employee( EmployeeID Int Primary Key Identity, Name Varchar(50), ManagerID INT default 0, ManagerName Varchar(50) default '' ) INSERT INTO Employee(Name,ManagerName) VALUES('Dilbert','Boss') INSERT INTO Employee(Name,ManagerName) VALUES('Boss','Dogbert') INSE...

Access SQL using TOP 5 returning more than 5 results?

I'm using the following statement SELECT TOP 5 rootcause, COUNT(IIF(accountability="Team 1",1,0)) FROM MOAQ WHERE CDT=1 GROUP BY rootcause MOAQ is another query that returns about 20 fields from 4 tables, nothing special. This works as expected and I get 5 results. If I add an ORDER BY clause on the conditional field though I start...

Multiple SQL Transactional Commands Across Different Database Connections

I am using the .NET 2.0/3.5 framework for my application. I need to run several SQL commands across multiple connections and each connection is on a different server (Oracle, SQL Server). I need to make sure these commands are transactional. For example: I need to perform an INSERT in a table on both Oracle and SQL Server databases, t...

SQL Server Server query - Count distinct DateTime field

Supposing we have the following records in an SQL Server table. Date 19/5/2009 12:00:00 pm 19/5/2009 12:15:22 pm 20/5/2009 11:38:00 am What is the SQL syntax for getting something like this one? Date Count 19/5/2009 2 20/5/2009 1 ...

Single SQL SELECT Returning multiple rows from one table row

We have a table which is of the form: ID,Value1,Value2,Value3 1,2,3,4 We need to transform this into. ID,Name,Value 1,'Value1',2 1,'Value2',3 1,'Value3',4 Is there a clever way of doing this in one SELECT statement (i.e without UNIONs)? The column names Value1,Value2 and Value3 are fixed and constant. The database is oracle 9i. ...

Comparing arrays in VB.NET

Let me provide a little detail to explain what I'm trying to accomplish before I get into the nuts and bolts of the question. I've got two data sources - one is a SQL Server containing part numbers, descriptions, etc. The other is a CAD system that does not have a database in a traditional sense. What I'm trying to do is read out the ...

What's the best way to update data in a table while it's in use without locking the table?

I have a table in a SQL Server 2005 Database that is used a lot. It has our product on hand availability information. We get updates every hour from our warehouse and for the past few years we've been running a routine that truncates the table and updates the information. This only takes a few seconds and has not been a problem, until no...

Attach and detach database

When i attach a Northwind database(using sp_attach_db) to SQL server 2005 ,it finished successfully. but when i run this query SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE (Categories, Description, '("sweet and savory" NEAR sauces) OR ("sweet and...

How to use the identity as a column value during an insert

I have a stored proc containing an SQL statement which is something like: CREATE PROCEDURE SaveUser @UserName nvarchar(10), @FirstName nvarchar(150), @LastName nvarchar(150) AS BEGIN INSERT INTO Users (UserName, FirstName, LastName) VALUES (@UserName, @FirstName, @LastName) SELECT SCOPE_IDENTITY() END Some users cannot...

two SQL COUNT() queries?

I want to count both the total # of records in a table, and the total # of records that match certain conditions. I can do these with two separate queries: SELECT COUNT(*) AS TotalCount FROM MyTable; SELECT COUNT(*) AS QualifiedCount FROM MyTable {possible JOIN(s) as well e.g. JOIN MyOtherTable mot ON MyTable.id=mot.id} WHERE {cond...

Data modeling with levels of detail, some of which are absent

I'm doing a data model for a roller derby league to track their matches. I track things like lap times, penalties per lap, penalties per period, and penalties per match. The problem is that in some cases, I will only have the overall data; I might have "penalties per match" for one match and "penalties per period" for another. So at the...

How do you use cfqueryparam in the ORDER BY clause?

I'm trying to be a good CF web developer and use <cfqueryparam> around all FORM or URL elements that make it to my SQL queries. In this case, I'm trying to allow a user to control the ORDER BY clause dynamically. <cfquery datasource="MyDSN" name="qIncidents"> SELECT IncidentID, AnimalID, IntakeDate, DxDate, OutcomeDate FROM Incide...

What is the difference between the two sql statements?

Good Day All we are trying to do is inside a trigger make sure the user is not inserting two fees that have 'alone' in the name. Those fees need to be handled individually. For some reason, it appears the top section of sql quit working two weeks ago. To get around it I recoded it the second way and get the correct results. What I...

Converting UUIDs from one format to another

I have an SQL document formatted as so (It has about 3000 entries like so): INSERT INTO DATA(UUID, IS_SIMULATED, ENVIRONMENT) VALUES('99b857afcb2e4f7dbf8657ea916ad9d7',FALSE,'SPACE') INSERT INTO DATA(UUID, IS_SIMULATED, ENVIRONMENT) VALUES('df8480e333c044f08ada939c66fa13f5',FALSE,'AIR') INSERT INTO DATA(UUID, IS_SIMULATED, ENVIRONMENT) ...

How can I sum and replace column data and do averaging all in a MS Access query?

I am pulling data from one table, called analyzedCopy, and using it to over-rite all information in another table called analyzed. The columns I'm sorting/filtering/manipulating, are readings_miu_id, ReadDate, ReadTime, RSSI. I am currently moving data from analyzedCopy to analyzed using the following sql that to get only the last time(h...

MySQL Type Conversion: Why is float the lowest common denominator type?

I recently ran into an issue where a query was causing a full table scan, and it came down to a column had a different definition that I thought, it was a VARCHAR not an INT. When queried with "string_column = 17" the query ran, it just couldn't use the index. That really threw me for a loop. So I went searching and found what happened,...

writing sql code in vb.net

hi, I have information entered into a text box on an ASP.net 3.5 page. when i click the submit button i would like this information written to a sql server database. Can someone please tell me what I need to do to accomplish this. The end-user should not see anything. I am using visual web developer 2008. the event handling code is pl...