I've checked this site but was unable to find what i needed. Perhaps there is a very simple solutio to this.
I have several databases (development, testing1, testing2, production).
I have recently modified and added some tables and stored procedures in the development database. I would like to push the changes to testing1 database. In SQL Management Studio, it's possible to right-click the a table and create EITHER an alter or create or drop script. The same is for stored procedures. I need to be able to create scripts that checks if the object (table, or stored procedure exists) and either 1) create it, if the object exist or 2) alter it, if the object exists.
Is there a tool that does this? or How can I do this via Management Studio?
ok...this (going on eight years now) sql server user has a seeminglyvery simple problem with his new developers edition of sql server 2005.I'm trying to script multiple tables and indexes in the mmc...and usingthe usual control/shift keys to select multiple objects isn't working.Is this a bug in the mmc, or am I losing my mind or is there somethingI haven't yet seen in bol?thanks in advance...
I'll try to explain this in detail so that it's easily understood. I sure could use some other eyes on this folks. I need help figuring this one out. Here is the project...
We have your basic Windows 2003 server environment with XP Pro on the workstations. I'm using a program called Tag. Yes I know this site did not develop tag but I'm hoping that this post will get you thinking and hopefully some of you might like the challenge of helping a fellow developer figure out a technical problem.
We have mp3's that all have some specific tags based on date and title for example. They would need to change dynamically on the fly without human intervention. Here is a generic list of tags we typically use.
--artist "Company Name Here" --title "My Test Title" --year "2006" --genre "News or whatever here" --comment "This is a test comment"
The TITLE, GENRE AND COMMENT would need to change dynamically. The rest are fixed and generic and dont need to change. I have different folders. For example:
C:AudioTaggedFolder One C:AudioTaggedFolder Two C:AudioTaggedFolder Three
I have the tag.exe and batch files in C:Program FilesTag folder. Here are the batch files I use.
test1.bat
for /f "tokens=1-4 delims=/.- " %%A in ('date /t') do (set Dow=%%A&set MM=%%B&set DD=%%C&set YYYY=%%D) tag C:AudioTagged*.mp3 --artist "Company Name Here" --year %YYYY% --genre "News" --title "Some kind of title here" --comment "This is a test comment"
mp3tags.txt (flat file that contains the tags)
--artist "Company Name Here" --title "My Test Title" --year "2006" --genre "News or whatever here" --comment "This is a test comment"
http://www.flickr.com/photos/ericowens_photography/ (when you get here look for a screen shot of an MP3 Tag toward the bottom.
So if I want this program to run (without human intervention) and put the tags that pertain only to those mp3's that need them how would I do this?
Example: C:AudioTaggedFolder One est1.mp3 needs...
--artist "Company Name Here" --title "Specific Title of it's own here" --year "2006" --genre "Specific genre of it's own here" --comment "This is a test comment"
Can this be done with VB Script and if so I need help writing it out. Can SQL Server be used to store the data but then call the script which then executes the Tag.exe program?
Please anyone's thoughts on this would be greatly appreciated.
I'm going to be getting involved with the creation and reconfiguration of DTS jobs, however I've noticed that they'll require VB Script, which I'm not familiar with.
Without me reading an 1000pp book on learning all uses of VB Script, are there any articles/resources that focus exclusively or distinctly on the most common uses of vb script in databasing?
On a separate note, are there any ready-made sample applications I could download and connect to a SQL database for study purposes?
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
While attempting to script out some multi-server jobs I am receiving this error:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Script failed for Job 'my job name'. (Microsoft.SqlServer.Smo) For help, click: <link removed to keep message width to a normal size :)> ------------------------------ ADDITIONAL INFORMATION: Unable to cast object of type 'System.DBNull' to type 'System.String'. (Microsoft.SqlServer.Smo) ------------------------------ BUTTONS: OK ------------------------------
This happens on any multi-server job. The reason behind needing to script this is the current database server that acts as our master server for SQL Server jobs is being replaced. I figured it would be easy enough to script out the job on the old server and run it (with minimal modifications) on the new server. Now, if I create any of these jobs manually on the new server and try to script it, it works just fine. Any ideas what might be the issue here?
I've tried doing it via SSMS from multiple servers and the issue persists.
Hi, I've just been given the task of finding out how to implement a backup procedure for our SQL server databases. Most are running 2000, some 2005. I'm a programmer, and I'm used to having a DBA to help me! I've seen a few methods on the web involving a stored procedure and running the task from task manager. I need to backup and restore all the databases in SQL Server 2000 and work out a way of displaying whether or not it was successful. Can anybody please point me in the right direction as I've no idea how to do any of this really. I guess if I could setup a sproc to loop through the databases that would help, but I'm not sure where to start. Thanks in advance.
Is there a way to (automatically) remove/disable the first statements like SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON which are generated by modify sp via mms 2014 interface?
-- SET ANSI_NULLS ON -- SET QUOTED_IDENTIFIER ON ALTER PROCEDURE [dbo].[sp_SendMail] @test INT = 0 AS begin --blabla end
I know it's a simple question, just can't figure it out: How do I script a new column into an existing table in SQL? I am using MS SQL 7.0 and need to create several new columns in existing tables. thanks all.
that appear between each Create? I can't see any options for changing then or eliminating them when generating the SQL Script for my stored procedures.
I have to come up a script that will be going across 60 different servers that will be looking for document type that are not being used across all servers and then deleting them.
To start with:
Select Enabled from MHGROUP.CUSTOM5 where Enabled ='N'
then delete records
and where would I run this type of script or should I say can this be done?
Anyone know how to write a script for sql 6.5 that identifies what column is a primary key on a table? (INFORMATION_SCHEMA doesn't exist) Thanks for the help in advance.
What I need to do - can it be done without scripting?
1.) I need to get a list of sp's from a table (EXEC SQL task)
2.) Next, loop through the list of sp's and execute (FOREACHLOOP)
3.) Then, write the output of each sp to a uniquely named file. (DATA FLOW task)
So far, I have had success with 1 and 2. But getting the DATA FLOW task to work has been imposssible thus far.
I tried everything I can think of. I even created a child package that gets called from the FOREACHLOOP, using a Parent Variable Configuration to pass down the name of the stored procedure variable to the child package. No dice.
I am ready to throw in the towel here.
So, my question is, can what I want to do even be DONE without scripting? I was trying to avoid the "manual" approach to this project. It seems like what I want to do "should" be so simple.
In sum, I am simply trying to read a list of sp's from a table, execute the sp's, then write the output of the sp's to a uniquely named file.
Can this be done without scripting? I am beginning to think NOT.
Can you please share or show me the code for scripting out stored procedures in SQL Server 2005 using SQL Cmd?. I need to perform the following:
1). Script out the text of the stored procedure
2). Output to a sql file (text file) under some directory like C:sql
3). Import the file into the destination db and run it.
The above three need to be automated in a DTS Package. I tried to use the DMO but my source server is a SQL2k5 and so DTS desginer does not let me use the ActiveX with DMO against SQL 2k5. I need to run the first step against a SQL2k5 machine and the second and third on a SQL2k machine.
I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?
Can you write a script that compares table changes. I have a testing database with a bunch of table changes that i need to move to production and instead of handtypeing the changes in I was wondering if there was a method of creating a script.
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
I am currently running SQL7 sp3 and will be moving the database to SQL2k. I have restored the database without any problems, but have not moved the dts packages. What would be the best solution: script the packages, save the packages to the 2k db, or just build from scratch? Any feedback would be appreciated. Thanks! -J-
I would like to move over all of my DTS packages and corresponding jobs onto another MS SQL server machine. The problem that I have is that I haven't been able to find any straight-forward info on how to script up these jobs, move them onto another machine and execute them. I have scanned through previous posts written by people with the same problem, but every response is simply "script up the jobs and move them onto another server." Maybe I'm missing something, but how "exactly" do I get a job to show up on this destination machine in EM, in the management/sql server agent/jobs folder. I did the jobs/all tasks/script job... but then what? I saved it on this destination machine, but am unclear as what to do next. The MS SQL Server Administrators companion book is of no help on this subject, and I do not understand how this simple task can be so confusing and is not documented. Any help would be greatly appreciated. Thanks in advance! TB
Many times here I have read about scripting DTS packages to copy them to another server. How is this done? Under "All tasks/Generate SQL Scripts" there is no option for DTS. Sould I be looking elsewhere??
I know there must be a way to script GRANTS for all users for specific objects(tables, sp's, etc). Can't find much in BOL, etc. Does anyone know how? Any comments would be appreciated.
Hi all, Is there any way to script all the triggers in a database just the way we script tables and stored procedures? currently I am using the system tables. regards, Harshal.
Does anyone here know anything about the DCS Scripting Language.
A contract vacancy has arisen using this language but I've never heard of it and there does seem to be anything on the internet about it. Can anyone help?
