Trying to get Pivot Table working in MS SQL 2005 - Database Discussion Boards

3. Not that I know of.
2. Size doesn't matter. Big Grin | :-D

1. Estimate and double. Laugh | :laugh:

If you have a number of large text fields, you could combine them all into one table with an ID (of some type) and a value of NVARCHAR(MAX), then only store the ID in the table that uses the value.

Wouldn't it be a better idea to have a model layer that sits in between the data access layer and the GUI. The properties in the model layer would know the limits of their respective database fields, and pass this on via an interface such as IDataErrorInfo or something similar. This way you can have GUI validation.

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Thanks Wayne.

Yep, that is the way it seems to be evolving. I already discovered I could make most of my business objects derive from a base class (say DatabaseRecord), which could help taking care of general field information handling.

I'd still appreciate an example though, so if you're aware of some article I should read, please let me know.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

I have a basic article on the IDataErrorInfo that follows the MVVM pattern Validating User Input - WPF MVVM[^] that should get you started.

Good Luck!

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Ah. That looks interesting. Thanks very much, I'll have to read that in the morning, and apply it right away.

Big Grin | :-D

Luc Pattyn [My Articles] Nil Volentibus Arduum

I agree with Wayne, your model should be aware of your data structure and it's limitations. As I always us stored procs that are generated this is a simple excercise, the generator reads the table info from the database meta data and creates the CRUD procs , the model code and the DAL (executes the procs and returns a list<> of the object).

Never underestimate the power of human stupidity
RAH

Thanks. I'm fully convinced by now that is the right way to do things. And I have implemented parts of it already, the TextBoxes now are fully aware of the field widths through a simple model layer and some dictionaries. While not fully automatic, it seems to work well.

Thumbs Up | :thumbsup:

Luc Pattyn [My Articles] Nil Volentibus Arduum

I have never used a pivot before and I am starting to think that one doesnt work. Please help provide guidence.

I have 3 tables

_Contracts, which contain contractid and sepworkstatus

_contracts
--------------------
1,[Sent]
2,[Sent]
3,[No Further Action]

_vendors which has umbrellaname and vendornumber _contracts.vendorid is foreign to _vendors.vendornumber

_vendors
---------------------
um1,1
um2,2
um2,3

and _UmbrellaData which has umbrellaname and a few other needless columns. umbrellaname connects _UmbrellaData and _vendors

_UmbrellaData
---------------------
um1
um2

The outer apply will grab a list of all the statuses and their counts for each umbrella company

The results will look similar to

um1,sent,1
um2,No Further Action, 1
um2,sent,1
um3,Executed

my output needs to look like this

umbrellaname, sent, [No Further Action], [Executed]
um1,1,null,null
um2,1,1,null

My current query looks like this

select
u.umbrellaname, u.stream, functionalarea,
[In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started]
from dbo._UmbrellaData u
outer apply (
select v.umbrellaname, c.sepworkstatus, count(c.sepworkstatus) as counte from dbo._Vendor v
join dbo._Contracts c on c.vendorid = v.vendornumber
group by v.umbrellaname, c.sepworkstatus
) as oa
pivot(max(oa.counte) for oa.sepworkstatus in (
[In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started]
)) as cw
where oa.umbrellaname = u.umbrellaname

Unfortunately when I run it I get the following errors.

Msg 8156, Level 16, State 1, Line 2
The column 'umbrellaname' was specified multiple times for 'cw'.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "oa.umbrellaname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "u.umbrellaname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "u.umbrellaname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "u.stream" could not be bound.

Why? How do I get a pivot to work with an outer apply?

Any help on this would be greatly appreciated.

You might want to work through this article[^], not so much for the detail but more for the way the pivot is constructed.

Never underestimate the power of human stupidity
RAH

Thanks. Somehow I had missed that article. It does give a shove in the right direction.

So far we have built a temp table instead. Maybe that can be made cleaner. Smile | :)

Don

I am trying to add a record to MySql using C# and MySql .net connectors. The field is varchar and I am using this as sql command statement:

C#

insert_command = "INSERT INTO cdr_log (cdr_raw) VALUES ('" + cdr_raw + "')";

but I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0')' at line 1

because sometimes my cdr_log will look like this:

09/11/11 07:18PM 299 02 19751002 00:00'04

so how can I avoid the error?

Thanks,
Jassim

You can use parameters, something like this:

cmd.CommandText = "INSERT INTO cdr_log (cdr_row) values (?p_cdr_raw)";
cmd.Parameters.Add("p_cdr_raw", MySqlDbType.VarChar);

...

cmd.Parameters["p_cdr_raw"].Value = cdr_raw; //suppose cdr_raw is the variable with the value you want to insert

cmd.ExecuteNonQuery();

jrahma wrote:
so how can I avoid the error?

The tick is a special character. You are ignoring that possibility.
That is the cause of the error.

There can be other special characters as well.

As noted in the other reply you should use parameter replacement, which many database APIs (different types, different programming languages) provide.

If you absolutely cannot do that then you would need to either
1. Determine what special characters exist for your database and deal with them yourself.
2. Limit all input to a known safe set of characters and verify each input value FIRST before attempting to use it in SQL.

Hi,

I have one machine (PC-A) running XP Pro and SQL Server 2005, and another machine (PC-B) running XP Pro and Visual Studio 2005. Both are on my local network. They can see each other.

On PC-B, I tried to connect to PC-A SQL Server, but failed. PC-A SQL Server is allowing remote connections. Windows Authentication is used for connections. What else did I not set right? Thanks!

Best,
Jun

AFAIK you may have to add rules to your server's firewall, so it allows inbound traffic on TCP port 1433, and possibly also UDP port 1434.

If it still fails to connect, watch the error message closely; stuff it in the Google search box and/or show it here.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Thabks for reply.

After I added TCP port 1433 and UDP port 1434 to PC-A firewall exceptions and tested connection, I got the error "Login failed for user 'PC-A\Guest'". How can I set up Windows Authentication login properly?

In my case, it is not a domain environment. There is no common domain account, but individual accounts on each machine.

Best,
Jun

I don't know how to do that with Windows authentication; I did solve it once using SQL Authentication: just give your database and your app the same username and password, so tje DB can be accessed from everywhere by anyone running your app. May not be the safest way, but it works.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Thanks.

I added PC-A's "Guest" account to the SQL Server's "Security\Logins" group and mapped it to specific databases I want to connect. Here, I might have broken some security, but at least got it working for now.

Best,
Jun

Hi,
I have a sql server table with fields EmployeeID, Date, TimeIN, TimeOut, Remarks.
Now there is a unique constraint on both EmployeeID and Date such that only one record exists for a particular Employee on a particular date.
Now the main application can insert and update records. I need a trigger to track changes such that on insert or update, the values are copied onto a data_log table. Here is the problem, I need the trigger to check if the row exists in data_log for that EmployeeID and Date, then update, else insert. So there should be only one record of each employee per date in the data_log table as well. I just want the most current change and need to overwrite previous log entry.

thanks

Just a thought... why are you bothering if all you are doing is copying the values into another table? An audit or log table should contain complete history, I've always thought and no unique keys from the parent table - duplicates are fine - create a compound index or a surrogate key if you need one. Prepared to be called out on this - just seems a bit pointless.

In the meantime you could check with:

SQL

if exists(select top 1 1 from [table] where [key] = [parameter])
begin
    -- insert
end
else
begin
    -- update
end

or similar: adjust to suit.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

I have to run extended operations on bulk records in the destination table.
I thought Delete if exists, then Insert would be a possible solution, but the trigger is working some times and failing other times, and when it does fail, it cancels out the transaction on the original table resulting in data loss.

I do not know which version of SQL server you have but if 2008 try MERGE in your trigger.

Hi,
I have two mysql tables with large amount of data.
Table_1 has fields PropID, FullStreetAddress, City, State, Zipcode, Remarks, Date which I want to pick. Table_2 has PropID,URL.
Now I want to pick top 60 rows from table_1 order by date desc. Each PropID has several URL records in Table_2. Out of those records, I need to pick only 4 for each PropID in table_1.
So in effect I would get a result set of 60 x 4 = 240 rows.
Selecting from table_1 and inner joining on PropID in table_2, I am getting a complete result set, but I need to limit that somehow to get 60 rows from table_1 and 4 rows for each of the 60 ids from table_2 in a single query.

thanks.

use

SQL

LIMIT 60

to get 60 rows from table_1

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

I know that part. But I need to create a View of some sort with 60 x 4 = 240 rows, I cant use Limit for both tables in a single query now, can i?