Incorrect syntax near ‘)’ calling stored procedure with GETDATE
You can’t pass in a function call as an argument to your stored procedure. Instead use an intermediate variable: DECLARE @tmp DATETIME SET @tmp = GETDATE() EXEC DisplayDate @tmp;
sql-server
Display varbinary Image in Gridview
The following will show how to retrieve an image from SQL Server and display it in a GridView on an ASP.NET web page. Create a table in the database: CREATE TABLE Surplus([Surplus Id] int not null, Department nchar(50), Category nchar(25), Item nchar(75), Visible bit, TransferableImage varbinary(max), CONSTRAINT PK_Surplus_SurplusId PRIMARY KEY([Surplus Id])); Note: If a table … Read more
The Best Way to shred XML data into SQL Server database columns
Stumbled across this question whilst having a very similar problem, I’d been running a query processing a 7.5MB XML file (~approx 10,000 nodes) for around 3.5~4 hours before finally giving up. However, after a little more research I found that having typed the XML using a schema and created an XML Index (I’d bulk inserted … Read more
How to do Sql Server CE table update from another table
Your second attempt doesn’t work because, based on the Books On-Line entry for UPDATE, SQL CE does’t allow a FROM clause in an update statement. I don’t have SQL Compact Edition to test it on, but this might work: UPDATE JOBMAKE SET WIP_STATUS = ’10sched1′ WHERE EXISTS (SELECT 1 FROM JOBVISIT AS JV WHERE JV.JBT_TYPE … Read more
Slow query with cfqueryparam searching on indexed column containing hashes
Your issue may be related to VARCHAR vs NVARCHAR. These 2 links may help Querying MS SQL Server G/UUIDs from ColdFusion and nvarchar vs. varchar in SQL Server, BEWARE What might be happening is there is a setting in ColdFusion administrator if cfqueryparam sends varchars as unicode or not. If that setting does not match … Read more
How to import data from .csv in SQL Server using PowerShell?
I wrote a blog post about using SQL with PowerShell, so you can read more about it here. We can do this easily if you have the SQL-PS module available. Simply provide values for your database name, server name, and table, then run the following: $database=”foxdeploy” $server=”.” $table=”dbo.powershell_test” Import-CSV .\yourcsv.csv | ForEach-Object {Invoke-Sqlcmd ` -Database … Read more
Populate Dataset With Table Names From Stored Procedure
Your SP is not actually returning multiple tables, its returning a selection of columns and rows from your tables, therefore there is no ‘table name’, and hence why they are named table1, table2 etc. If its important, you could return an extra column for each selection, and in that column fill it with the desired … Read more
Why would using a temp table be faster than a nested query?
“should be” is a hazardous thing to say of database performance. I have often found that temp tables speed things up, sometimes dramatically. The simple explanation is that it makes it easier for the optimiser to avoid repeating work. Of course, I’ve also seen temp tables make things slower, sometimes much slower. There is no … Read more
T-SQL for finding Redundant Indexes
There are situations where the redundancy doesn’t hold. For example, say ColumnC was a huuge field, but you’d sometimes have to retrieve it quickly. Your index 1 would not require a key lookup for: select ColumnC from YourTable where ColumnnA = 12 On the other hand index 2 is much smaller, so it can be … Read more
CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
On SQL Server Forum, at this page, it’s stated: The built-in CHECKSUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See this post for more explanation.