Scripting Constraints
Nov 30, 2001
Hi,
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?
TIA,
Seoras.
View 2 Replies
ADVERTISEMENT
Mar 16, 2004
Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
View 1 Replies
View Related
Jan 9, 2007
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
View 7 Replies
View Related
Jun 29, 2001
SQL 7
I am trying to run a script written for an asp page (pure vbscript) in a active script job. I am getting errors on:
Server.CreateObject("ADODB.Connection")
Yes, ADO is up to date and loaded. The error says
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server' Error on Line 14. The step failed.
Thoughts ..
Craig
View 1 Replies
View Related
Sep 14, 2000
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.
View 2 Replies
View Related
Oct 28, 2002
Does anyone know how to prevent or change the
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
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.
Thanks,
Scott
View 3 Replies
View Related
May 3, 2006
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?
Thanks
View 4 Replies
View Related
Oct 7, 2005
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.
View 10 Replies
View Related
Mar 21, 2007
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.
Sigh. This is driving me crazy.
Please, any advice!!
Thanks
View 14 Replies
View Related
May 15, 2006
Hi Gurus/MVPS:
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.
Please help!!!.
Thanks
Ankith
View 3 Replies
View Related
Nov 18, 2003
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?
View 6 Replies
View Related
Nov 4, 2004
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.
View 1 Replies
View Related
Mar 26, 2001
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-
View 2 Replies
View Related
Apr 10, 2001
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
View 2 Replies
View Related
Jun 5, 2001
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??
View 2 Replies
View Related
Aug 7, 2000
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.
View 1 Replies
View Related
Jul 16, 2002
How can I get a script of triggers (drop & create them) without scripting the tables with them ?
Thanks!
View 9 Replies
View Related
Jun 28, 2004
Is there any way to script DTS Packages? :confused:
View 1 Replies
View Related
Jan 29, 2008
Are you kidding me?
I can't create 1 object per file anymore AND I can't have the drop created along with the create
Are they forcing you to use ALTER?
What a piece of junk
View 7 Replies
View Related
May 7, 2004
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.
View 3 Replies
View Related
May 19, 2008
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?
View 1 Replies
View Related
Oct 28, 2004
Hi,
Does anyone have scripts that generate the following:
1. All PK
2. All FK
3. All Indexes
Separate from the table DDL.
( EM can generate but with table DDL as well )
rockmoose
View 5 Replies
View Related
Apr 11, 2006
Dear friends,
I have alot of tables to create in sql server 2005. They are all ready to copy and paste. Is there a window where I can just script all in at once?
Thankyou very much
Chopsmum
View 13 Replies
View Related
Mar 9, 2007
Hi all.
I am looking to write a script to create several new tables. What i would like to know is how do i check if they already exist before i create them and if they already exist dont create them? If they dont exist, create them.
Cheers people.
View 2 Replies
View Related
Mar 17, 2007
Does anyone have any insight as to what is wrong with my SQL statement? All tables exist in this DB however, I get the messages at the bottom.
CREATE TABLE animal_colors(row_id INT NOT NULL IDENTITY(1,1) ,color VARCHAR(50))
INSERT INTO animal_colors (color)
SELECT
[description]
FROM
color
DECLARE @i INT
DECLARE @row_count INT
SET @i = 1
SET @row_count = (SELECT MAX(row_id) FROM animal_colors)
WHILE @i < @row_count
BEGIN
UPDATE animal
SET color =
(SELECT TOP 1 color
FROM color
WHERE color.[description] = animal_colors.color
and animal_colors.row_id = @i
)
FROM color
INNER JOIN animal ON animal.color = color.color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i
DELETE FROM color WHERE color NOT IN (SELECT TOP 1 color FROM color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i)
AND color.[description] = animal_colors.color AND animal_colors.row_id = @i
SET @i = @i + 1
END
DROP TABLE animal_colors
Result:
Server: Msg 107, Level 16, State 2, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
View 5 Replies
View Related
Apr 3, 2007
Does anyone know of a way to script tables without the default brackets that surround the table/columns? I've looked under the scripting options but don't believe I can find anything regarding this. Thanks
View 5 Replies
View Related
May 17, 2007
Hi all,
I am new to SSIS...and wanted to know where can i get a lot of info on how to deal with ActiveX Scripttasks & Script tasks in SSIS...the place i am working has a lot of VB Scripting done in DTS Packages...having a hard time in understanding the Scripts, as i am more like back-end guy and wanted to learn a lot in SSIS, once i understand the scripts it will help me a great deal as to how to approach the tasks...Is there any website which teaches how to avoid Scripting in SSIS as i read somewhere that Scripting should be avoided as much as possible by making using of so many tasks in the SSIS tool.
I will look forward for someone to help me out and show me a way.
thanks
ravi
Nothing much that i can do..!!
View 1 Replies
View Related
Jul 9, 2007
Hi,
I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).
Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.
Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"
Regards
Mark
View 15 Replies
View Related
Jan 16, 2008
I currently have an odd requirement esp as its my first ever SQL script.
At present ignoring all else there are two tables:
USE [Live2]
GO
/****** Object: Table [dbo].[Person] Script Date: 01/16/2008 10:08:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[Pers_PersonId] [int] NOT NULL,
[Pers_CompanyId] [int] NULL,
[Pers_PrimaryAddressId] [int] NULL,
[Pers_PrimaryUserId] [int] NULL,
[Pers_Salutation] [nchar](10) NULL,
[Pers_FirstName] [nchar](30) NULL,
[Pers_LastName] [nchar](40) NULL,
[Pers_MiddleName] [nchar](30) NULL,
[Pers_Suffix] [nchar](20) NULL,
[Pers_Gender] [nchar](6) NULL,
[Pers_Title] [nchar](40) NULL,
[Pers_TitleCode] [nchar](40) NULL,
[Pers_Department] [nchar](30) NULL,
[Pers_Status] [nchar](40) NULL,
[Pers_Source] [nchar](40) NULL,
[Pers_Territory] [nchar](40) NULL,
[Pers_WebSite] [nchar](40) NULL,
[Pers_MailRestriction] [nchar](40) NULL,
[Pers_PhoneCountryCode] [nchar](5) NULL,
[Pers_PhoneAreaCode] [nchar](20) NULL,
[Pers_PhoneNumber] [nchar](30) NULL,
[Pers_EmailAddress] [nchar](255) NULL,
[Pers_FaxCountryCode] [nchar](5) NULL,
[Pers_FaxAreaCode] [nchar](20) NULL,
[Pers_FaxNumber] [nchar](30) NULL,
[Pers_CreatedBy] [int] NULL,
[Pers_CreatedDate] [datetime] NULL,
[Pers_UpdatedBy] [int] NULL,
[Pers_UpdatedDate] [datetime] NULL,
[Pers_TimeStamp] [datetime] NULL,
[Pers_Deleted] [tinyint] NULL,
[Pers_LibraryDir] [nvarchar](255) NULL,
[Pers_SegmentID] [int] NULL,
[Pers_ChannelID] [int] NULL,
[Pers_UploadDate] [datetime] NULL,
[pers_SecTerr] [int] NULL,
[Pers_WorkflowId] [int] NULL,
[pers_Dear] [nchar](40) NULL,
[pers_DMCCoID] [nchar](60) NULL,
[pers_Legacy_Contact] [nchar](119) NULL,
[pers_Accommodation] [nvarchar](40) NULL,
[pers_Group] [nchar](119) NULL,
[pers_Recruitment] [nvarchar](40) NULL,
[pers_Training] [nvarchar](40) NULL,
[pers_dob] [nchar](8) NULL,
[pers_origin] [nvarchar](40) NULL,
[pers_OCR] [numeric](24, 6) NULL,
[pers_level] [nvarchar](40) NULL,
[pers_booked] [datetime] NULL,
[pers_advisor] [int] NULL,
[pers_learnertype] [nvarchar](40) NULL,
[pers_qualification] [nvarchar](40) NULL,
[pers_SecondNVQ] [nvarchar](40) NULL,
[pers_learnerNo] [numeric](24, 6) NULL,
[pers_inductdate] [datetime] NULL,
[pers_initial_iag] [datetime] NULL,
[pers_MidPt_iag] [datetime] NULL,
[pers_exit_iag] [datetime] NULL,
[pers_lmt_advisor] [int] NULL,
[pers_Type_c] [nvarchar](40) NULL,
[pers_fund_type] [nvarchar](40) NULL,
[pers_inductionpack] [datetime] NULL
) ON [PRIMARY]
Also:
USE [Live2]
GO
/****** Object: Table [dbo].[Person_Link] Script Date: 01/16/2008 10:00:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person_Link](
[PeLi_PersonLinkId] [int] NOT NULL,
[PeLi_PersonId] [int] NOT NULL,
[PeLi_CompanyID] [int] NULL,
[PeLi_Type] [nchar](40) NULL,
[PeLi_CreatedBy] [int] NULL,
[PeLi_CreatedDate] [datetime] NULL,
[PeLi_UpdatedBy] [int] NULL,
[PeLi_UpdatedDate] [datetime] NULL,
[PeLi_TimeStamp] [datetime] NULL,
[PeLi_Deleted] [tinyint] NULL
) ON [PRIMARY]
My current script is:
INSERT INTO Person_Link
(PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_Type, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy, PeLi_UpdatedDate,
PeLi_TimeStamp, PeLi_Deleted)
SELECT MAX(pl.PeLi_PersonLinkId) + 1 AS Expr1, pr.Pers_PersonId, '92929' AS Expr2, NULL AS Expr3, '1' AS Expr4, NULL AS Expr5, '1' AS Expr6, NULL
AS Expr7, NULL AS Expr8, NULL AS Expr9
FROM Person AS pr INNER JOIN
Person_Link AS pl ON pr.Pers_PersonId = pl.PeLi_PersonId
WHERE (pr.Pers_CompanyId IS NULL)
GROUP BY pr.Pers_PersonId
The goal is for every row in the person table which does not have a companyID insert a row to the PersonLink table with a company ID of 92929.
Also the colum PersonLinkID in the PersonLink table does not auto ascend.
Can someone kindly point the errors of my ways?
View 2 Replies
View Related
Jan 29, 2008
Are they kidding?
I can't create one file per procedure and the DROPS are only created separately?
Are they forsing you to use ALTER as well?
Or am I missing something obvious?
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
View 6 Replies
View Related
Feb 6, 2008
Has anyone used this shiit ??
its an absolutely useless pile of crap ??
I can't do simple things that were done easily with Active X ..
USELESS...
View 7 Replies
View Related
Mar 21, 2008
I’ve a subquery which returns more than one row (user_group), and i need to convert those rows into a comma delimited list. i tried with various possibilities but unable to find the solution. can you pls.help.
here is my query below.
SELECT
user_ID,
user_Firstname,
user_Lastname,
user_Email,
Username = (Case When user_Username = ‘ ‘ or user_username is Null then ‘None’ else User_Username end),
user_creationdate,
Active = (Case When user_Active = 1 then ‘Yes’ else ‘No’ end),
User_Group = (Select group_name from usergroup a, usergroup_combo b where a.group_id = b.ug_groupID and b.ug_userid = user_id),
TheatreCode = (Case When User_TheaterCode = ‘ ‘ or User_TheaterCode is Null then ‘N/A’ else User_TheaterCode end),
JobTitle = (Case When user_JobTitle = ‘ ‘ or User_Jobtitle is Null then ‘N/A’ else User_Jobtitle end)
FROM [user]
thanks
View 1 Replies
View Related
