By default, I constructed my database using the dbo Schema. I have since created new Schemas, and want to change some of the tables from dbo.
Is there a way to change the existing schema of a table without disrupting the data that is inside the table?
For example:
Current: dbo.Products
Desired Change: NewSchemaName.Products
Thanks.
HI Gurus,
I am tring to add table to alraedy existing DTS pacakge to refresh data from other server.
I dragged items from tool list (SQL SERVER Entripice manager)
Ther ia m not able to connect those two icons(one source and other destination).
How can i proced to next step.
Thanks,
ServerTeam
Hi Guys,
I am new to sql server 2000, and need a little help.
I have a table called CMRC_Products with various columns, there is one column called Product Images that has the name of every image in my catalog over 4000, I want to add to each row in that particular column .jpg without loosing what is already in there
I have tried:
UPDATE CMRC_Products
SET ProductImage = ProductImage&' .jpg'
But I get an error,
any help would be much appreciated, cheers
Good morning (afternoon/evening) all.
I am researching a way to render data in a plain text file, with no formatting.
I plan on building out the result set to exactly what I am wanting and just need it to render itself without any formatting logic being taken into consideration.
The end result is to generate a QIF file from a random data set. QIF is just a formatted text file.
Am I making sense?
Any help would be greatly appreciated.
We are struggling to add an extra column to a Dtsx package. The package essentially compiles data into a table and then exports the data to a series of Spreadsheets. So far I have ended up doing the following: -
-Creating a new Folder for the Spreadsheet Templates, copying the existing spreadsheets into the new Folder and then added the new Column.
-Creating new Connection Managers in the Package to connect to these new templates.
-Adding the column to the various data flow tasks so that the new column is converted and then exported (data type is varchar(30), conversion type is Unicode text stream [DTNTEXT]).
Everything validates when we compile the dtx package. However when we run the package we are getting the following error: -
Started: 5:39:49 PM
Error: 2015-05-12 17:45:39.05
Code: 0xC0202009
Source: Data Flow Task Voluntary Excel [2379]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
End Error
[Code] ....
My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas.
let's say I have
dbo.tableA
sch1.tableA
sch2.tableA
sch3.tableA
And I just want to copy let's say sch1.tableA to a Different DB.
If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task.
Would please recommend any other way to achieve this.
Many Thanks in advance
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
I have 2 tables (master and masterbackup), trying to update and replace the status in one with the other based on master.number match
UPDATE master m
set m.status = (SELECT status from masterbackup where number = m.number )
FROM mastermasterbackup
I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.
It seems that the parser does not validate that a tables schema is missing.
This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.
CREATE PROCEDURE [dbo].[Address_Load]
@AddressID [int]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @intError int
BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @AddressID
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END
-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @intError = ERROR_NUMBER();
-- Log error here
RETURN @intError;
END CATCH
END
The stored proc parses fine and gets saved to the database but when executing it I get the following
Msg 208, Level 16, State 1, Procedure Address_Load, Line 10
Invalid object name 'Address'.
Is there any way to change this so the parsing will generate an error and not allow this into the database?
Thanks,
Cory
Hi,
I have the following scenario: N identical Databases (corresponding to different Fiscal Years, with names <Company Name>.<YEAR>). We want to consolidate the N DBs to a New Datawarehouse.
In SSIS we have designed a Dataflow that reads through a OLE DB Source (Connected to one of the N Databases) and maps to a OLE DB Destination (Connected to the NEW DB).
The question is, how we loop in SSIS through the N identical Connections, so to repeatedly execute the designed Dataflow, each time with a different Connection?
Thanks in Advance,
--Dimitris Doukas
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?
Thanks!
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.
I also have an inner join view with the initials from both tables as the joining field.
I want the users to be able to modify the data table (MainTable) but also display the users name field.
here is the sql of the view:
SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?
I have never done dropping a table and recreate it?
Hi There,
I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:
------------------------------------------------------------
Errors were encountered during the save process. Some of the database objects were not saved.
'Comments' Table
-Unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ...
... Cannot drop the table 'dbo.Comments' because it is being used for replication
-----------------------------------------------------------
If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)
I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please!
Let me know if I need to provide more info. Thanks.
HI everybody need help badly on the following
have to recordsets with the following with the following SQL
SELECT place, count(minus)as countminus
order by place
group by place
THE RESULT WOULD BE
PLACE countminus
ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0
MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus
THE RESULT WOULD BE:
PLUS COUNTPLUS
ABC 10
DEF 20
my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...
with this condition..
I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...
THE RESULT WOULD BE THIS.....
PLACE countminus COUNTPLUS
ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0
I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
Hi there,
Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.
Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?
Thanks.
Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.
SP3 has been applied to SQL Server 2000.
Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
Greetings,
I have two SQL Server tables on the same server and in the same database. I'll call them table A and table B. They have identical schemas. I need to insert all rows in table A into table B. (Don't laugh - this is just for testing and long run the tables will reside on different servers.)
Can someone please tell me the correct task to use for this and the connection type I need for both the source and destination?
Thanks,
Black Cat Bone
I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
Code Snippet
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
Hello,
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
I'm sure there must be a simple way to do this!
Many thanks,
Ben S
I am using the DTS wizard and having problems importing excel into an existing table.
Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies View RelatedI want to move an existing table from another.
Example
B.mdf dbo.xx to C.mdf dbo.xx
B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx
C.mdf dbo.xx is empty -
All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx
I was telling I could do a cut and paste to copy the dbo.xx
Thank you in advance.
~ Peaches ~
Hi,
I would like to know the procedure to be followed to add a table to the existing publication. The subcriber doesn't have the object yet. I have data in the source table and I want to add this table in a existing publication for transactional replication. Any advise is appreciated.
Thanks,
Ravi
Hi,
How would I select from TABLE A and add this data to TABLE B which has existing data that I want to keep. I would like to keep adding onto the old data. When I do an "Insert Into", it keeps on deleting my old data.
Thanks,
I have created two tables in phpMyAdmin 3.5.8.1. comments which stores comments users post on a website and registration which stores a users registration details (username, password, e-mail etc, etc).
I want to add the username field from the registration table as a foreign key to the comments table. How do I do this in phpMyAdmin?
Hi everyone!
I just started with SQL and SQL Server 2005. I cantfind in my books how to add a column to an existing table. It should be a primary key, auto_increment column.
Hope someone can help, many thanks in advance
Greetings from Vienna Austria
landau
i have table fff .it has two fields one is fno int , another is fnamevarchar(20)ffffno fname-------- -----------100 suresh102 rameshhere there is no not null constraint and identity column theni am add primary key constraint fno column pls help me
View 1 Replies View RelatedHi all,I have an existing table(Several actually) that I want to add a columnor two or three. I need to have, I think, at least two columns. Onefor Created date and believe this is a no brainer in that I just addthe column and set the default to getdate(). Two, for tracking amodified date of the row of data. I want to keep it simple. What do Ido to track the modify date of a row in SQL Server 2005? Anytime anydata changes on a row update the update column? Is a trigger the onlyway to accomplish this task?Another column could be used to track who changed it. Is there a neatway to determine username, machine name, MAC address or any othermethod? I pretty wide open on this one as I am not sure what I want touse here.TIA!!!
View 16 Replies View RelatedI trashed SQL Server2000 when I added the WINXP SP2, and had to reinstallit. When I did, my database(DEV_DATA) remained intact, but when I go inthru Enterprise Manager, it is not located in the system tree, and istherefore inaccessible. Can anyone suggest how I could get this databaseincluded.
View 4 Replies View Related
How do you alter the table to use the new partition (I know ALTER TABLE is in there but BOL doesn't give a valid example with the move option)? I can create the partition but I want to apply it to an existing table with no partition?
Thanks