DECLARE @mesto varchar(30);
DECLARE c CURSOR FOR SELECT mesto from MyPlaces
OPEN c
FETCH NEXT FROM c INTO @mesto
WHILE @@FETCH_STATUS = 0
BEGIN
select 'Здесь ' + @mesto
FETCH NEXT FROM c INTO @mesto
END
CLOSE c
DEALLOCATE cselect convert(varchar(255), memofield)По дням:
select convert(char(8),Date_and_Time,112) mydate, count(calls_id)
from calls
group by convert(char(8),Date_and_Time,112)
order by mydateПо месяцам:
select convert(char(6),Date_and_Time,112) mydate, count(calls_id)
from calls
group by convert(char(6),Date_and_Time,112)
order by mydate По годам:
select convert(char(4),Date_and_Time,112) mydate, count(calls_id)
from calls
group by convert(char(4),Date_and_Time,112)
order by mydateimport pyodbc
cnxn = pyodbc.connect("Driver={SQL Server};"
"Server=srvmssql;"
"Database=mydatabase;"
"Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute('SELECT TOP 10 * FROM MYTABLE')
for row in cursor:
print('row = %r' % (row,))
input()Пример запроса:
DECLARE @idName INT
SET @someColumn = 7
DECLARE @s VARCHAR(MAX)
SET @s = ''
SELECT @s = @s + CASE WHEN @s <> '' THEN ', ' ELSE '' END + CAST(myColumn AS VARCHAR)
FROM t1
WHERE someColumn = @someColumn
SELECT @s AS ResultListНе моё, нашёл где-то в инете, но проверил, код рабочий, запрос очень помог
SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY -- t.Name UsedSpaceKB desc
using System.Data.SqlClient;
string query = @"select 1";
using (var connection = new SqlConnection("user id=Vasya;password=P@$$w0rd;server=MyServer;database=MyBase;connection timeout=10;")) {
connection.Open();
using (var command = new SqlCommand(query, connection)){
command.CommandTimeout = 290;
using(SqlDataReader reader = command.ExecuteReader()){
int rowcounter = -1;
while (reader.Read()) {
rowcounter++; //счётчик строк в результате запроса
//заполняем значения в сохранённой строке
if (reader["My_Column"] is int) {
int n = (int)reader["My_Column "];
}
//…
}
reader.Close();
}
}
}
//добавить обработчики исключений
Для подключения к базе не под логином и паролем, а через доменную авторизацию под текущей виндовой учёткой строка будет такой:
using (var connection = new SqlConnection("server=MyServer;database=MyBase;connection timeout=10; Integrated Security=True;")) {}
Более-менее рабочий пример, чтоб например 4.5 не превратилось в 5
DECLARE @tmp_cnt Numeric (18,10) DECLARE @tmp_uet Numeric (18,10) … @tmp_cnt = 1.0 @tmp_uet = 4.5 … CAST ( @tmp_cnt * @tmp_uet AS Numeric(18,10))
Всё работает из-за (18,10) (precision, scale)
Ещё подробности здесь:
Select * from information_schema.columns