I am trying to write a query to exclude a set of particular users from our Users Table where they have particular domain names
We want to exclude all users that have a domain of @dnb.com & @test.com.
Can anyone suggest anything?
I'm sort of new at MS SQL Server - I'm trying to gather system table information from multiple servers about backups on our site and insert the rows the query returns into a table on one central server where I'm running the stored procedure from. I have the server names stored into a table but when I attempt to connect to servers, I get an error which tells me that I need to create a linked server by using sp_addlinkedserver stored procedure. People that I work with tell me they want to do this without creating linked servers - Is there a way to accomplish? When I run the query, it only retrieves data from the local server I'm running the stored procedure on, but like I mentioned, when I attempt to connect to other servers, I get the error message that it couldn't find the server name in "syservers" and that I need to run sp_addlinkedserver. This also happens if I run the same query in OSQL from Windows command line.
Any help on this would certainly be appreciated.
Thanks in advance.
Hello, i´m inserting values in a table defined as above,
/********
create table SERVICE_TYPE (
PK_TYPE numeric(8) not null IDENTITY,
TYPE nvarchar(32) not null unique,
DESCRIPTION nvarchar(256) null default(''),
USER_VISIBLE numeric(1)not null,
constraint PK_SERVICE_TYPE primary key (PK_TYPE)
)
go
**********/
these values are inserted throug ODBC with VC++,
in the case of the DESCRIPTION Column, i´m sending null strings CString(""). this value will be filled after.
What happens is that, when i query the database to show the records in this table, the database shows, for example:
PK_TYPE TYPE DESCRIPTION USER_VISIBLE
--------- ------ ------------------- -------------
1 1 (null) 1
i dont want to show the "(null)" value in the column DESCRIPTION, but a null string like ""
can anyone help me , thanks
Cristovão
Hi!
I created linked server to Oracle 8.0.5 on ODBC data source. When I run query like:
select * from linked_server_name..Schema_name.Table_name
it hangs up
I read in Books Online that the syntax is:
linked_server_name.catalog.schema.object_name
What is the _catalog_?
And also, is there a way to run PASS THROUGH QUERY against linked server, but not using OPENQUERY?
can any one sugest Quering AS2005 Cube to generate CSV or Excel in SISS 2005
i have a mdx query that runs fine in SQLserver 2005 but when use OLEDN source in SSIS and connect to Flatfile source i am getting OLEDB exception is there any work arround i have SQL Server 2005 SP2 on Windows 2003
Any help is really appreciated
thanks in advance
I 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
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
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'
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
Thanks!
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication.
The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed.
Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list.
Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances?
Thank you
Hello 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
hii all,can any one help me to get this information.
i am using sql server 2000 and i want to know:
1- all names of servers
2- all database names (catalogs) at specific server and only which the user created .
3- user name and password for each server.
thanks.
How do i get the email to be send automatically from SQL
My SQL Server is configured so if sends an email after running a job. The email is been succesfully sent by SQL, but is not atually sent until you login and start up Microsoft Outlook and client on Send and Receive.
Surely there is a way to get SQL to automatically send the message.
I have got outlook to send message immeditally.
My mail server is SendMail - SCO Open Server 5.06
Thanks
David
Is there a way SQL server to email/send a query results to a user ?
Thanks in advance.
Ivan
Trying to get a job step to send an email with an explaination in certain failure conditions. The problem is I want the recipient to be the job's operator.
don't know of a way to accesss the job's operator email address cleanly from inside a step
Any ideas?
IF I HAVE ONE OR MANY PACKAGES HOW DO SEND AN EMAIL TO MY BOSS IF IT BLOWS UP?? ex. INPUT IS MY TRUNCATE STEP, CONNECTION 1 IS MY ODBC, CONECTION 2 IS WHERE THE RESULT WILL BE PLACED INTO. AT ANY ONE OF THOSE STEPS, It BLOWS HOW TO SEND AN EMAIL. THE EMAIL IS ALREADY SETUP BECAUSE I TESTED IT IN THE QUERY ANALYZER WITH THIS STATEMENT ( EXEC xp_sendmail @recipients = 'robertk;laurac',
@message = 'The master database is full.',
@copy_recipients = 'anned;michaels',
@subject = 'Master Database Status') AND IT WORKS.
I want to write a stored procedure that will email Sales Associates and notify them if a customer has had a return number issed for return of defective product. I wanted to do this daily. I am using SQL Server 2000. Is there a way to get SQL to send an email without using Microsoft Exchange? I have a network that is using Workgroup mode and not domains. I can provide SQL a mailbox on our Linux mail server to send the mail.
View 1 Replies View RelatedHi all me again I gotta question, the Sr executive asst has a number of reports that gets emailed to different department, only problem is those departments are on a totally different server. I am guessing that we need to start SQL AGENT Emailing MAPI to do this for us, of course I could be wrong. This is one of the stored procedures that creates one of the reports
CREATE PROCEDURE createSecurityListtobeEmailed
AS
SELECT SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type INTO SecurityList
FROM SecurityListX
GROUP BY SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type
HAVING (((SecurityListX."Last Name") Is Not Null))
GO
I am trying to setup my sql to send automatic emails to different companies but I am not doing so well. The user puts an email address in the database and then presses a button (command button) that will execute a stored procedure xp_sendmail to a recepient. I need to know how to include read receipts and delivery receipts into the stored procedure, because right now its set up in his outlook as a rule to do this and I want to set it up in sql. The user deals with companies and these companies have several contacts with several email addresses which sql will store in a seperate table.
View 1 Replies View RelatedHow can I implement the functionality of sending mail with the help of SP or DTS?
Subhasish
I have tried a few different stored procedures but i'm looking for away to send email from a server that does not have email enabled. (I want to pass in the smtp server and email and password.
Does anyone know of any script?
Hello everyone
I am working on a little project for work which get the relvant data every 2mins and puts the data into a Data Grid in ASP.net 2.0
Now my SQL scipt runs every 2min and i need to know if at a certain time i could tell the SQL script to run the report and email me the results???
any help would be great
thanks alistair
Quick question ..does DatabaseMail work in sql server express 2008.And if not ... is there a simple work around / solution to get thesame effect .. ie to send email out from sql expressRegardsRob
View 1 Replies View RelatedEXEC master.dbo.xp_sendmail
@recipients=N'myemailaddress@somewhere.com',
@message=N'job failed.' ;
Do you know why the following error is produced?
xp_sendmail: Procedure expects parameter @user, which was not supplied.
Thanks
Hi, I've been asked to create a small application to send and receive emails with basic functionality like reply and forward
messages. All of them will be stored in a sql database.
I am mainly a programmer so I would like to know if you have any sample ERD I can use as a reference
or if you can help me define the entities, I am not sure how I need to handle the "replied messages" and "Forwarded messages"
Any help will be appreciated.
Armando.
I would like to automate a report to be emailed to an email address.
When viewing the report, clicked on new subscription.
In the subscription window what options should there be for Delievered By: ?
I currently have: Report Server File Share in the drop down. Should there be other entries in the list too?
Thanks
hi, can someone tell me how can i get all sql server names in my network. thanks
View 1 Replies View RelatedHello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies View Related