#Sql data entry from creator how to#
#Sql data entry from creator zip#

You can revise the PS script and test with other column data types that are not addressed in this tip, such as geometry, hierarchyid, DATETIMEOFFSET, and even UDT types and make it xxx, only 3 digits for milliseconds, but can have up to 7 digits for milliseconds.

The current PS script has a default precision of 3 digitsĪfter second, i.e. This PS script may need a little modification if you have columns with data type and you need more precision. We can put multiple queries or stored procedures in the -Query parameter of this Convert-QueryDataToSQL function. In this tip, we used PowerShell and SMO to dynamically generate a SELECT script that contains the result set of a query, and we can use this select query to easily populate or ::ToString to convert the byte data to string data. net byte inside Database.ExecuteWithResults, and we can then use

#Sql data entry from creator how to#

Note: one technical challenge here is how to convert values of binary or varbinary data type, fortunately, sql binary and varbinary data type is converted Now let's test this with a complex example: If ($ -gt 0) # we now start to generate the stringsįoreach ($t in $result.tables) #loop through each DataTableįoreach ($r in $t.Rows) #loop through each DataRowįoreach ($c in $t.Columns) #loop through each DataColumn $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

#Sql data entry from creator 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.In theory, given a query we can create a pure T-SQL solution with the following design: