Hello
guys and girls, I am using XML for the first time to send the information read in my tables. I want the XML to take the information and send it in an email. With that I want it to be configurable so if I want to change that email address I can.
Anybody help pls
Hi All,
I am creating a script to open an Excel file and copy the data into a couple of SQL Server2000 tables.
I am using OPENROWSET to open the file but I need to make it configurable so that I can change the name of the Excel file and name of the sheet.
Below is my code that works fine:
I would like to make the file name: CORAL 49pt.xls configurable as well as the path to the file.
In addition, I would like to make the sheet name configurable: 100nm$
I tried using QUOTENAME but it gives me an error.
Thanks!
Gloria
DECLARE SiteThickness_Cursor CURSOR FOR
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:Program FilesICE300 V2.2.2ThicknessFilesCORAL 49pt.xls',
'Select Site, [X Pos], [Y Pos], Thickness from [100nm$] Order By Site ASC')
OPEN SiteThickness_Cursor
FETCH NEXT FROM SiteThickness_Cursor
INTO @SiteNumber, @XCoordinate, @YCoordinate, @Thickness
WHILE @@FETCH_STATUS = 0
BEGIN
--Round the X and Y coordinates to 4 decimal places
--
--Add values into the tblSiteThickness table
INSERT INTO tblSiteThickness (idWafer, iSiteNumber, dXCoord, dYCoord, dThickness)
VALUES (@WaferID, @SiteNumber, ROUND(@XCoordinate, @DECIMAL_PLACES), ROUND(@YCoordinate, @DECIMAL_PLACES), @Thickness/@ANG_TO_MICRONS)
FETCH NEXT FROM SiteThickness_Cursor
INTO @SiteNumber, @XCoordinate, @YCoordinate, @Thickness
END
CLOSE SiteThickness_Cursor
DEALLOCATE SiteThickness_Cursor
Hello,
I'm tryng to set up a CLR Trigger, when executing i get this error:
Msg 6522, Niveau 16, État 1, Procédure Trigger_EnvoiEmail, Ligne 1
Une erreur .NET Framework s'est produite au cours de l'exécution de la routine ou de la fonction d'agrégation définie par l'utilisateur 'Trigger_EnvoiEmail' :
System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Mail.SmtpClient.Initialize()
at System.Net.Mail.SmtpClient..ctor(String host)
at AffYEmailQueue.MailHelper.SendMailMessage(String from, String destinataire, String bcc, String cc, String subject, String Htmlbody, String Textbody, Int32 idAFFY)
at AffYEmailQueue.Triggers.EnvoiEmail()
Can anybody help me ? thanks
Kind of a newby sql question, but here goes:I have a sql 2005 database that I have a job that runs Sunday morning at 12:30 am. I set it up using SQL Svr Mgt Studio 2005. Under the Management directory I set up Database Mail to work with my local SMTP server. I can send a test email just fine.I then set myself up as an operator with my email address. (Under operators directory) I then went back to the properties of the job I set up, and under 'notification', chose e-mail operator (me) when Job Succeeds. The job runs, itt suceeds, but NO email!It flat out won't work. there are NO entries in teh( email) log for errors either. Anyone? TIA Dan OR is it better to script these jobs using xml? I don't have time to learn a new thing right now, just need it to work!
View 1 Replies View Related
Is it possible to configure the Sql Server log provider so the log server can be changed via a configuration?
I want to do this without redeploying packages and without errors.
I have a problem when I try this: A package was developed using Sql Server log provider on ServerA. The log provider connection was overridden by a Sql Sever configuration. The package was deployed into an environment where ServerA is not available, and the log connection overriden with ServerB (via a Sql Server Configuration). This results in a connection error saying SSIS failed to connect to ServerA. If errors are ignored, then the package continues and produces logs on ServerB as required. We are running SP2.
-------------------------------------------
I ran more tests from the command line and found the problem only exists within Visual Studio. When run from the command line the package configurations kick in before logging starts. So that will work fine in production where we will be running from the command line. I will leave the post here as I have needed to spend some time on this issue and I am sure others have too.
It would be good if someone could provide full documentation about the order of events as a package starts - both within Visual Studio and from the command line.
To the pros:,I have one question about designing tables and interface (web/windowsapplications). The requirement is like this.I want to design an application to manage upload / review / managefiles which has some meta data associated with them as well. The filescan be any documents. This application can be used in differentcompanies same way, but each company wants to use different fieldnames.So, I want to design tables generic enough to allow custom labellingfields. So if I have a tableT1 with ID, FileName, FileSize, FileAuthor.... some company may wantto use different field names. What's the best way to design tableslike this and how to maintain relationships, stored procs, triggersetc linked to these tables.Many thanks in advance,DS
View 1 Replies View RelatedI have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies View RelatedUnder IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.
Any idea?
thanks
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
need to do ilke this
Code Snippet
@list_email = (SELECT emall FROM tblLogin)
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
@recipients = @list_email
Code Snippet
IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )
BEGIN
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
Date_born AS 'td','' ,
Age AS 'td','' ,
BirthdayToday AS 'td','' ,
BirthdayThisWeek AS 'td'
FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>
<tr>
<td>name</td>
<td>fname</td>
<td>date</td>
<td>age</td>
<td>aaaaaaaaa</td>
<td>bbbbbbbbbbbbbbb</td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'rrr@iec.co.il',
@copy_recipients='rrrrr@iec.co.il',
@body = @body,
@body_format ='HTML',
@subject ='ggggggggggggggggggggg',
@profile_name ='ilan'
END
ELSE
print 'no email today'
We have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine
1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes
1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?
Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
View 78 Replies View RelatedOn Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
Hi,
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Thank you
Hello,Is it necessary to upgrade the Client Connectivity Tools on all clientmachines after the SQL Server database server is upgraded from Version7.0 to 2000?Thank you in advance!Eddy
View 1 Replies View RelatedHello ALL
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
Please help me with the best way to do this
Thanks in advance
Hi,
While using Sql Native Client ,VB Application giving error
"Transcation Cannot Start because more than one ODBC connection is in use"
But when useing SqlServer client then work perfectly ok.
Please guide where to use Sql Native Client.
Thanks
Rizwan
I have tried this on several different operating systems (VISTA and XP ) and several versions of SQL NATIVE CLIENT including 2005.90.3042.0
I have a 64 bit "SQL NATIVE CLIENT" connection that fails. The exact same connection and code succeeds on 32 bit.
My customer and I prepend "oledb:" to the connection string and it starts working.
FAILS on 64 bit:
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Wrigley_Test;Data Source=MONGO;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RIPTIDE;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
WORKS
oledb:Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Wrigley_Test;Data Source=MONGO;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RIPTIDE;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False
I debugged my code to the point that I know it is happening when I call CreateAccessor for my SQL statement.
m_hr=m_pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, GetCols(), (m_pDBBinds+IsBookmarked()), 0, &m_hAccessor, NULL);
Error:
Microsoft SQL Native Client: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Does anyone have any suggestions?
Can somebody help me convert this SQL2000 Function TO C# Function pleaseCREATE Function dbo.CalculateNextRentDate ( @Rent_Payment_Date datetime, @Frequency varchar(50) , @Number int)RETURNS DATETIMEASbeginDECLARE @NextPaymentDate DatetimeSET @Number = @Number - 1.IF @Frequency = 'month' IF @rent_payment_date = dateadd(month, datediff(month, 0+@Number, @rent_payment_date) + 1, -1) BEGIN SET @NextPaymentDate = dateadd(month, datediff(month, 0, @rent_payment_date) + 2, -1) END ELSE BEGIN SET @NextPaymentDate = dateadd(month, 1+@Number, @rent_payment_date) END return @NextPaymentDateend
View 1 Replies View RelatedOur company has its Departments And Services.
Now We are making it online.
Both have separate email list, phone numbers, and more.
Will I make one table and adds the field Type (Values: D or S).
Or make them separate.
Remember one thing If we merge them then Email And PhoneNumber Table will also me merge
other wise they will also separate.
What is better.
How to make our own DTS package I have to split the data on my own.. Data is very biig almost 30 to 40 million. I need to splitt them into 10k chunks in database and with my desired table name.
Waiting for a +tive reply.
Regards
Shani ;)
hi i have a tabel in my database i tray to generat report for this tabel
this tabel have all this fields:
company_id
emp_no
seq_no
interval_date
in_time
in_type
out_time
out_type
wage_code
status
i want all his colums can be search
but with company_id
like if he enter company_id and emp_no okay give him result if he enter Company_id and interval_date okay give him result
i write this
Code Snippet
SELECT company_id, emp_no, seq_no, interval_date, in_time, in_type, out_time, out_type, wage_code, status
FROM interval
WHERE (company_id LIKE @CompanyID) AND (emp_no LIKE @EmployeeID) OR
(company_id = @CompanyID) AND (interval_date = @IntervalDate) OR
(company_id = @CompanyID) AND (in_time = @InTime) OR
(company_id = @CompanyID) AND (in_type = @InType) OR
(company_id = @CompanyID) AND (out_time = @OutTime) OR
(company_id = @CompanyID) AND (out_type = @OutType) OR
(company_id = @CompanyID) AND (wage_code = @WageCode) OR
(company_id = @CompanyID) AND (status = @Status)
but in report preview it tell me i must enter intrevalDate ?
Hi have have this problem, I have a table called PABX that has all the callings registry and what I need to do is for each client(PABX.cod_client) I have 2 types of calls (VC1, VC2) , and for these types I need to select all the registries chaging the dialed number(PABX.NRTELEFONE) for the new one (TROCAR.NRTELEFONE) and for those client that doesn't need to change select the PABX.NRTELEFONE
is it possible through SQL Server 2000(via stored procedure) or I'll need to do it by my application using a vector ?
Thanks
hiiiiiiiiii I am creating a web application using vb.net in which i m using the concept of classes. now i am done all the code for inserting the values in the database using the class but it is difficult to fetch the values from the database using select command and sending them to a WebForm . i want to know how i send send the values coming from the select command to a datagrid or another web controlif possible provide me a sample code thanks for your help
View 2 Replies View Relatedhello all i have a 2 questions hope that u can help me my first question is: in ms access there was a data type named yes/no and it was a checkbox is there a checkbox data type in sql server 2005?
my second question is i need the connection code between asp.net 2005 and sql server 2005 i searched in here for that code but i got more confuse i found two codes and both are not working so hope u can give me the right connecting code. that's all thanks
hi all,
Now i want to log some information (e.g.time,count...)during SP execution,how can i do it in Sql?
Thanks
Can anyone tell me how I would go about making my SQL server accessable from the Internet, or know of any good tutorials to get me started, I haven't had much luck looking on google.
I need to access an SQL database from one server on another server for a web application.
Thanks
Hi guys, I have a car_race table which has these fields
car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 2
b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
car_id 1
race_id 4
b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
Hello!
I have two tables
users and pictures.
table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid
when I do this query:
"select userid from pictures where userid=123"
then It will do a clustered index seek
But If I do any of those:
"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"
It will do a clustered index scan.
How can I force it to seek my index instead of scan?
Thanks!
i want to make a database for pharmacy using sql server and VB.NET
so any body can give me illuminations
thanks in advance
george albert
I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?SELECT DISTINCT u.userId,u.lastName,u.firstName,u.address1,u.address2,u.city,u.state,u.zip,CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0 THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))END CPGkeyFROM [...] JOIN [...]I'm hoping there is something in a way of....If userClass Between 1 and 3 ThenIF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))End IF as CPGKey,Thanks in advace.
View 1 Replies View RelatedI need to restore a copy of database to a new db name
i'm trying by creating a new db and restoring by doing
RESTORE DATABASE [userr] FROM DISK = N'D:sqlbackupsuser.bak' WITH FILE = 4, NOUNLOAD, STATS = 10
GO
i'm getting an error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'userr' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What's the best way to do this?
I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.
Here's the statement:
Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,
[Code]....
If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname