Multiple Sp_rename In Transact-SQL Script Won't Work

May 6, 2008

Hi,
Recently I have been trying to write a script in order to redo this entire database, it's actually only about rectifying column and table names as well as a few erroneous relations, etc. The idea is that the actual data is okay it's just the organization that is completely messed up (spaces in column and table names, etc.)

Anyway my problem is this, a part of this script is about mass-renaming columns and tables the code is as follows in the Query Analyzer:





Code Snippet

sp_rename 'DOMAINE.[Id DOMAINE]', 'ID_DOMAINE', 'COLUMN';
sp_rename 'DOMAINE.[Nom DOMAINE]', 'NOM', 'COLUMN';

When I barely analyze the code (Ctrl+F5) it gives me the following error:
Serveur : Msg 170, Niveau 15, État 1, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'sp_rename'.

Which would roughly translate into:
Server : Msg 170, Level 15, State 1, Line 2
Line 2 : Incorrect syntax near 'sp_rename'

When I execute it, it gives the same error. Why does it do that when i give it the two lines while if i enter the lines one by one it works just fine. How is a SCRIPT supposed to work if I have to "baby-feed" every statement separately.


PS:
i am working on SQL Server 2000 (in Query Analyzer menu ?/About .. it says SQL Version 8.00.194

View 5 Replies


ADVERTISEMENT

Transact SQL :: Multiple Update Top On Commit Transaction Doesn't Work

Jul 10, 2015

I have this sql stored procedure in SQL Server 2012:

ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;

[Code] ....

View 4 Replies View Related

Transact SQL :: How To Make Procedure Work For Multiple Values In Parameter

Jun 7, 2015

Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do

exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO

[code]....

View 2 Replies View Related

SP_rename

Nov 28, 2007

Hi There,
 Why does the following statement doesn't work when i execute this in the master database? How should it be altered?
 EXEC sp_rename 'REPLICATIETEST.dbo.USERLOG2','REPLICATIETEST.dbo.USERLOG
Kindest Regards

View 5 Replies View Related

Sp_rename

May 16, 2008

Does anyone know how to do a sp_rename thats not held to the current database?

View 3 Replies View Related

Sp_Rename Problem...

Jul 20, 2005

SQL Server 7.0I have a stored procedure that builds some tables. To minimize down time(it is the backend for a website), I build the tables into temp (not #temp)tables, drop and rename the tables with SP_Rename. On ONLY one of the tablerenames (1 of 8), I get the following Error:sp_rename error: 15248 Severity 11Error: The parameter @oldname is either ambiguous or the claimed @itemtype((null)) was wrong. It refers to Line 256.Again I get no errors doing the exact same process on the other 7 tables.Anybody have any ideas?Barry

View 3 Replies View Related

Table Corruption After Sp_rename

Oct 5, 2000

Sql 7. SP 2

After renaming a table Table_tbl to Table_old
and vise-versa.

When selecting from Table_old (for yesterday's data)
I get errors stating that Page belongs to Table_tbl.

Any suggestions on how to successfully rename Old to New, etc.
And not have cross-linked data pages??

View 1 Replies View Related

DDL Triggers And Detecting Sp_rename

Jan 2, 2008

I am trying to use DDL triggers to detect all changes to a table.

Sometimes a user directly or (more likely) through the SQL Server Management Studio GUI renames a column via sp_rename.

I can't seem to figure out how to detect such renames using a DDL trigger. Is there any way or is this a limitation of the DDL trigger system?

View 9 Replies View Related

2005 Breaks Sp_rename For Constraints?

Aug 30, 2006



BooksOnline indicates that sp_rename can still be used to rename constraints such as primary keys, foreign keys, defaults, etc. in SQL Server 2005. However, when I try to do so I get this:

"Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong."

After looking at the code for sp_rename, it looks like this problem might be related to another bit of apparent misbehavior on 2005's part: when I execute "select object_name()" with the Id number of a constraint, it returns the name, but when I execute "select object_id()" with the name of that same constraint, it returns null. Why is that?

Thanks,

Ron

View 6 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary', 
@profile_name  =
'SQL SMTP',
   
[code]....

View 3 Replies View Related

Transact-sql Code Does Not Work. HELP!

Aug 1, 2006

Hi all, I am new to transact-sql and hoped that someone here might be able to help. I have a db with a field called "part" ... part contains text in the format:

xxxxx-xx-xxxxx

(the number of x's before or after each hyphen vary) such that I could have xxx-xxxxx-xxx as the part.

Someone wrote this transact-sql to take my current "part" field and move it to three different part fields ... a,b,c

If xxxxx-xx-xxxxx was the part in the original db, then a would contain xxxxx .. b contains xx ... c contains xxxxx

Make sense? Here is the code. It doesn't work and I can't seem to figure out why! It looks like it is just trying to find the hyphens and cut out the text from that... but it fails with an error that says "invalid length parameter passed to the substring function". Note, if I take off the "-1" from each line below... then the function works but produces the wrong data.

char(45) = "-" (hyphen)

UPDATE [dbo].[parts] SET
a = SUBSTRING(part,1,CHARINDEX(CHAR(45),part)-1),
b = SUBSTRING(part,CHARINDEX(CHAR(45),part)+1,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))-1),
c = SUBSTRING(part,CHARINDEX(CHAR(45),RIGHT(part,LEN(part)-CHARINDEX(CHAR(45),part)))+CHARINDEX(CHAR(45),part)+1,LEN(part))
GO

Does anyone know what I can do to fix this or is it much more difficult to do than what it seems?

Thanks!

View 10 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following  database structure

Stock        Depth41     Depth12    Depth34
AAA            1              2              1
BBB             2            2               4

How can I show  Each Depth column as seperate row

AAA          1
AAA          2
AAA          1  as follows

View 3 Replies View Related

Transact SQL :: Recursive CTE Work Flow

May 7, 2015

how recursive CTE works...Synthesise table with non-recursive CTE

;WITH Employee (ID, Name, MgrID) AS
(
SELECT 1, 'Keith', NULL UNION ALL
SELECT 2, 'Josh', 1 UNION ALL
SELECT 3, 'Robin', 1 UNION ALL
SELECT 4, 'Raja', 2 UNION ALL

[code]....

the first part query will return one row
IDNameMgrIDnLevelFamily
1KeithNULL11

then this result set using after union all query .how the level + 1 condition is working ?  and how its return values .

View 7 Replies View Related

Transact SQL :: Update Statement In Merge Does Not Work

Jul 29, 2015

In the following t-sql 2012 merge statement, the insert statement works but the update statement does not work. I know that is true since I looked at the results of the update statement:

Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

[Code] ...

Thus can you show me some t-sql 2012 that I can use to make update statement work in the merge function?

View 3 Replies View Related

Transact SQL :: Create Custom Work Day Calendar

Nov 17, 2015

I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays.  This is the syntax I have to start with, but it is presenting with an error:

Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.

What should I alter?

CREATE TABLE dbo.CreateCustomCalendar
(
wk_Date INT IDENTITY NOT NULL,
FullDate DATETIME NOT NULL,
WeekDayName VARCHAR(9) NOT NULL,
IsWorkday varchar(20) NOT NULL
)

[Code]....

View 7 Replies View Related

Transact SQL :: BETWEEN Using String Dates Doesn't Work In Some Cases

Sep 21, 2015

I put this series of select statements to verify that the BETWEEN statement is working properly. I should always get “Between” below.

SELECT
CASEWHEN'1/1/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
SELECT
CASEWHEN'1/31/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END

[Code] .....

View 4 Replies View Related

Transact SQL :: Way To Configure ODBC Driver For Informix DB To Connect / Work With SSMS?

Aug 18, 2015

Basically, I am asking if it is possible to use SSMS (Query Analyzer window) with Informix.  I am thinking linked server.  If I have the ODBC driver installed on my workstation (win 7 pro VM workstation -- SSMS on same VM machine) is this doable? Do I just do the standard linked server routine or is there a special way (if it is even doable).

View 4 Replies View Related

Transact SQL :: Calc Work Date Using Business Days Field In Calendar Table

May 19, 2014

I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.

Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.

------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.

------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
    @WorkDays VARCHAR(5),
    @DateFromValue AS DateTime )
    RETURNS DATETIME

[Code] ....

View 10 Replies View Related

Transact SQL :: Multiple Select CTE

Jun 11, 2015

I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.

I got a wonderful select statement that gives me the latest Results for each Account.

WITH cte AS
(
SELECT
Accounts.AccountID,
Accounts.AccountName,
Results.ResultTime AS LastUpdated,

[Code] ....

I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:

SELECT SUM([Lots]) AS Longs
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 0;

SELECT SUM([Lots]) AS Shorts
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 1;

But it has to "join" the CTE somehow so that I get the correct answer for each Account row.

View 14 Replies View Related

Transact SQL :: COUNT And SUM Of Multiple Columns

Sep 2, 2015

I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.

CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]....

View 8 Replies View Related

Transact SQL :: With CTE N Multiple Select Statement

Apr 29, 2015

SELECT FirstSet.Country,FirstSet.[Month]
,ABC.ABC1
,DEF.DEF1
FROM (
SELECT [Answer Text]'Country',interview_start 'Month',[ID respondent],

[Code] ....

I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my  output ok but when I try to receive that output from write another select statement then its show error.

Msg 102, Level 15, State 1, Line 276
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 351
Incorrect syntax near the keyword 'as'.

View 8 Replies View Related

Transact SQL :: Multiply Multiple Columns From One Row

Nov 23, 2015

I have a table with the following format

I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].

Is this possible using T-SQL 2012?

View 5 Replies View Related

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date
-
ET_C_IsValid 

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies View Related

Transact SQL :: Concatenation Of Multiple Fields

Oct 29, 2015

I have the folloiwng fields:

ProbationLengthYears AS Decimal
ProbationLengthMonths AS Decimal
ProbationLengthDays AS Decimal

Sample Data:

ProbationLengthYears = 2.00
ProbationLengthMonths 0.00
ProbationLengthDays 0.00

I have successfully concatenated and converted all three fields into one field as Follow:

 SELECT
  CONVERT(varchar(10),CAST(cc2.ProbationLengthYears AS INT)) + ' Year(s)' + ' ' +
  CONVERT(varchar(10),CAST(cc2.ProbationLengthMonths AS INT)) + ' Month(s)' + ' ' +
  CONVERT(varchar(10),CAST(cc2.ProbationLengthDays  AS INT)) + ' Day(s)'

 The result is:

  2 Year(s) 0 Month(s) 0 Day(s)

Ideally I would like the result to be as follow because the probation months and days are 0:

  2 Year(s).

View 4 Replies View Related

Transact SQL :: Concatenate Multiple Fields Into One

Dec 5, 2015

I have a table where I need to concatenate all values into one field separated by a comma.  If the field is null display a blank value.  This is my table structure and example output

Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)

[code]...

View 5 Replies View Related

Transact SQL :: Update Same Row Multiple Times

Nov 9, 2015

I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.

Example: if the column has a string "b" then replace with "B" and  if the column has a string "d" then replace with "D" . I can  write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement

column before the update : bcdxyz
after the update: BcDxyz

Following is the sample data

declare @t1 table
(
    c1 varchar(5),
    c2 varchar(10),
    c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')

[Code] ....

View 5 Replies View Related

Transact SQL :: SUM Of Multiple Rows With Where And Group By

Apr 30, 2015

I have a table Transaction that looks something like the following :

TransactionID          

Currency        Credit             Debit
1     USD      500               0
2     Afcu          6000              0

[Code] ....

I write query like this

select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source

And it came like

Balance Source           
1500                        USD
6000                        Afcu
6800                        INR
7000                        Pfc
-200                         AUD

But I also want to add  Afcu , Pfc with USD and  want output like

Balance Source
14500                        USD
6800                          INR
-200                           AUD

View 3 Replies View Related

Transact SQL :: Can Have Multiple Statements Under CASE-THEN

Jun 3, 2015

So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....

CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]

View 3 Replies View Related

Transact SQL :: Display Data In Multiple Row

Aug 5, 2015

I have a table like dependent eg. 

Dependent 
emp id   Dept ID  Child Name  
1             11          C1
1             12          C2
1             13          C3
1             14          C4
1             15          C5

These is input table and i want output like 

Child1   Child2  Child3
C1          C2        C3
C4         C5          null

View 8 Replies View Related

Transact SQL :: Pivot On Multiple Results

Nov 9, 2015

I have a table similar to below:

itemID | part
1         | A
1         | B
2         | A
2         | A
2         | A
3         | C

I need the table to look like the following:

itemID | part1 | part2 | part 3
1         | A        | B       | null
2         | A        | A       | A
3         | C        | null    | null

There will _never_ be more than three parts to an item, and it does not matter what order they are in.

I cannot get pivot to work for me.

View 2 Replies View Related

Transact SQL :: SELECT From Multiple Tables

Jul 13, 2015

I have the following two tables....

tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo
tblWagesWeeks
-weekID
-weekDay
-date

I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.

View 11 Replies View Related

Transact SQL :: First Values In Multiple Columns Selection

May 19, 2015

I'm trying to run something like this:

Select ID, FIRST(forename), FIRST(surname) from table1
GROUP BY ID;

I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.

There are records like:

ID      forename     surname
--------------------------------
1    John    Kormack
1   James  Dope
2   Erin    Dupes
3   Will  Hugh
3    Walter Heisenberg

So I want to pull out:

1 John Kormack
2 Erin Dupes
3 Will Hugh

How can I run it in TSQL?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved