sql

Insert into sql_variant column in sql server

I am trying to insert a value to sql_variant column in sql server from an xml column ex. INSERT INTO [dbo].[TestColumn] ( Id, Attribute, AttributeValue ) SELECT Id, 'TestName', CAST(CustomColumns.query('//TestName') AS nVarchar(MAX)) FROM [dbo].[Clmnt] (NOLOCK) I got this error, Operand type clash: nvarch...

LLBLGEN: Linq to LLBGEN don't work

I want to make custom select from the database table using Linq. We use LLBGEN as ORM solution. I can't do LINQ query to Entities Collection Class unless I call GetMulti(null) method of it. Is it possible to do LINQ query to LLBGEN without extracting all table first? BatchCollection batches = new BatchCollection(); Bat...

Postgresql PSQL sequence stare-down. Why does this not execute to completion?

I'm in psql trying to create a table for a python admin. It's actually an extension of a working admin, so I'm adding a new table and some relations. When I enter in the following sequence, it never executes to completion: BEGIN; CREATE TABLE "work_projectinteractiveasset" ( "id" serial NOT NULL PRIMARY KEY, "name" varchar...

Truncating a table referenced by a foreign key

Hey, We have two tables in a SQL Server 2005 database, A and B.There is a service which truncates table A every day. Recently, a foreign key constraint was added to table B, referencing table A. As a result, it isn't possible truncating table A anymore, even if table B is empty. Is there any workaround to get the same result as truncat...

Simple SQL Data Modeling question

Given that I have a table that holds vehicle information and one of those pieces of information is VehicleType (usually 6-20 characters), what are the technical reasons why it is better to design the tables like this: Vehicles VehicleID VehicleTypeID (INT) (relates to an INT in the VehicleTypes table) versus this: Vehicles Veh...

I would like to make a field part of a many to many relationship

I have the following table called stores id store zip 1 Market 1 01569 2 Market 2 01551 3 Market 3 10468 4 Market 4 10435 Zip code table INSERT INTO `zip_codes` (`restaurants_locations_id`, `zip`, `state`, `latitude`, `longitude`, `city`, `full_state`) VALUES (1, '06001', 'CT', ...

Parameterizing a SQL IN clause on an integer column?

Jeff Atwood asked the original question about parameterizing a SQL IN clause, but I want to do this with an integer column. If I try the code from the original post I get the following exception, which makes sense: Conversion failed when converting the varchar value '%|' to data type int. Anyone try this before? ...

help translate this week query from Oracle PL/SQL to SQL Server 2008

I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database: SELECT TRUNC( /* Midnight Sunday */ NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL) ) AS week_start, TRUNC( /* 23:59:59 Saturday */ NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1 ...

CTE and last known date processing

Input @StartDate = '01/25/2010' @EndDate = '02/06/2010' I have 2 CTEs in a stored procedure as follows: with CTE_A as ( [gives output A..Shown below] ), with CTE_B as ( Here, I want to check if @StartDate is NOT in output A then replace it with the last known date. In this case, since @startdate is less than any date in output A...

Trying to insert a row using stored procedured with a parameter binded to an expression.

Environment: asp.net 3.5 (C# and VB) , Ms-sql server 2005 express Tables Table:tableUser ID (primary key) username Table:userSchedule ID (primary key) thecreator (foreign key = tableUser.ID) other fields I have created a procedure that accepts a parameter username and gets the userid and inserts a row in Table:userSchedule Problem: ...

SQL is this equivalent to a LEFT JoIn?

Is this equivalent to a LEFT JOIN? SELECT DISTINCT a.name, b.name FROM tableA a, (SELECT DISTINCT name FROM tableB) as b It seems as though there is no link between the two tables. Is there an easier / more efficient way to write this? ...

SQL UNION ALL problem with adjacent columns

I'm getting a formatting problem when I use UNION ALL under Excel 2007. I have 12 worksheets one for each month I'm trying to apply UNION ALL to combine all the data from these 12 worksheets. Each worksheet has 3 columns 2 with numerical data and one with date. I've had the problem with the last worksheet. My numeric column turns into st...

How should I migrate DDL changes from one environment to the next?

I make DDL changes using SQL Developer's GUI. Problem is, I need to apply those same changes to the test environment. I'm wondering how others handle this issue. Currently I'm having to manually write ALTER statements to bring the test environment into alignment with the development environment, but this is prone to error (doing the s...

Cannot render images locally with Sql Server 2005 Report Services SOAP service calls

I am trying to render images from SSRS 2005 via the SOAP call. When the reports service is installed on the same server as my website, the images generated do not render in the page. However, when I connect to the reporting service URL from the same web code on a separate machine the images render just fine. Is there a dependency that...

Database schemas WAY out of sync - need to get up to date without losing data

The problem: we have one application that has a portion which is used by a very small subset of the total users, and that part of the application is running off of a separate database as well. In a perfect world, the schemas of the two databases would be synced up, but such is not the case. Some migrations have been run on the smaller ...

Converting sql query to EF query- nested query in from

Hey guys Just wondering how the following sql query would look in linq for Entity Framework... SELECT KPI.* FROM KeyPerformanceIndicator KPI INNER JOIN ( SELECT SPP.SportProgramPlanId FROM SportProgramPlan PSPP INNER JOIN SportProgramPlan ASPP ON (PSPP.SportProgramPlanId = @Sport...

exception with Linq to SQL using sdf file

Hi, I've set up a project with an SDF local database file and am trying to access it using an LINQ To SQL (".dbml") file. I have used the connection string provided by the sdf file and can instanciate the object with out a problem: thisDataContext = new MyDataContext(GetConnectionString()); However, whenever i try to access any inform...

Can I use SQL to update multiple rows with multiple values from another table?

I've got a one to many table with ids and traits for those users. I want to copy one user's settings to another. userid trait onoff ------ ----- ----- 1 1 on 1 2 on 1 3 on 1 4 on 2 1 off 2 2 off 2 3 off 2 ...

Is there a way in SQL profiler to filter by INSERT statements?

I am trying to identify SQL INSERTS that occur when a record is viewed from a Windows application. In SQL Profiler I have a long list of stored procedures that fire when the record is viewed, however it is very cumbersome to identify the stored procedure then locate the inserts, then find the stored procedure in the object explorer (even...

index help for a MySQL query using greater-than operator and ORDER BY

I have a table with at least a couple million rows and a schema of all integers that looks roughly like this: start stop first_user_id second_user_id The rows get pulled using the following queries: SELECT * FROM tbl_name WHERE stop >= M AND first_user_id=N AND second_user_id=N ORDER BY start ASC SELECT * FROM tbl_...