|
Hallo
I have 3 Tables
<br />
1st table with idfrist - descriptionfirst<br />
2nd table with idsecond - descriptionfirst<br />
3rd table with id - idfirst - idsecond - value<br />
Now i'd like to built a transformed tabel:
colheader with all items from idfirst
rowheader with all item from idsecond
values as sum from 3rd tables value (cols are( tofirst - tosecond - value)
The transformation works fine for all values entered.
But if one col from the colheader isn't used in the 3rd table,
there is no output column for this field defined in the first table ?!?
here si what is already have done with 2 tables
<br />
TRANSFORM Sum(third.value) three<br />
SELECT second.tofirst<br />
FROM third RIGHT JOIN second ON third.idsecond = second.tosecond<br />
WHERE (((third.tofirst) Like "*"))<br />
GROUP BY third.tofirst<br />
PIVOT second.idsecond;<br />
thx for helping
Tim
|
|
|
|
|
Got to this link and register free for
training your self for VS2005
https://www.microsoftelearning.com/visualstudio2005/
or
http://www.financialexpress.com/latest_full_story.php?content_id=107645
Microsoft dont give anything free except training
Vikas Amin
Embin Technology
Bombay
vikas.amin@embin.com
|
|
|
|
|
Must you spam every bloody forum multiple times?
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 21:38 Saturday 5th November, 2005
|
|
|
|
|
|
i am trying to get the data about database from a database(msql/access) using schemas(oledb)but the result is i am unable to get all information like column datatypes big/long/var-Binary and if a column is of type long then weather it is a autotype or simple long etc
in simple i wana get all information of the database. Suggest me a way
Know it share it that is correct knowledge (...kittu)
-- modified at 3:26 Saturday 5th November, 2005
|
|
|
|
|
Hi, take a look at ADO.NET 2.0. In .NET 2.0 it is much easier to get schemas.
'A programmer is just a tool which converts caffeine into code'
|
|
|
|
|
I am Using VS C# 2005 But unable to get all meta dadata info
/************************************************
try
{
ref_con.Open();
DataTable dt1 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
_dataset.Tables.Add(dt1);
DataTable dt2 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
_dataset.Tables.Add(dt2);
DataTable dt3 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { });
_dataset.Tables.Add(dt3);
DataTable dt4 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints, new object[] { });
_dataset.Tables.Add(dt4);
DataTable dt5 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, new object[] { });
_dataset.Tables.Add(dt5);
DataTable dt6 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { });
_dataset.Tables.Add(dt6);
DataTable dt7 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Constraint_Column_Usage, new object[] { });
_dataset.Tables.Add(dt7);
DataTable dt8 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { });
_dataset.Tables.Add(dt8);
_dbtables.GetTableList(dt1, dt2,_dataset);
_dbcolumns.GetColumnList(dt1, dt5, dt6, _dataset);
results = true;
}
catch (Exception excp)
{
System.Windows.Forms.MessageBox.Show(excp.Message,"Failed To Get MetaData",
System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Error);
}
finally
{
if (!ref_con.State.Equals(ConnectionState.Closed))
ref_con.Close();
}
but still unable to get all info
try this using a table containing columns of AutoNumber,long
and see the datatable value-----Provider_Types.
there same number value for some(binary) data types
************************************************/
Know it share it that is correct knowledge (...kittu)
-- modified at 21:34 Saturday 5th November, 2005
|
|
|
|
|
I am using MSDE on my computer. I have application that uses SQL. The application and MSDE are on the same computer. My connecting string follows:
this.cnKennel.ConnectionString = "Data Source=(local)\\NetSDK;packet size=4096;integrated security=SSPI" + ";persist security info=False;initial catalog=kennel";
Because I will be installing this application on many computers I would like not to specify the computer name/server name. Can someone tell me how I can do this.
|
|
|
|
|
I guess I don't clearly understand the question.
If the MSDE will always be on the same computer as the application, then your current connection string will always work (provided MSDE installed with the instance name NetSDK).If MSDE is installed without an instancename, then just DataSource = (local) will always work on the same box.
If the MSDE will be on only one of many computers, then you have no choice but to specify the name of the server (or its IP address if using TCPIP network library) and the instance name (if installed as an instance).
If there will be multiple clients, you likely should read the server name part of the connection string from a configuration file for your app, so it can be changed without recompling.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Thank you much, that was it!!!
|
|
|
|
|
Hello
I want to get data from MSSql sample database: pubs
I registered it in ODBC as sqlMY
(i use dialogWindow, without database support)
I do:
CDatabase *dat;
CRecordset *rec;
dat=new CDatabase;
dat->Open( "sqlMY", FALSE, FALSE, "ODBC;", TRUE );
new CRecordset(dat);
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "select * from authors", CRecordset::none
);
And it shows error messege in rec->Open(...) , error is about SQLFetchScroll/SQLExtendedFetch
1. What i do wrong?
2. How can i access to my row data in CRecordSet (shouldi do CMyRecordSet() derived from CRecordSet and add there my data column as variables)?
thanks
Best Greetings
Michal
|
|
|
|
|
ok, i found, but....
<quote>virtual BOOL Open( UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none );
(...)
lpszSQL
A string pointer containing one of the following:
A NULL pointer.
The name of a table.
An SQL SELECT statement (optionally with an SQL WHERE or ORDER BY clause).
A CALL statement specifying the name of a predefined query (stored procedure). Be careful that you do not insert whitespace between the curly brace and the CALL keyword.
even if i do
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "authors",CRecordset::none );
it still have error
df
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
1. @@Servername is always going to give you the name of the server on which the SQL Server resides, regardless of which network client is used to execute the sp.
2. "G:/Export" always refers to a drive that is local to the SQL server (either a physical drive letter or a mapped drive). Prifixing it with the servername acomplishes nothing, since as the error messge states, that is an invalid way to specify a path.
3. You might be able to specify a UNC name: //SomeComputerName/somesharename/export,
but you would need to
a. share the destination drive (Say as Exports)
b. provide the sharing source comuter name either as a parametr value, or 'hard coded' into the SP. For example, if the destination computer is named "FileServer1",
and it shares the exports directory as Export_Share, then you would replace "G:\export" with "//FileServer1/Export_Share"
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
Hai,
I am having a table(Suppose Table1) with some data. After wards I have made some changes in the table so that the primary key changed and added some more columns(some references also). How I can write the script to modify the table with this change with out dropping the table. Because I need the data saved in the table. How I can write the upgradable script for that.
Please show me the right way. Thanks very much.
Thank You,
Rahul.
"Learn from Mistakes for suceess otherwise get buried with the Mistakes"
|
|
|
|
|
You could use ALTER TABLE[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
May be it's late now... but in Enterprise Manager, when you enter in 'design table', after you make the changes, there's a button in the tool bar that says Save Change Scripts, that will save a script that'll do exactly what you are asking for  . So you can run this script in another server for example. The script alters the table, it doesn't drop and remake it.
daniero
|
|
|
|
|
daniero wrote: The script alters the table, it doesn't drop and remake it.
Not necessarily. Depends on what you are doing. Sometimes this script will copy the data to a temp table, drop the existing table and recreate it, then rewrite the data back into it. Alot depends on what you are modifying, and whether or not default values can be used. Otherwise...great suggestion....letting sql server create the script is the best thing to do.
|
|
|
|
|
hellow to all ..
i have a tabels with data , i have field that have dates ...
how can i write an sql statment that pick the first date from all the dates that the tabel have ?
by first i mean the smaliest date ..
thxx
|
|
|
|
|
SELECT MIN(dateColumn) FROM MyTable
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
|
Hello,
I have a datagrid which l want to save the contents to the database. I am not sure how to do this. Would l have to save one row at a time, or can l save everything and at once. I am using C# with SQL Server 2000. I normally write stored procedures for database operations. But with the datagrid I am not sure how this is done.
Some code examples would be very grateful.
Thanks in advance,
Steve
|
|
|
|
|
Hi all,
How can i make the change to user the @@servername variable so when I run the out of a different server I don’t have to change the code. I want to make change so I don't have to put the server name (such as @@servername = testserver) how can i define that?
CREATE FUNCTION fIsProduction ()
returns bit
AS
begin
declare @Prod bit
SELECT @Prod =case when @@servername = 'testServer'
then 1 else 0 end
return @Prod
end
|
|
|
|
|
Why not put it in a configuration file that you read at startup?
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
