Showing posts with label group. Show all posts
Showing posts with label group. Show all posts

Monday, March 26, 2012

How to use t-sql to email and attachment

I am new to sql and want to write a stored procedure to email a database group an attachment of a report that was created. Can someone please point me in the write direction thanks.WHat do you mean by report ? Is it a report like from Reporting Services or is it just a results set from a query ? DO you want to swnd it via SQL Mail or SMTP ? DO you use SQL 2k or SQL 2k5 ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I want to send an export via smtp using sql 2005. Is there a built-in stored procedure that will allow me to send an smtp message, and attach my export csv.|||You could use the new Database Mail feature. But for your needs it is easier to use a SSIS package since you can export the data from table(s) to file and email it using built-in tasks. You could do the same within SQL Server also but you have to xp_cmdshell to run BCP out and that poses security risks among other things.

Friday, March 23, 2012

How to use tables from two servers under the same group?

Hi
I have two servers under the same group in my enterprise manager, I need to run insert query on a table in one server and select into the insert from the other server.
So what is the syntax to do it ?? hope it's possible...
Thanks,
Inon.First of all, the group in Enterprise Manager doesn't dictate whether this is possible or not, so that isn't a factor, really.
The factor in this case is whether the two servers can "see" eachother, meaning if you can get a result when connected to one of the server executing a query like this

select * from [OtherServername].[DBName].dbo.[tablename] where 1=2
alt.
select * from [InstanceName\OtherServername].[DBName].dbo.[tablename] where 1=2

(Yes, even if you replace the placeholders, I wrote this query on purpose to return 0 rows, all I'm interessted in for the moment is wheter you get a result or not.)

If one of the two above queries work, you've can write as I did, and if it doesn't, you need to setup a link between the two servers as described in the SQL Server books online help on the command sp_addlinkedserver.|||I added a linked server, I can almost connect to it... the only thing left is to specify the login information since the defualt 'sa' can't login on the linked server.

So, I guess it's in the provider string (@.provstr), can you remind me what is the syntax to specify the login information ? And correct me if it's not supposed to be in the provider string.

Thanks,

Inon.|||Ummmm ... don't know exactly what you are trying to do here, but it sounds like you are trying to login to a server you aren't supposed to be able to login to. If you don't know the sa password you can use another user/password that has access to the tables you need, but I would strongly suggest you get hold of the sa password and use it in the link between the two servers, that way you won't have any access limitations at all, if that is possible ...|||Ummmm ... don't know exactly what you are trying to do here, but it sounds like you are trying to login to a server you aren't supposed to be able to login to. If you don't know the sa password you can use another user/password that has access to the tables you need, but I would strongly suggest you get hold of the sa password and use it in the link between the two servers, that way you won't have any access limitations at all, if that is possible ...

No no no... I know the sa password, I asked how/where do I specify it ?
You see, I added a linked server without specifing the login information, now, when I try to connect I get message "Login failed for sa..." this is because I did not specify the password anywhere, my question is, where do I specify it ??

Inon.|||If you're using the Enterprise Manager you can specify the password in the tab under Security/Linked servers (Properties/Security).

There you should see a text saying something like :

"For a login not defined in the list above, connections will be made with the following context :" followed by 4 options. Tick the last one, fill in remote login (sa) and the password for sa in the next field.

I'm sure there is a way to specify it in a command, but I just aint got the time to check it in the help for the moment.

Friday, March 9, 2012

how to use group by (group tasks based on projects)

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

Project1-->task1

task2

task3

task4

Project2-->task4

task5

task6

.............................................projectN.....................

how to write query for this

i have 2 tables:

Project .......>columns are projectid

Task->columns are projectid, taskid

|

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

In this case you would not have to use a group by.

Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||its giving taskid 's null|||

Consider this example

Project

1

2

3

Task

1,A

1,B

2,C

I you want all tasks and their projects, you could write

SELECT ProjectID, TaskID

FROM Task

GROUP BY ProjectID, TaskID

ORDER BY ProjectID, TaskID

This would return

1,A

1,B

2,C

but would provide nothing about Project 3 because it has no tasks.

Given the structure of the table (no repeated tasks), you might even get away with

SELECT ProjectID, TaskID

FROM Task

ORDER BY ProjectID, TaskID

However, if you wanted all projects and to include their tasks, if available, then Jens is correct

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

This would return

1, A

1, B

2, C

3, NULL

The NULL on the last row exists because Project 3 is shown (from the Project table) but there is not equivalent task in the Task table.

The word LEFT from LEFT JOIN tells SQL to return all rows from the Project table and, if available, the row data from Tasks too. If there is no row in Task for a Project in Project, then NULL is placed in the task specific columns.

By dropping the word LEFT, it would limit the result to only those projects that had tasks (but the earlier examples are simpler).

If you want to replace NULL with something more useful, using ISNULL, you could write

SELECT p.projectid, ISNULL(t.Taskid, 'No associated tasks')
FROM Project p
LEFT JOIN Task t ON p.projectid = t.taskid
ORDER BY p.projectid, t.taskid

This would now return

1, A

1, B

2, C

3, No associated tasks

regards,

Niall.

Sunday, February 19, 2012

How to use 2 fields for same Group

Hi,
I am trying to impelement the report which will group either by
UserType or CompanyType depending on whichever field is returned by
Stored Proc. At any given point SP will fill only one of above field
while other will be blank. I need to display the name of the filled
group field in report.
Also would like to know how to use multiple expression in Group
property for a row/column group.
Thanks,
MaheshRather than return two fields use an isnull() in the SP and return one field.
"Mahesh" wrote:
> Hi,
> I am trying to impelement the report which will group either by
> UserType or CompanyType depending on whichever field is returned by
> Stored Proc. At any given point SP will fill only one of above field
> while other will be blank. I need to display the name of the filled
> group field in report.
> Also would like to know how to use multiple expression in Group
> property for a row/column group.
> Thanks,
> Mahesh
>|||You have to write custom code with a select case/switch statement.
Custom Code you get to by right clicking the gray area outside the
report page and choosing Propeties ... look for Code tab.
Then do function:
Shared Function GetGroupBy(byval incomingParam as string) as string
Select Case incomingParam
Case "abc"
Return "Column1"
Case "def"
Return "Column2"
..... etc
Then in the designer do = Code.GetGroupBy(') the question might be
filled in by a drop-down box of parameters that you defined.
Does that help?|||Thanks Chad,
Could you please eloborate the your solution. Here I am not using
dropdown box but is using data feild returned by dataset.
Actually I am trying to use a below expression in the group field.
=iif(
Fields!CompanyName.IsMissing,Fields!UserTypeNamee.Value,Fields!CompanyName.Value)
My SQL Stored proc will return ether CompanyName or UserTypeName
Please suggest the solution.
Many Thanks,
Mahesh