sql-server

More advanced logic for ordering results of a query in T-SQL?

I am currently writing a SQL query that should display a tree-view of areas inside a building with areas, sub-areas, etc. Unfortunately I have been unable to mimic the ordering used by some of our software tools. I'm restricted to MS SQL 2000 so the question of order becomes much more complicated and I'm just over my head at this point. ...

Error when trying to insert date into datetime column

Hi there, I have a form that is trying to insert some data into an SQL Server 2008 database. The form has a function to get the current date/time and then insert it into the database as follows; $now = date("Y-m-d H:i:s"); $q = "INSERT INTO ".TBL_USERS." ( username, password, userid, userlevel, email, created, updated, timestamp, fulln...

Eliminate group of NULLs from result set

Hi, I have a query that returns a result set similar to the one below: Quarter | Count ------- | ----- 1 Q2 2009 | NULL 2 Q2 2009 | NULL 3 Q2 2009 | NULL 4 Q3 2009 | 26 5 Q3 2009 | 27 6 Q3 2009 | NULL I don't want rows 1-3, because their combined Quarter-Count is NULL. But I do want rows 3-6 because at least one ...

SQL Table Rowcount different from Select Count in SQL Server

Hi, I am using Microsoft SQL Server. I have a Table which had been updated by 80 rows. If I right click and look at the table properties the rowcount say 10000 but a select Count(id) from TableName indicates 10080. I checked the statistics and they also have a rowcount of 10080. Why is there a difference between the Rocount in Prope...

How to bind Image field to PictureBox in VB.Net 2008

Hi all, I have a table with image field that I need to bind to System.Windows.Forms.PictureBox, and I need to Load new images o Overwrite existing images or delete the image int the field with ADO.NET dataset object. ...

TSQL Sweepstakes Script

I need to run a sweepstakes script to get X amount of winners from a customers table. Each customer has N participations. The table looks like this CUSTOMER-A 5 CUSTOMER-B 8 CUSTOMER-C 1 I can always script to have CUSTOMER-A,B and C inserted 5, 8 and 1 times respectively in a temp table and then select randomly usi...

Not able to get desired output while generating xml files from SQL query from SQL Server

I am executing this query select category "ROOT/category", question "Category/question", option1 "Category/option1" from testDB2 for XML PATH ('ROOT') , ELEMENTS Presently the database has three entries and the xml file i get is this <ROOT> <ROOT> <category>maths</category> </ROOT> <Category> <question>2+2?</question>...

SQL Server 2005 Identity Specification not respecting seed value

I am in the process of converting an Access database to SQL Server 2005. I have successfully migrated the data and original schema using SSMA and am now in the process of normalizing the database, which requires me to add a few unique identifiers. Some of the columns we had were previously created using an AutoNumber data type, which is...

Getting Monthly Data Even If Empty

I'm trying to get a report built up from data mining our accounting software. We have a table that stores the balances of each account in a general ledger for a given period (which is 0-12, 0 being carry over from last year, 1-12 being the corresponding month), the amount, and other data I don't need. I'm trying unsuccessfully to get ...

Statistics update seems to be causing problems

Woke up this morning with a page that our cluster was down. It came back up right away. I found log error logs with entries about IO taking longer than 15 seconds. Our monitoring server had tried to ping the server and had a timeout error. I checked one of our monitoring tools to see what was going on at 4:30 in the morning. It seems to...

ASP.net site designed/built on SQL Server Transistion to DB2

OK long story short, we designed and built a web application connecting to a standalone SQL Server 2000 database using asp:SqlDataSource and System.Data.SqlClient, now we are looking at migrating to a db2 cluster. Aside from the connection string, do we need to do anything on the web application? I am clueless about DB2. We have close to...

Create a SQL Server database on a remote machine mdf file path issue

I'm trying to create a MS Sql Server database on a database instance running on a remote machine. When I'm doing so I need to be able to specify the path to the database (.mdf) file. If I try to create a database in a folder which doesn't exist, SQL Server will just fail (wouldn't it be nice if it created the folder structure automatic...

Select MAX(field)+1 FROM ... Concurrency issues

Hello Im afraid about concurrency on partner application cause in the last days it was having troubles with CRUDS Operations, especially with inserts. So I ran SQL Profiler and note that his insert statement dont use transaction and also he is using : INSERT INTO TABLEA VALUES ( (SELECT MAX(NUMBERFIELD) +1 FROM TABLEA), ....); How avo...

How to set an array value using sp_OASetProperty

I have a stored procedure that manipulates some data in an excel spreadsheet. I am importing data by creating a QueryTable object. I would like to set TextFileColumnDataTypes property for the object but I can't figure out how to pass in an array to the sp_OASetProperty routine. This is what my code looks like but obviously it doesn't wo...

How do I publish an alteration to a replicated stored procedure (SQL 2000)?

We have several stored procedures marked as articles for replication in our SQL2000 database. If we update any of them via ALTER PROCEDURE, the changes are applied to the master, but never published to subscribers. Am I missing a setting, or does SQL require a complete reinitialization/snapshot to move the changes out? ...

Caching to a local SQL instance on a web server

I run a very high traffic(10m impressions a day)/high revenue generating web site built with .net. The core meta data is stored on a SQL server. My team and I have a unique caching strategy that involves querying the database for new meta data at regular intervals from a middle tier server, serializing the data to files and sending those...

multiple cte in single select statement where ctes can refer to each other ...

Expanding on following question (Multiple Select Statement) I would like to know if I can do following: WITH cte1 as ( SELECT * from cdr.Location ), cte2 as ( SELECT * from cdr.Location WHERE cdr.Location.someField = cte1.SomeField ) select * from cte1 union select * from cte2 So accent here is on ...

Seek & Scan in SQL Server

After googling i came to know that Index seek is better than scan. How can I write the query that will yield to seek instead of scan. I am trying to find this in google but as of now no luck. Any simple example with explanation will be appreciated. Thanks ...

Does anyone have any performance metrics of ADO.NET connection pooling vs. the create-and-destroy method?

I'm using WCF, SQL Server and ADO.NET. I'm looking at two implementation options for the data access layer. The Enterprise Library that uses connection pooling A custom solution that does not use connection pooling. Every time the database is accessed a connection is created, used and then destroyed. Option 2 looks like this: using ...

Integration Services vs raw SQL (T-SQL)

When would you use Integration Services and when would just use SQL/stored procedures? What are the advantages of using one or the other? If you are migrating data from a legacy system, would you use SSIS or just SQL? ...