Database

Re: Changing CommandText changes Parameters

13-Sep-10 17:36

Mycroft Holmes wrote:
it really is the UI code misbehaving

I doubt that very much. It's the DataAdapter and CommandBuilder (and probably Clippy) trying to be helpful.

Mycroft Holmes13-Sep-10 17:45

Mycroft Holmes

13-Sep-10 17:45

PIEBALDconsult wrote:
It's the DataAdapter and CommandBuilder

Sure but they are in the UI and in C#/VB rather than the database.

Never underestimate the power of human stupidity
RAH

Re: Changing CommandText changes Parameters

PIEBALDconsult13-Sep-10 18:01

Problem with a T-SQL 2005 PIVOT

13-Sep-10 18:01

They have nothing to do with the UI; there are no visible parts, they do the same thing when I write a library with a text editor.

Steven J Jowett10-Sep-10 0:14

Steven J Jowett

10-Sep-10 0:14

I am trying to run the following query on a MS SQL Server 2005 Express.

SELECT * FROM 
(
	SELECT * FROM vuWeighTicketReport 
	WHERE [TicketDate] >= '2010-09-10' 
) AS TicketData PIVOT (
	SUM(ProductWeight) FOR ProductCode IN (
		[Blood],[Bonecake],[Carcass],[CFat],[Desiel],[DMeal],[Feather],[FMeal],
		[Greaves],[Head],[Misc],[Offal],[PBlend],[PFB],[PMeal],[Skin],[Slurry],
		[Tail],[Tallow],[TBottom],[Thigh],[TMeal],[Wash]
	)
)

SQL Managment Studio tells me there is an error near ')'
I know the error is after the PIVOT statement, but what the actual problem is, I don't know.

I would be very grateful if someone could point my mistake out to me.

Thanks

Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.

Re: Problem with a T-SQL 2005 PIVOT

Simon_Whale10-Sep-10 1:05

Simon_Whale

10-Sep-10 1:05

SQL

SELECT * 
FROM vuWeighTicketReport
WHERE [TicketDate] >= '2010-09-10'
AS TicketData PIVOT 
(
 SUM(ProductWeight) FOR ProductCode IN ([Blood],[Bonecake],[Carcass],[CFat],[Desiel],[DMeal],[Feather],FMeal],[Greaves],[Head],[Misc],[Offal],[PBlend],[PFB],[PMeal],[Skin],[Slurry],[Tail],[Tallow],TBottom],[Thigh],[TMeal],[Wash])
)

try that

As barmey as a sack of badgers

Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

Re: Problem with a T-SQL 2005 PIVOT

Steven J Jowett10-Sep-10 1:35

Steven J Jowett

10-Sep-10 1:35

Thanks for the reply, but your suggestion did not work.
I have now resolved the problem, and my final SELECT query now looks like this :-

SELECT * FROM 
(
	SELECT * FROM vuWeighTicketReport
	WHERE [TicketDate] >= '2010-09-10'
) AS Tickets PIVOT 
( 
	SUM(ProductWeight) FOR ProductCode IN 
		(
			[Blood],[Bonecake],[Carcass],[CFat],[Desiel],[DMeal],[Feather],
			[FMeal],[Greaves],[Head],[Misc],[Offal],[PBlend],[PFB],[PMeal],
			[Skin],[Slurry],[Tail],[Tallow],[TBottom],[Thigh],[TMeal],[Wash]
		)
) AS TicketProductWeights

The only difference to the original is that I have added AS TicketProductWeights to the end.

Typically, the problem was stiring me in the face, and I couldn't see it. D'Oh! | :doh:

Thanks again.

Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.

SQL Syntax request...Please Help...

new_phoenix9-Sep-10 12:21

new_phoenix

9-Sep-10 12:21

I need to develop a query that identifies all of the records from one table that are not contained within another table. Inner Joins will not work. I know that there is an approach that will do this but I do not remember it.

Luc Pattyn9-Sep-10 13:51

Luc Pattyn

9-Sep-10 13:51

try

SELECT * FROM table1 WHERE field1 NOT IN (SELECT field2 FROM table2 WHERE ...)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

PIEBALDconsult9-Sep-10 14:19

9-Sep-10 14:19

Outer join

Davewjones9-Sep-10 15:47

Davewjones

9-Sep-10 15:47

select * from table1 a left outer join table2 b
on a.id=b.id
where b.id is null

This will give you all records in table1 that aren't in table2, assuming id is the common key.

David Skelly10-Sep-10 3:47

David Skelly

10-Sep-10 3:47

There was a thread on here a few weeks ago about the merits of exists versus in versus outer join. I can't remember what the conclusion was and I can't find the thread now.

J4amieC10-Sep-10 5:36

J4amieC

10-Sep-10 5:36

Exists was better.

PIEBALDconsult11-Sep-10 15:49

remote connection sql express 2008

11-Sep-10 15:49

Yes, I took part. I prefer outer join -- because of portability and such.

mersad009-Sep-10 10:50

mersad00

9-Sep-10 10:50

Hi,
look im going to configure sql express 2008 for accepting remote connection, BUT, automically ... I mean the enduser will not set anything, after installing sql express it whould be configured for network connections and etc.
actually i need sql scripts for this...

Re: remote connection sql express 2008

Eddy Vluggen10-Sep-10 1:08

Eddy Vluggen

10-Sep-10 1:08

As in a local Sql Express database that you just installed? Enabling is explained here[^], but a word of warning though; it allows Sql Express to receive commands that originate from another computr - and that might be someone with malicious intent.

If someone could get into the server without permission, chances are that they could execute scripts. That could include scripts that modify the filesystem and/or the registry!

Next point to consider; even if you install Sql Express with the option to allow remote connections to be made, there might still be a firewall disallowing it.

I are Troll Suspicious | :suss:

Re: remote connection sql express 2008

mersad0010-Sep-10 10:45

mersad00

10-Sep-10 10:45

Thank you, but the link does not work!
this is the link for sp of enable remote:
http://www.sql-articles.com/index.php?page=Scripts/remoteconn_dos.php[^]
which opens no downloadable text page!

Re: remote connection sql express 2008

mersad0010-Sep-10 10:51

mersad00

10-Sep-10 10:51

http://www.sql-articles.com/scripts/enabling-remote-connections-via-dos-or-t-sql[^] the actual link! THANK YOU Laugh | :laugh:

Recover SSIS file

parth.p9-Sep-10 6:31

parth.p

9-Sep-10 6:31

Hi guys, I have an old SSIS file which needs editing now. The problem is that the person who developed it has left and unfortunetly he used "User Key encryption". Mad | :mad:

Does anyone have any idea on how (if possible) can I get it back? I would really appreciate any type of help. Smile | :)

- Stop thinking in terms of limitations and start thinking in terms of possibilities -

About the datatype size

Dhyanga9-Sep-10 3:08

Dhyanga

9-Sep-10 3:08

Hi,

I am creating one table and it has field like DetailInfo. Since the user can insert short or long details, I am wondering which particular datatype with maximum size to be used ? Well nvarchar has max 5000 but is there anyway i can use more than that size ? So shall I use nText or nvarchar in this case ?

thanks in advance

suchita

PIEBALDconsult9-Sep-10 4:19

9-Sep-10 4:19

Which database?

If Sql Server, look into nvarchar(max). Cool | :cool:

Dhyanga9-Sep-10 5:29

Dhyanga

9-Sep-10 5:29

Yes. Sql Server.

suchita

Corporal Agarn9-Sep-10 6:58

Corporal Agarn

9-Sep-10 6:58

Note that nText is going away in future SQL server releases. The nvarchar(max) should do what you want.