Re: Please Heeelp - connection error driving me crazy - Database Discussion Boards

kozu31-May-07 6:01

31-May-07 6:01

Kubben,
Thanks for the reply, but changing the where clause didn't work. I need to change the 'convert' part of the select in order to alter how the data is being displayed, but to what?

I tried casting it to char, and it partially fixed my problem:

select cast(OrderDate as char(11)), SUM(Qty)
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by cast(OrderDate as char(11))
order by cast(OrderDate as char(11))

The result is:

Jun 1 2005 2
Jun 2 2005 1
May 30 2005 1

but now the problem is that the ordering will be done alphabetically and not by date.
stuck again Frown | :(

Re: Convert SQL datetime to just date

Shahil Shah31-May-07 6:13

Shahil Shah

31-May-07 6:13

Hi
If you are viewing the data in a datagrid you can use the following data formating expression:
{0:dd/MM/yyyy} without the need to use the convert staement you are using

otherwise try this:

SET DATEFORMAT mdy
select CAST(OrderDate as varchar(11)) as OrderDate, SUM(Qty)
where OrderDate between '05/29/05' and '06/10/05'
group by OrderDate
order by OrderDate

Shahil

Re: Convert SQL datetime to just date

kozu31-May-07 6:24

kozu

31-May-07 6:24

Shahil,
The 'order by' will do the sorting in alphabetical order, since now OrderDate is a string, so I'll get 'June' before 'May'.
I need to be able to still sort by date.

thanx

Re: Convert SQL datetime to just date

kozu31-May-07 6:36

kozu

31-May-07 6:36

okay,
I think I found a way around this: add a dummy column and sort by that

select cast(OrderDate as char(11)), SUM(Qty),
convert(datetime,convert(char(10),OrderDate,101))
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by cast(OrderDate as char(11)), convert(datetime,convert(char(10),OrderDate,101))
order by convert(datetime,convert(char(10),OrderDate,101))

Re: Convert SQL datetime to just date

Shahil Shah31-May-07 6:38

Shahil Shah

31-May-07 6:38

Hi kozu

try this:

create table #tmp
(
OrderDate datetime,
qty int
)

insert into #tmp(orderdate, qty)
select OrderDate, sum(qty)
from orders
where OrderDate between '05/29/80' and '06/10/05'
group by OrderDate
order by OrderDate

select CAST(OrderDate as varchar(11)) as OrderDate, Qty
from #tmp

DROP TABLE #tmp

Shahil

Re: Convert SQL datetime to just date

Michael Potter31-May-07 9:45

Michael Potter

31-May-07 9:45

Form your result set then use it as the source for the final query where you can format your data.

SELECT
    CONVERT(VARCHAR(20),OrderDate,101) AS OrderDate,
    TotalQty
FROM
    (
        SELECT  
            CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME) AS OrderDate, 
            SUM(Qty) AS TotalQty
        FROM 
            Orders
        WHERE 
            CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME) between '05/29/05' and '06/10/05'
        GROUP BY 
            CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME)
    ) AS subqry
ORDER BY 
    OrderDate

Speed wise, I think kubben is correct. I would remove the CAST/CONVERT in the WHERE clause of the subquery with a little intelligent manipulation of the date ranges.

Please Heeelp - connection error driving me crazy

ComCoderCsharp31-May-07 4:11

ComCoderCsharp

31-May-07 4:11

Hi guys

I'm working in Visual studio on a website and using ADO.NET to connect to my pubs sql-database. I keep on getting error messages when I test the website in my browser. Is there anyone who can tell me what could be wrong - I've been through all the configurations steps e.g setting the remote connections... at least I think so...

I've gotten error messages like "login failure" and "The user is not associated with a trusted SQL Server connection" what might be wrong???????????

The strange thing is that if I chooe e.g. a gridview from the toolbox there is no problem at all for this autogenerated gridview to connect to the server - how can this be? - I've tried to use the autogenerated connection string from the toolbox gridview, but that doesn't seem to help...

her is some connection string that I have tried out
string CONN_STRING = "Server=.\\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI";
or
string CONN_STRING = "Server=.\\SQLEXPRESS;Initial Catalog=pubs;uid=sa;pwd=";

please someone help me...

All the best
Al

Re: Please Heeelp - connection error driving me crazy

Colin Angus Mackay31-May-07 4:17

Colin Angus Mackay

31-May-07 4:17

ComCoderCsharp wrote:
how can this be?

Because Visual Studio is running in your account. The web application is running in the ASPNET account - You have to tell SQL Server about this by adding ASPNET as a windows authenticated login and giving it permission to use the appropriate database.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Please Heeelp - connection error driving me crazy

ComCoderCsharp31-May-07 5:07

ComCoderCsharp

31-May-07 5:07

Hi thanks for the reply

Maybe this could be the solution.. but it leads me to another question: How do I add windows authenticated login and give permission to the appropriate database, I can't seem to figure it out...sorry if this is a stupid question, I'm kind of new to configuring the database connections...

Thanks in advance
Al

Re: Please Heeelp - connection error driving me crazy

Colin Angus Mackay31-May-07 8:13

Colin Angus Mackay

31-May-07 8:13

In SQL Server 2000 use the Enterprise manage. In the security tab for the server you can add the log in. In the users tab for the individual database you can then set up access to the database and the objects it contains.

In SQL Server 2005 there will be a similar mechanism in the SQL Server Management Studio.

How to check if a field already exists in a table

SWDevil31-May-07 3:13

SWDevil

31-May-07 3:13

Hi,

I need to check if a field exists in a certain table (in an SQL database), and if not then add that field to the table.
For Instance I have Table "A" that should contain field "a". So I want to check if field a is already defined for the table, and if not then add it.
How can I d o this? Is there an SQL statement to check if a field exists?

Thanks

Re: How to check if a field already exists in a table

Colin Angus Mackay31-May-07 4:14

Colin Angus Mackay

31-May-07 4:14

SWDevil wrote:
Is there an SQL statement to check if a field exists?

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName')
BEGIN
ALTER TABLE ....
END

Re: How to check if a field already exists in a table

SWDevil31-May-07 5:12

SWDevil

31-May-07 5:12

If I need to add more than one fields can I write than one ALTER statements:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName')
BEGIN
ALTER TABLE ....
ALTER TABLE ...
ALTER TABLE ...
ALTER TABLE ...
END

Re: How to check if a field already exists in a table

Colin Angus Mackay31-May-07 8:14

Colin Angus Mackay

31-May-07 8:14

You can write as many ALTER TABLE statements as you like. Or you can put it all in one ALTER TABLE statement. It is up to you.

Normalising Single Table to Two

Brady Kelly31-May-07 2:06

Brady Kelly

31-May-07 2:06

I have a single table with many phone numbers per person, i.e. person data is repeated for each phone number for that person. I while ago I normalised this using SQL updates, but I can't remember how I did it and maybe here someone can give me a better way anyway. The original table has no PK, and my first step is to get all unique person data, using distinct, into a new Person table. I then want to extract phone number columns from each record, and add a column to tell which phone record belongs to which Person record.

My problem is, using distinct to get unique Person data precludes including any ID unique to a Phone record, and I have to real columns that I can use to pair new Person records with Person records in the source table except comparing all columns, and some are null, so this is out. Any suggestions?

Re: Normalising Single Table to Two

kubben31-May-07 2:34

kubben

31-May-07 2:34

I would add a new column to the existing table and update it with the primary key you created based off it matching to your new table. So your primary key would be duplicated in the old table, but that will allow you to link properly when creating your phone table.

Hope that helps.
Ben

Re: Normalising Single Table to Two

Brady Kelly31-May-07 3:17

Brady Kelly

31-May-07 3:17

My problem is matching records in the new and old tables! I have to basically compare all fields from the old table to corresponding fields in the new table, and in T-SQL comparisons don't work with null values. So, I have to, just for one field, for example:

and (new.IDNumber = old.IDNumber) or (new.IDNumber is null and old.IDNumber is null)
etc.
etc.

<edit>
I've just been made privy to the trick of using isnull() on all the field comparisons.

"A little learning is a dangerous thing; drink deep, or taste not the Pierian spring: there shallow draughts intoxicate the brain, and drinking largely sobers us again.", by Alexander Pope

My Blog

Re: Normalising Single Table to Two

kubben31-May-07 3:23

kubben

31-May-07 3:23

Well, if you are able to write a distinct select statement to create the new table to create a primary key, then you can use those same columns to update the existing table with the primary key. I don't know if you have many other choices.

Ben

Re: Normalising Single Table to Two

Brady Kelly31-May-07 3:51

Brady Kelly

31-May-07 3:51

I didn't, so thanks. With your suggestion and Excel to generate the comparison lists, it wasn't too bad.

How to Transfer SQl Database to a Cd and be Opened with SQl on the other side

Vimalsoft(Pty) Ltd31-May-07 0:18

Vimalsoft(Pty) Ltd

31-May-07 0:18

Hi
My Department is Using SQl for the Database, and i have run some selected column and data into the table, and i want to have it on a CD as Backup so i can give it to other Company that uses SQl , for our calculation based on our Business. i have a SQl Query, that is ready

How can i see to it that the data is copied to the Cd and will be views in SQl on the other Company

Thanks

Vuyiswa

Re: How to Transfer SQl Database to a Cd and be Opened with SQl on the other side

Colin Angus Mackay31-May-07 1:40

Colin Angus Mackay

31-May-07 1:40

Save it as a backup file, burn the file to CD and have the other company restore the file from the CD.

Re: How to Transfer SQl Database to a Cd and be Opened with SQl on the other side

Vimalsoft(Pty) Ltd31-May-07 2:38

Vimalsoft(Pty) Ltd

31-May-07 2:38

I need the Steps on how to do it

thanks

Vuyiswa

Re: How to Transfer SQl Database to a Cd and be Opened with SQl on the other side

Colin Angus Mackay31-May-07 4:15

Colin Angus Mackay

31-May-07 4:15

Step 1: Take a backup of your database
Step 2: Copy the .BAK file to the CD
Step 3: Send the CD to the appropriate person
Step 4: Get the appropriate person to RESTORE from the BAK file.

Re: How to Transfer SQl Database to a Cd and be Opened with SQl on the other side

Vimalsoft(Pty) Ltd31-May-07 4:25

Vimalsoft(Pty) Ltd

31-May-07 4:25

tHANK YOU

Vuyiswa

Split it

The_Server31-May-07 0:10

The_Server

31-May-07 0:10

Hi all,

I have a table with 50 rows

I need to split the resoults of the select to pages.

page 1 will have the 1st 15 rows
page 2 will have the 2nd 15 rows
page 3 will have the 3rd 15 rows
page 4 will have the remaining 5 rows

is there a query that can make my life easy

somthing like

SPLIT_IT ( [page_num], [rows_in_page], SELECT * from myTable)

thanks

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.