Hi,
I was wondering if anyone had an idea if it's possible to run a stored procedure that would basically output a .sql file used to recreate the database elsewhere. kinda like the data publishing wizard does.
?
tx
Hi some days ago i put a thread in this forum asking how can i publish my database on a shared server, and i was adviced to use Data Publishing Wizard Tool. But now i'm facing another problem, the database is on the server being filled with data by my clients throught the system that i'm developing but because the system is under construction i have to change some tables once in a while, something like add one or another constraint, add or fix an stored procedure, add for example a field named "Archive" so i dont delete data from the database instead i archive it.. this are the only changes i'm doing.. the database is well modelated so i don't need to do further changes ...
So my problem is how can i reflect this changes on the shared server without deleting the data that are already there? Is it possible, or i have to do it manualy on the server what i have already done on the database hosted on my localhost?!
Hi,
I needed to recreate/publish a DDL and data file(of a database) that was produced using Database Publishing Wizard. The size of the file is 4G. I receive the following error when I try to open it up in the new query window of sql server 2005 (sp2)for execution.
"The operation could not be completed. Not enough storage is available to complete this operation."
I know my server has 829G of free storage space available. i receive no errors when I open up a smaller file also created by database publishing wizard.
What possibly is causing this?
Thanks in advance
I've been working with the Database Publishing Wizard and have found what I initially thought was a bug but have decided is a rather nasty "feature". Or am I missing something?
I have membership implemented in a live database. I took a backup and loaded it into a local database. I created a new table locally that is foreign-keyed off aspnet_roles, and added some data.
Using the Publishing Wizard for a partial deployment, I followed the steps which seem perfectly clear to script out ONLY the new table and its data. The screens provide a nice GUI for selecting specific database objects, and I carefully chose the one new table.
From good habits I looked at the script before deploying it. It drops and recreates aspnet_roles and aspnet_applications as well (and inserts the data from the development tables).
Presumably this is because of the foreign key relationships. But there's no need to do so. What if I or another developer had added in some roles to the live table in the meantime? This script would blow them away.
Am I missing an option with the Wizard that would script out only what is specified? I've tidied up the script (i.e. removed the undesirable sections) - but this seems to be a naughty wizard. Any thoughts?
We are trying to script out database schema and data with the Database Publishing Wizard and are having problems with output script trying to create views and stored procedures before the tables they depend on.
I've seen discussion of this on their codeplex site and back in April 2007 they said that the CTP they just released fixed the problem - but we are still seeing it. Does anyone know a solution for this (beyond the obvious copy and pasting into the right order)?
What we are trying to do is move the SQL and some basic reference data into source control and a CI process.
Now here is something I have been asking the SQL Server Team for a long long while : http://www.microsoft.com/downloads/details.aspx?familyid=16D8C1E9-A6A3-43A2-9BA7-916C6EB5023B&mg_id=10046&displaylang=en . I hope this will be of use to many of us
View 1 Replies View RelatedThe SQL Server group at Microsoft has just launched the SQL Server Hosting Toolkit with the release of the Database Publishing Wizard Community Technology Preview 1.The objective of the SQL Server Hosting Toolkit is to enable a great experience around hosted SQL Server. The Database Publishing Wizard works toward this mission by making it easy to upload a database from a development box up to a shared hoster. In its first incarnation, the Database Publishing Wizard is a command line tool that generates a T-SQL script designed to be executed in the script execution windows provided by most hosters in their database management consoles. In the coming months we'll be adding a GUI and building seamless integration between the tool and an upload service we'll provide to hosters for deployment.Details on the Database Publishing Wizard as well as the download can be found at http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard.We plan to be releasing advances in the Toolkit frequently so check out this first CTP and give us your feedback!Thanks,Dan WinnSenior Program Manager, Microsoft SQL Server
View 15 Replies View Relatedi want to use Replication in sql server 2000 at specified condtion ,mean if any condition is satisfied then the data is publish to subscriber database?
View 6 Replies View RelatedHi All,
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
Help!!!
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
I would like to be able to regenerate the log files for our SQL databases without impacting the data files. I tried:
- detaching the database,
- saving the datafiles to another drive,
- dropping and recreating the database with the same structure as before,
- detaching the database again,
- overlaying the data files with the ones I saved previously,
- then reattaching the database.
The reattach fails saying that the files can't be used for different databases.
If I try to bring in log files that are the same size as the database log files I'm trying to recreate, I get the same error only the log files are the ones in error.
I've tried recovering/recreating the database with the "for attach" parameter, but I get the same error.
More information... The problem I'm having that requires the log files to be regenerated is realted to an upgrade to Win 2003 EE. We upgraded the server last week and since then, the backups have been failing. I've tried reloading a backup of the database but the only backups we have are prior to the upgrade and I think, because the database logs are operating system files, the logs don't get loaded correctly and I still have the same backup problem. I logged a case with Microsoft, but they haven't been able to help.
I have some tables that are created as views in a database.
Everytime I modify table structure the view needs to be recreated.
I am developing a simple Stored Procedure which takes table name as parameter and drops teh view first and then cretaes it again.
The statements required would be
drop view viewname
create view viewname as
select * from ffship.dbo.viewname
The error I get is that
'CREATE VIEW' must be the first statement in a query batch
Any help appreciated.
Thanks,
Kiran.
I have a working Data Import from Excel 2013 to SQL Server 2012.But when I save the package to SQL and Execute it, it fails.I think this is due to Datatype issues.In the Wizard I set the Global On Error Action to Ignore when dealing with Excel columns which contain both Numbers and Text. This works fine.So I suspect that when the package is saved and executed it uses the default Global On Error Action - which is fail. Is there a way to change this setting in SSMS or elsewhere?
View 1 Replies View RelatedThen I deleted an entire database from the "Enterprise Manager". And refreshed the Query Analyzer.
Then I again tried to create the database. CREATE DATABASE ContactManager ON PRIMARY(NAME = ContactManager,FILENAME ='C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 5MB)LOG ON(NAME = ContactManager,FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.ldf',SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 2MB)GoNow it says, database already exists.
I used 'EXEC sp_helpdb' to view all the databases, but the database 'ContactManager' was not there.
Where is the problem?
I have a client who was cleaning up his server to free up disk space and actualy deleted de transaction log file for server database. I can't bring back the backup because it was also deleted (smart...). I still have the .mdf file. Is there a way to bring back the database? I tried to detach and reatach database without success, so the database is no longer attached.
Any help would really help.
Thanks in advance,
Stephane Jeffrey
How to get all the index properties like unique, clustered of an index.
I need to recreate all the indexes in a table dynamically
1) need to get the list of indexes
2) drop them
3) change some properties and recreate the indexes, to do this I need to know the columns, uniqueness, clusteredness in step 1 above. How can I get to that?
In oracle we can depend on tables like index_columns, index_constraints....
thx
Hello all,
I'm in a bit of a bind. We have a database that spits out a classic corruption error message whenever attempting to run scheduled maintenance:
Error:605, Severity: 21, State: 1
Attempt to fetch logical page ### in databbase 'db1' belongs to object 'table1' not object 'table2'
SQL Books Online says to check with DBCC checkDB and CheckAlloc asap, which I did, and CheckAlloc confirmed the corruption. Books Online's only advice at this point is to restore the DB from a clean backup.
Herein lies the problem. One I'm assuming has happened to many a DBA as well.
This particular message has been going on for months, and there is no "clean" backup of the database, because no-one here at the time knew there were any problems to take precautions. Now I'm here with a corrupt database, and no idea how one goes about reconstructing a DB from scratch. Does anybody out there know any good options?
Thanks in Advance
Fenderson
I would like to know if there is a way to drop/ disenable all the indexes in a maintenance plan?
Or is it better to write scripts for dropping indexes and recreatig the same?
Purpose: Need to drop indexes(not the Primary key) before loading data and recreate the same after loading
Looking forward for suggestions/Solutions!
Thanks,
Janani
Hi Guys,
I am trying to DROP a table and recreate the table in a stored procedure and then I am trying to INSERT records into the newly created table in the same stored procedure.
I know I don't have to DROP the table, but I am trying to just get it to work. The process runs without error, but when I refresh the tables, the table I created isn't there. The T-SQL is as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportFilesPremier]
AS
DROP TABLE dbo.[PremierTest]
CREATE TABLE [PremierTest] (
[AYQ] nvarchar(6) null ,
[CYQ] nvarchar(6) null ,
[Description] nvarchar(50) null,
[PIP] [decimal] (17,2) ,
[BI] [decimal](17,2) Not null,
[PD] [decimal](17,2) Not null,
[COLL] [decimal](17,2) not null,
[COMP] [decimal](17,2) not null,
[DCRAT] [nvarchar](2) null ,
[Agent][nvarchar](3) null ,
) ON [PRIMARY]
begin transaction
insert into [PremierTest]
select *
from dbo.[new agt type tri 0804]
WHERE dir_ceded_ind = 'C'
commit transaction
Any information on how I can tweak my code so it works properly would be greatly appreciated. Thank you.
In my database, I am currently trying to delete a user and recreate it for ownership. I get the following error message
An exception ocurred while executing a Transact-SQL statement or batch. The database principal owns a schema in the database and cannot be dropped. SQL Server Error 15138.
What do I need to do? Thanks
David
When Importing data from Access 2000 to SQL Server 2000 I get an error when the column's data type in Access is Memo if I try to set the column's data type in SQL Server to text or ntext. Here is the error that I am getting.
Query-based insertion or updating of BLOB values is not supported.
I have tried to change this to a varchar data type but the fields can be very large and some are over the 8000 limit.
Short of programmatically adding the data, is there another way to do this in SQL Server? I cannot use the Access upsize wizard.
Miranda
Hi all,
I have created a new database from a backup. When I run the application, it seems like it is not getting the right indexes but it is in Development environment. So, I ran a script to recreate the index by DBCC DBREINDEX(TABLENAME). Still not getting it right. mainly it is not using the primary key index. I believe that dbcc dbrenindex will also reindex primarykey indexes. Any one know, how to overcome this problem. It is a production issue and I would appreciate for your prompt advise.
Note: Does anyone has any script to drop the primarykey index and to recreate it.
Thanks,
Joe
Due to some problems, we have to reformat Our Windows NT4.0 Server.
The SQL Server 2000 has several ODBC Configured as
"User DSN" and "System DSN" .
Question: Is there any way that we could copy these User DSN and System DSN
Config Files so that after Rebuilding Server AND Database, we could Re-aaply/
Re-create these ODBC DSN specifically "System DSN" as they are More Number.
Thanks
Due to some problems, we have to reformat Our Windows NT4.0 Server.
The SQL Server 2000 has several ODBC Configured as
"User DSN" and "System DSN" .
Question: Is there any way that we could copy these User DSN and System DSN
Config Files so that after Rebuilding Server AND Database, we could Re-aaply/
Re-create these ODBC DSN specifically "System DSN" as they are More Number.
Thanks
Hi all,
I have created a new database from a backup. When I run the application, it seems like it is not getting the right indexes but it is in Development environment. So, I ran a script to recreate the index by DBCC DBREINDEX(TABLENAME). Still not getting it right. mainly it is not using the primary key index. I believe that dbcc dbrenindex will also reindex primarykey indexes. Any one know, how to overcome this problem. It is a production issue and I would appreciate for your prompt advise. I have also ran the update statistics on all tables but with no luck
Note: Does anyone has any script to drop the primarykey index and to recreate it.
Thanks,
Joe
Due to some SSQL application upgrade, some unnecessary columns have been added to the tables and the stored procedures. The values in these columns are NULL and these columns have been added at the end of the tables and some SP.
As my application doesn't require these columns thereby I have re-created the old tables using other environments where these columns were not present.
Can I do the same thing for stored procedures as well i.e. to drop the SP and recreate using other environment which are not having the unnecessary columns.
Here is the scenerioI am trying to copy a database from a live sql server to a local server.I wanted to copy the tables, triggers, procedures, functions, usersSo I have "Copy tranfer of objects " in the DTS wizard.But the process fails informing that the users are not there in the local server and fails the process.Now what is my expectation is that why the user logins every thing is not copied to the local sever.Help would be more appreciated. Pretty urgent.
View 30 Replies View RelatedHi,
I have two environements:
1) Development (Full access to SQl Server 2005)
2) Hosting SQL Server 2005, ONLY accees to the database MYDatabase, NO access to master
I would like to copy a table from my development to hosting. I would like to have an exact replica of the table in hosting. I DO NOT want to copy the whole database, just a table. I was able to do this without problems in SQL 2000/Enetrprise Manager.
I have tried to copy the table using the new import/export data wizard. I did enabled Identity Insert option. There are few problems:
1) The drop table and recreate option (under Mappings) is disabled, I can not check it. IS THIS a BUG? I have latest SP 3 aplplied
2) When I just run it I get the following errors. Also the problem is that it runs in trunsaction, I was hoping it would skip the duplicate records and just copy the ones I need. Seems it is all or nothing, very common problem in Windows based systems (same problem in Explorer when copying files)
3) I was hoping that such a simple task as mirroring a table would be easly achieved, please help
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.(SQL Server Import and Export Wizard)
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_classifieds_HotelRoomTypes'. Cannot insert duplicate key in object 'dbo.classifieds_HotelRoomTypes'.".(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - classifieds_HotelRoomTypes" (37) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.(SQL Server Import and Export Wizard)
I want to tune the indexes on my database and I am trying to use the SQL Server Profiler to collect data for the Index tuning wizard to analyze. My question is what do I need to trace with the profiler so that the Index tuning wizard can work? I am looking at the trace properties in Profiler at the Events, Data Columns, and Filters tabs but I have no idea of what I need to capture.
Thanks in advance.
Mike
I have not used the copy objects wizard that much. I used it today to copy 4views from my dev box to production. It copied the views, but also wiped outmy data in all the tables that the views are built around!!! Inproduction!!!!Can someone provide me some insight into why this happened?Thanks,Chad
View 6 Replies View RelatedWe are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .
We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)
Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.
I have a need to do a quick and dirty import of a flat file into a 2005 database. When I try to run the Import Export wizard from my machine, I get an error when it tries to connect to the database server. It's the lovely generic error that says my server doesn't allow remote connections. Well it does allow remote connections. I can RDP to the server and run the same import and it works. So why doesn't it work when I try to run it from my machine? Am I missing a setting somewhere?
I am using SQL Native client trying to connect to a named instance of sql (2005, sp2) as my destination.
TIA
-A.
Is there any way to incorporate the SQL 2005 Mining Wizard into my program? Sure, I can create my own mining wizard using DMX, but that's quite a bit of work. Has anyone tried this? Is it possible? Any pointers?
-Young K