For some unknown reason I'm running into a problem when passing a variable to a full text search stored procedure performs many times slower than executing the same statement with a constant value. Any idea why and how can that be avoided? This executes very fast: SELECT * FROM table WHERE CONTAINS (comments, '123') This executes ver...
I am just learning ASP.NET c# and trying to incorporate best practices into my applications. Everything that I read says to layer my applications into DAL, BLL, UI, etc based on separation of concerns. Instead of passing datatables around, I am thinking about using custom objects so that I am loosely coupled to my data layer and can ta...
A fellow developer changed all the values in the userid column of table map. I need them changed back, because userid is also a key in the profiles table. Thankfully, for reasons that aren't worth going into, map and profiles share another column in common, employeeId. So I'd like to take all the values for userid as found in profiles...
I have a situation where our developers extended a Third party database (MS SQL) by adding tables, views, stored procedures, and functions. Recently when the vender issued updates to the database they dropped all of our custom objects. The question now is what are some best practices that will allow us to extend the third party database...
Not sure how to take my current expression and get the week ending date of the week. My current expression returns the date, one week from today. DateAdd(DateInterval.WeekOfYear, 1, today()) What I want to do instead is return the end date (Saturday) of that week instead. What do I need to add to this expression to get the desired r...
I just came across the interesting problem of trying to trim the leading zeroes from a non-numeric field in SQL. (Since it can contain characters, it can't just be converted to a number and then back.) This is what we ended up using: SELECT REPLACE(LTRIM(REPLACE(fieldWithLeadingZeroes,'0',' ')),' ','0') It replaces the zeroes with sp...
I have an insert query that gets generated like this INSERT INTO InvoiceDetail (LegacyId,InvoiceId,DetailTypeId,Fee,FeeTax,Investigatorid,SalespersonId,CreateDate,CreatedById,IsChargeBack,Expense,RepoAgentId,PayeeName,ExpensePaymentId,AdjustDetailId) VALUES(1,1,2,1500.0000,0.0000,163,1002,'11/30/2001 12:00:00 AM',1116,0,550.0000,850,NU...
Hello, Trying to debug a deployed SSRS security customization (to use forms auth) as described here: http://msdn.microsoft.com/en-us/library/ms160724(SQL.90).aspx. There is a section about debugging your application... I followed the steps I think, but I cannot debug this. Has anyone got any advice as to how to debug a custom Forms ...
Using a SELECT statement in MS SQL 2005 TSQL, and no source table, just variables as listed below... @FirstName varchar(20) @LastName varchar(20) @ZipCode varchar(5) ...what syntax will build XML resembling the following? <XMLDATA><REC FirstName="JOHN" LastName="SMITH" ZipCode="98052" /></XMLDATA> ...
Using SQL Server 2005 and 2008. I've got a potentially very large table (potentially hundreds of millions of rows) consisting of the following columns: CREATE TABLE ( date SMALLDATETIME, id BIGINT, value FLOAT ) which is being partitioned on column date in daily partitions. The question then is should the primary key be ...
Is this valid ANSI SQL?: SELECT 1 AS X ,2 * X AS Y ,3 * Y AS Z Because Teradata (12) can do this, as well as this (yes, crazy isn't it): SELECT 3 * Y AS Z ,2 * X AS Y ,1 AS X But SQL Server 2005 requires something like this: SELECT X ,Y ,3 * Y AS Z FROM ( SELECT X ...
Hello Folks I have been trying to get a function going that calculates what Shift the Employees worked from their StartTime and EndTime. Here is the code i have so far, there seems to be calculating the shift wrong. Shift 1 from 08:00:00 - 16:30:00 Shift 2 from 16:00:00 - 00:30:00 Shift 3 from 00:00:00 - 08:30:00 Also the shift with ...
Suppose I have two columns in a table that represents a graph, the first column is a FROMNODE and second one is TONODE. What I would like to know is that how will we find all the nodes that are two steps away from a particular node. Lets suppose I have a node numbered '1' and i would like to know all the nodes that are two steps away fro...
Suppose I have a VarBinary[MAX] column, can I insert or update into that column using a type derived from System.IO.Stream? How? I think that I can obtain a read-only stream from such a column using a SqlDataReader, calling GetSqlBytes() on the reader, getting the SqlBytes instance, and then referencing the Stream property on that. ...
My kind webhost (1and1) royally asked me to go elsewhere to do something like this. I have 2 sites. One of them was developed by a .Net programmer. Now I am contracted to implement a PHP site and fetch data from the .Net site. There is an ASP.Net form that a customer fills and when they hit submit, the data gets stored in SQL Server DB...
Is a nvarchar(max) less performant than a nvarchar(100) for instance? ...
I have an user defined table function in SQL Server that aggregate data from several tables including a couple of tables of another database. That is done hardcoding the name of the database in the queries, but we want to make the database name configurable (because our databases usually share the server with the databases of other appli...
I have a quite complicated query which will by built up dynamically and is saved in a variable. As second part i have another normal query and i'd like to make an inner join between these both. To make it a little more easier here is a little example to illustrate my problem. For this little example i used the AdventureWorks database. ...
I need to add a couple of columns to a table that is a part of a replication set. It is not a constraint coulumn or a part of any article filters and it allows NULL. I have a pretty good idea that I can run this --> ALTER TABLE tblPlanDomain ADD ReportWageES VARCHAR (100) NULL and NOT force all my clients to reinitialize but I was hopin...
I am trying to configure auditing on my SQL Server using Service Broker. I did all the configuration needed to capture the DDL Events (queue, routes, endpoints, event notification). It is working properly except that I am not able to get the hostname of the client from where the DDL event originated from. Using the service broker's act...