Date: 09/19/05     Keywords: database, sql

Alright, here's the scenario. I have 65 million row table of zip codes... the table just has three columns: zip_cd varchar(10), lattitude, and longitude values. Zip_cd is varchar(10) because we have to store zip + plus 4 values in "xxxxx-xxxx" format... and no, my senior DBA has already overruled me adding two more numeric columns which would offer more optimized indexing. There's a clustered index on the zip_cd and we only ever run lookups on zip_cd, to get lat/long values.

Anyway, we have one simple query as follows:

--

DECLARE @zip_cd varchar(10)
SET @zip_cd = '60440-0001'

IF EXISTS(SELECT zip_cd
FROM serverX.databaseY.dbo.geodata
WHERE zip_cd = @zip_cd)
PRINT 'one'
ELSE
PRINT 'two'

--

Starting late last week, one of our servers running this query started taking forever and a day. Upon running it manually and checking the execution plan, I see that SQL Server, in all of its wisdom, decided to run the "SELECT zip_cd" portion WITHOUT the WHERE clause on the remote server, THEN apply a "filter" step for the WHERE clause. I attempted to replicate this on other servers and 2/3s of our other servers runs the query correctly, by sending everything to the remote server, whereas 2 other servers also just pull all 65 million rows down THEN filters it locally.

So my question is this... how in the hell can I force SQL Server to run the query in one fashion, rather than another? I know about using table and query hints, but the problem is that this is a remote server scenario, which Query Analyzer was complaining that index hints won't work.

Help?

x-posted...

Source: http://www.livejournal.com/community/sqlserver/34761.html

Date: 09/19/05     Keywords: database, sql

Alright, here's the scenario. I have 65 million row table of zip codes... the table just has three columns: zip_cd varchar(10), lattitude, and longitude values. Zip_cd is varchar(10) because we have to store zip + plus 4 values in "xxxxx-xxxx" format... and no, my senior DBA has already overruled me adding two more numeric columns which would offer more optimized indexing. There's a clustered index on the zip_cd and we only ever run lookups on zip_cd, to get lat/long values.

Anyway, we have one simple query as follows:

--

DECLARE @zip_cd varchar(10)
SET @zip_cd = '60440-0001'

IF EXISTS(SELECT zip_cd
FROM serverX.databaseY.dbo.geodata
WHERE zip_cd = @zip_cd)
PRINT 'one'
ELSE
PRINT 'two'

--

Starting late last week, one of our servers running this query started taking forever and a day. Upon running it manually and checking the execution plan, I see that SQL Server, in all of its wisdom, decided to run the "SELECT zip_cd" portion WITHOUT the WHERE clause on the remote server, THEN apply a "filter" step for the WHERE clause. I attempted to replicate this on other servers and 2/3s of our other servers runs the query correctly, by sending everything to the remote server, whereas 2 other servers also just pull all 65 million rows down THEN filters it locally.

So my question is this... how in the hell can I force SQL Server to run the query in one fashion, rather than another? I know about using table and query hints, but the problem is that this is a remote server scenario, which Query Analyzer was complaining that index hints won't work.

Help?

x-posted...

Source: http://community.livejournal.com/sqlserver/34761.html

Date: 09/19/05     Keywords: sql

What do smart people do on a Saturday night? Play Smath of course and argue over the validity of using parentheses in mathematical equations like this:

12 = (7) + 5

I still think it's completely valid, see SQL Server confirms it:

SELECT (7) + 5
--yields
--12

So after demonstrating that it was valid in SQL I was basically told that SQL Server is like retarded or something and like it doesn’t adhere to real mathematical principles?

Any opinions out there? Is SQL Server math stupid, is there a real "math property" that supports this? Any opinionated mathematicians out there that can give some insight?

Source: http://www.livejournal.com/community/sqlserver/34391.html

Date: 09/19/05     Keywords: sql

What do smart people do on a Saturday night? Play Smath of course and argue over the validity of using parentheses in mathematical equations like this:

12 = (7) + 5

I still think it's completely valid, see SQL Server confirms it:

SELECT (7) + 5
--yields
--12

So after demonstrating that it was valid in SQL I was basically told that SQL Server is like retarded or something and like it doesn’t adhere to real mathematical principles?

Any opinions out there? Is SQL Server math stupid, is there a real "math property" that supports this? Any opinionated mathematicians out there that can give some insight?

Source: http://community.livejournal.com/sqlserver/34391.html

Date: 09/15/05     Keywords: no keywords

Does anyone know of a size limit for exporting to Excel in Reporting Services?

I have been deploying reports extensively in RS and the only problem I've run into concerns large reports. Large is around 20000 rows with 30 columns. Either my machine looks up (Excel using 99% CPU) or I get a "Server Unavailable" error.

Anyone else have this happen? Any ideas?
Thanks!

Source: http://www.livejournal.com/community/sqlserver/34096.html

Date: 09/14/05     Keywords: asp, sql, microsoft

Alright... imagine this scenario. Server 1 has two tables. Server 2 has views to server 1's two tables.

From Server 2, you're attempting to execute an UPDATE on said views. (No, you cannot simply update server1 and the two tables directly, that's not an option so don't ask.) If you run a single UPDATE with a WHERE clause against view 1, it'll work fine.

BUT if you add in an INNER JOIN constraint against view 2, instead of updating just the handful of records you wish to UPDATE , it'll UPDATE EVERYTHING!!!!

Example:

-- Server1
CREATE TABLE tempOne (
rowid int IDENTITY(1, 1),
myvalue int
)

CREATE TABLE tempTwo (
rowid int
)

-- Server2
CREATE VIEW viewOne AS (SELECT * FROM server1...tempOne)

CREATE VIEW viewTwo AS (SELECT * FROM server1...tempTwo)

-- Insert 5 random values into tempOne then insert '2' and '4' into tempTwo, which will later be used as a constraint

-- Server2
-- This works just fine, should return rowid 2 and 4 of viewOne.
SELECT *
FROM viewOne t1
INNER JOIN viewTwo t2
ON t1.rowid = t2.rowid

-- This is what does NOT work
UPDATE viewOne
SET myvalue = myvalue * -1
FROM viewOne t1
INNER JOIN viewTwo t2
ON t1.rowid = t2.rowid

Instead of rowid 2 and 4 being UPDATED, ALL 5 rows will be UDPATED. What GIVES?!?! I see via SQL Profiler that the query is sent from Server2 to Server1 and redefined there in a really messed up fashion. Am still trying to wrap my head around how I can rewrite this query. And no, I cannot not use the views and directly jump to Server1 because in my real scenario, it could be ServerX... I don't know until runtime, hence why we have the generic views.

Help?

--

UPDATE: Alright, I've done some more digging and figured a few things out.

First, this has NOTHING to do with views. The problem has to do with remote server UPDATE statements. I ran some more tests without views but with direct table references as a WHAT IF, and ran into the same results. But when I'd tweak the UPDATE statement slightly, I managed to generate a SQL Server error which I did a lookup on and found the following:

http://support.microsoft.com/default.aspx?scid=kb;en-us;814581

Basically the table I'm updating in question, doesn't have any PKs or UNIQUE constraints on it. My basic example, that would be easy to toss in. However the actual Production table I'm dealing with does NOT have any PKs, nor can I enforce any (don't ask, makes me want to sob). In other words, I'm basically screwed.

*sigh*

Source: http://www.livejournal.com/community/sqlserver/33825.html

Date: 09/08/05     Keywords: mysql, database, sql, postgresql, web, linux, microsoft

(my background)
Using MySQL and PostgreSQL on FreeBSD and Linux machines
(the problem)
I have four databases (actually MDF and LDF files) from a client. They want to know if they can extract data from the four databases by year and place them in to individual databases. This sounds ok, but I am new to Microsoft's SQL server. I sacrificed one of my personal dev machines and installed 2k3 Enterprise and 2000 SQL server. Created new databases with the same name as the files and then replaced the new files with the original ones from the client. Super.
Now what?

Again my background is in the command line/web front end so I am running a little blind. What is the simplest way to see the data in each database? What are some _quality_ sites I can read (because googling information you have no idea about takes time to filter out the bs).

Source: http://www.livejournal.com/community/sqlserver/33194.html

Date: 08/31/05     Keywords: database, sql, microsoft

I use Microsoft SQL Server 2000, for use with Microsoft Great Plains. Right now, I am working on building a backup server, incase the main server goes down (which has happened 2 times in the past 6 months).

My question is, is there a way to restore a database using a script that could be run from a Windows Command Box (cmd.exe)?

If you need more information, just let me know.

Thank you very much,

Peace,
Kevin

Source: http://www.livejournal.com/community/sqlserver/32532.html

Date: 08/26/05     Keywords: sql

I'd like to create an column in one of my tables that contains unique five digit numbers to use as an identifier. I'm a bit of a SQL Server newbie, so forgive me if this is simple. I found the identity() property, which is works well enough to give me ascending numbers, but I want my numbers to always have five digits.

So I'd want the first record to be 00001, the second to be 00002, the third to be 00003, etc. Right now, I'm getting the first record as 1, the second as 2, the third as 3, etc.

Thoughts?

Source: http://www.livejournal.com/community/sqlserver/32440.html

Date: 08/26/05     Keywords: no keywords

How do you allow shared development of a Visual Studio.Net 2003 project, such that
everyone in a team can see a project and make modifications to it?

Source: http://www.livejournal.com/community/sqlserver/32082.html

Date: 08/22/05     Keywords: no keywords

Does anyone know of a way to include column headings in a bcp extract which uses a query. I've searched through the bcp options, and I can't find anything. There's got to be a way!

Source: http://www.livejournal.com/community/sqlserver/31891.html

Date: 08/18/05     Keywords: programming, database, sql

As part of my job I use T-SQL, mostly for writing stored procedures. So I'm familiar w/ select/insert/update/delete/cursor/view etc.. But I never created a complicated database or a table schema from scratch. We have a DBA who usually does that.

Now, our DBA is swamped, so my boss wants me to take a part of our project and design a database for it (to be reviewd by our DBA). While it's flattering that she thinks so highly of my abilities, I feel that I need some serious education before I take this on.

Please, advise me on a good book on database design!!! Preferably a clearly-written easy-to-read one (think Kernighan & Ritchie's "The C Programming Language ", not Stroustrup's "The C++ Programming Language"). If you don't know an easy-to-read one, a hard-to-read-but-very-informative will do!

TIA

Source: http://www.livejournal.com/community/sqlserver/31387.html

Date: 08/17/05     Keywords: database, asp, sql, microsoft

Okay, so I really have no idea what I'm doing with this, so I'm sorry if this seems kinda dumb.

I'm trying to install the "Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A" on the computer here, and it seems to install fine.

I go to the command line and run the setup file with my chosen password. No problem (I hope).

My end goal is to try and get one of the ASP.NET starter kits to install and think there's a database system on this computer (So I don't have to use MS Access. Ew.), but whenever I go to install the program, I choose the database on the LocalHost, and it gets no connection.

So, I'm wondering, is there something else I have to do to start up the Desktop Engine and get it running?

I'm kind of flying blind here, and the read-me doesn't make a whole heck of a lot of sense.

So, thanks for any help you can give me!

Source: http://www.livejournal.com/community/sqlserver/31202.html

Date: 08/16/05     Keywords: sql

Related to my last post with only the most tenuous of links. (I've been trying to see what's been going on with these stupid tables, which takes a lot longer than three minutes, but am too damned lazy to run the queries in Query Analyzer.)

Is there a simple way of importing VFP (6.0) tables containing memo fields into SQL Server (2000)? No matter how I try to do it, the import completely ingores the memo fields. E.g.:

VFP
column 1 - character(5)
column 2 - memo
column 3 - memo
column 4 - int

SQL
column 1 - character (5)
column 2 - int
column 3 - filled with NULLs
column 4 - filled with NULLs

I've tried going straight through ODBC and copying out to text then importing from SQL from the text file; same result.

This is something that's plagued me on and off throughout the years, but now I'm actually trying to do it for a project at work instead of for my own gratification, so I've got people who need this stuff...

thanks.

Source: http://www.livejournal.com/community/sqlserver/30747.html

Date: 08/16/05     Keywords: no keywords

"There has been no recent activity in the results pane, so the results pane will automatically be cleared in one minute to free server resources."

Is there any way to stop this process from occurring, both the annoying dialogue box popping up and the clearing of the results pane?

Source: http://www.livejournal.com/community/sqlserver/30527.html

Date: 08/12/05     Keywords: asp, sql, microsoft

gotta love it when you research a weird error you're getting and microsoft says this.

SYMPTOMS
When you use the Microsoft Visual Studio .NET debugger to debug an ActiveX Data Objects (ADO) application that contains Transact-SQL code, you may receive the following error message:
System.Data.SqlClient.SqlException: General network error. Check your network documentation.
You may notice this behavior when you restart Microsoft SQL Server while debugging the ADO application.
Back to the top Back to the top

STATUS
This behavior is by design.

...

BY DESIGN?

ok, microsoft. you win today. :|

anyways, on a related but not so related note.. our middle tier component that handles data connections started giving us the infamous "General network error. Check your network documentation." upon an executenonquery call on a stored procedure after we applied service pack 4 on SQL Server. anyone know why oh why SP4 is doing this?

please enlighten while i sniff out the chatter on the .NET boards.

xposted to aspdotnet and csharp

Source: http://www.livejournal.com/community/sqlserver/30271.html

Date: 07/06/05     Keywords: database, sql

Alright, here's an oddball situation for everyone. Seems we have a client that is running the above version of SQL Server 7.0 - SP3 on NT4.0, and is attempting to upgrade to SP4. But the SP doesn't seem to be applying itself. From what I've been told, they check the version numbers after applying the SP and rebooting, and it continues to remain the same, not switch over to the newer SP4 version number. And now this got passed off to me (being the lowest on the already short totem pole), to attempt to research.

Whee... don't suppose anyone out there might miraculously have any insight into this?

And to answer some of the obvious questions, no I have no idea why they just don't upgrade past NT4 and 7.0... no, I don't have direct or even indirect access to the machine, and cannot attempt to apply the service pack myself. SQL Server continues to chug along happily. No errors are logged anywhere as far as I know either.

x-posted to databases

Source: http://www.livejournal.com/community/sqlserver/30199.html

Date: 06/28/05     Keywords: sql

Pretty much any moron can write SQL queries.

But can you write them into a poem using formal meter?

You should try it - what have you got to use?

Source: http://www.livejournal.com/community/sqlserver/29720.html

Date: 06/27/05     Keywords: asp, sql

Kimberly Tripp is a badass and you can find a 98 minute interview with her on .NET ROCKS.

Kim Tripp is back and more intense than ever. After talking about her famous USB Key demonstration of SQL 2005 partitioning, she gets into details of SQL 2005 that would make even the SQL team go "hey, that's cool!" This is one of those shows that you'll have to listen to several times to completely grasp. We know we will!

http://perseus.franklins.net/dotnetrocks_0110_kimberly_tripp.mp3.torrent

Source: http://www.livejournal.com/community/sqlserver/29642.html

Date: 06/26/05     Keywords: database, sql, microsoft, google

Hello everyone. I apologize in advance for being the typical newbie at this sort of thing, but I don't really have a choice. I just landed a new summer job at an Embassy and I really need some help.

I have been giving the task of sorting out maps in my department. I'm to build a database where I index all of the maps so that people in the Embassy can have easier, more organized access to them. However, I have never, ever done anything like this before and unfortunately I am stuck with Microsoft Access (I've already been told that SQL is far superior to MA). Believe me, I would switch to SQL in a heartbeat because I have heard that it is more user-friendly, but MA is what I've been given to work with and I cannot download anything onto my computer.

So far I've set up tables and created a couple of forms. I've probably already done something wrong just through that. I realize that people take courses to learn how to use MA and it's going to take more than a few LJ comments to help me get through this. What I was hoping you guys could direct me toward are some detailed MA tutorials online. I'm having specific problems trying to understand what 'queries' are and how to set up a main switchboard. The purpose of this index is to serve as sort of a search engine to look up these maps, and I don't know how to do that.

So if anyone has any links to tutorial sites, hopefully more specific to what I'm trying to do, that'd be great. I've already Googled for help and I've found a few tutorials, but I'm hoping for even more help.

Sorry about my sob-story. I'm really in a bind here. MA is making me feel really, really unintelligent.

Source: http://www.livejournal.com/community/sqlserver/29345.html