I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:
- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window
FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).
Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.
We have a pretty large SQL Server database, just shy of 400 GB. It is divided up into 24 different data files. The previous DBA here said it is faster to backup the backup individual data files to seperate files. So what he did was issue 6 backup statements like the one below as a step in an agent job.
BACKUP DATABASE MYDATABASE FILE = 'Data', FILE = 'data2', FILE = 'data3', FILE = 'data4' TO Data1_4 WITH FORMAT
Next step:
BACKUP DATABASE MYDATABASE FILE = 'data5', FILE = 'data6', FILE = 'data7', FILE = 'data8' TO Data5_8 WITH FORMAT
etc.. in sequence.
What I question is how this is faster than just issuing a backup for the whole database? Has anyone ever ran into this before?
Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
Trying to track the users who are logged in and making changes to my database. I have granted permission through the Windows Groups and the log displays the group names and not the user names. Is there something to set to display the user and the group?
I am trying to backup the sql database files from a local computer to a server location through vb.net 2005. Is there a simple way to do this? Every time I try to detach the database, I cannot because the database is in use. Is there something I am missing with this?
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database? I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors. But I have heard a lot of discussion that you can't just simply backup SQL Server data files? I'm wondering how sound the backup I've created is... Any suggestions?
I have 2 networked PC's both running vista ultimate
1st is Laptop and is running its own SQL Server at laptoplaptopSQL 2nd is Desktop and is running its own SQL Server at desktopdesktopSQL
Now both machines have seperate windows login accounts.
When I go SQL Server management studio I go to browse and each machine can see the other machines SQL Server, but when I go to login I get SQL Login falied for users" The user is not associaed with a trusted SQL server connection".
So I then go to logins new login and try to add my other pc's user account. The problem I see is that when I go to search and then location it only shows its own PC's location and not the location of my other networked pc? So if I am on Desktop and in my theory want to add laptopuser to the desktop SQL Server logins I get:
"create failed for login laptopuser
An exception occurred while executing Transact SQL statement laptopuser is not a valid windows NT name. give the complete name
Can multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
I have a link that inserts postid, catid, and postdate. I am trying to get count of catid(how many times its in the table) then display the number on a page. i have never tried this before. one more thing. Can you put a datalist inside a formview when the formview is databound already? Can someone help? Thank you.
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
I am recieving complete row in one single column and I have pipe delimeter in this row . I want to retrieve the data in individual columns...Currently row is in one column
tblClients -------------------------------------------------- ClientID (varchar(3)) | ClientName (varchar(100)) -------------------------------------------------- 001 | First 002 | Second
tblProjects --------------------------------------------------- ProjectID (varchar(6)) | ProjectName (varchar(100)) --------------------------------------------------- 001001 | First Project1 001002 | First Project2 002001 | Second Project1 002002 | Second Project2
As you can see, the ClientID is the first 3 digits of the ProjectID, signifying that a certain project belongs to that client. I know it's a bad design, but I can't change the DB, I have to use it.
The problem is that I need to join the two tables based on the ClientID, but I don't know how to just retrieve the first 3 numbers in the ProjectID.
I need something like: tblClients INNER JOIN tblProjects ON tblClients.ClientID = tblProjects.First3DigitsOfTheProjectID
In previous versions of SQL Server you were able to execute single steps within a DTS package. It doesn't seemt to be possible in visual studio. Does anybody know otherwise?
Suppose my database has 3 table A though C and I need to get the number of records in each. I could do it as individual selects like SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
or SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A UNION SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B UNION SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
Is there any problem in unioning statements like these? In reality there were over a hundred tables and the TSQL was generated by looping through SYSOBJECTS. I eventually used the first construct as the XML was simpler to generate.
I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.
In Pablo Castro webcast, First Look at ADO.NET 2.0, he mentions the use of UpdateBatchSize which I think would be handy.However, I was not able to get it to work.
Dim t As New Global.System.Data.SqlClient.SqlDataAdapter("Select * from Table_1", Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)Dim d As New Global.System.Data.DataTablet.FillSchema(d, SchemaType.Source)Dim cb As New Global.System.Data.SqlClient.SqlCommandBuilder(t)For c As Int32 = 1 To 10000Dim r As Global.System.Data.DataRow = d.NewRowr("id") = Global.System.Guid.NewGuidr("val") = CType(Rnd(), Int32)d.Rows.Add(r)Nextt.UpdateBatchSize = 100t.Update(d)d.Dispose()t.Dispose()
What ends up showing in Sql Server Profiler (Sql Server 2005) is each Insert being executed in it's own statement:exec sp_executesql N'INSERT INTO [Table_1] ([id], [val]) VALUES (@p1, @p2)',N'@p1 uniqueidentifier,@p2 int',@p1='3793CB5E-3B7A-45E7-9A53-0BD528BB6B07',@p2=1 I think I made this example very simple and yet can't fathom why it won't batch the statements. I'm somewhat aware of SqlBulkCopy and was very pleased with that speed, but don't think it would handle the Update,Delete,Insert that the SqlDataAdapter.Update would. Originally started using xsd DataSets until I saw the data tutorials in the Learning section here in which case I copied out the autogenerated TableAdapter classes and fuddling with them to do what I want since batching was not something I saw in TableAdapters. Does anyone see what I'm missing here?Nathan
Please can someone advise me on how I can create a statement that will query each individual row within a table.
As the data will vary month by month I'm unable to state the amount of rows that will need to be queried.
An Overview: We have web-based application with a SQL back-end. When changes are made to a record a copy of that info is sent to another table (a quick snapshot, name, date, number, department etc.). At the end of the month I need to apply this data to an imported itemised phone bill so that I can make sure that if the mobile phone had been reallocated to another person I can change the cost accordingly. As it stands the operator has to print a copy of the phone details before reallocating to another person. Then at the end of the month manually changes the data within the itemised bill by referring to the printouts. Ideally I would prefer to automate the whole process.
Please find below the script - its very much in its infancy!!
CREATE PROCEDURE sp_reallocate AS select * from tbl_mobile_reallocate order by allocation_change_date
update tbl_mobile_import set empno = tbl_mobile_reallocate.empno from tbl_mobile_reallocate where tbl_mobile_import.mobno = tbl_mobile_reallocate.mobno and tbl_mobile_import.invoice_date <= tbl_mobile_reallocate.change_date
update tbl_mobile_reallocate set status = 'Closed' GO
I think jthis is a bug. I have a table created and populated on its own filegroup. I backup the db(all filegroups) and the trans log then I drop the one table. When I try to restore from my backups, it insists that I back up the trans log again. I do, then do the restore of both the filegroup and the trans log. The restore finishes, but my table is still not there and I can never get it back.
First of all, I`m a very new entrant to the world of SQL Server. So I`ll make some mistakes.
I need to know how to write a stored procedure that will insert or update INDIVIDUAL rows. All the documentation I`ve seen is pretty coy on how to actually do this.
Any actual examples, or any tips at all would be greatly appreciated.
I am writing a utility that browses all steps, connections, and tasks in a given DTS package. One of the functions is to rename each item using its description, ie, removing spaces, formatting it, and then appending it to the standard DTSTask_ActiveScriptTask_MyCustomNameDerivedFromTh eDescription. I am being sure to change everything to match this in both the Step and task for a given set. The behaviour that is occuring is this. The package appears to be named correctly, and even seems to run correctly, but none of the precedence constraints, it, OnFail, OnSuccess, OnCOmpletion seem to show up in the DTS Designer Environment. The package does seem to be running correctly, any idea what would cause this behaviour?
SQL Server's Books On-line seems to hint that this can be done. Does anyone have any info or practical application of this? Can someone confirm that this is possible please.
In my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)
At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.
What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.
The point of this is to then SELECT the connections identified by the UserID pairs.
I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?
I have SQL Server running on our web server located at a managed hosting site at Rackspace. I've got some developers who are going to be editing various sites and will need access to specific databases. Is there something built in to MSSQL, or a third party app, which will allow for this kind of remote access? They will be doing everything from restructuring these DB's to just inputting data.
In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
I have a Job that runs every half an hour, and has about 30 steps. If a step fails it moves on to the next step (the steps are not dependant on one another)... However I would like to receive notification if that occurs. I have SQL 2005, with database mail enabled and set up (works fine for other jobs)... The question is how do I get it to send me an email if a single step fails, however the job on a whole succeeds?
Do I need to set up a step between each job?
Step 1 An actual step, on failure: step 2 On success Step3 Step 2 A step that emails the failure of step 1, then goes to step 3 Step 3 An actual step
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
My customers are having problems to restore their own db.
They use SQL Server Management Studio and when they try to restore their db from a device and add the backup file an error message comes up stating that the user does not have permission to C:Program FilesMicrosoft SQL ServerMSSQLBackup.
How can I set a different backup location for each user?
