Why Adox Can Not Append AdUnsignedBigInt Field ?
Jan 22, 2008
my code:
Code Block
void AppendTableTest()
{
// Define ADOX object pointers, initialize pointers. These are in ADOX namespace.
_CatalogPtr m_pCatalog = NULL;
_TablePtr m_pTable = NULL;
try {
HRESULT hr = S_OK;
TESTHR(hr = m_pCatalog.CreateInstance(__uuidof(Catalog)));
// Open the catalog
m_pCatalog->PutActiveConnection("Provider='Microsoft.JET.OLEDB.4.0';data source='c:\new.mdb';");
//m_pCatalog->Tables->Delete("MyTable");
TESTHR(hr = m_pTable.CreateInstance(__uuidof(Table)));
m_pTable->PutName("MyTable");
m_pTable->Columns->Append("Column1",adInteger,0);
m_pTable->Columns->Append("Column2",(DataTypeEnum)21,8);
m_pTable->Columns->Append("colBinary",adBinary,8);
//m_pTable->Columns->Append("Column3",adVarWChar,50);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
printf("Table 'MyTable' is added.
");
// Delete the table as this is a demonstration.
//m_pCatalog->Tables->Delete("MyTable");
printf("Table 'MyTable' is deleted.
");
}
catch(_com_error &e) {
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
TRACE("
Source : %s
description : %s
", (LPCSTR)bstrSource, (LPCSTR)bstrDescription);
}
but i got a invalid type error. I cannot understand.
why adox can not append adUnsignedBigInt field ?
Somebody can give me a answer? Thanks.
View 1 Replies
ADVERTISEMENT
Aug 27, 2004
Hello,
How do I append data on an update?
I have a table with a field that is nVarchar(1000) and the initial insert is a few sentences. If I wanted to add to that row using an update statement and without starting at the end of the sentences, how would I write that?
Update table set fieldname = 'more data' where value = @variable
instead of
Update table set fieldname = 'initial data more data' where value = @variable
and the 'more data' appends to the initial data... hmmm
help please.
View 3 Replies
View Related
Jan 9, 2004
I deliberately intend to add some duplicates to one of my tables. For eg
Job User IsAdmin
JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5
JobID 235User ID 2
JobID 235User ID 9
JobID 235User ID 10
JobID 235User ID 1
I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:
Job User IsAdmin
JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5 True
JobID 235User ID 2
JobID 235User ID 9 True
JobID 235User ID 10
JobID 235User ID 1 True
Thanks
View 1 Replies
View Related
Jun 14, 2004
Hi all,
I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!!
Here's the proc:
WHILE @Counter < 4
BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot INNER JOIN
tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND
tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN
tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND
tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate
WHERE (tbl_SurgerySlot.ExtractDate = @ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND
(tbl_SurgerySlot.StartTime > @DateFrom) AND (tbl_SurgerySlot.StartTime < @DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1)
SET @FieldName = 'Days' + CONVERT(VARCHAR(20),@Counter)
INSERT INTO tmp_Availability (@FieldName)
VALUES (@AppointmentsCount)
SET @DateTo = DATEADD(Day,1,@DateTo)
--Increment the loop counter
SET @Counter = @Counter + 1
When I run the above the follwoing message is displayed:
Server: Msg 208, Level 16, State 3, Line 36
Invalid object name 'tmp_Availability'.
The object IS valid so I'm lost....
View 8 Replies
View Related
Feb 27, 2006
Hi,
Can anybody tell me how to append data to ntext field?
Joydeep
View 9 Replies
View Related
Dec 10, 2007
I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work:
UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &"
I get an error saying:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text.
How can I append some text to the end of a field? Thanks for any help!
View 14 Replies
View Related
Jul 26, 2004
How can I append a string to the field value in the select list
SELECT Code + '-20' FROM tb.....
I want to the above to return 2000-20 for example.
How can I do this?
Mike B
View 1 Replies
View Related
Dec 8, 2006
We€™re calling ADOX::Table::Keys::Append to make a primary key for a table using the SQLOLEDB provider. We€™re passing an ADOX::Key with Name set to a custom name, Type set to ADOX::adKeyPrimary, and a single column added. For the Append call, the 2nd parameter is ADOX::adKeyPrimary, the third is a vtMissing (VT_ERROR with DISP_E_PARAMNOTFOUND), the last two are empty BSTR€™s.
On XP, this works well (msadox.dll file version 2.81.1117.0). On Vista, this fails with an invalid parameter error (msadox.dll version 6.0.6000.16386).
Has the interface changed in some way, is this functionality not supported, or is it broken?
View 1 Replies
View Related
Jan 30, 2007
I need to rename tables in code VB2005 (access database). On the
forum I found post about ADOX and this code to rename the tables:
Private Sub RenameTables(ByVal sTextToRemove As String)
Dim i As Integer
Dim dbRename As Database
Dim Connect As New PrivDBEngine
dbRename = Connect.OpenDatabase(tbDBPath.Text)
dbRename.CreateTableDef()
For i = 0 To dbRename.TableDefs.Count - 1
If dbRename.TableDefs(i).Name.Length >= sTextToRemove.Length - 1 Then
If dbRename.TableDefs(i).Name.Substring(0, 9) = sTextToRemove Then
dbRename.TableDefs(i).Name = dbRename.TableDefs(i).Name.Substring(9)
End If
End If
Next i
dbRename.Close()
dbRename = Nothing
MessageBox.Show("Tables in " + tbDBPath.Text + " have been renamed.
Rename Access Tables", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
But it doesn't work because i get an error on Dim dbRename As Database Dim Connect As New PrivDBEngine
What I need to do for this to work?
Thank you!
View 3 Replies
View Related
Jul 19, 2007
Hi Everybody,
I want to add new column 'firstaname' into the existing table "geo" already two columns namely
'salcode' 'lastname'.I run the following code it runs without error, when i opened the database see the structure of table, i didn't find new column in the table "geo"
here is the snippet of code what i am using.
/* FieldsPtr fields;
FieldPtr field;*/
_bstr_t name("firstname1");
//pRstTitles->get_Fields(&fields);
/* pRstTitles->Fields->Append(name, adVarChar , 15, adFldUnspecified);
pRstTitles->CursorLocation = adUseClient ;
pRstTitles->LockType = adLockOptimistic ;
pRstTitles->Open("geo",
_variant_t((IDispatch *)pConnection,true), adOpenStatic,
adLockOptimistic, adCmdTable);*/
pls pls pls help me out .. I need it very urgently.
View 13 Replies
View Related
Jan 15, 2008
Hi all,
I have been pulling my hair trying to figure out what the guys at microsoft were thinking when creating the ADOX library. I have an access table that is syncronized with a SQL server. The table has a primary key with two columns [User] and [Program]. The SQL Server has both columns in as the primary key columns and I have a syncronization mechanism that is responsible for several things, one of which is to recreate the Access data structure. All works well for all tables except this one. I have tried to create the multi-column key in several ways, none that worked. Let me show you what I am doing:
CatalogClass catDCDLocal;
Column c;
catDCDLocal = new CatalogClass();
catDCDLocal.let_ActiveConnection(dbAccess.buildConnectionString(Settings.CattDCDLocalPath, Settings.SecurityDBPath, s.UserID, s.Password));
foreach (Table tbl in catDCDLocal.Tables) {
if (tbl.Name == "Users") {
/* This is retarded so need to clean up... Users table has a primary key consisting of 2 columns */
for (int i = tbl.Keys.Count - 1; i >= 0; i--) { //remove the keys
tbl.Keys.Delete(i);
}
for (int i = tbl.Indexes.Count - 1; i >= 0; i--) { //remove the indexes
tbl.Indexes.Delete(i);
}
tbl.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyUnique, "User", "", "");
tbl.Keys[0].Columns.Append("Program", DataTypeEnum.adWChar, 6);
}
}
I have also tried:
tbl.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyUnique, "User", "", "");
//tbl.Keys[0].Columns.Append("Program", DataTypeEnum.adWChar, 6);
Key k = tbl.Keys[0];
Column col = tbl.Columns["Program"];
//col.ParentCatalog = catDCDLocal;
k.Columns.Append(col, DataTypeEnum.adWChar, 6);
Nothing works for me ;-(
View 18 Replies
View Related
Oct 29, 2007
I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.
Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.
When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."
Am I on the right tracks here or do I need to adopt a different approach?
Code block to illustrate the problem :-
Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);
m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");
m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);
m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);
// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;
//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;
m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!
m_pRs->Update();
m_pRs->Close();
m_pConn->Close();
View 3 Replies
View Related
Jun 28, 2007
I'm using ADOX 2.8 for table creation: The following is an example of a column defintion:
If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)
If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject
If CreateNewTable Then CreateNewTable = a.CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18) 'text
Where CreateColumn looks like this:
Public Function CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0) As Boolean
'ADOX.CreateColumn - Called by Common.CreateNewTable
'CreateColumn creates a column described in the Table object so it assumes it is set.
'One method of setting it is to call Select Table after opening the database
If Not Me.ConnectionIsOpen Then
MsgBox("CreateColumn - Failed to Create Column : " _
& ColumnName, MsgBoxStyle.Exclamation, cNoConn)
Return False
End If
Dim col As New ADOX.Column
col.Name = ColumnName
Try
col.Type = Datatype
Catch e As Exception
MsgBox("CreateColumb - Failed to Create Column : " _
& ColumnName, MsgBoxStyle.Exclamation, e.Message)
col = Nothing
Return False
End Try
If Size <> 0 Then col.DefinedSize = Size
Try
Table.Columns.Append(ColumnName, Datatype)
Catch e As Exception
If Err.Number() <> 0 Then
MsgBox(Err.Source & "-->" & Err.Description, , "Error")
End If
MsgBox("CreateColumb - Failed to Append Column : " _
& ColumnName, MsgBoxStyle.Exclamation, e.Message)
Return False
End Try
col = Nothing
Return True
End Function
in CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18)
the 18 specifies the field width in the database. Yet no matter whether I use adWChar or
adVarWChar, Access always shows the field size to be 255.
Does anyone know why or how to fix that?
View 1 Replies
View Related
Jun 5, 2007
Hai,
I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.
The error message is "Invalid Argument".
I use the following code for creating the linked table
Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table
Dim ADOConnection As New ADODB.Connection
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")
catDB = New ADOX.Catalog
catDB.ActiveConnection = ADOConnection
tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB
' Set the properties to create the link.
Dim adoxPro As ADOX.Property
adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True
adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString
adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl
End With
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")
'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)
'Append the primary index to table.
catDB = Nothing
End Sub
If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.
Thanks in advance
Sudeep T S
View 4 Replies
View Related
Nov 26, 2007
Hi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow
Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Any idea?
Marco
View 7 Replies
View Related
Feb 26, 2007
I want to create a stored procedure that will take filtered entries from one table and insert them into another table. I have created stored procedures using variables but what is the best way of taking data from one table to another?
View 3 Replies
View Related
May 4, 2006
i hav column in table which already conatins data, now i want to append some more data to it.how do i do it so that earler content does not get deleted
View 2 Replies
View Related
Nov 15, 2006
Hi
I'd like to create a table on our SQL server that I can append records to when running a query.
What i intend to do is create a new table with the same fields as the query then when the query runs i want it to append the results to this new table. Is this possible? How would I go about creating the table, primary key etc?
I've tried doing this through an access front end but its not efficient and was a bit of a struggle to be honest.
Also, we need other people who may not have access to enterprise manager to be able to run this append query.
Danny
View 7 Replies
View Related
Aug 31, 2007
I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValueSo the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql. So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.
View 4 Replies
View Related
May 20, 2005
My Problem is
I have 2 views --> 2 Databases (2 Products) but there are same fields (Same structure)
and I have to created the report by Crystal Reports to compare the Quatity of all product in my Company
So how Can I combine them (2 views with the same recoed but not the same data)
thanks for helping me
Kate
View 1 Replies
View Related
Jul 5, 2001
I need to copy data from one SQL table to another SQL table. Is is possible to use DTS to Append and update data from one table to another....along the line of using a Microsoft Access append or update query?
View 1 Replies
View Related
May 28, 2000
I have a huge table with data.I run a procedure everyday to update the table's data with our daily current sales.Let's say the table's primary index is product type.The procedure recreates a skeleton of the table to make sure that it will add only those records whose product type is new to our database thus ignoring the rest of the records due to duplicate key violation error.Now this used to work in access where I used to get a message saying that only 200 out of 4000 records were added,3800 were ignored due to violation key errors.
But in SQL Server, no records at all are being added.Is there a way to overcome this problem?? I tried using "set xact abort" but it only worked in case of foreign key violation but not primary key.
I would really appreciate your inquiry.
Thank you.
View 2 Replies
View Related
Feb 24, 2007
Here is a sample query:
SELECT column + 'xyz'
FROM table
Works great and I get the data I need, but how do I get this into a new column?Any help would be appreciated.
Thanks
View 3 Replies
View Related
Dec 1, 2007
Hi guys I have a quick Q:
I have two tables:
tblOne
ID/ PageName / Desc /URL
12/Home Page/This is the home page/www.fff.com
tblTwo
ID/ Name /Link
34/News Page/www.bbb.ie
I wish to create a new table or stored procedure to append one table(which has 4 colums) onto the other(which has only 3 columns) giving the following results:
ID / Name /Link
12/Home Page/www.fff.com
34/News Page/www.bbb.ie
Thanks for help in advance!
View 2 Replies
View Related
Oct 26, 2004
I would highly appreciate help to :
I have two databases that are of the same schema but in one-database-A the records are a bit older than the other database-B. I would lilke to sync the records from database-B to database-A appending the records to database-A. The schemas have constraints,primary keys and other defaults in common.
What is the best way to do this task? I tried to use DTS but it fails throwing error Primary key violations on the tables.
Thanks a lot for a good suggestion.
Vinnie
View 4 Replies
View Related
Apr 5, 2006
I have a bcp command that works fine except that I want it to over write what is in the table rather than append to it. Is there a way to do this or another method I should try?
View 5 Replies
View Related
Jul 23, 2005
Hello all,Number of rows in a table was increased significantly. Is there a way to seewhether data was inserted in to the table, or whether data was appended tothe table?Thanks in advance,Do.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Sep 15, 2006
What would be the steps to create a SSIS job to append records from an ODBC table to a SQL table, adding only the records from the source that do not already exist in the destination?
I have another post on this subject, with a good suggestion for the approach, but I need some more detailed instructions for implementing it:
Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior
View 4 Replies
View Related
Jan 17, 2008
hello everyone,
i have the following problems:
I have a ChartReport that make a lot of chart, one for each page of report, this use as datasource myDataSet_Charts.
I have a SummaryReport that make a table, only a page report, this use as datasource myDataSet_Charts too.
I have need to produce a report that have in the first page the SummaryReport output, and in append the ChartReport output.
There is a simple way to make this?
View 5 Replies
View Related
Jul 9, 2007
I have one cloumn with 10 rows and I want to read all those value to textbox, or another way I want to append all value to each other, I think I have to loop through it, so how can I do this. I have this column in SQL table. some plese tell me how to do this
thank you
maxmax
View 4 Replies
View Related
Mar 26, 2008
I try to get the value Pos1 from the SQLQuery from SQL Server 2005 Express, and set to iPos1 variable.
webcodefile1.vbDim myConnection As SqlConnection = _
New SqlConnection(ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString)Dim myCommand As SqlCommand = New SqlCommand("GetRandomPosition", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Append(CmdPuzzle.CreateParameter("@Pos1", adTinyInt, adParamOutput)) <---- in aspnet, the Append is not being used, what should i use instead?
iPos1 = MyCommand("@Pos1")
View 1 Replies
View Related
Jun 7, 2004
Hi All,
When calling my SELECT statement, I would like to have Column name and Data type as the first record. Is this possible to do?
I would like to make a SP that would select all data from any table(with a header row) with just:
_sp_getData ' table_name'
I looked at sysobjects, and syscolumns tables, but was unable to put it all together. I have over 200+ tables, so creating variables for every column is very cumbersome.
Something like this:
select 0 AS SortCol ,
cast(min(case ordinal_position when 1 then column_name end) as varchar) as col1,
cast(min(case ordinal_position when 2 then column_name end) as varchar) as col2,
cast(min(case ordinal_position when 3 then column_name end) as varchar) as col3,
cast(min(case ordinal_position when 4 then column_name end) as varchar) as col4,
cast(min(case ordinal_position when 5 then column_name end) as varchar) as col5,
cast(min(case ordinal_position when 6 then column_name end) as varchar) as col6,
cast(min(case ordinal_position when 7 then column_name end) as varchar) as col7,
cast(min(case ordinal_position when 8 then column_name end) as varchar) as col8,
cast(min(case ordinal_position when 9 then column_name end) as varchar) as col9
from information_schema.columns where table_name = 'authors'
union all
select 1 , au_id, au_lname, au_fname, phone, address, city, state, zip,
cast(contract as varchar)
from authors
Is not an option.
Thank you very much in advance,
Vla Orlovsky
View 5 Replies
View Related
