A Way To Do The Execute Script And Script The Schema AND The Data (run It On An Empty Database And Autocreate)?
Apr 21, 2008
I'm trying to find out if this is possible.. i know that you can do an execute script command and have it export the results to a script file or clipboard.. then in a new database, run that in the query window and it will recreate the table schema.. but is there a way to do the entire table, contents and all via a script in a similar fashion?
Hi i wanna delete all the records from an large database 200 -300 tables, because i want make some changes an start from scratch,but keep the structures of the database key , index etc, i tried to generate script but when i run to many errors , plz help 10x
I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data. Does anyone know how to do that? Many thanks for replying.
I am making a form that takes input for 1 to 5 students using VWD. With the help of previous posts I have been able to make the database insert query work properly. In my form I have a radio list that has the user select if they are entering information for 1, 2, 3,4, or 5 children. Depending on how many children are selected on the radio list, I am displaying the proper number of textboxes and validating the data using the handy RequiredFieldValidator. Now I am at the point where I want to perform the instert to the database depending on the selected number of children in the family. What is the general rule for best practices. Please keep in mind that it is my understanding that ALL fileds in a SQL insert statment must have data. Should I ...1) create alternative SQL statements depending on the textboxes displayed OR2) is it more common to insert a standard string or integer, depending on the datatype, into the unused textboxes to populate the unused fields? Sincerely,Mike
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I have granted execute on the dbo schema to a sql user so that he can execute available procedures in the dbo schema. However when he executes a procedure ie..
exec dbo.myproc
The following error is returned:
Msg 229, Level 14, State 5, Line 2
SELECT permission denied on object 'MyType', database 'Mine', schema 'dbo'.
MyType is a table
How can I correct this behavior?
I don't want the user to be able to access the tables except via the procedure calls.
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
I have created a Web Setup Project for my web application which also contains a database. Currently I am having troubles scripting the database and run it during my Custom Action for the setup project.
I used the publishing wizard to create a sql script that contains both the schema and the data for the database. However when I run the script using
Code Block SqlCommannd.ExecuteNonQuery();
it fails. I believe this is because the ExecuteNonQuery method does support SQL statements like GO etc that are part of the SQL Script. So I now believe my approach is wrong. So my question is how should I script my database (both schema and data) and run this script during the Custom Action of my setup?
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master] go create database [test] go
USE [test] GO
CREATE ROLE [ReadOnlyRole] GO
USE [test] GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole] GO
This is kind of a elementary question, but how do you export an empty copy of a SQL 2005 database to a backup file? Basically I need a backup without any of the data. Thanks!
I have an MS SQL database that appears to have all the data in it but when I do a check on the tables they are all "0 Mb". How can this be? Is my table data being hidden?
I have a dataset that sometimes is empty. I have it as a detail row in a table on the report, but since the dataset is null the row does not appear. What I would like to do is have it say "No results" if there are no results, otherwise have it show the results.
I tried to add a textbox and reference the textbox on the table, but it was a no go.
I have this complex logical model with 60 tables which i have converted to phycical model and into sql 7.0 database now the database is completely new for a new internet project without even single row of data now i have to optimise database and determine if the indexes created by the the designing team are good in sql 7.0 will the give me optimised database or not . I just dont know what to do
1) is it possible for me to use indextuning wizard without the data whenever i try to to use indextuning wizard it askes me to use a workload file or make workloas file useing a trace as i do not have the data i just have to capture a trace by saying select * from <table> as the work load file does not return any records the index tuning wizard does not suggest any changes In the indexing tell me HOW DO I DETERMINE WHAT INDEXES TO CREATE FOR A OPTIMISED DATABESE ?
2) IT IS NOT POSSIBLE FOR ME TO PUT DATA IN ANY ONE TABLE AS ALL THE TABLES HAVE fk AND pk RELATION SHIP so what do i do
3) WHAT DOES dba normally do when he recives an empty data base to optimise
4) where do i make views to optimise the data
5) how do I populate data in so many tables
will puting dummy data help ?
Can some one help me with some SP which will populate the table with dummy data by some kind of do while loop with counter incrementing with 1000 rows
please help me and if i am not clear pl tell ask me the questions on my default email or
hi,my sql database log file has been fulled recently ..... becuasethere are 55 millions records in main 3 tables .... so how i can emptylog file ...i don't want to attach new log file or save any pervious log info.....thanks for helping me ... and my company ..Abdul SalamSr. DBA + ProgrammerXebec Groups of Business.
I have an interesting problem. I am unable to see any database triggers in the Database Triggers folder under programmability in SQL 2005. I have been able to create and alter triggers in the database that function exactly as their supposed to, yet they never show up as visible in the Triggers folder. I would love for one of the luminaries on this forum to share some wisdom with me and let me know what setting is the culprit. It is driving me batty.
I have a database which gets its daily feed from a ftp file. Now is there any way i can figure out the empty tables in the databese which doesnot get any data on the feed.
I have a block of code that does a Fill() with a DataAdapter, which seems to throw an exception if no values are returned. Then, when I try to bind the DataSet that gets filled, an error occurs, it doesn't even try to bind the column headers or anything.
This process seems to work fine when some rows are found, but it is not always guaranteed that the row count will always be > 0. The nice thing about using the Fill() function, is that it creates all my column headers automatically, so I was just wondering if there's a way to do a Fill() then a subsequent DataBind() to a DataGrid which would automatically format the columns even when no rows are found?
Here's a bit of the source.. if the last line finds no rows, it throws an exception ( I assume this is normal):
strSQL = "SELECT * FROM Prods"; cm.CommandText = strSQL; da = new SqlDataAdapter( cm ); da.Fill(shelf);
I would like to empty out my 2k8 database (remove all the data in the tables only) And reloaded it with the data from production (2k5). I know I would have to dis-enable any constraints, triggers and remove all indexes before I can run a Truncate/Delete on all tables correct And then import the data via Wizard or script And lastly enable all the constraints & triggers, rebuild the indexes. Is this the only way to go about this? I don't want to do a backup & restore because the 2k5 doesn't have a 20% of the tables that are in 2k8. The 20% of those tables in 2k8 i'm not going to remove.
I am trying to do a lookup for a value in a codes table and if that code is not there; add it to the table, my problem is if the lookup table is blank no records to begin with it will not add any data, that means the data source is empty so there no data going thru the data pipeline. So how do I get data in the table if we start with an empty table? If I am not clear I can explain it more.
I need to find all empty files in the database (SQL Server 2008R2 SP2) The files become empty after the archival of a partitioned tables.
I was trying this:
select f.name, * from sysfiles f (nolock) left join sys.filegroups fg (nolock) on f.name = fg.name left join sys.indexes i (nolock) on i.data_space_id = fg.data_space_id left join sys.all_objects o (nolock) ON i.[object_id] = o.[object_id] where i.name is NULL and o.name is NULL
Did not work.
My file names are the same as the filgroup names containing the file, this is why I was joining by name.
Greetings! I just found this group after wasting hours trying to make SSRS work with databases that are already created (that is, the DBA created two empty databases for me, X and XTempDB).
I keep getting "this connection string is invalid can't use this to connect to SQL Server" in the Database Setup tab.
The problem is that to create its own database, SSRS needs dbcreator and securityadmin roles (please correct me if I'm wrong here). And I'm having a helluva problem trying to convince the DBA to give me temporary dbcreator and securityadmin roles!
Is there any document or something that I can use to convince him that SSRS will not do anything like recreating the login (because he said that the login has already existed and has its role set up in many other databases)?
Any help, pointers, suggestions, etc. is very much appreciated.
