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]
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?
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.
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.
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
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.
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!"
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.
Is there an easy way to script the tables from within a database toinclude the indexes etc...Basically, if I want to script a number of tables, I can right handclick on them and generate the script that way. What I'd like to do isthis same process in T-SQL if possible so I can build an SP which I canthen run.It's really just a discussion point. Say we have a number of verysimilar databases whereby there is a database per client and thestructure is almost the same. This was done for simplicity (rightly orwrongly) and there is a fair amount of data.When we take on a new client, we would need to take a copy of thestructure and create a new database with the tables from a known'base'.We would likely need a table to hold the names of the tables to includefrom each database. We would then create an SP passing the name of thedatabase to copy from and one to copy to. This would then create ascript we can then automatically run to create a new database. Couldthis be done in an SP where you pass these parameters, it createsanother SP with the script and then runs it ?I've simplified some of this, so I hope it makes sense. It's notsomething I would have normally done, but I'm curious how this problemwould be approached. Often people would take a script (or use a savedone) and run that after manually creating the database.Thanks in advanceRyan
I have a question on coding, just want to see if there is a better wayto do this.Here is some sample data combinations that would come from 2 differenttables.Item Method Price Cost PercentageA-1 L 100 50 10A-2 D 110 55 15A-3 U 90 40 65Ok, here is what I want to do.If L then 100 x (1+(10/100))if D then 110 x (1-(15/100))if U then 40 x (1+(65/100))To produce 1 number per item.I can do this obviously using multiple When Then Else statements.LikeCase when method='L'then Price * (1+(percentage/100))else case when method='D'then Price * (1-(percentage/100))else Cost * (1+(percentage/100))endendHowever I was thinking there might just be a better way to do this thatI am not familar with, plus I could easily have 8-10 of these methodsthat I need to code.Any ideas on a better way?Thanks in advance.
