I have a LINQ entity that I need to create a special string value for. It is 7 different values separated by "-". Some of the fields used are in that table, while some of them are in different tables. I would like to add this field to the entity somehow so that I don't have to create this string myself every time I need it. I was thinki...
I have an ADO.NET connection object to an SQL Server (which will use connection pooling and all the nice things about it) in one Winforms application. We're adding some new stuff to this application and we thought that we could do it in LINQ to SQL, the question is, if we open a new connection like this: MyDataContext dc = new MyDataCo...
I have a dynamic query which reads like this Alter PROCEDURE dbo.mySP -- Add the parameters for the stored procedure here ( @DBName varchar(50), @tblName varchar(50) ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements f...
Long time back in past I had read somewhere that if we want to enter data in "image" typed field of table whose value is more then 8kb, then we must explicitly specify parameter type to SqlImage. Is this true? I don't find any such documentation about it anymore. I tried to use Reflector to reverse engineer the SqlClient in order to see...
SQL Server Syntax: UPDATE #RELATIONSHIP SET SEGMENT_START_DT = #SEGMENTS_LANE.SEGMENT_START_DT, SEGMENT_END_DT = #SEGMENTS_LANE.SEGMENT_END_DT, SEGMENT_ID = 'XCSLANE' +'-'+ #SEGMENTS_LANE.LANE_ID +'-'+ CONVERT(VARCHAR,#SEGMENTS_LANE.SEGMENT_START_DT,113) FROM #SEGMENTS_LANE WHERE #RELATIONSHIP.LANE_ID = #SEGMENTS_LANE.LANE_ID AND #RELA...
I am using LinqToSQL and a trusted connection to handle database selects/updates/inserts/deletes. In the past I have always used stored procedures and only granted execute permission to the Application Pool identity for the particular sproc in the database. This is my first LinqToSql project (and I really love how much it helps). I wo...
Does anyone know of a way to import Google Calendar entries into a database using SSIS. I know I can export Calendars using the ICal format - but there is no native way to parse these files in SSIS. The only way I can think of doing it is to write a script component to parse the file. I'm wondering if anyone has any experience doing s...
I've got a log table in SQL Server that looks like this: CREATE TABLE [dbo].[RefundProcessLog]( [LogId] [bigint] IDENTITY(1,1) NOT NULL, [LogDate] [datetime] NOT NULL, [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RefundId] [int] NULL, [RefundTypeId] [smallint] NULL, [LogMessage] [varchar](1000) COLLATE S...
When 2 decimal(30,10) numbers are divided in Sql Server 05, 2 last decimals seem to be getting lost (not even rounded off, simply truncated). For example: Declare @x decimal(30,10) Declare @y decimal(30,10) Declare @z decimal(30,10) select @x = 2.1277164747 select @y = 4.8553794574 Select @z = @y/@x select @z Result: 2.28196731...
I have these two very simple tables: Orders OrderId autoincrement CustomerOrderId Int ... Customers CustomerId AutoIncrement ... OrderNumber Int The requirement is to have for each customer an index that will store their own unique order counter (no need to judge the design, I'm only trying to help fixing a bug ...
I have a situation where I am displaying records on a page, and I need a way for the user to select a subset of those records to be displayed on another page. These records aren't stored anywhere then, it is a dynamically generated thing. I know I could use jquery to pass in a comma-separated value to my other web page, but I'm not sur...
I would like to monitor index usage for an sql database, in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? And which scripts could be useful? (I'm aware of this question about identifying unused objects, but this applies only to the current run of the sql server. I would like to monitor ...
So Oracle has NULLS FIRST, which I can use to have null values sorted at the top followed by my column value in descending order: ORDER BY date_sent NULLS FIRST What is comparable in SQL Server? There are these alternatives, assuming the date values are NULL or in the past: ORDER BY ISNULL(date_sent, GETDATE()) DESC ORDER BY (CASE W...
I have many operations in the database that need to trigger application code. Currently I am using database polling, but I hear that SQL Server Service Broker can give me MSMQ-like functionality. Can I listen to SQL Server Service Broker queues from .NET applications running on a different machine? If so, should I do it? If not, what w...
I was wondering if there are any standards for encrypting sensitive data in Database which are compliant w/ SOX or HIPAA. Or does the Encrypt Function in SQLServer necessary?. or handle in Business logic. Any ideas or links we have. ...
I have an expensive operation that calls the database, and populates an array of objects for use in comboboxes. Because it can take ~2 minutes, I cache it on start up. When the user needs to use these comboboxes, I need to make sure they have the current data. How can I do this? ...
My application serializes data into various XML attributes, and depending on data it might send it as text, or as base64. In the latter case, attribute name will be "attribute-base64". So, on SQL server side it is possible to use following convention to decode XML: declare @DataXml xml set @DataXml='<root v="test data"/>' ; --or: set @D...
I want to pull back information about a loan. One piece of information is a certain fee amount. If I simplify my query down to the loan number and fee amount, I still can not figure it out. The first query returns what I expect, one loan number and a 0 for the fee amount (fee was not applied) while the second one I can not get to work...
After downloading the September 2009 StackOverflow data-dump and running Brent's import query, I'm getting the following message: Msg 7119, Level 16, State 1, Procedure sp_xml_preparedocument, Line 1 Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes. Msg 8179, Level 16, State 5, Procedure usp_ETL_Load_Posts, Line...
Im going through a project I have taken over, and on the database side I have noticed that the previous programmers have written a bunch of triggers to delete child records. The thing is, these records already have a a foreign key relationship with the parent record I am deleting. The delete triggers are nothing but simple delete state...