Database Discussion Boards

basementman27-Jun-03 3:47

27-Jun-03 3:47

select
C.CompanyName,
S.MailShotDesc,
SUM(case E.Status when 'S' then 1 else 0 end) 'Sent',
SUM(case E.Status when 'F' then 1 else 0 end) 'Failed',
SUM(case E.Status when 'Q' then 1 else 0 end) 'In Queue'
from
Company C,
MailShot S,
EMails E
where
S.CompanyKey = S.CompanyKey and
E.MailShotKey = S.MailShotKey
group by
C.CompanyName,
S.MailShotDesc
order by 1, 2

onwards and upwards...

Re: CUBE, PIVOT and my head

basementman27-Jun-03 3:49

basementman

27-Jun-03 3:49

This format is the simple way to cross-tab detail records with different statuses.

onwards and upwards...

Re: CUBE, PIVOT and my head

Paul Watson3-Jul-03 23:01

Paul Watson

3-Jul-03 23:01

Thank you very much basementman, much appreciated and it works like a bomb Smile | :)

Paul Watson
Bluegrass
Cape Town, South Africa

Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"

Trigger problem

Imtiaz Murtaza26-Jun-03 2:33

Imtiaz Murtaza

26-Jun-03 2:33

Friends i made a trigger in SQL server. The purpose of this trigger is very simple i.e to find out the number of records updated in the table and then print it. For this purpose i am using:

select @count = count(*) from updated

This is working fine in my home computer, but i am getting error in my office computer that:

Invalid object 'updated'

i.e SQL server is unable to find the table 'updated' which is its own internal table.

How can i get rid of this error.

Thanks

Imtiaz

Re: Trigger problem

basementman26-Jun-03 4:06

basementman

26-Jun-03 4:06

the tables exposed to triggers are 'inserted' and 'deleted'. I expect that at home, you actually have a user table named 'updated' in your db, which is why it is working.

onwards and upwards...

Re: Trigger problem

Imtia26-Jun-03 6:48

Imtia

26-Jun-03 6:48

zbasementman wrote:
the tables exposed to triggers are 'inserted' and 'deleted'.

Both of these are also not working for me Frown | :(

Re: Trigger problem

treiber1-Jul-03 8:08

treiber

1-Jul-03 8:08

I think if you change your select to this format, it may work:

select @count = count(COLUMN_NAME) from INSERTED

which data type can i use ? for storing a long string.

Asim N.26-Jun-03 2:15

Asim N.

26-Jun-03 2:15

hello,
i have long string to store in my database.
i am using varchar, but its too small for storing it.
i used text before, but i can't use where clause with it without using LIKE nad IS NULL.
so which datatype should i use for storing storing complete information?
LOOKING FOR GUIDENCE
ASIM NAZIR

Asim

Re: which data type can i use ? for storing a long string.

Itanium26-Jun-03 2:35

Itanium

26-Jun-03 2:35

i think varchar is best option.

sorry for my bad English.

Re: which data type can i use ? for storing a long string.

basementman26-Jun-03 4:09

basementman

26-Jun-03 4:09

If you can get away with it, use a varchar(7500). If your data is larger that 7500, you will need to use text.

You can also use PatIndex and CharIndex with text fields.

So, if you want to do an equality join, you can use something like:

where
CharIndex('StringToSeek',TextField) = 1

onwards and upwards...

Adding a new record

Itanium26-Jun-03 1:52

Itanium

26-Jun-03 1:52

Hi All!!

I have binded my text fields to DataColumns of table at runtime.In data navigation mode, all text fields of my application are disabled. When I press the 'Add New' button, all fields are enabled and cleared.
Problem is that when I focus to next control, all the data of current record is again loaded in all textboxes Confused | :confused:

. While I want to remain them clear.

plzzzzzzzzzzzzzzzzz help me.
Thanx in Advance

sorry for my bad English.

Website can't access SQL2k DB

-- NA --25-Jun-03 22:21

-- NA --

25-Jun-03 22:21

I have a website that needs to access a SQL Server 2000 database hosted on the local machine. I've created an ODBC connection to the database and created users with applicable permissions in the database itself, but when I try and login with the query analyzer or from the website I get the error "SGL doesn't associate this user with a trusted connection." This is the DSN I'm using.

DATABASE_DSN = "Provider=SQLOLEDB.1; Password=abcd; Persist Security Info=True;User ID=abcd;Initial Catalog=extrosite;Data Source=LocalServer"

Please help Big Grin | :-D

Paul

I have been afraid always. When you see something insurmountable ahead of you, say to yourself: "All right! I am afraid. Now that I've been properly afraid, let's go forward." That is the whole secret. - Jeanne d'Arc

modified 18-Jul-18 11:59am.

Re: Website can't access SQL2k DB

-- NA --26-Jun-03 0:39

-- NA --

26-Jun-03 0:39

Don't worry peeps I've solved it Cool | :cool:

Paul

modified 18-Jul-18 11:59am.

Re: Website can't access SQL2k DB

Rick Crone26-Jun-03 7:51

Rick Crone

26-Jun-03 7:51

How?

Re: Website can't access SQL2k DB

-- NA --26-Jun-03 7:54

-- NA --

26-Jun-03 7:54

a) I had to configure my SQL server to accept SQL authentication by opening Enterprise Manager, opening properties for my server, and on the Security tab setting authentication to SQL/Windows NT as opposed to NT, and...
b) fixing my connection string. The new one became:

DATABASE_DSN = "Provider=sqloledb;Data Source=(local);Initial Catalog=extrosite;User ID=abcd; Password=abcd"

Paul Wink | ;)

modified 18-Jul-18 11:59am.

which way i should go ?

Asim N.25-Jun-03 18:09

Asim N.

25-Jun-03 18:09

hello,
i am reading values from an html page, now i want to put them into database.
now, should i put them directly i.e. get one by one from the page and continue putting them to database accordingly. or
i should first write the values to a .txt file (say a.txt) and then enter them to databse using this text file.
?
am using SQL SERVER2000 AND C#
ASIM

Asim

Re: which way i should go ?

SimonS27-Jun-03 20:40

SimonS

27-Jun-03 20:40

I'm presuming you are looking for tags or something?

If one insert fails should they all fail?

Probably get all values from page, then insert one by one.

Cheers,
Simon

"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
animation mechanics in SVG (my first abstract photo)

ADO

AticAtac25-Jun-03 5:11

AticAtac

25-Jun-03 5:11

Hi,

i insert a new recordset in ADO

_RecordsetPtr rs;
...
rs.AddNew()

After this i set the fields in rs with my application data and do a
rs.Update()

My Problem is that if copy back the data from rs to my application
i get an error on fields which have a standard value like datetime (getdate()).
One method to avoid this problem i've found was to close and open the recordset again,
but in this case i have to move the rs to the right position, using GetAbsolutPosition won't work because in some tables my primary key consists of more than 1 column so there is no guaranty new recordset goes to the end of the table.
Any help /ideas to solve this problem are welcome

thx in advance !

WHERE CLAUSE problem.

Asim N.25-Jun-03 2:22

Asim N.

25-Jun-03 2:22

hello,
am working in c# an dtrying to connect to a sql server database.
my where clause is not working. without whre it's fine.

exception is :

the text,ntext and image data types can't be compared or sorted , except when using IS NULL OR LIKE operators.
now how can i use where clause.
e.g. plz
ASIM

Asim

Re: WHERE CLAUSE problem.

basementman25-Jun-03 4:36

basementman

25-Jun-03 4:36

I think the error message is self-explanitory.

onwards and upwards...

Connection is busy with results of another command

audio-video24-Jun-03 21:48

audio-video

24-Jun-03 21:48

I get this problem using an OleDBConnection and executing a source code that inserts and deletes on multiple tables under same transaction (connection.BeginTransaction()). The problem decreases if I remove transactions management but the only crucial work around, for the moment, is closing and opening the connection before any insert, which is not acceptable solution.

Thanks,
Luigi

Code Database Help;

Bo Hunter23-Jun-03 10:46

Bo Hunter

23-Jun-03 10:46

I am having trouble with the layout of the database to store source code.
I need someone to explain to me how to build the db so that I can create
categories and sub categories in a treeview. I am going to add the nodes
at runtime from a nodes table. The biggest thing is how to have multiple
nesting levels. Or if the subcategory has some code and more subcategories.

I know this dont begin to be all the info needed but it will give me a start.

Thank You
Bo Hunter

Re: Code Database Help;

Ray Cassick23-Jun-03 11:03

Ray Cassick

23-Jun-03 11:03

Run a search on google for 'celko tree' and see the large amount of information on how to do just this.

It's not that hard, just takes some planning.

Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
Jörgen Sigvardsson wrote:
If the physicists find a universal theory describing the laws of universe, I'm sure the a**hole constant will be an integral part of that theory.

Re: Code Database Help;

basementman24-Jun-03 5:04

basementman

24-Jun-03 5:04

Here are some table layouts we use for a hierarchial "file system" that is browsed via a tree in a web browser:

create table ic_DocumentCategory
(
DocumentCategoryKey int NOT NULL IDENTITY,
CompanyKey int NOT NULL,
Description varchar(80) NOT NULL,
VisibleToInternalUsers bit NOT NULL,
VisibleToExternalUsers bit NOT NULL
)
GO
create unique clustered index ic_DocumentCategoryDesc_idx
on ic_DocumentCategory (CompanyKey, Description)
create unique index ic_DocumentCategory_idx on ic_DocumentCategory (DocumentCategoryKey)
GO

create table ic_DocumentFolder
(
DocumentFolderKey int NOT NULL IDENTITY,
DocumentCategoryKey int NOT NULL,
ParentFolderKey int NULL,
Description varchar(80) NOT NULL,
OwnerInternalUserKey int NULL,
VisibleToInternalUsers bit NOT NULL,
VisibleToExternalUsers bit NOT NULL
)
GO

create unique clustered index ic_DocumentFolderCatParentDesc_idx
on ic_DocumentFolder (DocumentCategoryKey, ParentFolderKey, Description)
create unique index ic_DocumentFolderKeyDesc_idx
on ic_DocumentFolder (DocumentFolderKey, Description)
create unique index ic_DocumentFolderParentDesc_idx
on ic_DocumentFolder (ParentFolderKey, Description, DocumentCategoryKey)
GO

create table ic_DocumentFolderAccess
(
  DocumentFolderAccessKey int            NOT NULL IDENTITY,
  DocumentFolderKey       int            NOT NULL,
  CompanyKey              int            NULL,
  CompanyUserKey          int            NULL,
  RoleKey                 int            NULL
)
GO
create unique clustered index ic_DocumentFolderAccessFolder_idx
  on ic_DocumentFolderAccess (DocumentFolderKey, DocumentFolderAccessKey)
create unique index ic_DocumentFolderAccess_idx
  on ic_DocumentFolderAccess (DocumentFolderAccessKey)
GO



create table ic_DocumentFolderFile
(
  DocumentFolderKey       int            NOT NULL,
  AttachmentKey           int            NOT NULL,
  CompanyUserKey         int            NOT NULL
)
GO
create unique clustered index ic_DocumentFolderFile_idx
  on ic_DocumentFolderFile (AttachmentKey, DocumentFolderKey, CompanyUserKey)
create unique index ic_DocumentFolderFileFolderAttach_idx
  on ic_DocumentFolderFile (DocumentFolderKey, AttachmentKey, CompanyUserKey)
create unique index ic_DocumentFolderFileUserFolderFile_idx
  on ic_DocumentFolderFile (CompanyUserKey, AttachmentKey, DocumentFolderKey)
GO

This syntax is for MS SQL7 or higher. Hope it gives you some ideas...

onwards and upwards...

Timeout expired.

meaya22-Jun-03 21:34

meaya

22-Jun-03 21:34

I have error :

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

If you've got a solution to my problem, please help me !

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