I was wondering how to modify a column's properties, like its datatype or length.
I know I'm supposed to do something like this: ALTER TABLE MyTable ALTER COLUMN MyColumn int(20)
My main concern is, what will the database do to all the data in the column, if the column used to be, say nvarchar(50)? Will I lose the data in the column if I change the datatype? And what if I had data in this column that was longer than 20 characters? Will the data now be truncated? What can I do to make sure that nothing happens to the data once I change the datatype?
How do I modify data in a sqlDataSource object (like inserting records, editing, deleting) similiary to how I modify data in a GradView using a DataList or repeater? I want to be able to have my own EditTemplate and be able to edit say four fields while I'm only viewing like two of them in the datalist/repeater. <asp:DataList ID="DataList1" runat="server" DataKeyField="GameId" DataSourceID="SqlDataSource1" RepeatColumns="1"> <ItemTemplate> <%# Eval("field1") %><%# Eval("field2") %>.... </ItemTemplate> <EditTemplate>......</EditTemplate>
When I'm trying to add a edit-button (<asp:Button runat="Server" ID="EditButton" Text="Edit" CommandName="Edit" />) and click it nothing happens....What is missing?
i am using a view grid to display data the viewgrid is tied to an sqldatasource control i want to give the user the ability of modifing data by colomn instead of by row how could i do ?
Trying to change some of the rows in a table specifically one column. column type is varchar ex. of data current desired $345,434.0 345434.0 (stripping out of $ & commas) 435.0 leave as is general txt leave as is
having trouble updating data in table tried using temp table, but update command make it so it won't see table
ex. piece of code update currency_conversion set currency_varchar = cast (cast (currency_varchar as money)as varchar) select * from currency_conversion where substring (currency_varchar from 1 for 1) = '$' ;
I have an existing VB6 application which uses an Access 2003 backend datafile, however, I would now like to modify this to use an SQL 2005 datafile, but I don't know how to go about this or the implications when the application is installed on another machine.
The code I use for accessing the Access 2003 file is below, I want to convert this code to access an SQL 2005 datafile instead, can anyone give me some pointers on the best way to go about this, or code sample.
Dim rst As ADODB.Recordset Dim dbs As ADODB.Connection
Set rst = New ADODB.Recordset Set dbs = New ADODB.Connection
In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.
In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?
I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.
I've created a Union All data flow task which unions string fields from a couple of queries. I found that I needed to increase the length of the string field in the queries. Increasing the length causes an error in my Union All task. I can't seem to find a way to change the property of the fields in the Union All task. Most of the other data flow tasks have an advanced editor where I can change the length or type of the fields on the input and or output. I can't seem to find this for the Union All task.
Is there any way short of deleting the Output column and recreating it? (I like the order of my original output columns)
If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??
Trying to modify the workbook connection string but it is greyed out and unable to change the Provider= from SQLNCL10 to SQLOLEDB. I am able to change the PowerPivot Data Connections connection string but not the workbook connection string. On the forum only see where people have asked the question but it seems like the people who've asked ended up recreating the data models.
Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case! in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:
1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very confusional , and very difficult to maintance.
2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.
You should not give limitation to Sql Function, should just limit the using context of Sql Function!
The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!
I need reflecting changes of csv file in oracle DB. Suppose, I load single csv file in oracle DB which contains 10 rows. After some time, I have loaded another CSV file which has the modified row of the previously loaded csv file. So, how can I capture the CSV changes and how it is going to get reflected in oracle DB?There is no unique column in csv file to identify particular row.
Hi, all experts here, Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows? I am looking forward to hearing from you and thank you very much in advance for your help. With best regards,
I have a fairly simple data flow task that loads data from one table (OLE DB Source) into another table (OLE DB Destination). The data type for one of the pairs of columns is nVarChar(120) and it contains version information that looks like a decimal. When I run the export, the destination has a trailing zero added after the decimal point as if it were a numeric column which invalidates our comparisons (string 1.0 is not the same as string 1.00). There is no cast or convert done to this column, it is a straight copy. Any ideas what could be causing this or how to fix?
I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.
The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!
Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?
First of all I am not familiar with SQL so bear with me.I have a field called stock_code, the data in that field, looks likethis000000851. I need to replace only the first two charachters '00' with'DR'.If I use the replace function, it will of course replace everyoccurance of 00 which I dont want.Apart from exporting the data out to excel and changing it thereThanksAlan
How do I go from a text data type to a numeric data type without having to delete the column and put it back out there. I tried to changed the data type but it gave me a error saying that I couldn't go from text to int data type. Help.
Hi All,I have a varchar(255) column for storing text in english and inhebrew.It was a stupid mistake to set the data type to be varchar, becausenow I need to store text in german and francais too.Question: Is it safe to change the data type from varchar to nvarchar,without damaging the data that's already present?(I have more than 1000000 records stored...)Thanks,Danny
Hello, I'm working on a distributed application which requires data from a central database to be shown to various users. In particular users should be able to open "views" on the database, specifying the data they are interested in, and being shown the result.
This can be easily accomplished modeling the view specifications as SQL select queries, sending those queries to the server, and visualizing the result. This approach is very flexible, supported, fast and definitely the way we would like to implement it.
The problem is that we have an additional requirement. In general the data the users might be interested in is not static, and when that data changes, those changes should be propagated to the client views.
There are lots of solutions and technologies aimed to solve very similar problems, but no one seems to support this scenario well.
1. You can re-run the queries every x time (polling)... but this is hardly an option when you have hundreds of client-views that require data to be updated in real-time (so "x time" should be, say, 1 second).
2. You can use query notification... but the documentation explicitily says it doesn't scale well and shouldn't be used with a large number of clients (which is exactly what is needed in this case).
3. You can use replication services... but this technology doesn't really seem to be created to support this scenario.
4. You can use notification services... but even this technology seems to be focused on different scenarios. I haven't looked at it extensively anyway.
5. You can use DML triggers to track data changes... but you still don't have the publish-subscribe mechanism to register client views and the mechanism to know which client views are affected by the changes.
6. You can implement a business logic tier that relates modification requests to client views... and basically do everything from scratch without using existing services.
Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?
Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.
If I have an ACID-based SQL engine and I run a long-running transaction that changes a record at some point during the transaction, and at the same time it's running another transaction changes a record that the long running transaction has/will change/d, will they both complete or will one transaction fail? If they both complete who wins the final state of the record? The long running one or the other one? (is it based on when the transaction actually started "point in time" or is it specific to when the record was changed during the transaction "point in time"?)
I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.ALTER TABLE database_tableALTER COLUMN my_bit_column INT; Thanks.
Hello all, I am trying to modify the output of a SELECT statement in a VB asp.net page that pulls data from a SQL DB. I only need to modify one column in the gridview, but I'm having some issues. Here is the situation:SELECT TOP 24 ID, RecID, Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM mTrafficORDER BY Timestamp DESC The Timestamp column returns a number value (i.e. 564566).In terms of a date, 564566 doesn't mean anything to the user, so I need to convert this number into a recognizable and accurate date. The formula I need to implement is Timestamp/1440 - 1 The following SELECT statement returns the number as a date, but makes all records the same date: SELECT TOP 24 ID, RecID, Convert(datetime, Timestamp/1440 + 1) as Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM mTrafficORDER BY Timestamp DESC What do I need to do to implement this function dynamically into my gridview? Thanks in advance!
We have a SQL Server setup as a publisher to 15 subscribers. We need to change the path of the data & log files to a new drive (added a new harddisk). We plan to take a cold backup of the database and shift the data & log files to the new drive. Then we just attach the data & log files from the new path.
Will this disturb my existing replication Setup? Is the the correct procedure for changing the path of the existing data & log files? What is the appropriate method for shifting data & log file of a live database to a different location (directory/drive) ?
I've got a customer who wants reproducible/historical reporting. The problem is that the underlying data changes.
I tried to explain that this can't be done (can it?), but he doesn't understand.
To illustrate the situation - Let's say a teacher wants to track spelling test scores for her students. The below are scores for students A, B, and C (for January, February, March)
A: {70,80,85} B: {70,65, 80} C: {100,90,100}
So, I can generate a historical report that charts the class average and student trend - that's pretty easy.
Now, in April, we find that the school board has mandated that the British spelling of words is ok, so now the cumulative scores (for January, February, March, April)
He wants a report showing the January average as (70+70+100)/3 = 80, when really it is (90+80+100)/3 = 90.
Now imagine that there are actually thousands of data points changing like this... Now also imagine that we add and remove students on a regular basis...
He and his office manager get frustrated when I explain that the reports are not simple - in their mind it is. They have determined the solution is to get a report writer and buy Crystal Reports... I've tried to explain that the problem is that the report specification is unclear (basically - they don't understand what they want). The situation is ok for now, I'm just trying to plan for when they figure out that buying Crystal Reports won't change their situation (except they are done several thousand dollars)...
I moved a database from one server to another, in this database are references to a UNC path on the server. now I need to find a way to modify the server name that was in each row to reflect the new server name and path, ex:
\server1path1data to now say \server2path2data
how do I selectivly modify just some of the data? or do I have to basically re-write the entire column for each row?
Hey all,I have a basic table that looks something like this.CREATE TABLE MyTable(ID INT IDENTITY PRIMARY KEY,Company_ID INT NOT NULL,Round VARCHAR(50) NOT NULL,Details VARCHAR(250) NOT NULL)It has a few rows of data that look like this:Identity Company_ID Round Details--------------------------------------------1 5 A Blah, blah.2 5 B Generic data, blah blah.3 5 WERT More generic blah blah.Now what i'm trying to do during my select statement is select all the rowsthat belong to company_id 5 but if any of the rows round value contains thetext "WERT" convert that text into just a "--" for presentation purposes,but still select that row. I can't seem to figure out how i would transformthe text in the select statement? My immediate thought was substring /replace but i would need to combine it with an if else statement which i'veno idea how to make work in a select (sub-query maybe?) statement. Is thispossible? Perhaps i'm stuck iterating through the returned data within theclient application before presenting?Any help, as always, would be greatly appreciated.Muhd
