Sveta82,
можно долго спорить, по поводу выбранной концепции в представленном вами коде...
если результат выполнения в Менеджмент Студио вас устраивает, ни чего менять не надо.
если на вашем сервере, в БД [BD-1]
не созданы объекты, выполните один раз ваш код:
EXEC dbo.sp_addlinkedserver @server = N'Server2',
@srvproduct=N'OLE DB',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N'C:\Users\Света\Desktop\Задание \BD-2.xls',
@provstr=N'Excel 8.0'
EXEC dbo.sp_addlinkedserver @server = N'Server3',
@srvproduct=N'OLE DB',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N'C:\Users\Света\Desktop\Задание \BD-3.xls',
@provstr=N'Excel 8.0'
EXEC dbo.sp_addlinkedserver @server = N'Server4',
@srvproduct=N'OLE DB',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N'C:\Users\Света\Desktop\Задание \BD-4.xls',
@provstr=N'Excel 8.0'
SELECT * FROM sys.servers
SELECT * FROM sys.linked_logins
GO
USE [BD-1]
GO
CREATE VIEW Serv2 AS
SELECT DISTINCT S2.*
FROM [Server2]...['2016-01-02$'] S2 JOIN [BD-1].dbo.Bill D1 ON S2.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S2.CellID = C1.CellID
UNION
SELECT DISTINCT S2.*
FROM [Server2]...['2016-01-15$'] S2 JOIN [BD-1].dbo.Bill D1 ON S2.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S2.CellID = C1.CellID
UNION
SELECT DISTINCT S2.*
FROM [Server2]...['2016-01-23$'] S2 JOIN [BD-1].dbo.Bill D1 ON S2.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S2.CellID = C1.CellID
UNION
SELECT DISTINCT S2.*
FROM [Server2]...['2016-02-04$'] S2 JOIN [BD-1].dbo.Bill D1 ON S2.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S2.CellID = C1.CellID
UNION
SELECT DISTINCT S2.[Date],S2.[Duration],S2.[Type],S2.[Phone A],S2.[Phone B],S2.[LAC],S2.[CellID]
FROM [Server2]...['2016-01-18$'] S2 JOIN [BD-1].dbo.Bill D1 ON S2.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S2.CellID = C1.CellID
GO
CREATE VIEW Serv3 AS
SELECT DISTINCT S3.*
FROM [Server3]...['2016-01-02$'] S3 JOIN [BD-1].dbo.Bill D1 ON S3.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S3.CellID = C1.CellID
UNION
SELECT DISTINCT S3.*
FROM [Server3]...['2016-01-09$'] S3 JOIN [BD-1].dbo.Bill D1 ON S3.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S3.CellID = C1.CellID
UNION
SELECT DISTINCT S3.*
FROM [Server3]...['2016-01-28$'] S3 JOIN [BD-1].dbo.Bill D1 ON S3.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S3.CellID = C1.CellID
UNION
SELECT DISTINCT S3.*
FROM [Server3]...['2016-02-03$'] S3 JOIN [BD-1].dbo.Bill D1 ON S3.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S3.CellID = C1.CellID
UNION
SELECT DISTINCT S3.*
FROM [Server3]...['2016-02-08$'] S3 JOIN [BD-1].dbo.Bill D1 ON S3.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S3.CellID = C1.CellID GO
CREATE VIEW Serv4 AS
SELECT DISTINCT S4.*
FROM [Server4]...['2015-07-01$'] S4 JOIN [BD-1].dbo.Bill D1 ON S4.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S4.CellID = C1.CellID
UNION
SELECT DISTINCT S4.*
FROM [Server4]...['2016-01-01$'] S4 JOIN [BD-1].dbo.Bill D1 ON S4.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S4.CellID = C1.CellID
UNION
SELECT DISTINCT S4.*
FROM [Server4]...['2016-01-19$'] S4 JOIN [BD-1].dbo.Bill D1 ON S4.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S4.CellID = C1.CellID
UNION
SELECT DISTINCT S4.*
FROM [Server4]...['2016-06-30$'] S4 JOIN [BD-1].dbo.Bill D1 ON S4.Date = D1.Date
JOIN [BD-1].dbo.Cell C1 ON S4.CellID = C1.CellID
GO
CREATE VIEW AllResults AS
WITH Serv2All As(SELECT * FROM Serv2), Serv3All As(SELECT * FROM Serv3), Serv4All As(SELECT * FROM Serv4)
GO
далее, в Visual Studio, создайте проект WidowsForm.
на форму положите два DataGridView и кнопку, c названиями по умолчанию.
щёлкните два раза по кнопке в дизайнере, скопируйте вашу строку "namespace", вставьте следующий код:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApp2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = GetDt("SELECT * FROM AllResults ORDER BY [Phone A], Date");
dataGridView2.DataSource = GetDt("SELECT [Phone A], COUNT (*) AS \"CallQty\" " +
"FROM AllResults GROUP BY[Phone A] HAVING COUNT(*) <= 3");
}
private DataTable GetDt(string strSqlQuery)
{
DataTable ret = new DataTable();
string strCnn = "Server=.\\SQLEXPRESS;Database=BD-1;Trusted_Connection=True;";
SqlDataAdapter da = new SqlDataAdapter(strSqlQuery, strCnn);
da.Fill(ret);
return ret;
}
}
}
и замените строку namespace на свою.
если потребуется, измените строку подключения string strCnn = "Server=.\\SQLEXPRESS;Database=BD-1;Trusted_Connection=True;"; на свою.
