Replication Trigger Problem (SQL 2005): Assistance Please
Jun 5, 2006
Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.
Here is the problem:
When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:
execute sp_mapdown_bitmap 0x0000000040, @bm output
The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).
Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.
We are getting this behavior on several SQL Server 2005's...
Additional notes on the environment:
-CLR Triggers written in VB, on most tables performing application functions
-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.
-Again this problem only exists on some tables in the database.
Any assistance would be greatly appreciated.
View 18 Replies
ADVERTISEMENT
Mar 2, 2004
Hi,
Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.
I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.
Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)
Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.
Thanks in advance for your help!!!
Cheers
View 6 Replies
View Related
Jul 23, 2005
I have a trigger that I created to log changes in one table to anothertable but it is horribly inefficient.I am hoping that someone with more experience than I can see a way tomake this trigger more efficient.------------ALTER TRIGGER tContacts_ChangeLogON dbo.ContactsFOR UPDATEASSET NOCOUNT ONDECLARE @tablename varchar(20),@record_id_column varchar(30),@colname varchar(30),@colvalue varchar(8000),@insertstmt varchar(1500),@username varchar(20)SELECT @tablename = 'Contacts'SELECT @record_id_column = 'ContactID'DECLARE columns_cursor CURSOR LOCAL FORSELECT COLUMN_NAMEFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME = @tablenameAND (POWER(2, (ORDINAL_POSITION-1) % 8) & CONVERT(INT,SUBSTRING(COLUMNS_UPDATED(), (ORDINAL_POSITION-1)/8 + 1, 1))) <> 0SELECT * INTO #del FROM deletedSELECT * INTO #ins FROM insertedSELECT @username = RIGHT(SYSTEM_USER, LEN(SYSTEM_USER) -CHARINDEX('',SYSTEM_USER))OPEN columns_cursorFETCH NEXT FROM columns_cursor INTO @colnameWHILE @@FETCH_STATUS = 0BEGINSELECT @insertstmt = 'INSERT INTO ' + @tablename + '_ChangeLog (recordid, fieldname, changedfrom, changedto, username, datetime ) ' +'SELECT d.' + @record_id_column + ', ''' + @colname + ''', d.' +@colname + ', i.' + @colname + ', ''' + @username + ''', GETDATE()' +'FROM #del d INNER JOIN #ins i ON d.' + @record_id_column + ' = i.' +@record_id_column + ' WHERE (i.' + @colname + ' <> d.' + @colname + ')'+' OR (i.' + @colname + ' IS NOT NULL AND ' + 'd.' + @colname + ' ISNULL) OR (i.' + @colname + ' IS NULL AND ' + 'd.' + @colname + ' IS NOTNULL)'-- INSERT INTO Debug (value) VALUES( @insertstmt )EXEC( @insertstmt )FETCH NEXT FROM columns_cursor INTO @colnameENDCLOSE columns_cursorDEALLOCATE columns_cursor
View 8 Replies
View Related
Jul 3, 2006
I need some help here in creating a conditional update trigger. The purpose of this trigger would check to see if a contact already exist in the database on an insert and update only the fields that are null.
So How would I compare each field from the CONTACTS Table against my INSERTED Table?
Inserted.FirstName (COMPARE) Contacts.Firstname
Inserted.LastName (COMPARE) Contacts.LastName
Inserted.Email (COMPARE) Contacts.Email
I will be using the email address as the check for the duplicate record and if a duplicate is found... Instead of not allowing the insert I want to compare the existing record and update any fields that are NULL in Contacts with Inserted.
I have no idea on how to compare all of the fields.
Any help appreciated.
sadler_david@yahoo.com
View 1 Replies
View Related
May 14, 2004
We still have old SQL 6.5 with sp5a running and push replicate to 5 remote sites.
Is there a way to avoid a trigger to avoid to be replicated as in SQL2000 with "NOT FOR REPLICATION"?
Since there is no way to do a constraint check to accross table, is there any way to do a cross table check without using a trigger?
thanks
-D
View 1 Replies
View Related
Feb 21, 2002
Hi Guys,
I have a database replicated to another database in a remote server.
We have a trigger in one of the tables in remote Database(replicated), which will fire as soon as there is an insert into this table. This trigger is maintaining the business logic. This trigger will insert the records into another table depending on what kind of information obtained from the new row(which was replicated).
Problem is, since we are using replication ( it uses bcp internaly), the trigger is not firing. I some how wanted to add these new records to the other table (automated). Can you guys please advice me on how to implement this.
I appreciate your help
Thanks,
Jeyam
View 1 Replies
View Related
Sep 4, 2012
I have setup transactional replication between 2 computers.
In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion of any rows in a table. If an attempt to delete rows in that table occurs, this trigger will write the deleting rows to a local file C:
empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv, and then email this file to a list of recipients. This trigger only exists in the publisher.
Yesterday, while I'm working on the publisher DB, I accidentally executed a stored procedure which attempted to delete some rows in the table. The trigger fired, a file C:2012.09.04.09.01.01.tbl_deleted.csv generated, and the email was sent. But then problem occurs.
Problem: When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said:
Command attempted:
if @@trancount > 0 rollback tran
Error messages:
Attachment file C: emp2012.09.05.01.01.43.36.tbl_deleted.csv is invalid.
(Source: MSSQLServer. Error number: 22051)
Similar errors occur periodically, and the filename (C: empyyyy.mm.dd.hh.mm.ss.tbl_deleted.csv) in the error messages reflect the actual time of the error.
View 3 Replies
View Related
Jan 4, 2007
Hi,I have an issue with my replication at the moment. I will try todescribe the scenario accurately.I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connectto the publisher to upload/download changes. I have a trigger set up onone table which updates another, here is an example of the trigger:"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotesFOR INSERT, UPDATE, DELETEASdeclare @projTotal as moneydeclare @projId as intdeclare @projcurrtype as intselect @projId = project_id from insertedselect @projcurrtype = proj_curr_type from qt_projects where project_id= @projId--Get project total from the sum of table [qt_quotes]select @projTotal = (selectsum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@projcurrtype))as quoteTotal from qt_quotes where project_id = @projId)--Update projects record with new project totalupdate qt_projectsset proj_act_totl = @projTotalwhere project_id = @projId"I feel my trigger maybe setup incorrectly in that replication thinks aninsert is occurring instead of an update. (Im quite new to triggers)What is happening is a conflict is occuring with the following message:"The row was inserted at Server.Publisher' but could not be inserted at'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGNKEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred indatabase 'Publisher', table 'qt_projects', column 'project_id'."What is also happening as a result of this conflict (I think) is therecord in question is getting deleted from the Publisher. This iscausing huge problems as it is proving quite difficult to get theserecords back in the system due to identity values.Can anyone guide me to what might be happeing here, is it the trigger?
View 8 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
Oct 19, 2006
Does anyone know how to do the following. I'm trying to mimicreplication with triggers.I have 2 databases, each have these 2 tables.1.USERSID intNAME varchar(20)2.CHANGESTABLE varchar(20)TYPE varchar(10)col1 varchar(20)col2 varchar(20)On the USERS table I have a for insert trigger. Whenever a new user isadded the trigger puts an entry into the CHANGES table such as("USERS", "INSERT", "1", "Fred")I now have an application (vb.net) that monitors the CHANGES table onserver1. If it finds an entry it determines the table using the TABLEcolumn and performs the necessary insert and deletes the entry fromCHANGES. Now the problem is server2 also has an for insert trigger onthe USERS table so it puts an entry into CHANGES on server2. As youcan imagine this goes around in a loop.What I was hoping for was someway of saying, "I'm inserting from myapplication so don't do the trigger".Any ideas gratefully appreciated.Steve.
View 9 Replies
View Related
Apr 28, 2008
What problems do trigger driven replication impose? I've got two SQL servers. When one is updated, triggers will automatically update the information on the production server. What problems can arise from this? Does it create problems when mass updates and inserts are being performed on tables with triggers? Thanks
View 3 Replies
View Related
Jul 14, 2006
Kill may be overdramatic, but...
I am testing a merge publication with a push subscription. The publication contains a single table. That table has an INSTEAD OF UPDATE trigger marked NOT FOR REPLICATION and an AFTER trigger that needs to go off during replication.
During Synchronization neither trigger goes off. If I drop the instead of trigger the after trigger will go off. What is going on?
Thanks,
View 11 Replies
View Related
Feb 2, 2007
Hi,
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record.
The second trigger inserts record in to another database table and also updates a column of the table "Sample".
The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser.
Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
It would be helpful if u could reply soon.
Thanks and Regards,
Swapna.B.
View 12 Replies
View Related
Sep 25, 2007
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Thanks in advance!
Scott
View 5 Replies
View Related
Jan 24, 2007
Hi,
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
thnx,
pdns.
View 4 Replies
View Related
Jul 11, 2007
Hello everybody!
I hope that someone could help me.
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
I configure the connection to use the IUSR.
The source code that i use:
repl.InternetUrl = @"http://laptop/SQLMobileIIS/sqlcesa30.dll";
repl.Publisher = @"laptop";
repl.PublisherDatabase = @"database";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"Pubdatabase";
repl.Subscriber = @"SQLMobile";
repl.SubscriberConnectionString = @"Data Source='" + nomeFicheiroBD + "';Password='3409'";
The error that returns is:
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
Does anybody knows what i can do?
HELP I NEED SOMEBODY TO HELP!
Thanks!
View 1 Replies
View Related
Apr 10, 2008
I have written following code in my application
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program
Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition
Error: A request to send data to the computer running IIS has failed. For more information see HRESULT
Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider
Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace DeviceApplication1
{
public partial class Form1 : Form
{
string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1()
{
InitializeComponent();
}
private void DeleteDB()
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"DotNetCF";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"PubDotNetCF";
repl.Subscriber = @"SubDotNetCF";
repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(filename))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}
// Display Same Data In Another DataGrid : dataGrid1
SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'");
SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn);
if (dsJobLists == null)
{
dsJobLists = new DataSet();
}
try
{
dsJobLists.Clear();
daJobLists.Fill(dsJobLists, "JobLists");
dataGrid1.DataSource = dsJobLists.Tables["JobLists"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}
private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}
private void Form1_Load(object sender, EventArgs e)
{
Sync();
DeleteDB();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime())
{
// TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'.
this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists);
}
}
}
}
I have created a merge replication correctlly( I suppose, there were no errros)
Please help
Your help will be appriciated
View 1 Replies
View Related
Aug 3, 2006
If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.
BTW, this is transactional replication. Sorry, I left that out of my original post.
Thanks,
Grant
View 1 Replies
View Related
Oct 23, 2007
Hi Folks,
I have a slight problem designing two drop down lists accessing my database as some clients are listed twice because they are located in multiple citys
I have one table called Clients with the following columns
Client
City
F_name
L_name
ID
I am using SELECT DISTINCT Client FROM Clients for my first Dropdownlist with a postback etc.
e.g.
Brown
Black
The Second Dropdownlist must select the different Distinct City Values where they are located on selecting Dropdownlist one
e.g.
Brown > London, New York
or
Black > Singapore and Boston
What SQL Code can i place so the second dropdownlist will select the correct values on selecting the first Dropdownlist.
Thanks,
Sully
View 4 Replies
View Related
Sep 16, 1999
I need some explanantion on the following query that I'm trying to run:
if exists (select * from sysobjects
where id = object_id ('slice_trace')
and sysstat & 0xf = 3)
delete from slice_trace
where control_seq_number = 130
and claim_number = 7912450
When I run this query within a database where the slice_trace table does NOT exist, it still seems to execute the delete statement and hence fails because the object is not there.
Essesntially I need this statement to execute only in databases where the table exists.
View 3 Replies
View Related
Apr 9, 2008
I new it was a mistake changing from mysql to mssql! I'm having difficulty with the following and would really appreciate any help you could offer.
I have two tables.
The first [users] has personal details:
[id]
[business_name]
[title]
[surname]
[firstname]
The second [orders] has their orders:
[order_id]
[user_id]
[order_date]
[order_price]
I need a query that returns the latest record from [orders] for each member, with member details, but only where the latest order was within (now)-425.
Many thanks.
View 1 Replies
View Related
Mar 19, 2008
Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table
MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))
3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19
I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:
3.02 15
4.02 16
8.02 13
10.02 15
12.02 19
In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.
Please advice.
Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
I have received a table of data that has a field containing dateinformation. Unfortunately it was derived from a MainFrame dump andoriginated as a txt file and was then ported into an Access MDB filebefore it became an SQL table. The date format is vchar(50) andactually is comprised of 6 charecters ie: 010104 for Jan 1 2004. Ineed to run a select statement for a range of dates such as 010104thru 030104. Unfortunately being a charecter field this returnsincorrect results under a majority of cases. Back in my dBase daysthere was a VAL() that could be used in this case but I have beenunable to find anything comperable in SQL. Can anyone help me please?Thanks in advanceSteve
View 2 Replies
View Related
Jul 27, 2007
Error: "A connection was successfully established with the server, but an error occurred during the pre-login handshake. (provider: SSL Provider, error:0 - The certificate chain was issues by an authority that is not trusted.) (Microsoft SQL Server)
I am running SQL Server 2005 Developer ed. Windows XP SP2
Trying to connect over the internet to a SQL Server 2005 Workgroup ed. SP1 on Windows Small Business Server 2003 SP1
I have had success doing this before.
I can terminal sevice in to the box and confirm my credentials work
"Force Enycrption" has not been enabled on either the server or the client
The Certificate tab is clear under "Protocols for MSSQLServer", but the server does have certs I can see them in the "Certificate" dropdown.
Any help would be great.
John
View 11 Replies
View Related
Feb 28, 2008
Hey all,
I have been working for some time on this T-SQL statement and getting it to work the way I would like it to work. I have been reading through books and everything, but am still having trouble. I am pretty new to T-SQL so I am probably making some beginners mistakes.
Basically I am working on a SQL 2005 server and I have a single database with two tables. Below are the tables and the fields that I am using from each. The table name is in bold and the fields are plain.
Users
username (PK, varchar(7), not null)
full_name (varchar(50), not null)
call_history
queue (varchar(6), not null)
update_date (datetime, null)
status (char(1), not null)
Ok, now that you have the gist of the tables and fields. What I am trying to do is the impossible I think. Basically I work in a call center that supports software. The queue field is the identifier of what specialist is handling any call within the table. It can be linked to the "username" on the Users table. One thing I just noticed is that the varchar has a difference. Will that affect my below select statement?
SELECT u.full_name AS 'Specialist Name', COUNT (*)
FROM dbo.call_history c
LEFT OUTER JOIN dbo.users u ON c.queue = u.username
WHERE (c.status = 'P') AND (DATEDIFF(dayofyear, update_date, getdate()) >= 6) AND (DATEDIFF(dayofyear, update_date, getdate()) <= 9)
AND queue IN('alatif', 'AWILLI', 'AYOUNG', 'BPRING', 'CSKINN', 'DALDEN', 'DBACCH', 'DGIZZI', 'DKUSSA', 'DMCCUE',
'EKEPFE', 'GBACKH', 'GJONES', 'HESTAL', 'JBANKS', 'JCRICH', 'JDELGA', 'JFOLCH', 'JGRAVE', 'JHARRI',
'JLI', 'JMYERS', 'JPOPPE', 'JRICHA', 'JRIMME', 'JTHOMP', 'JWELLS', 'KDUKHI', 'KSTANL', 'LCHAMP', 'LGABOR',
'LHARVE', 'LMONTG', 'LSHORT', 'LTOM', 'MBECK', 'MJONES', 'MVANDE', 'NBROWN','NTOMPK', 'PELLIS',
'RATTAR', 'RDODGE', 'TANDRO', 'TBROWN', 'TDAVIS', 'TNDREX', 'TNORRI', 'YSOSA', 'YWILLI')
GROUP BY full_name
ORDER BY full_name
So here is my dilemma:
I am trying to emulate a spreadsheet that was given to me by my boss. This is pretty much going to determine whether or not I can get a job as the Web Developer. So the spread sheet displays the number of calls that have not been updated in X amount of days for all of our reps. I have been trying so many different varieties from nested SELECT statements now to joins. To put it simply enough, do I need to do a query that populates column by column?
Here is what it should look like:
http://i135.photobucket.com/albums/q129/tico1177/CropperCapture1.jpg
As you can see, I have to include everyone even if they have zero. That is where the problem comes in. When I use the above statement, the list shows up but takes away people from the list instead of keeping them and placing a null for the count. I have tried placing a HAVING statement at the bottom of the query to compensate for COUNT(*) = 0, but I get an error. I think because I have a WHERE statement.
I am trying to see if this whole thing can be done with a SQL statement to avoid having loop though in code. I basically want to populate a datagridview with the information that I gather.
Is this possible?
All help is greatly appreciated!
View 26 Replies
View Related
Jan 14, 2008
I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!
View 2 Replies
View Related
Feb 5, 2008
I'm using SQL 2000 and would like to send a generated email using this stored procedure:
select Libraryrequest.LoanRequestID, Titles.Title, requestors.fname+ ' ' + requestors.lname as [Name], libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedatefrom libraryrequestjoin requestors on requestors.requestorid=libraryrequest.requestoridjoin Titles on Titles.Titleid = Libraryrequest.titleidwhere duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101')
I know I need to go to Management, SQL Server Agent, Jobs, New Job. Do I put the stored procedure in the descriptions part? After that I'm lost what do I do.
Thanks!
View 10 Replies
View Related
May 23, 2008
Good Morning,
I need to write a query to provide MaxDate of each Exam and still show the ID of that Exam. My table is
ID Exam Date
1 FMS 1/1/2006
2 FMS 1/1/2007
3 FMS 1/1/2008*
4 ECS 1/1/2006
5 ECS 1/1/2007
6 ECS 1/1/2008* My attempted query isSELECT ID, Exam, Max(Date) AS MaxOfDateFROM Table1
GROUP BY ID, Exam;
My query actual results
ID Exam Date
1 FMS 1/1/2006
2 FMS 1/1/2007
3 FMS 1/1/2008*
4 ECS 1/1/2006
5 ECS 1/1/2007
6 ECS 1/1/2008* My desired results
ID Exam Date
3 ECS 1/1/2007
6 ECS 1/1/2007
I would appreciate any help that could be provided.
Thanks!
View 17 Replies
View Related
Jul 28, 2005
First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...
View 1 Replies
View Related
Jul 24, 2001
-- Joins are not yet my friend.
-- I want all sites even if they are not in the syslog table.
-- select count(gssites.sname) from gssites
-- where gssites.rectype = 'S' = 67
-- this query returns 13 rows, I want all 67
declare @start int, @end int
set @start = 1
set @end = 0
selectgssites.sname'SyncSite'
,syslog.resultcode'RES'
,rtrim(convert(varchar(2),datepart(month,SYSLOG.ON DATE))
+'-'+convert(varchar(2),datepart(day,SYSLOG.ONDATE ))
+'-'+convert(varchar(4),datepart(year,SYSLOG.ONDATE)) )'SyncDate'
from Syslog
left outer join gssites
on gssites.sname = substring(syslog.siteid,9,8)
wheregssites.rectype = 'S'
AND (SYSLOG.RECTYPE='G')
AND (SYSLOG.ONDATE
Between (GetDate()-@start)
And GetDate()-@end)
AND (SYSLOG.SITEID<>'')
AND (SYSLOG.RESULTCODE='SUC')
ORDER BY SyncSite, syslog.ondate
--TIA
jEfFp...
View 2 Replies
View Related
Nov 30, 2005
Hi,
I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:
ClaimNumber LineNumber SequenceNumber
abc123 1 1
abc123 2 2
abc123 3 3
def321 5 1
def321 6 2
ghi456 2 1
jkl789 3 1
jkl789 4 2
So...
SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable
Is there any way to do this?
Thanks,
Dennis
View 4 Replies
View Related
Mar 5, 2007
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!
View 10 Replies
View Related
Mar 19, 2008
Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table
MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))
3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19
I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:
3.02 15
4.02 16
8.02 13
10.02 15
12.02 19
In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.
Please advice.
Thanks in advance.
View 3 Replies
View Related
