$result = $db.ExecuteWithResults($Query) Write-Error "Database cannot be accessed or does not exist, please check" Write-Error "Server cannot be accessed, please check" $svr = new-object .Server $ServerInstance # SQL Server instance name, default to local server # assume you have installed SQL Server 2012 (or above) Feature Pack, mainly the Shared Management Object file Simple select statements, or stored procedures that will return table-like result setĪ string which is a select statement with union all to other select statements The target SQL Server database, against which the query will run The SQL Server instance name, which the query will connect to
Generate insert statements from table sql server zip#
sql file, zip it and then send it to my recipient.Ĭonvert-QueryDatatToSQL -ServerInstance MyServer -Database MyDB -Query "exec sp_who2 active"
This solution is wrapped as a PowerShell function, and as such, I can easily embed it into lots of my DBA automation work. So I will use PowerShell, with SMO to come up with a more robust and generic solution.ĭatabase.ExecuteWithResuts method to execute one or multipleĭataColumn, and based on the column data type, process the column value accordingly Time-consuming to do with a T-SQL solution since the sp_who2 result has two identical columns. It becomes very difficult to do when you do not know what data set you will getįrom a stored procedure (assuming depending on parameter values the stored procedure may returnĮven for a simple example, say I want to script out the results of "sp_who2 active", it will be This is easy when you have a simple query, but the problem with this pure Parsing this to get the column names and their data types and together with the table values to construct the expected SELECT query.Using the query to dump the data into a temporary table, something similar.
SET = + sp_executesql script may not work in every single case, as there are data types not accounted for here.In theory, given a query we can create a pure T-SQL solution with the following design: SET = + ' - ' + NEXT FROM TableCursor INTO TableCursor
SET = + - + ' - ' + SET = 'UNION ALL SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY ' + + ') & amp amp lt & amp amp gt 1 THEN '','' ELSE '''' END +' + + '''(' Sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_idįETCH NEXT FROM TableCursor INTO = 0ĮLSE SET = + ',' + ',' Sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id Sys.types t ON c.user_type_id = t.user_type_id ISNULL(i.is_primary_key, 0) 'Primary Key', DECLARE TABLE (ColumnName VARCHAR(255), DataType VARCHAR(50), INT, INT, INT, is_nullable BIT, PrimaryKey BIT, ColumnID INT)ĭECLARE VARCHAR(10) = CHAR(13) + CHAR(10)
0 kommentar(er)
