tsql

SSIS issue when communicating with Oracle (T-SQL vs. PL/SQL?)

I have an SSIS package set up to pull data from an Oracle database into SQL Server. I recently ran into an issue that was preventing this data pull. The following code works fine in Oracle SQL Developer (it returns rows, as it should): SELECT a.MyField , a.MyOtherField, a.FromDate FROM MyTable a WHERE a.FromDate BETWEEN CONCA...

Select all records created or modified after a specific date in all tables in a SQL Server db

I would like a list of all new or modified records created after a specific date in all tables in a SQL Server database. I am using SQL Server 2005 and ssms. Is there a way to do this with one query, or some other tool? How could I do this for one table? ...

To compare column name with the corresponding values in Microsoft SQL Server

Hi all I have a function which takes two parameter type and value. According to the type it will search the corresponding table whether the value exist or not and return a boolean value. Now I have created a table with the type as columns. I want to pass the column name and its corresponding values to be passed to the function. For exa...

Constraints in SQL Database

I need to have a table in T-SQL which will have the following structure KEY Various_Columns Flag 1 row 1 F 2 row_2 F 3 row_3 T 4 row_4 F Either no rows, or at most one row can have the Flag column with the value T. My developer claims...

Convert varchar to date.

I (unfortunately) have some dates that were saved into varchar columns. These columns contain dates in the following format: mmddyy For example: 010110 I need to import these values into a table which is set to datetime and formats dates like this: 2010-09-17 00:00:00.000 How can I convert the string above into a dateti...

after joining two tables the result does not match with the original table

I have problem with inner joining 2 tables: LOOKUP and PERF. LOOKUP ...has only creative name and "perf." table has the creative name as well as the values to it. I need to get the values into LOOKUP (where both the table has common creative name). FYI: UCID is nothing but creative name Also, creative names have duplicates in both the...

T-SQL between periods gaps

Hi, I have some data on my table like: DAY | QTY | Name 1/1/2010 | 1 | jack 5/1/2010 | 5 | jack 2/1/2010 | 3 | wendy 5/1/2010 | 2 | wendy my goal is to have a SP requesting a period of time (example: '2010-1-1' to '2010-1-5'), and get no gaps. Output example: DAY | QTY | Name 1/1/2010 | 1 | jack 2/1/2010 | 0 |...

Query to find all FK constraints and their delete rules (SQL Server)

In SQL Server 2005, can I issue an SQL query to list all FK constraints on tables within the DB, and show the delete rule? (ie nothing, cascade, set null, or set default) The output I'm looking for is something akin to: FK_NAME ON_DELETE ================================== FK_LINEITEM_STATEMENT CASCADE FK_ACCOUNTREP_...

How to refer to a variable create in the course of executing a query in T-SQL, in the WHERE clause?

What the title says, really. If I SELECT [statement] AS whatever, why can't I refer to the whatever column in the body of the WHERE clause? Is there some kind of a workaround? It's driving me crazy. ...

time format in SQL Server

Does anyone know how can I format a select statement datetime value to only display time in SQL Server? example: Table cuatomer id name datetime 1 Alvin 2010-10-15 15:12:54:00 2 Ken 2010-10-08 09:23:56:00 When I select the table I like the result will display as below id name time 1 Alvin 3:12PM 2 Ken ...

sql server agent proxy account

Hi, I am trying to use proxy account for non sysadmin to grant them exec permission on xp_cmdshell. What I did is: USE [master] GO CREATE CREDENTIAL [proxyaccount] WITH IDENTITY = N'domain\user', SECRET = N'password' GO USE [master] GO CREATE CREDENTIAL [proxyaccount] WITH IDENTITY = N'domain\user', SECRET = N'password' GO USE [msdb] ...

TSQL Computed column limitations

CREATE TABLE [dbo].[MembershipModule]( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ParentId] [uniqueidentifier] NULL, [TargetId] [int] NULL, [WebContentId] [uniqueidentifier] NULL, [Name] [varchar](35) NOT NULL, [NameUpper] AS (isnull(upper([Name]),'')) PERSISTED NOT NULL, [UriPrefix] [varchar](max) NULL, [UriText] [varchar](...

Why must QUOTED_IDENTIFIER be on for the whole db if you have an indexed view?

Yesterday I added some indexes on a view in my MSSQL 2008 db. After that it seems like all the store procedures need to run with QUOTED_IDENTIFIER set to ON, even those that don't use the view in question. Why is it so? Is this something I can configure on the db or do I have to update all my stored procedures to set the QUOTED_IDENTIFI...

Is NOLOCK the default for SELECT statements in SQL Server 2005?

I have only SQL Server 2008R2 installed though I need to communicate with customers having 2005. [1] tells: "NOLOCK This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements" [2] doesn't seem to mention it, but my checking in SSMS/SS 2008R2 shows that n...

T-Sql Algorithm Question

Hello, I have a T-Sql Statement as follows; Insert into Table1 Select * From Table2 I want to know the running sequence. Does the insert waits select statement to finish before starting or it starts asap select statement starts returning values and expects new records from the select statement to continue. This is a plain stored pr...

How to reverse values in a string in T-SQL

Using T-SQL, I'm trying to find the easiest way to make: "abc.def.ghi/jkl" become "abc/def/ghi.jkl"? Basically switch the . and / Thank you ...

Need help for SQL select query for this two table

Table Capture image : http://img844.imageshack.us/img844/6213/99730337.jpg ------------ PositionTable------------ ID ContentFK Position 11 100 1 12 101 1 13 104 2 14 102 2 15 103 2 16 105 3 17 106 3 18 1...

LINQ to SQL use of => operand on String not allowed

Possible Duplicate: string1 >= string2 not implemented in Linq to SQL, any workarround? To give a brief summary of what I want to achieve we have a table called Application and it stores application version numbers. The contents of that Table for example is ApplicationID VersionID 1 R1.01.01.01 ...

index seek while calling the UDF itself, index scan while wrapping the UDF in sp

Hi, I have a UDF that does most of the joins and when I run it, it read about 100-150 pages (from sql profiler) but if I wrap this UDF in sp then the sp will do the read about 243287 pages. Furthermore, the UDF itself performs index seek but the sp performs index scan on one of the searched columns. Can anyone please advise? ...

SQL - Insert query Inside another Insert with updated Identity seed

I have 2 tables T1 AND T2 T1 has 10 unique records with a primary key (Identity Seed) T2 has multiple entires with Foreign Key for each record in T1 T1 has 2 columns : PrimaryKey - DATA T2 has 2 columns : PrimaryKey - FoeignKey (this FK is the Primary Key of T1) I need to write a query which will select all the records from T1 and INS...