Hi, with some help today I was able to get my stored procedure running
and have the results emailed to me. However, this is how its showing
up:
Accounting_Year WK_IN_FYEAR Location
GL_Account
Col Data Difference
--------------- ----------- ------------------------------------
----------
-------------------- -------------------- --------------------
2007 49 Test1
500-001
-2587872.0200 -2587872.0200 .0000
2007 49 Test2
500-001
-3344713.5000 -3344713.5000 .0000
2007 49 Test3
500-001
Is there anyway to line them up side by side properly? When i have two
colms selected the format comes out ok. Thanks for all the help
again!
Here is the sp:
CREATE PROCEDURE [dbo].[spEmailVariance]
(
@SubjectLine as varchar(500),
@EmailRecipient VARCHAR(100)
)
AS
DECLARE @strBody varchar(5000)
set @SubjectLine = 'Weekly Flash Update'
SET @strBody =
use dbWebsiteLO SELECT A.vehicleref,A.manufacturer,A.cvehicle_shortmodtext,A.derivative,min(a.ch) as price,A.capid,B.studioimages,(SELECT term FROM vwAllMatrixWithLombardAndShortModel WHERE vehicleref = a.vehicleref and ch = price) FROM vwAllMatrixWithLombardAndShortModel A LEFT OUTER JOIN dbPubMatrix..tblCarImages B on A.capid = b.capid WHERE a.source <> 'LOM' AND a.type = 'car' GROUP BY a.vehicleref,a.manufacturer,a.cvehicle_shortmodtext,a.derivative,a.capid,b.studioimages
I get Invalid column name 'price'. I'm trying to reference the "min(a.ch) as price"
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
My question: Is it okay to drop all the auto generated column statistics? (for the following scenario)
- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A). - Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records. - We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.
I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".
How can I add entities to my Sqlce- database?
I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.
What do I have to change to make successfully a SaveChanges()?
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above. The error is copied below,
Any help on this greatly appreciated...
ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
Interestingly,even if I use @attachments option by giving a specific file name,then also it is sending the mail in which the contents of the attached file are appended to the mail! Is there anything that I shall configure somewhere to make it behave properly? I am using 7.0 standard edition with no SP.Any help is greatly appreciated.
I'd like to access to a SendMail object with an ActiveXScript. I access it by the Tasks property of the package object. I can retrieve the name or the description but I cannot access to the specific properties of the SendMail object (fileAttachments, ccline, ...) How can I do it ?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Server: Msg 103, Level 15, State 7, Line 6 The identifier that starts with 'xp_sendmail @recipients = 'test@test.com' is too long. Maximum length is 128.
When I am executing the following sendmail code,it is apending the results to the mail.Actually, I want the results to be sent as attachment,in .txt format. Any help is greatly appreciated.
at the end of my ssis package flow, i want to invoke a sendmail task. now, when configuring the sendmail service, i see i can only enter the name of my smtpserver. however, i see the task fails, probably because a user and a password is missing (i have it, but don't see where to type it in the SSIS).
(i want to use the smtp server of my internet provider)
I have a sendmail task following a data flow task. How can I set "To" attribute on the sendmail task before running it ? for example, read a config table and detemrine who the mail should be sent to...
I have setup SQL Mail on a SQL Server 2000 (service pk 3a) with outlook 2002.
SQL Mail give me the success message when I test the profile.
When I send a test message to an operator from SQL Agent it works fine.
When I send a message from the Outlook 2002 client (logged in as the Service-SQL domain account), I can send a message without a problem.
However, when I send an xp_sendmail message (xp_sendmail @recipients='email@address',@message='test',@subje ct='test') it tells me 'Mail Sent' in the result window, but the email never arrives or shows up in the 'sent items' folder in outlook 2002.
For example the result of an Execute T-SQL Statement task.
This task does not have a hook-up for variable assignment, the SendMail can take in a variable. How do you pass the result of EXEC T-SQL statement be assigned to the variable in the SendMail task?
Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?
i've a db running sql server express sp2. the db size now is 1.1 gb i've a table with a varchar column of size 20 . when i try to increase the column size to 50 i get a timeout exception, and the the cloumn size is unchanged. this table has 2.5 million records
i use sql server management studio express to do the changes
is there a way to increase this timeout or whtever i can do to update this column size?
I am using one Publisher with many subscribers for Merge replication of a database. I wanted to change size one column in a table. So I added a dummy column (through Filter column). Copied data, dropped old column (through Filter column), added new column, copied data, and dropped dummy column. On subscriber A, replication is OK. On subscriber B, I get the following message. -------------------- Initializing Connecting to Publisher 'REPLIC' Retrieving publication information Retrieving subscription information A column was added to or dropped from the replicated table. The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber. The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber. A column was added to or dropped from the replicated table. --------------------
I made a new snapshot, reintialized subscription with upload, and tried to sychronize again. But same message.
Question 1. Is there anyway to make replication? Question 2. If not, can I at least upload data changes before dropping and making new publication?
Hi all--I am writing a DTS package looking to back up all databases in sequence from a specific instance of SQL Server. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of SQL tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure of a particular database to back up to file, execute the following SQL task:
SELECT @srvname = @@servername select @db_name = db_name() set @msg = 'Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed. Thanks--SQL Server';
SELECT @srvname, @db_name, @msg
(Many thanks to Whitney Weaver and SQL_Menace for help with sections of this code)
Upon success of #1 and/or #2, go on to the next database, repeating cycle of the SQL tasks above for the next database in the task. For example, this logic goes like:
1. Back up master database; if fails, send email in SQL task; if either task is successful, goto database #2;
2. Back up model database; ...<etc>.
My problem is the current database set in @db_name. I apparently need to reset this variable for each database that gets backed up, and I can't seem to get these set as values to be passed from SQL Task #1 to SQL Task #2. Any suggestions on how I might achieve this?
Hi, I have a sendmail task in my ssis package. In the From field, if i hardcode my from and to address as user@company.com it works fine and mail is sent. But i want to read the from and to values from a database table. SO i defined the package level variables and getting the values into the variables. SO at From if i specify the variable name it gives a compiel time error"the address in th from line is mal formed. it is either missing @ character or not valid". how do i specify teh variable for from address? I feel, may be i can use expression, but i don't know how to do that. Can anyone help me with this?
I need to change the size of a column of a table from char(255) to char(500). I used the line:
alter table table_name alter column column_name char(500)
When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.
What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?
