How To Add Columns And Use Them At Run Time Using VB 2005

Sep 11, 2007

In order to allow my customers to use their existing data and install updated application, I want to be able to add columns to existing CE tables at startup time, and allow the user to enter data into the new column(s) immediately. I used the ALTER TABLE command as shown in the example below:

cmd.CommandText = "ALTER TABLE tblStores ADD dtDemoDate datetime, dtDemoMode int"

cmdexe = cmd.ExecuteNonQuery()
To add the column, But this does not set the columns in the attached dataset. The program sees the columns and data is able to be added, but is not then in the underlying table? What else do I need to do?

Thank you - b2bMan

View 1 Replies


ADVERTISEMENT

Select Two Columns At Same Time

Aug 27, 2007

 
how can i retrieve two columns from sqltable with - seperating the results and a common column name
someth like this
select id ,name from user
id     name
1         a
2         b
3         c
i need the result set to be
my_reports
1-a
2-b
3-c
is that possible, if so, could anyone tell me how to do?
 

View 3 Replies View Related

Updating Several Columns At The Same Time

Jun 18, 2002

Good morning,
I want to update two columns in the same time by making a select from another table. Some think like the following;

Update TBL1 o set (o.COL1,o.COL2) =
(select f.COL1,f.COL2 from TBL2 f where f.PKY = o.PKY)
where o.COL3 = 100;

Thinks to helping me .

View 1 Replies View Related

Updateing 2 Columns At The Same Time

Jul 3, 2007

Hi please help me out !!
I have to update the incidents table.
The initiator and initdept are two corresponding columns in incidents table whoes value I have to get from another table Employee where (initiator means Empname and initdept means title)
if i just do

select Empname,title from employee

it gives me correct output while

select initiator,initdept from incidents

It gives the initiator correct but instead of giving the initdept
it gives output as initaiator
The Output is given below

initiator initdept
Jeff C. Taylor Jeff C. Taylor
Randy S. Jonas Randy S. Jonas
Mike lewis Mike lewis

it should give the out put like

initiator initdept
Jeff C. Taylor Software Engg
Randy S. Jonas Tester

I hope you got my question!
now I hav to update incidents table such that it should give initiator as well as its corrosponding initdept

thanks

View 5 Replies View Related

ODB Datareader Time Columns

May 26, 2006

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF







View 11 Replies View Related

How Can I Update 2 Columns At A Time In 2 Tables

Mar 11, 2002

Hi
I have a 2 Tables EMP, STU
In EMP Table there is a Column "Country"
In STU Table there is a Column "City"
Where the EMPID = STUID
on this conditon how can i update those 2 columns

This is for one Table one column update how can i do 2 at a time
I don't want to do in 2 seperate UPDATE statements

UPDATE EMP
SET EMP.Country = 'USA'
WHERE EMP.EMPID = STU.STUID


Can some one can help me to fix this issue..


From
Madhavi

View 2 Replies View Related

How To Replace Values In Different Columns At The Same Time

May 14, 2004

HI,

I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".

NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.

HERE IS HOW THE PROC WRITTENED FOR IT GOES :-


DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR

EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END


THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.

BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'

FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-

CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5


NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"

CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?


AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .

PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.

View 6 Replies View Related

Adding Columns With Different Time Unit

Oct 29, 2014

I have three different columns, hour(s), min(s), sec(s)

I can add it up, but will like to convert it into. hrs, mins and sec.

this is how, i am adding it up into seconds.

SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1

How can I convert the total seconds, so that i can input the result in a new column.

View 1 Replies View Related

How To Alter Multiple Columns At Time

Dec 4, 2006

Hi all

ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>

above query alters the sigle column but how to alter multiple columns at time

saivyshnav
Junior DBA

View 7 Replies View Related

Combine 2 Columns To Get Date Time

Aug 28, 2007

adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39

I would like to combine adate with atime to get adatetime

View 5 Replies View Related

Can I Use OUTER JOIN On 2 Columns At The Same Time?

Sep 12, 2007



I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.
I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.
I have following query which does not produce desired result


SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)




The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.
I'm pretty sure it's simple solution but can not figure out myself.

View 4 Replies View Related

Dynamic Time On Columns With Reporting Services

May 17, 2005

Hi all!

If I have some mdx I'm using in reporting services like this:

select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls

The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.

I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.

What do you think? Would a matrix be more flexible in this case?

Richard

View 7 Replies View Related

Get Latest Records When Date And Time Are Separate Columns?

Mar 26, 2012

I have 2 tables:

TransactionsImport (which is the destination table)
TransactionsImportDelta

I need to do the following:

Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.

Problem is date & time are in separate columns:

Table structure:

Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600

View 2 Replies View Related

T-SQL (SS2K8) :: Matching Closest Time Between Two Columns Between Tables?

Mar 10, 2014

I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.

Example lets say once per hour.

I want to join to a table called Weight that records the newborns weight at any given time.

Example lets say 3x during the day.

1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm

I want to figure out which weight recording is the closest to a given temperature recording and return that one row.

View 2 Replies View Related

Transact SQL :: Represent Time Interval Between 2 Date Columns

May 20, 2015

I have a simple table as shown:

I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have

entry(EventID = 2)  - exit(EventID = 1), and so on

View 7 Replies View Related

Is There A Way To Drop All The Columns Called Some_name In All The Tables At The Same Time

May 15, 2008

Hi,

In my DB, many of my tables have a column named upsize_ts I have already been told that it is not linked in any way with the data in the DB. The data type of these columns is timestamp and every record in those colum is this : "<Binary>"
Now maybe it's because the whole DB comes from an export from an Access DB, or maybe not. I just had a few questions:

1/ Does anyone know why that column got generated and what it means? (optional)
2/Now the real question: is there any statement allowing me to "mass-drop" all those columns named "upsize_ts" in every table of my DB at the same time? Or at least any way not to do it one drop table at a time?


Thanks.

View 8 Replies View Related

Sync Of Binary Columns In SQL 2005 EV And SQL 2005 EX

Jul 5, 2006

Hi.

I'm working on an application that will use SQL Server 2005 Everywhere Edition for single user installations (i.e. notebooks) and SQL Server 2005 Express Edition for network installations. The single user databases will synchronize with the network databases.

The databases will have to store a large number of documents of different kinds, most of them will be less than 200 KB in size. According to the SQL Server 2005 Express Edition documentation, I should avoid using the IMAGE data type and opt for VARBINARY(MAX) instead. The current version CTP of SQL Server 2005 Everywhere Edition does not support VARBINARY(MAX) (the maximum size is 8000 bytes).

Will the final version of SQL Server 2005 Everywhere Edition support VARBINARY(MAX)? Or should I use the IMAGE data type in both schemas or IMAGE in the SQL Server 2005 Everywhere Edition database and VARBINARY(MAX) in the SQL Server 2005 Express Edition database?

Do I have to consider anything special when the databases are synchronized via replication because of the large number of rows with binary fields?


Regards,

Gerrit

View 1 Replies View Related

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Aug 7, 2007



Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View 5 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View 1 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Installing 2005 For The First Time

Mar 15, 2008

ok. Here we go again. they got my Servers built yesterday and here I am on a saturday doing the installs

I ran SQL SERVER 2005 Standard Edition Setup on my Windows 2003 Enterprise Server with SP2

it ran through and installed Client tools, BOL and such.. but did not install an actual Instance. I never got the Advanced button to choose which components to install. What did I do wrong????????

I need to install a Named Instance on Both Server.. one will be DEV one Production. help?

View 3 Replies View Related

Age At Time Of Service - SQL 2005

Feb 4, 2008

I need help with the following field:

'Age @ TOS' = DATEDIFF (YY, patientprofile.birthdate, patientvisitprocs.dateofservicefrom),

PatientProfile.Birthdate - 01/01/2001
PatientVisitProcs.dateofservicefrom = 07/01/2001

In my report, I get a '0' as my Age @ TOS. What I would like to have is 7 months vs the '0' it is reporting. I would like to try accomplishing this without doing a function if possible. I know I used YY in my DATEDIFF. I only noticed this today because of a few patients in my DB were under 1 yr at the TOS.

View 1 Replies View Related

No More Datatype TIME In SQL 2005...what To Do???

Jul 2, 2006

I understand that SQL 2005 doesn´t support TIME ( which is, for me,
incredibly weird). Now, could anyone please help me solve my problem. I
want to make it possible to insert into database a start-time for the
first runner in a team. And when he finishes, a finish-time. This
finish-time I will also use as a start-time for the second
runner....a.s.o. Furthermore I want to be able to get the results from
the database to present them in several ways. This also means I can't
simply insert the time as a string since it won't change hour at every
60 minutes. I have read somewhere that it should be possible to create
own datatypes, but I have absolutely no knowledge to that. Could anyone
please help me?

View 14 Replies View Related

XML Columns In SQL 2005 &&amp; SSIS

Mar 6, 2007

Is there a way to pull data from XML columns with SSIS? I mean parsing the XML and pulling only data from XML column without putting it into an XML file first. I have been using xpath queries (XML value method) to do this. Is there any other way?

Thanks

View 2 Replies View Related

Date Time In Sql Server 2005

Dec 3, 2007

hi all,
in my sql server 2005 , i 've a table gg containing a column 'date'.its data type is datetime ,null
now i want to insert only the date to this field from vb.net page. i dont want "time" to insert in this page. one more thing ,i know varchar will work for what i need but i want to use date time. If anyone who knows how this is possible pls do send me the code.
 thanks swapna

View 10 Replies View Related

Installing Sql Server 2005 For The First Time

Feb 12, 2008

I will be installing SQL Server 2005 enterprise edition for the first time and was looking for some hints...

unfortuntely, I don't know alot about what kind of applications they will be using the databases for.. but I do know that I have 400GB to allocate. My manager suggested a seperate volume for the SQL Server Binaries.. not sure why... ANY OPINIONS ON THAT???

This is what he suggested
C: OS
D:SQL Server application install
E:datafiles
F:Logfile
e:tempdb

any hints, suggestions, opinions, things to watch out for... ???????

I was thinking more along the lines of
c:OS and application installs (sql server etc..)
E:datafiles and TempDB
F:Logsfiles

View 6 Replies View Related

SQL 2005 Time Sensitive Question

Apr 24, 2006

I have a problem installing sp 1 for sql 2005 express. How can I check if I have sql server or the sql 2005 express edtion and if it is a beta or not. I want ot install the SP1 OF SQL 2005 When I go to the add/remove program section I see sql 2005 edition and not sql 2005 express edition, very confusing because it indicates to look for sql 2005 express edition in the add/remove program and remove it. I don't even know if I have a beta release or not at this point anymore. Is sql 2005 express packaged with VWD 2005 express edition.

View 3 Replies View Related

Reporting Services 2005 Time Out

May 6, 2008

Hello,
We are using Reporting Services 2005 with large amount of data.
Our reports displays the data in various presentation (charts, tables, Images and matrixes).
Sometimes the underline queries takes a lot of time (few algorithms implemented by SQL and few by web services).
We tried to run the reports in various scenarios and we are getting a timeout n each of the scenarios:
1. Running the report with windows application (based on Report Viewer control)
2. Running the report via subscription.

We noticed than the timeout occurs after about 20 minutes.
WE changed the following parameters:
1. The system timeout in the report server database: 60000
2. Reports execution timeout (site level): Do not timeout report execution
3. Reports execution timeout (report level):
a. Do not timeout report execution
b. A very big number

View 1 Replies View Related

List Columns In A Table In SQL 2005

Apr 2, 2008

Hi, I know sys.tables and sys.columns gives me a list of tables andcolumns in a SQL 2005 database.How can I list Columns in a specific Table please?Thanks in advance,Ronny

View 3 Replies View Related

Calculating Time Between Two Date In Sql Server 2005

Mar 17, 2008

Calculating time between two date In Sql Server 2005help me .. 

View 3 Replies View Related

Time Format - What's The Smallest Unit SQL 2005 Can Take?

May 2, 2007

saw my AS400 has this time stamp: 2007-05-02-11.29.56.811195

is SQL2005 able to have time stamp to this details?

View 1 Replies View Related

First Time Installation Of SQL Server 2005 Develop

Oct 13, 2007

Just placed the SQL Server 2005 - Developer Edition into my Drive...

I get prompted with the following options..
This DVD contains 32-bit (x86) and 64-bit versions of this edition of SQL Server 2005. Click the link that matches your environment to begin
x86-based operating systems
x64-based operating systems
Itanuim-based operating systems
I'm new to SQL Server... I just need to start learning how to use it... because it is being used at work... Don't need the all the features installed, just the normal table and query for the moment...
I will be installing it on my PC... i
Microsoft Windows XP - Home Edition - Version 2 - Service Pack 2
My local Hard Drive remaining capacity is 12.6GB
External Hard Drive remaining capacity is 11.0GB
Tried looking everywhere for the answer... lol (even looked at YouTube for a simple installation video....
Thanks for your help

View 14 Replies View Related

Time Out Expired Error When Using Ado And SQL Server 2005

Oct 18, 2007

Any help would be appreciated.

We have a C++ program that connects to a remote SQL Server database. Recently the system we pull data from upgraded to SQL Server 2005 and my program can no longer connect to the database. We're getting TimeOut expired errors (not login timeout expired) within 15 - 20 seconds after attempting to open the connection. I have confirmed with this other system that my id does not show up in any logs on their end.
Here is some background:
Nothing has changed on my end. We have another system that my team supports that is able to connect, and they have changed nothing. They're using a different connection method. We're using oledb and they use odbc and a different provider. I confirmed with the SQL Server team that this id made a connection.

Connect String:

Provider=SQLOLEDB.1;;User ID=XXX;Password=xxxx;Data Source=serverdb instance;Initial Catalog=xxxx;
From what i've read, etc this connect string should work still

Here is an example of the code and context of this connect string

'Set connection object
pTEST_HR(pConnect.CreateInstance(__uuidof(Connection)));
pConnect->ConnectionString = OurConnectString;
pConnect->Open("","","",adConnectUnspecified);
'Create and open recordset
TESTHR(pRstEmployee.CreateInstance(__uuidof(Recordset)));
pRstEmployee->Open("Employee", This is the point at which we get a timeout.
_variant_t((IDispatch *)pConnection,true), adOpenKeyset,
adLockOptimistic, adCmdTable);

pCmd->CommandText = SomeString;

pRstEmployee = pCmd->Execute(NULL,NULL,adCmdText);

Server Info:
I have succesfully pinged the server
The client is not in the same domain as the db server

Error:
Appl: A database error has occured ->
Error
Code = 80040e31
Code meaning = IDispatch error #3121
Source = Microsoft OLE DB Provider for SQL Server
Description = Timeout expired

Other info:
Not sure about my client database provider.
This is coded in Visual C++ 6.

I'm not knowledgeable on SQL Server configuration, etc.

Things i've tried so far
Change provider from SQLOLEDB.1 to SQLOLEDB
Change provider to SQLNCLI
Had SQL server query time out set to never time out. The remote login timeout changed to 600 seconds from 30 seconds. However we are getting a timeout in only a few seconds, so i find that interesting.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved