Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?
I am using the procedure below :-
CREATE PROCEDURE mailtest AS
declare @sql varchar (255)
SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON
In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C: and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.
We have a tree structure containing section names. Each node is a section name and each section can have subsections. I have to copy the tree structure but need to maintain the parent-child relationship established within the id / parent_id fields. How do i acheive this?For example i have the treeSection 1 |-Section 1.1Section 2 |-Section 2.1The "Section" table contains 3 fields: id, parent_id, and caption. ID is the identity of the section record and parent_id contains NULL or the ID of this record's parent to create a child. So "Section 1" (id=1, parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1" (id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).I would like to copy this sucture to create 4 new sections but they need to maintain their id/parent_id relationships BUT with new IDs. For this i created the following stored procedure:----------------CREATE PROCEDURE [dbo].[CopySection] AS -- Declare a temporary variable table for storing the sections DECLARE @tblSection TABLE ( id int, parent_id int, caption varchar(max), ) DECLARE @newAgendaID int, @newSectionID int; DECLARE @tid int, @tparent_id int, @tcaption varchar(max);BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Copy the desired sections into the local temp variable table INSERT INTO @tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id; -- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID DECLARE c1 CURSOR FOR SELECT * FROM @tblSection ORDER BY parent_id FOR UPDATE OF parent_id; OPEN c1; FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption; WHILE @@FETCH_STATUS = 0 BEGIN -- Insert the new Section and record the identity INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@tparent_id, @tcaption); SET @newSectionID = SCOPE_IDENTITY(); -- Update the temp variable table with the new identity from the newly created real section in tblSection -- Update all temp variable records to point to the new parent_id UPDATE @tblSection SET parent_id = @newSectionID WHERE parent_id = @tid; FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption; END CLOSE c1 DEALLOCATE c1 END----------------The critical "UPDATE @tblSection" part doesnt seem to update the temp variable table with the @newSectionID (the actual section identity obtained after inserting a real record into the tblSection table). So in the end the inserted records into tblSection still point to the incorrect parent_id instead of the copied record's parent_id.Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset? I've tried using both table variables and temp tables but no luck.
I have two different SQL server installations (7 and 2000) that I have configured for using the SP_processmail procedure. Both servers can properly execute xp_startmail, xp_stopmail, xp_readmail, and xp_findnextmsg. HOWEVER, both have different problems with SP_processmail.
Using this command (and a host of many other variations): sp_processmail @filetype = 'CSV', @separator = ',', @dbuse = 'ryantest', @set_user = 'guest'
SQL 7.0 says: Server: Msg 18007, Level 16, State 1, Line 0 Supplied datatype for @set_user is not allowed, expecting 'varchar'
I have tried every variation possible, including not specifying the yser which should result in it defaulting to the "guest" account. The message is always the same.
SQL 2000 says: Server: Msg 18025, Level 16, State 1, Line 0 xp_sendmail: failed with mail error 0x80004005 Queries processed: 0.
I have checked all my configurations and read every KB article - I can't find anything wrong.
Can someone PLEASE respond if you have any experience with SP_PROCESSMAIL on your servers.
The business rule is, the sales manager is commissioned on the avg. numberof appointments set up per salesrep per day during the month.I have 2 tables: The UserLog table records only 1 entry per day per user(salesrep). This will log how many salesreps worked a particular day. Thesecond table logs any appointments set up.UserLog: ID, UserName, EnteredTimeAppointment: ApptID, EnteredTime, ApptDateI figured that, for a given date ranged, I could1. sum the number of appointments2. sum the number of days worked2. sum the salesreps / number of days = avg number of salesreps per day3. number of appointments / avg number of salesreps per day = avg numberof appointments per sales repBut this logic is flawed. If I average out every day and then take anaverage of this daily average, I get a different result. Any ideas on howto best solve this problem?Thanks.
Hi Guys, I have a syntax error in my SQL SELECT Query that is driving me crazy. All I am doing is adding a conditional with a literal value and that is breaking the query.This Query Works: SELECT Quantity, CAST(Gallons AS VARCHAR(10)) + ' ' + Style, Colour FROM ORDER_TANK_LINK LEFT JOIN TANKS ON TANKS.TankID = ORDER_TANK_LINK.TankID LEFT JOIN COLOURS ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID This One is Broken:SELECT Quantity, CAST(Gallons AS VARCHAR(10)) + ' ' + Style, Colour FROM ORDER_TANK_LINK ON ORDER_TANK_LINK.OrderID = 1 LEFT JOIN TANKS ON TANKS.TankID = ORDER_TANK_LINK.TankID LEFT JOIN COLOURS ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID Obviously I haven't explained the design of the tables, but because of the fact that I have another query that is so similar working perfectly, I didn't think you'd need it. The exact error is "Incorrect Syntax Near the Keyword 'ON'". OrderID is definately a value of the ORDER_TANK_LINK Table and is of type int. Thanks in advance!
I'm trying to do a basic update query which is working on other pages but not on this page. Dim uid As Integer = CInt(Session("uid")) Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn) With cmd.Parameters cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text) cmd.Parameters.AddWithValue("@uid", uid) End With cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.So it doesn't seem to read my updated textfield or something im clueless.Kind regards, Mark
I've got 2 identical DB's on the same server, one production, one test. No we don't have a test env, but at least I'm not testing on a prod DB (some people here do, trust me).
I've got a prod VB6 app that used SQL sp's. I've pointed the ADO connection string to the test DB while I make the changes I need to make, and I'll obviously change it back before I'm done etc.
I made some VERY minor changes to one of the sp's (added a variable, changed some stuff), tested thoroughly in query analyzer (runs with no errors in <1 sec), altered my ADO command accordingly and when I executed I get this:
run time error '-2147217871 (80040e31): [Microsoft][ODBC SQL Server Driver] timeout expired
The connection to the server is fine, I've tested that - it trips on the execution of the command:
Set rstCalls = conHelpArchiveConnection.Execute(SQLQ)
so here's my question: The prod version of this app works like a charm, and the test app times out. I'd rather not toy with the connection timeout setting on the server. Any thoughts on what could be causing this?
Any help would be appreciated, I'm ready to throw in the towel. Well at least until tomorrow morning ;)
Hi -I hope some one can help me with this.I am using sql server 2000[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 200316:08:15 Copyright (c) 1988-2003 Microsoft Corporation EnterpriseEdition on Windows NT 5.0 (Build 2195: Service Pack 4)]I have a stored procedure that is using cursors and a few joins, andwrites to a few tables. (I can post the code if that will help) Thestored procedure takes approximately 27 seconds to complete whenexecuted inside query analyser. However, if I run the stored proceduresource directly inside query analyser (like a long sql script), ittakes only 3 seconds!! These results are consistent and reproducible.I would think a stored procedure stores the plan, and I would expectbetter optimization. Why am I witnessing the opposite behaviour? Anyone has any experience?The server is manned by DBAs (I work at a large corporation), so Ibelieve it is well configured. We have noticed similar behaviour ondata restores on a different physical server.Thanks in advance,-praty77
I'm currently evaluating Visual Studio Team System using SQL Server 2005 (9.00.3042.00, SP2, Enterprise Evaluation Edition) and from time to time, I get this error:
Error: 18456, Severity: 14, State: 16.
If I try to connect to the database using SQL Server Management Studio, I get the error:
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)".
If I restart the SQL Server services, everything works fine! This is driving me nuts!
Additional information: - The user accounts and security are OK - they have been double checked, and once the services are restarted everything works fine. - SQL Server is configured to accept remote connections - again, once the services are restarted eveything works fine. - I have disabled meanwhile the following protocols: VIA, Named Pipes, Shared Memory both at the Server and Client - and the problem still remains.
Our application drives SSIS packages from ASP.NET web services. To alleviate some of the package load time overhead application caches SSIS Application object and several instances of "pre-loaded" packages in ASP.NET Application context. As needed the code uses cached SSIS Application instance to execute "pre-loaded" packages. Is this thread-safe?
Is it just me, or are features missing in Management Studio? I mean, where is the "Export Objects" option when exporting data? Why is it so difficult to register a remote server? Why do the object views suck? And most important, why is the documentation the worst software documentation I have EVER SEEN?! Things that were easy in Enterprise Manager with Client Network Utility and Query Analyzer seem impossible, difficult, or maybe just hidden in Management Studio. If it is there, the documentation sure doesnt tell you how to find it. Please someone tell me that it's just me. Tell me that the features are there but Im not doing something right. I feel like putting my head in between my door opening and banging the door against it.
Assuming I should be using values from temp inserted to insure correct record... Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form AFTER INSERT
AS SET NOCOUNT ON -- Checkbox Driven: IF a_form.missingCheckbox = -1 THEN Insert into b_items (form_ID, parent_ID, ItemTitle) Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven: IF a_form.incorrectTxtbox <> 'na' THEN Insert into b_items (form_ID, parent_ID, ItemTitle) Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on Create Table a_form ( form_ID int Not Null, parent_ID int, missingCheckbox bit, missingNote varchar(100), incorrectTxtbox varchar(50), replaceWithTxtbox varchar(50) )
--Target table Trigger inserts into Create Table b_items ( items_ID int Not Null, form_ID int Not Null, parent_ID int, ItemTitle varchar(150) )
I am trying to use xp_sendmail with no luck. My SQL mail works fine and I am logged in as sa. Here is the csript that I am using. The script works on my local box(which is running Win 2K Advanced Server and SQL 7). It will not work on my production boxes. Any suggestions?
I am using xp_sendmail in a stored procedure.I need to update the fields after the mail sent. Is there a way to capture the errors or server messages that occurs. I am using the following code.....
If @send_Mail=0 Begin update MasterleadPool/*Updates the MasterleadPool table once the mail sent */ Set EmailSent='Y', Dateout=getdate() where AssToID=@AssToID and EmailSent='N' and AssToFlag='Y' End
I used @@error to capture the error but it does'nt work. I got server message as follows.... So the problem is it should'nt go to update statement if there is any type of errors in sending a mail..
Server: Msg 17903, Level 18, State 1, Line 0 MAPI login failure.
However in doing this, it never works (go figure or I wouldn't be submitting this). I have defined the variable @EMAIL_ADDRESS_LNK appropriately to reference a field in a table and I'm positive that the value in the field is valid.
What am I missing? If this is not possible, is there a workaround. Thanks in advance for any assistance you can provide.
How robust is xp_sendmail? I would like to roll thru 10 - 20 thousand records, strip the email and send a newsletter...
I'm a definite newby when it comes to mail servers (and how they interact with SQL Server 7.0)...so I'm not sure whether this type of processing would crash/stall the server.
can xp_sendmail handle this type of processing?
I appreciate your help,
p.s. can u point me to any good articles on the subject?
I have a query that works on its own, however when I put it into xp_sendmail it fails. It appears that the "set" command does not work. Here is the query. Any suggestions? Can you use variables within xp_sendmail @ query section?
EXEC master.dbo.xp_sendmail @recipients = 'Richard Peoples', @subject = 'The following Budget Checked items need attention.', @query ='DECLARE @A1 CHAR (20) DECLARE @A2 CHAR (2) SET @A1 = 'Yvette Palomo' SET @A2 = 'N' IF (select COUNT (*) from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK where RETURN_TO_ANALYST = @A1 AND BUDGET_CHECK_CLEAR= @A2) > 0 begin select A.JOURNAL_ID, A.PROJECT_ID, A.ACCOUNT, B.XLATLONGNAME from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK A INNER JOIN FSPROD75.dbo.XLATTABLE B ON A.BCM_LINE_STATUS = B.FIELDVALUE where (((A.RETURN_TO_ANALYST)= @A1) AND ((A.BUDGET_CHECK_CLEAR)=@A2)) AND (B.FIELDNAME = "BCM_LINE_STATUS") end'
I'm using XP_Sendmail on an NT Server and it works fine. We have a new Windows 2000 server, which the SQL Mail has been set up correctly, and the test passes.
When I use execute xp_sendmail in the query analyzer on the 2000 server, it just sits there and processes for over an hour, without any error messages.
select @MB_Free = MB_Free from #FreeSpace where Drive = 'J'
if @MB_Free < 550 exec master.dbo.xp_sendmail @recipients = 'test@yahoo.com', @message = 'Running low on free space'
I can run it as a job step. However, the problem is that we don't have Microsoft Outlook to run xp_sendmail proc to get notify when the free space on the drive is low. I can run mailsend which is os command and add that as a job step. But I don't know how to combine the code above and mailsend, since one is the t-sql and the other one is command script. Any help is appreciated.
I have recently added columns to a table that is part of a xp_send mail script. meaning i run a query off of the table that had the columns added. I am now getting the error: failed with operating system error 32
I have no idea what this means. This is a copy of the script. exec xp_sendmail 'dionne, jim;eddens, david;Wiggs, Alexander;Miller, Debbie;conmdi', '(scrbbususcnc01) Failure 322 Load', @Attachments = '322Error.txt;\scrbbususcnc01archive322msg322M SG.txt;', @query = 'Use [maersk data warehouse] create table #Duplicate_Records (Equipment_Number varchar(10) ,Activity_Date varchar(6) ,Activity_Time varchar(4) ,Sighting_Code varchar(2) ,CountOfEquipment_Number int) insert into #Duplicate_Records SELECT STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code, Count(STG_INTERMODAL_322MSG_TBL.Equipment_Number) AS CountOfEquipment_Number FROM STG_INTERMODAL_322MSG_TBL GROUP BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code HAVING (((Count(STG_INTERMODAL_322MSG_TBL.Equipment_Numbe r))>1))
insert into #None SELECT DISTINCT STG_INTERMODAL_322MSG_TBL.* FROM [#Duplicate_Records] INNER JOIN STG_INTERMODAL_322MSG_TBL ON ([#Duplicate_Records].Sighting_Code = STG_INTERMODAL_322MSG_TBL.Sighting_Code) AND ([#Duplicate_Records].Activity_Time = STG_INTERMODAL_322MSG_TBL.Activity_Time) AND ([#Duplicate_Records].Activity_Date = STG_INTERMODAL_322MSG_TBL.Activity_Date) AND ([#Duplicate_Records].Equipment_Number = STG_INTERMODAL_322MSG_TBL.Equipment_Number) ORDER BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code;
create table #Real (Equipment_Number varchar(10) ,Activity_Date varchar(6) ,Activity_Time varchar(4) ,Sighting_Code varchar(2) ,CountOfEquipment_Number int) insert into #Real SELECT [#None].Equipment_Number, [#None].Activity_Date, [#None].Activity_Time, [#None].Sighting_Code, Count([#None].Equipment_Number) AS CountOfEquipment_Number FROM [#None] GROUP BY [#None].Equipment_Number, [#None].Activity_Date, [#None].Activity_Time, [#None].Sighting_Code HAVING (((Count([#None].Equipment_Number))>1));
SELECT STG_INTERMODAL_322MSG_TBL.* FROM [#Real] INNER JOIN STG_INTERMODAL_322MSG_TBL ON ([#Real].Equipment_Number = STG_INTERMODAL_322MSG_TBL.Equipment_Number) AND ([#Real].Activity_Date = STG_INTERMODAL_322MSG_TBL.Activity_Date) AND ([#Real].Activity_Time = STG_INTERMODAL_322MSG_TBL.Activity_Time) AND ([#Real].Sighting_Code = STG_INTERMODAL_322MSG_TBL.Sighting_Code) ORDER BY STG_INTERMODAL_322MSG_TBL.Equipment_Number, STG_INTERMODAL_322MSG_TBL.Activity_Date, STG_INTERMODAL_322MSG_TBL.Activity_Time, STG_INTERMODAL_322MSG_TBL.Sighting_Code;
drop table #Duplicate_Records drop table #None drop table #Real', @subject = '(scrbbususcnc01) Failure 322 Load' , @Attach_Results = true, @width = 3000, @Separator = '|'
it gives error saying that @table_var must be declared. even if i use temporary table, the message i get is "cannot reference object in tempdb database."
I want to send a reslut as a xls file. so I did it throug BCP. The same time i need to send file in mail. Here i am stucked. Could any one please advise me to solve this problem?
