Trouble Modifying A Table
Mar 13, 2007
Hi There,
I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:
------------------------------------------------------------
Errors were encountered during the save process. Some of the database objects were not saved.
'Comments' Table
-Unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ...
... Cannot drop the table 'dbo.Comments' because it is being used for replication
-----------------------------------------------------------
If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)
I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please!
Let me know if I need to provide more info. Thanks.
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Dec 19, 2002
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?
Thanks!
View 3 Replies
View Related
Jun 18, 2008
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.
I also have an inner join view with the initials from both tables as the joining field.
I want the users to be able to modify the data table (MainTable) but also display the users name field.
here is the sql of the view:
SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
View 3 Replies
View Related
Jan 21, 2014
I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?
I have never done dropping a table and recreate it?
View 3 Replies
View Related
Jun 14, 2007
HI everybody need help badly on the following
have to recordsets with the following with the following SQL
SELECT place, count(minus)as countminus
order by place
group by place
THE RESULT WOULD BE
PLACE countminus
ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0
MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus
THE RESULT WOULD BE:
PLUS COUNTPLUS
ABC 10
DEF 20
my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...
with this condition..
I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...
THE RESULT WOULD BE THIS.....
PLACE countminus COUNTPLUS
ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0
I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
View 2 Replies
View Related
Jul 28, 2006
By default, I constructed my database using the dbo Schema. I have since created new Schemas, and want to change some of the tables from dbo.
Is there a way to change the existing schema of a table without disrupting the data that is inside the table?
For example:
Current: dbo.Products
Desired Change: NewSchemaName.Products
Thanks.
View 4 Replies
View Related
Apr 15, 2004
Hi there,
Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.
Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?
Thanks.
Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
View 7 Replies
View Related
Jul 3, 2007
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.
SP3 has been applied to SQL Server 2000.
Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
View 2 Replies
View Related
Jan 9, 2008
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies
View Related
Nov 26, 2001
I'm a horrible noob everyone, so my apologies come up front.
I know Oracle allows me to just do an
INSERT INTO table
SELECT column1, column2
FROM table2
But I cannot get this to work in MS SQL 2000. (See question #3)
Here's my script that I'm trying to execute from Enterprise Manager:
From this table:
CREATE TABLE ISIS_DATA
(ISIS_STATUS_ID VARCHAR(15) CONSTRAINT ISIS_DATA_ISIS_ID_PK PRIMARY KEY,
ISIS_NAME VARCHAR(50),
ISIS_CLASS_EXPIR_DAT VARCHAR(20),
ISIS_SEX_BD_CAT_SCHL VARCHAR(20))
Where this is a sample row of data:
A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G
I want to run that data through some substrings etc. and dump it into another table like this:
//THIS TABLE WILL BE WHERE WE PUT THE RESULTING DATA FROM
//EXECUTING THE SUBSTRs AND OTHER FUNCTIONS TO PREP THE DATA FOR BASIS.
CREATE TABLE ISIS_DATA_PREP
(ISIS_STATUS_ID VARCHAR(15),
STATUS VARCHAR(5),
STUDENT_ID NUMERIC(15) CONSTRAINT ISIS_DATA_PREP_STUDENT_ID_PK PRIMARY KEY,
LAST_NAME VARCHAR(25),
FIRST_NAME VARCHAR(20),
MID_INIT CHAR(1),
CLASS NUMERIC(2),
EXPIR_DATE VARCHAR(10),
SEX CHAR(1),
BIRTHDAY VARCHAR(10),
CAT NUMERIC(3),
SCHOOL VARCHAR(5))
Using this script so far:
INSERT INTO ISIS_DATA_PREP
SELECT B.STATUS, B.STUDENT_ID, B.LAST_NAME, B.FIRST_NAME, B.MID_INIT,
B.CLASS, B.EXPIR_DATE,
B.SEX, B.BIRTHDAY, B.CAT, B.SCHOOL, A.ISIS_STATUS_ID
FROM ISIS_DATA A,
(SELECT SUBSTRING(ISIS_STATUS_ID, 1, 1) STATUS, SUBSTRING(ISIS_STATUS_ID, 2, 9) STUDENT_ID,
ISIS_NAME LAST_NAME, SUBSTRING(ISIS_NAME, 5, 1) FIRST_NAME, SUBSTRING(ISIS_NAME, 5, 1) MID_INIT,
SUBSTRING(ISIS_CLASS_EXPIR_DAT, 1, 1) CLASS, SUBSTRING(ISIS_CLASS_EXPIR_DAT, 2, 8) EXPIR_DATE,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 1,1) SEX, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 2, 8) BIRTHDAY,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 10, 1) CAT, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 11, 2) SCHOOL,
ISIS_STATUS_ID
FROM ISIS_DATA) B
WHERE A.ISIS_STATUS_ID = B.ISIS_STATUS_ID
I keep getting this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?
So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.
Now I get this message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
I look up this error on MS's knowledge base and get something along these lines:
...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....
QUESTION #2 -
What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.
QUESTION #3 -
My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.
Sorry for the absolutely massive post. : ) And thanks for any feedback.
Lance
View 2 Replies
View Related
Feb 7, 2001
I would like to know how to (if it is at all possible) in SQL (or even ADO) to retrieve all the data concerning database X's
a) Tables
b) Tables column names
c) Tables column's data types
I don't want to use the doa.tabledefs object.. .i would prefer to do it in SQL, but using ADO objects (since I am developing stuff in VB) would be ok too.
Please Help.. i have been going crazy trying to find anything useful.. email me back please!
Thanks,
Matt
View 2 Replies
View Related
Mar 9, 2007
I have a table where I want to delete some data from but I get this error.
You might have a record that has a foreign key value related to it, or you might have violated a check constraint.
What to do????
View 1 Replies
View Related
Mar 17, 2008
I created a package using the Import Data wizard and everything works fine when I re-execute it. I needed to change the name of the destination table so I went into SQL Server and renamed the table. I then went in and edited the DTSX file via a text editor and changed the OpenRowset setting from [CMBS].[dbo].[raw_Note] -> [CMBS].[dbo].[T_Raw_Note]
<property id="104" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[CMBS].[dbo].[T_Raw_Note]</property>
There are no other references to the table in the DTSX, i saved the file and then re-ran.
When I run, I get invalid object name dbo.raw_Note (old table name). Is there some data being cached somewhere or hidden elsewhere that it would reference the old table? When I go back into the DTSX file, the correct name is in there so I don't know where it is getting the old name from? Any help would be appreciated.
Tim
View 3 Replies
View Related
Mar 17, 2008
Let me first describe my report: I have the following table
Header
Group 1 row--There is a textbox that in this row that when toggled makes visible the two below group 1 rows and the detail row.
Group 1 row
Group 1 row
Detail row
Group 2 row-- This row has a textbox that can hide the below group 2 row.
Group 2 row-- This row contains a subreport.
Currently, I have a parameter that allows the user to conditionally collapse or show the tables' information for printing purposes so the user doesn't have to go through and click on so many plus signs. My problem is I would like to use a parameter to conditionally hide both of the Group 2 rows so that the user cannot view or click on them. The information in Group 2 is extra and not always needed, so I would like to provide the user with a way to hide this information when they go to view the report. However, I imagined this would be easy enough. I went to group2's visibility tab through the table interface and set the expression to "=IIF(Parameter!ShowIndexes.Value, FALSE, TRUE)". However, when I view the report the report is hiding the detail information as well as the Group2 rows.
Anyone have any ideas?
View 1 Replies
View Related
Mar 26, 2008
Hello!
I've been playing around with local reports for a while now. Trying to convert alot (about 800) reports made in a custom format(not XML) to the .RDLC used in MS Reports. I've been able to solve most converting problems by first figuring out how to make the different appearances of the report editor and testing, and afterwards implementing these in my converter program and testing until it makes the desired appearance on screen. Unfortunately I haven't been able to design all the reports similar to the originals(with the editor), so I was hoping somone here might be able to help me with this.
I'm trying to make a box around my table(the repeating part in the body) but find it kind of hard to do this. There are alot of data and the box should be on every page(I really hope this is possible).
I have made a simple report with a header and footer. In the header I have some textboxes and a horizontal line at the bottom. In the footer I have a horizontal line at the top, and som text at the bottom. This was intended for making the top and bottom lines in my 'box'. The rest of the box is made from two vertical lines, one on each side of the table in the body area of the report. These go down from the top of the body to the bottom.
The height of the header is 1 cm, the height of the body is 24.7 cm and the height of the footer is 4 cm making a total of 29.7cm (standard A4 paper height). Hope someone can help me solving this.
Greetings
Øyvind Johansen
Norway
View 3 Replies
View Related
Aug 13, 2007
How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
View 3 Replies
View Related
Mar 6, 2008
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen
View 6 Replies
View Related
Jan 10, 2002
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:
ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY
but this does not work.
Any idea how to accomplish this?
Thanks!
Mike
View 1 Replies
View Related
Apr 28, 2004
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.
Alter Procedure usp_UpdateJobName
@JobNameOld nchar, @JobNameNew nchar
As
UPDATE
JobName
SET
JobName=@JobNameNew
FROM
tblRMADATA
WHERE
tblRMADATA.JobName=@JobNameOld
View 4 Replies
View Related
Apr 28, 2004
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata
So how and where can I change my package.
Thanks.
View 2 Replies
View Related
May 5, 2004
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
View 1 Replies
View Related
May 29, 2008
Hi,
I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.
What I want to add is the following data.
For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate
Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.
Any assistance is much appreciated!!
thanks once again
mike123
SELECT CONVERT(varchar(10),referDate,112) AS referDate,
SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',
count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,
SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',
SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'
FROM tblreferemails R
WHERE DateDiff(dd, referDate, GetDate()) < 5
GROUP BY CONVERT(varchar(10),referDate,112)
ORDER BY referDate DESC
CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)
CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]
GO
View 3 Replies
View Related
Nov 19, 2007
Greetings
I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL
select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1
EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2
How can I modify the query to achieve the expected output?
Thanks:
View 9 Replies
View Related
Feb 13, 2008
Hi our reports server has some reports on it.
we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO".
can we get rdl file from reporting server ?
View 3 Replies
View Related
Sep 27, 2007
Can I modify 2000 DTS in 2005.I mean to say add some functionality to the existing DTS.
View 1 Replies
View Related
Oct 16, 2007
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID.
I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure?
Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
View 4 Replies
View Related
Dec 6, 2004
I probably know that I don't want to do this, but I have an odd case.
I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database.
I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that.
I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion.
Thanks,
Pete
View 2 Replies
View Related
Jul 31, 2002
Hello ,
I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .
Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified .
The table is configured for transactional replication .
I will have to drop the entire publication and create all the subcribers again .
Any help in this regard please .
Sameer
View 2 Replies
View Related
Feb 1, 2001
I've learned to create DTS packages, saving them in a SQL database. But how can one modify a saved DTS package? And how does one delete a saved DTS package?
thanks,
Boog
View 2 Replies
View Related
Jan 3, 2000
I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username?
Thanks,
-Darin.
View 2 Replies
View Related
Aug 27, 2003
Hello All,
I would like to update some values in my table that have this value: XXXX-XXXX. I would like to update the '-' and change it to a '_'. Does anyone know the best way to do this?
I tried this but I get a "Subquery returned more than 1 value error"
UPDATE PS_MTCH_RULES
SET MATCH_RULE_ID = (SELECT SUBSTRING (MATCH_RULE_ID,1,4)+'_'+SUBSTRING(MATCH_RULE_ID,6, 4) FROM PS_MTCH_RULES WHERE MATCH_RULES_ID <> 'ERS')
Any assistance would be appreciated.
Thank you.
E David Ramos
View 1 Replies
View Related
