SQL

(why do INSERT and UPDATE have to be so different to each other, anyway?)

CREATE PROCEDURE St_R334b
@Region varchar(12),
@fYear char(4),
@fMonthStart char(2),
@fMonthEnd char(2)
AS
BEGIN

DECLARE @Months int
DECLARE @TotalRev float

SELECT @Months = convert(int,@fMonthEnd) - convert(int,@fMonthStart) + 1

SELECT @TotalRev = Sum(t.MON_REV_C)
FROM tdrs_data AS t
WHERE t.fYear=@fYear AND (t.fMonth BETWEEN @fMonthStart AND @fMonthEnd)

SELECT c.CorporateID, c.CorporateName, c.Region,
Sum(t.MON_REV_C) AS mon_rev_c_sum,
Sum(t.MON_REV_C)/@TotalRev AS mon_rev_pct_of_ttl,
Sum(t.MON_REV_P) AS mon_rev_p_sum,
Sum(t.MON_PAX_C) AS mon_pax_c_sum,
Sum(t.MON_PAX_P) AS mon_pax_p_sum,
rev_class =
CASE
WHEN ((Sum(t.MON_REV_C)/@Months) * 12) > 3000000 THEN 1
WHEN ((Sum(t.MON_REV_C)/@Months) * 12) > 1000000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 500000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 100000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 3000000 THEN 'Over $3 MM'
WHEN ((Sum(t.MON_REV_C)/@Months) * 12) > 1000000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 500000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 100000 AND ((Sum(t.MON_REV_C)/@Months) * 12)

What's the reason for using a sub-DOS CLI in 2004? Not that I mind using a command line--but why can't it act more like BASH? My problems are really more with Oracle than with SQL.

http://www.cygwin.com/

What is it good for
selecting stuff from places where things are like %other things.
-- dave225 (right.knewi...), November 5th, 2004 2:10 PM. (Dave225) (later) (link)

--------------------------------------------------------------------------------

bother things? mother things? what what what?
-- Mr Noodles (infinitecow...), November 5th, 2004 2:11 PM. (Mr Noodles) (later) (link)

xpost

*RUNS AWAY*

SQL joins are one of the things that are a pain in the ass to learn to master, but once you can do them and understand why you'd want an inner join vs an outer join, you will be happy. And a huge geek.

fairly good joins 101: http://www.onjava.com/pub/a/onjava/2004/01/07/SQLJoins.html

Thank goodness I don't have to worry about this stuff any more.

use HAVING after the group statement.

e.g. HAVING COUNT(1) > 4

Stupid Microsoft Query.

I wish you could manifest Inner Joins within a left outer. Instead you have to create a view and link to that!

In my oracle database no one else ever goes in there, so I can create all the views I want.

In a nutshell, we've got data for our organisation's contacts and such like hosted remotely on an SQL database. We've made significant changes to it recently, and wanted to upload new tables. To cut a long story short, our data hosts say that their insurance indemnity prevents them from assisting us, which means we can't upload the data we need to upload.

It appears we have two choices, neither of which I'm familiar with how to do:

1) Our database is a SQL Server one, so we need to create a new database in that, then either send our host company the raw datafiles (detached database) or a backup (.bak file) of the contents.

2) Setup a DTS job to import the records directly onto the SQL server from our local server

I'm afraid that I'm utterly stumped on both of these, and need some help. Can anyone advise, or even better, be able to help directly?

Yours hopefully.

I would give more explicit steps but I'm kind of going off dim memories here and I'm late for work.

The big problem *I* had was that the wizard didn't know how to take account of foreign key relationships. So, the package it created wouldn't run, because it would try to import data into the new tables in the wrong order. The only way to get around it was to use the wizard's output as a starting point, make notes on where it failed, then use the DTS package editor to add dependancy data to the package - "do not copy B until A is complete" and so on. The DTS editor is graphical, so this is a matter of dragging arrows around on the screen.

xpost: as Dan says, detaching and transferring the data file is probably easier for what you're doing.

- We had an access database
- That became an SQL one
- I copied the tables into Excel, did some clean up on them and want to put them back into the SQL databse.

The hosting company said nope, they couldn't write over the old tables with the new ones as their insurance didn't indemnify them for this. This means I have 4 tables in Excel which I want to put back into our SQL database / create a brand new database with. This is what I have no idea how to do - any tips? Better still, would anyone on here be able to do it for me? Happy to pay that special someone...

Just create an Excel connection to the table, and Data Transformation Task to the SQL Server database you want to copy it to, bob's your uncle.

Can I just create a new set of tables on Enterprise Manager and import the data from excel tables and then point the Access database at those new tables instead, and the leave the old tables there to slowly degrade, accuracy wise?

who set up the original tables in the first place?

To Ken's - it is lost in the mists of time

Alternatively, do something like what Ken suggested: make new tables with backup copies of the old data, then delete the actual tables' contents and replace with the new data.

OK so I usually use SQL Server 2000 so am not used to dealing with MS Query (in Excel)

How do I use date functions in Query??

I want to do something like this:

SELECT * FROM [TABLE]
WHERE order_date = getdate()

But Query won't accept getdate, now, date, or anything else I can think of. Is it just too fucking shit to deal with date functions? There's nothing in Help about it.

I have a workaround using a parameter, i.e.

SELECT * FROM [TABLE]
WHERE order_date = ?

and then put =today() in a cell on teh Excel sheet and use that as the parameter, but that can't be the best way to do it, surely??

?

-- Jaq (js...), February 23rd, 2006 3:09 PM. (Jaq) (link)

I'm now attempting to use it and it's fucking horrible. I didn't have too much trouble using DTS apart from a couple of bugs here and there, but this SUCKS.

It's not even stable. I was copying 700,000 rows from one server to another, and it ended up inserting the data from 500,000 of them into one row. I ended up with a varchar field of length 512,000,000. I've since worked out this happens when the server is running low on memory, so I can't run anything else when an SSIS package is running. Great.

Today I found that the SQL Server Destination object is basically useless, as trying to copy a datetime field from OLE DB Source (also SQL Server) to SQL Server Destination will fail with a type conversion error, even though the source & destination columns are both datetime. You have to use an OLE Destination DB object for it to work.

And apparently the migration tool to upgrade DTS to SSIS is so unreliable even Microsoft have advised not to use it.

ARGH. I know it's a Microsoft product, so I shouldn't be surprised, but this is SO BAD it defies all expectation. I'd rather just write everything in stored procedures than use this, but I don't think my manager will agree unfortunately, so I'm stuck with it :'(

declare @cmd nvarchar(4000)
set @cmd = 'bcp "SELECT TOP 1 Photo FROM dbo.ImageWarehouse" queryout "c:datatest2.jpg" -T -n'

exec xp_cmdshell @cmd

hey so like.. i'm running a website on drupal and somehow ran out of memory on my virtual machine - bringing it to its knees! are there any simple diagnostic tools i can try to see if particular queries are being extra hoggy?

never used drupal but it's a php framework, right? what DB are you running under it? mysql?

i spent WAY too much time today setting up the MySQL-python-1.2.2 lib. I love open source- build, error. google error. fix file. build. get new error. google. fix new error. build. reads config wrong. fix config. build. repeat 3 more times. finally install. 25 minutes down the drain. on the other hand, it's a nice lib once it's running.

getting ruby on rails to work with mysql on Leopard was the delightful way I spent my entire day.

My best is that it's not MySQL going badzooks, Tracer. Use top or a similar tool to track down who's hogging memory at the process level. That'll at least allow you to rule out MySQL.

I was trying to re-install mysql because I'd forgotten the root password, and was trying to follow the instructions <a href="http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html";>here</a> except that at the step where I'm supposed to enter "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld.exe", something funky happened. Now, every time I boot up my computer, mysqld.exe crashes. I can at least bring up the mysql console, though I can't do anything because I don't have the password. A regular uninstall/reinstall doesn't fix things. Is there a way to unfubar things, or at least start over with a clean uninstall?

Nuke the database directory. Also, you don't need to reinstall to reset the root password on MySQL.

Database directory being where Mysql is installed?

Not necessarily. The directory where all the database files are kept. I don't know where this is on Windows.

running Mysql on windows is just wrong. but yeah, one of your endless config files should tell you where you have that directory.