Help with cross products and functions

Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:

> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>

Help with combining data from multiple rows into one column in a view

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.

I have a view that looks sort of like this (but with a lot more
entries of course)

UniqueIdentifyierColumn1Column2
1 9999 100
2 9999 200
3 9999 300

What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.

UniqueIdentifyierColumn1Column2Column3
1 9999100100, 200, 300
2 9999200 100, 200, 300
3 9999300100, 200, 300(gaholmes@.comcast.net) writes:

Quote:

Originally Posted by

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.
>
>
I have a view that looks sort of like this (but with a lot more
entries of course)
>
>
UniqueIdentifyier Column1 Column2
1 9999 100
2 9999 200
3 9999 300
>
>
What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.
>
UniqueIdentifyier Column1 Column2 Column3
1 9999 100 100, 200, 300
2 9999 200 100, 200, 300
3 9999 300 100, 200, 300

This may not be doable in a view, particularly if you are on SQL 2000.

SQL Server MVP Anith Sen has a collection of methods to attack this
problem at http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with Column Widths

How can I find out the sizes of the columns that are returned from a query?
I need to know so that I can pad each one to it's full size with spaces so
that everything will line up using a mono spaced font in a textbox .NET
control. If I fire off a query and get back a DataSet of results, then
when I loop through them and populate a large multiline textbox, each
column's contents are only as long as the data contained in it, not the
full width that the column is designed as in the schema. I'm using c# in
.NET. Is there a way to find out what the full width should be for each
column returned from a query?
-- Rob
Pull ThePlug to reply by email...this sounds like UI formatting again ...use the
Sring.PadRight(totalLength, char)
method to format it to the length yout want...
Message posted via http://www.webservertalk.com|||or if you really want the length of the text
select datalength(description),description from [table]
for each of the columns that you would like to "padright"
Message posted via http://www.webservertalk.com|||In a tiered architecture, the formatting in done in the front end and
not in the database. Doesn't C# have such functions?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1112972652.223954.175560@.g14g2000cwa.googlegroups.com:

> In a tiered architecture, the formatting in done in the front end and
> not in the database. Doesn't C# have such functions?
>
Yes, I want to do it in the front-end. I am going to use the pad()
function, but I don't know the column widths to pass to the pad() function.
This app is very similar to Query Analyzer in that it will allow you to
type in your query and then it will execute it and return the results. I'm
giving the users the option of viewing the results in a DataGrid, or in a
textbox so they can easily "copy and paste" from the text to some other
application. Query Analyzer does this too, and their "text" output mode
has all of the columns presented in a "padded" format, all neat and
aligned. If I fill a textbox control with the returned results from a
query, each column is trimmed of any extra spaces before I get it. I'm
trying to figure out how to put those spaces back before displaying the
results.
Thanks for your reply...
-- Rob
Pull ThePlug to reply by email...|||"baie dronk via webservertalk.com" <forum@.webservertalk.com> wrote in
news:892513b471ec4827844239655f3aabf0@.SQ
webservertalk.com:

> this sounds like UI formatting again ...use the
> Sring.PadRight(totalLength, char)
> method to format it to the length yout want...
>
This is exactly what I intend to do, but I have no idea how to tell what
the totalLength should be for each column. This app allows users to type
in their SQL query and then execute it. The results returned are displayed
either with a DataGrid, or in a textbox where they can "copy and paste" the
results to another app or whatever. This behaviour is *EXACTLY* like
Microsoft's Query Analyzer. Their "text" output is all padded and lined up
nicely, which is what I'm trying to mimic.
Thanks for your reply...
-- Rob
Pull ThePlug to email...

Help with column Insert

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'll show my code, and the bold is explaining what I want inserted and where from. I'd apprechiate if someone could help me with syntax etc. There are 2 about getting value from another table and one about just putting in straight forward text..:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

If 10 people are to receive the message, are there 10 message records or one?

If one, how are the 10 usernames supposed to be formatted? comma-separated values, separated by semi-colons?

|||

I was hoping to have 1 record, with a large list of usernames. I assume it will work if they are seperated by anything, but with a space - peoples messages are called up if the recievername column has their username in it. Will it work if it also has other writing (i.e. other peoples usernames)?

Thanks

Jon

|||

jbear123:

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

I just took the time toreally pay attention to what you are doing in business terms instead of just the coding issues.

This is a collection's application? If you sent out a message with my name on it, telling hundreds of other people I hadn't paid up, I would be really angry.

Angry enough to tell you impolite things and never do business with you again.

Depending upon what country you live in or are sending the messages to, it might even be illegal. Particularly if you made a mistake and they did not owe you anything - that would be libel under US law.

That said, I have one other question. The payment instructions are tied to the user who is logged into the page, not the user receiving the message? That was a bit surprising. Or are they tied to the user receiving the message?

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

david wendelken:

As for filling in the values, you just need a few strings to store them in.

Issue a query to get the list of usernames and loop thru them. Use the StringBuilder to concatenate them.

Issue another query to get the payment instructions.

This is what I was after. Would you mind explaining this a bit more throughly for someone of a lower coding ability..? i.e. how do I issue the query to get the list of usernames? Compared to the code I posted, how would it be structured? Concatenate them?

Re: Legal issues - Thank you for your concern, but you shouldn't worry about it.

Thanks for your help!

Jon

|||

You already know how to issue a sql command via the SqlCommand object. Instead of an update command, you need to issue a query:

My guess as to your query would be "select distinct usersname from transactions where itemid = @.itemid"

You will have to pass in the itemid as a parameter, and issue an ExecuteQuery instead of an ExecuteNonQuery. You will be putting the results of the query into a DataReader and looping thru it. Just google or look it up any pretty much any asp book. This is basic, beginner level stuff and it's well documented all over the place - so I'm not going to do it again. :)

Inside the DataReader loop, you can concatenate the usernames you return into a string. It's best to use the StringBuilder class when you are looping. Again, google or look up StringBuilder in the manual.

I think you would be better served slogging thru this step yourself rather than having someone hand it to you. You'll learn it better, and this is bread-and-butter code that you'll use all the time, so it needs to be second nature. I'll keep an eye out on this thread in case you get stuck someplace.

|||

Hi,

I think I have the idea - could you confirm this for me:

I keep the insert commands, but before them I create a command to select all the info that I want inserted (from different tables etc), then using the insert command reference the results of the select command info?

If thats not what yopu meant, would that work anyway?

Thanks,

Jon

|||

I hope it will work as I have done it now..I created datalists showing the data I want to be inserted.

The only thing I cant do is link the results of the datalist (where i selected the data I want(visible=false)) etc to the add parameter section.

E.g:

command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.recievername",how do I link to results of the datalist here? I'd have to have a comma in between each result);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());

Please could you briefly explain, as soon as I know that I can apply it to problemsall over the page and my site will be done!

Thanks alot

Jon

|||

Before you get to this point, create a string to hold the receivername values.

Loop thru the datalist, and for each entry in the datalist, append the next receiver.

|||

I'll give it a go. Just briefly before I leave you alone for a while (sorry!)-

If I create a string, how do I do that for many values (i.e. many usernames),

By loop thru, you mean just use find the string on the datalist?

And what do you mean by append the next receiver? use: + 'next receiver'?

Cheers,

Jon

|||

Hi,

I have:

SqlCommand command = new SqlCommand();
command.Connection = con;

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView;
string receivername = dv[0]["receivername"].ToString();
command.Parameters.AddWithValue("@.recievername", receivername);
command.Parameters.AddWithValue("@.message", TextBox2.Text);
command.Parameters.AddWithValue("@.subject", TextBox3.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
command.Dispose();
return true;

so far. How can I make values seperate by commas?

Thanks,

Jon

sql

Help with booleans with UPDATE command.

Hello,

I'm using a Gridview to display a list of servers. Each server has a column in a table called "Enabled." I want to create a button that is supposed to toggle the value called "Enabled." I am able to make the button either to set Enabled to true or false, but I don't know how to make it toggle.

Here is the command:

UpdateCommand="UPDATE [ServerStatus] SET [Enabled] = 1 WHERE [ServerName] = @.ServerName"

The button is a buttonfield in the gridview:

<asp:ButtonField ButtonType="Button" CommandName="Update" HeaderText="Toggle" ShowHeader="True" Text="Toggle" />

Does anyone know the syntax, or a way to make the button set Enabled to true when it is false, and false when it is set to true?

UPDATE [ServerStatus]SET [Enabled] =CASE [Enabled]WHEN 1THEN 0ELSE 1END WHERE [ServerName] = @.ServerName

That should work for you if you want SQL Server to do the toggling.|||

Works like a charm.

Thanks dude.

help with binding

hi guys! help please..how will bind my texbox (TxtLastName) to my dataset (dsLastName) column (User_LastName). Thanks in avdance!well first of all i guess this is the wrong section you have posted in....

secondly if you want to add the value of a specific row in a dataset to a textbox you can use something like

TxtLastName.Text = dsLastName.Tables[index].Rows[index].Columns["User_LastName"]

if however you want to bind it you might as well look over here and get some clue
http://www.velocityreviews.com/forums/t95227-bind-textbox-to-dataset.html

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.I should give some more info to make the analysis easier...

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

I want the SQL to add a Count column and the Fact table data should look like this (one count per day).

Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0

The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?

Thanks. Sorry I wasn't more clear.|||I still don't understand what you are trying to do. To get a count of meals, why don't you just
SELECT COUNT(*) From Table WHERE Type = 'Meal'

Do you want to count the number of days that have one meal so that if a single day has multiple meals, it just counts as one:
SELECT COUNT(*) From Table WHERE Type = 'Meal' GROUP BY datepart(year, Date), datepart(dayofyear, Date)|||Yes, I want to count one meal per day, however I still want the other line items to show as zero, because they have costs.

I'll try your SQL, but won't it drop the other 2 line items?

Thanks.|||select date expression
, count(*) as numberofmeals
, sum(costs)/count(*) as averagecostofmeal
from yourtable
group by date expression|||another solution :
select Date, Type into ##temp from yourtable group by Date, Type;
select Type,count(type) from ##temp group by type;

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.This is tough to help you do. The problem is that we (or I at least) don't understand your schema, so I'm not sure what you've got stored how.

One thing that I'd strongly suggest is to avoid using reserved words like "Count". You can use them, but it makes everything more work. In this case, I'd suggest using meal_count because it avoids the collision with a reserved word and it is more meaningful to some poor bozo like me that might try to help you!

-PatP|||This does not help. I was only giving you an example, I am using the 'Count' for the column title only, and am returning the same rows if I change the column title. I want to edit the SQL so I only count the number of days they had a meal charged.

Thanks.|||I'm sorry, I was trying to explain that I didn't know enough about your problem to help. If you can post the CREATE TABLE statements for your tables, and the SELECT statement (all of it), then I could get a lot closer. As it is, I don't know enough to give you anything more than guesses.

-PatP

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype = of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column = 'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects = access this column.
I can change the datatype without any problems from either Enterprise = Manager or the new Sql Server Management Studio Express. I am working with = a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio = Express. I would much prefer to use an automatic update script.
TIA
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich NietzscheOk, a bit of an update. I have discovered that the error is due to a =DEFAULT constraint on the columns in question. Problem is, this constraint =has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =detect the DEFAULT constraints, remove them, alter the column and add the =constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =it in a T-SQL script.
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche|||This is why you always should name your constraints. Here's an example on how to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the
columns in question. Problem is, this constraint has system generated name, so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically detect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Tibor Karaszi wrote:
>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =constraint reference.
The example you posted does not work with 2000. I guess the system tables =follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =suits my immediate needs.
--
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype =
of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column =
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects =
access this column.
I can change the datatype without any problems from either Enterprise =
Manager or the new Sql Server Management Studio Express. I am working with =
a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio =
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
Ok, a bit of an update. I have discovered that the error is due to a =
DEFAULT constraint on the columns in question. Problem is, this constraint =
has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =
detect the DEFAULT constraints, remove them, alter the column and add the =
constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =
it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
|||This is why you always should name your constraints. Here's an example on how to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the
columns in question. Problem is, this constraint has system generated name, so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically detect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Tibor Karaszi wrote:

>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =
constraint reference.
The example you posted does not work with 2000. I guess the system tables =
follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =
write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =
suits my immediate needs.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
sql

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype =
of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column =
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects =
access this column.
I can change the datatype without any problems from either Enterprise =
Manager or the new Sql Server Management Studio Express. I am working with =
a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio =
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich NietzscheOk, a bit of an update. I have discovered that the error is due to a =
DEFAULT constraint on the columns in question. Problem is, this constraint =
has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =
detect the DEFAULT constraints, remove them, alter the column and add the =
constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =
it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche|||This is why you always should name your constraints. Here's an example on ho
w to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.
TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT
constraint on the
columns in question. Problem is, this constraint has system generated name,
so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically d
etect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it
in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Tibor Karaszi wrote:

>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =
constraint reference.
The example you posted does not work with 2000. I guess the system tables =
follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =
write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =
suits my immediate needs.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche

Help with adding column

hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!just create a new table and populate it like this:

insert into NewTable
select col1, col2, 'some value', col3 from OldTable

then you can drop your old table. be sure to create indexes, fks, etc on the new table as appropriate once it's populated.|||i need to add 1 column between column 2 and 3.

The physical order of data in a database has no meaning.

Just use alter table and add the field to the end.
You can always specify the display in your select statements.

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

Help with a multiple table query

Hi,
I need help in constructing a query against my Db I have. The DB I have has
122 Tables and 15 columns in each. I have information in a column in one
table that I need to query with data in a column on another table. In other
words, In Table ad, i have a column called ENTRY_ID that I want to query
against data in the column PUBLISH_DATE in table INSERT.
This is what I have so far.
select a.ad_number,a.entry_date,b.publish_date
from ad a, ad_insert b where a.entry_date = '2004-11-03'and b.publish_date =
'2004-11-04'
I know I have records but the query produces no results so
any help on what I am missing would be greatly appreciated.
On Wed, 3 Nov 2004 20:00:03 -0800, Dave Lugo wrote:

>Hi,
>I need help in constructing a query against my Db I have. The DB I have has
>122 Tables and 15 columns in each. I have information in a column in one
>table that I need to query with data in a column on another table. In other
>words, In Table ad, i have a column called ENTRY_ID that I want to query
>against data in the column PUBLISH_DATE in table INSERT.
>This is what I have so far.
>select a.ad_number,a.entry_date,b.publish_date
>from ad a, ad_insert b where a.entry_date = '2004-11-03'and b.publish_date =
>'2004-11-04'
>I know I have records but the query produces no results so
>any help on what I am missing would be greatly appreciated.
Hi Dave,
The syntax looks okay - if there are matching rows in both tables, they
should show up. Without knowing your table structure and the data that's
in your table, it's hard to get more specific than this. If you need more
specific answers, you'll have to provide more specific information first.
See www.aspfaq.com/5006.
Some general remakrs that might or might not apply to your query:
* The date format 2004-11-03 is ambiguous - do you mean November 3rd or
March 11th? The only date formats that are completely safe to use are:
- yyyymmmdd (for date only - remember: no seperators in this format!),
- yyyy-mm-ddThh:mm:ss (for date plus time - note the capital T),
- yyyy-mm-ddThh:mm:ss.mmm (idem, but including milliseconds).
* I assume that the dates are stored using the datetime datatype. A common
pitfall is to forget that datetimes always include a time component as
well as a date component. If you don't supply the time, SQL Server will
assume midnight as default. The above query is comparing a.entry_date to
'2004-11-03T00:00:00.000' and won't match is a.entry_date is equal to
(e.g.) '2004-11-03T16:45:00.000'. If you want to find all rows with
entry_date 20041103, regardless of the time, use a.entry_date >=
'20041103' AND a.entry_date < '20041104'.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with "Create View" statement and Eorror Message

Hi all,
I am trying to create a view with approx. 3000 columns... and got the following error message:

"CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
Anyone can help to answer my question?

Thank you in advance.As much information vertically, not horizontally.

Frankly, if you are trying to create a view with 3000 columns, the problem is in your design, not SQL Server's limitations!

Why are you doing this? Maybe somebody here can find a better approach for you to take.|||No, a single row in a table can only contain a bit short of 8 kilobytes. A given row in a result set (therefore in a view) can only contain 1024 columns, and there are some limitations on the 1024th column.

That said, how on earth would you make use of a view that wide ?!?! What would you do with it ?

-PatP|||I definitely recommend printing it on legal-size paper set to landscape orientation, using Arial Narrow font.|||You can you go over the row limit in a physical table but you just get some warning about inserts and updates. See it often in poor designs or lack there of.|||Are you by any chance trying to crosstab that 40-year history you were talking about in that other post? That's the only thing I can think of that would give you that many columns. :D|||I do not have to see/create all the columns. However I would like to know the limitation. I just tried running the same query again for 950 columns which was succussful.
Maybe I have to run 3000 columns separately to create 3000/950 views. Can I union them together as a one object/something? In addition,
How am I going to update number of views on daily basis? :confused:|||All kidding aside.

Why don't you provide us with some more information about what exactly you are trying to do? Of course the ddl might be too much 411. But if you give us enough info one of might come up with something or at least some advice.

Some of the folks in this forum are as smart as they think they are. Myself excluded. I am as dumb as I seem. darrrrrrrrrrrrrrrrrrrrrrr!!!!!!!!!!|||This post is related to the one "updating daily information in a history table (was "Help-Brainstorming")"... which provides details.

Sorry about the confusion. And thank you for the help.|||Good Morning All,
Hope you all had a great weekend!
I think I am probably asking a silly question but...
I created a view by using the following statement:

create view TestView1
as
select date as Date,
XXXXX= sum(case when ID='XXXXX' then Field1 else 0 end),
YYYYY= sum(case when ID='YYYYY' then Field1 else 0 end)
from MyTable
Group by Date

The structure of MyTable is:
Date(datetime) ID(char 10) Field1(float)
1/1/65 XXXXX -999.999
1/4/65 XXXXX -999.999
...
2/24/05 XXXXX 500
2/25/05 XXXXX 550
1/1/65 YYYYY -999.999
1/4/65 YYYYY -999.999
...
2/24/05 YYYYY 600
2/25/05 YYYYY 650

when I run "select * from TestView order by date"
The actual results I got:
Date XXXXX YYYYY
1/1/65 0.0 0.0
1/4/65 0.0 0.0
...
2/24/05 500 600
2/25/05 550 650

This is the results I should expect:
Date XXXXX YYYYY
1/1/65 -999.999 -999.999
1/4/65 -999.999 -999.999
...
2/24/05 500 600
2/25/05 550 650

What is wrong with my create view statement? Do I have to specify the datatype?

Thank you for the help in advance.|||What is wrong with it?

The question is, "What good is it?"

What is the practical use of a view with 3000 cross-tabbed columns? You can't print it. You can't display it. You can't use it practically in any other views or procedures.

What are you planning to do with this?|||This is going to be the data source for another application (Matlab).
And this is desired format. It does not matter if I can display them all as long as I can display partially to make sure the information is there and the expected data format.

Should I put information into a table instead of View?

Thank you for the help!|||MatLab can't accept normalized data?

And it can't do its own crosstabs?

That is pretty weak.

I'm sorry, but I just can't suggest any solution along the lines you are thinking, because I think it is going to cause you severe problems in the future.|||Matlab is a statistic package to do math calculation and generate graphs. It may take 5+ hours to run the results therefore I am thinking to use SQL to generate the expected data source format to feed into Matlab.
If you think the only possible solution should be on Matlab side, I guess I have to work on that.
However, do you know why my actual result from my sql statement shows data as "0.0" instead of expected "-999.999" which is stored in the table?

Any suggestion and comments are truely appreciated!
shiparsons|||I don't have that problem..you probably shouldn't be using float though...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] varchar(15), [Date] datetime, Field1 float)
GO

INSERT INTO myTable99([Date],[ID],Field1)
SELECT '1/1/65', 'XXXXX', -999.999 UNION ALL
SELECT '1/4/65', 'XXXXX', -999.999 UNION ALL
SELECT '2/24/05', 'XXXXX', 500 UNION ALL
SELECT '2/25/05', 'XXXXX', 550 UNION ALL
SELECT '1/1/65', 'YYYYY', -999.999 UNION ALL
SELECT '1/4/65', 'YYYYY', -999.999 UNION ALL
SELECT '2/24/05', 'YYYYY', 600 UNION ALL
SELECT '2/25/05', 'YYYYY', 650
GO

CREATE VIEW myView99
AS
SELECT [Date]
, SUM(CASE WHEN [ID]='XXXXX' THEN Field1 ELSE 0 END) AS X
, SUM(CASE WHEN [ID]='YYYYY' THEN Field1 ELSE 0 END) AS Y
FROM MyTable99
GROUP BY [Date]
GO

SELECT * FROM myView99
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO|||Brett,
Thank you for the help! You are right. It works fine.
The problem was on my end. In my statement I had a space was quoted in for ID field. (ID=' XXXXX ' instead of ID='XXXXX')

:p

Help using GetColumnInfo()...

Hi, I'm trying to use the following piece of code to obtain the column names from the table in SQL Server, but somehow I'm getting the wrong number of columns in ul_numColumns (see code), and only the first column name in pstr_stringBuffer. Presumably I should be getting a whole string with all the column names there, but I don't. Any suggestions for what I may be doing wrong?..

Thanks in advance

int GetColumnNames(void)

{

try

{

pCommand.CreateInstance(__uuidof (Command));

pCommand->ActiveConnection = pConn;

pCommand->CommandText = "SELECT * FROM t25_pallet_status"; // SQL Syntax...

pRecordset.CreateInstance (__uuidof (Recordset));

pRecordset->CursorLocation = adUseClient;

pRecordset->Open( (IDispatch *) pCommand, vtMissing, adOpenStatic,

adLockReadOnly, adCmdUnknown);

// Get ADORecordsetConstruction interface from the the ADO Recordset

ADORecordsetConstruction *p_adoRecordsetConstruct;

pRecordset->QueryInterface(__uuidof(ADORecordsetConstruction),

(void **)&p_adoRecordsetConstruct);

// From it, we can get the OLEDB <code>IRowset

IRowset *p_rowset;

p_adoRecordsetConstruct->get_Rowset((IUnknown **)&p_rowset);

p_adoRecordsetConstruct->Release(); // don't need it anymore

// The IColumnsInfo that contains ordinals

CComPtr<IColumnsInfo> spColumns;

// Get the the IColumnsInfo from IRowset interface

p_rowset->QueryInterface(&spColumns);

// At this point, we may now release p_rowset

p_rowset->Release();

// IColumnsInfo will give us the DBCOLUMNINFO structure

ULONG ul_numColumns;

DBCOLUMNINFO *p_columnInfo = NULL;

OLECHAR *pstr_stringBuffer = NULL;

// Now get the DBCOLUMNINFO data

spColumns->GetColumnInfo(&ul_numColumns, &p_columnInfo, &pstr_stringBuffer);

// Clean up

CoTaskMemFree(p_columnInfo);

CoTaskMemFree(pstr_stringBuffer);

}

catch(_com_error & ce)

{

PrintComError(ce);

return 0;

}

}

You can do the following to get all the column names

for(ULONG j=0; j<ul_numColumns; j++)

printf("%S\t", p_columnInfo[j].pwszName);

Hope this helps

|||

Thanks a lot Raj,

I kind of sussed it out for my self a short while after I posted the message. I didn't really understand how GetColumnInfo() worked, and I was expecting pstr_stringBuffer to return a string with ALL of the column names (separated by a common delimitter or something). It became apparent to me that I had to loop through and read from a different variable (pwszName) in order to get those names.

I have to say there's very little on examples out there to use functions like this one. Can anyone recomend me a good website where I can get sample code from in C or C++ for the Visual Studio 2003 environment?

Cheers

Help Using a field value in a url jump statement

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fine
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
"535d58cc-a1b3-da11-9864-001320020c86" & "');"
but this one does not allow for the pointer hand to show up when cusror is
over the row
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
Fields!res_dealCompanyid.Value & "');"
Do I need to convert Fields!res_dealCompanyid.Value to a string?
Can I assign it to a variable and then use it in the Jump to URL?
I do not know what else to try
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> "535d58cc-a1b3-da11-9864-001320020c86" & "');"
> but this one does not allow for the pointer hand to show up when cusror is
> over the row
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> Fields!res_dealCompanyid.Value & "');"
> Do I need to convert Fields!res_dealCompanyid.Value to a string?
> Can I assign it to a variable and then use it in the Jump to URL?
> I do not know what else to try
> Mike

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking You
Hi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table us
ing * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table i
s having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
--
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>