Say, I have a script nicely formatted in SSMS and it's annotated with all kinds of comments in different languages. But when I copy and paste this nice thingy into Word with syntax highlighted I will get a syntax-highlighted message with those comments garbled, as if reading the source text with one code page and pasting it using another...
We use a binary(16) field to store IP Addresses. We do this as it can hold both IPv4 and IPv6 addresses and is easily used with the .Net IPAddress class. However I have created the following SQL function to convert the binary address to the IP Address string for reporting purposes. CREATE FUNCTION fn_ConvertBinaryIPAddressToString ( ...
Assume one fine day an admin comes to the office like he always does to do his admin tasks on Sql Server Databases and to his surprise finds a database missing. He has no clue of who dropped it or was it from an external batch or SQL injection etc ... where do one start an investigation and what are the important parameters/ findings tha...
Hi , I know the DENSE_RANK() function of the ranking function and its job well. But always I only write this function’s name and it do its job implicitly can cannot see how it work explicitly and I like to see its structure which do the operations within this function with T_SQL language. Would you please help me with it. ...
Could anyone advice me on how I could run an UPDATE statement in SQL Server to take a number of given DATETIME fields and deduct two months from them? ...
I am trying to get the following query to display the results in alphabetical order by City except for the fact that I want "Berlin" to appear at the top of the list So the results would look something like Berlin Algeria Australia Fiji Greece ... Hope that makes sense, I currently have the following... SELECT CompanyName, City ...
Imagine a table with like a hundred of different columns in it. Imagine, then, that I have a user-data table from where I want to copy data to the base table. So I wrote this simple insert-select statement and this error pops up. So, what's the most elegant way to figure out which column raises the error? My initial thoughts on the solu...
How to execute one Stored Procedure inside other Stored Procedure? NOTE: Both Stored Procedure's reside in different servers ...
CASE 1: I have a table with 30 columns and I query using 4 columns in the where clause. CASE 2: I have a table with 6 columns and I query using 4 columns in the where clause. What is the difference in performance in both cases? For example i have table table A { b varchar(10), c varchar(10), d varchar(10), e varchar(10), ...
Error message: Warning : SQM1014: Unable to extract function 'dbo.ProductFamilyIndex_EN' from SqlServer. Null or empty full-text predicate. function defined as: CREATE FUNCTION [dbo].[ProductFamilyIndex_EN] ( @topn int, @keywords nvarchar(4000) ) RETURNS TABLE AS RETURN ( select top (@topn) ProductFamilyID from ...
select * from table where category=@categoryid I'm not sure how easy is this but I couldn't get my head around it. I want to be able to change where clause on above query so that if use 0 instead of 1-2-3 or 4 as @categoryid it would select all categories. i don't have any category with 0 in database. ...
Hi, I'm dealing with chunks of data that are 50k rows each. I'm inserting them into an SQL database using LINQ: for(int i=0;i<50000;i++) { DB.TableName.InsertOnSubmit ( new TableName { Value1 = Array[i,0], Value2 = Array[i,1] } ); } DB.SubmitChanges(); This takes about 6 m...
First I need to create a sproc that will give me just a list of years from this year back to 5 years before this year. I was thinking of somehow using a table variable? After that I need to use that value as a parameter in another sproc. How would I then say show all Dates that contain the year that is picked? I know how to write the pa...
Hi, I'm trying to connect to a sql server 2005 database via JDBC. I get the error: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permission was denied on the object 'MyTable', database 'MyDatabase', schema 'dbo'. The schema I use to connect is "MyUser". How do I connect using MyUser as opposed to dbo? Thanks! ...
I have the following UDF. CREATE FUNCTION [dbo].[udf_GenerateVarcharTableFromStringList] (@list varchar(MAX), @delimiter char(1) = N',') RETURNS @tbl TABLE ([Value] varchar(200)) WITH SCHEMABINDING AS BEGIN DECLARE @chrind INT DECLARE @Piece nvarchar(4000) SELECT @chrind...
I have a number of read only databases alter database [ReferenceData] set READ_ONLY that is used by our application and was wondering what type of things I can tweak for maximum performance? So far, I came up with the following goals: Force padding in indexes to 100% fill factor index fragementation be less than 10% If space isn't a p...
i have a column with dates, but it is a varchar: 8/31/2010 9:48 8/31/2010 9:49 8/31/2010 9:51 8/31/2010 9:52 8/31/2010 9:55 8/31/2010 9:59 8/31/2010 10:11 8/31/2010 10:13 8/31/2010 10:16 8/31/2010 10:37 8/31/2010 10:42 i made sure that none of these will be a BAD date: SELECT * FROM qcvalues.dbo.batchinfo WHERE ISDATE(reporttime) <> ...
I have data in following format Table1 e_id e_name e_type ------------------------- 1 CBC 2 2 ABC 3 3 N2 1 4 CBC1 3 5 ABC1 3 6 N1 1 table2 N_ID N_Name --------------- 3 N2 6 N1 Table3 N_ID E_ID ------------ 3 1 3 2 3 3 6 4 6 5 6 6 And...
Hi, Is this the most efficient way to delete from DeletedProducts table where there are not references found in the ProductFileInfo table? Sample: DELETE FROM DeletedProducts WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo Or is NOT EXIST a better way to perform this. Note: ProductFileInfo has over 20 Million...
I got this function from someone on here: create FUNCTION [dbo].[fnSplitString] (@s varchar(512),@sep char(1)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop...