MS SQL Server :: Repost!: Extract Data Meeting Specific Criteria.

String Index Function (substring / Charindex) - Extract Specific Characters From Data

Aug 5, 2013

SQL Query. What i need is to be able to extract specific characters from data.

Eg name

1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results

i.e. 1.2-KPIA1-App-00001 1.2 KPIA1

*I need this in part of a script as there is thousands of rows of data.

View 4 Replies View Related

ORDER BY Specific Criteria

Aug 25, 2005

Hi,

I was wondering if it is possible to order a recordset by specific values.

That is, I want my recordset to be ordered alphabetically, but I want to set the order of the alphabet.

For example, I don't want my recordset to be odered by A, B, C, D ...I want it to be ordered by G, V, T, A ... or something.

Can you do something like
Code:

SELECT * FROM myTable ORDER BY myField (G, V, T, A, B, C, Q, X)

...etc

Thanks

e

View 2 Replies View Related

Excluding Based On Specific Criteria

Jan 2, 2007

I am trying to accomplish the following.

There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.

The first and second table have one field in common - the Account number.(ACCT_NUM)

the exclusion is based in steps..

1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.

how do i do this... pls suggest

Thanks

View 1 Replies View Related

An Easy Way To Reference The Nth Row From A Table Meeting A Condition X, And The Mth Row From The Same Table Meeting Condition Y

May 10, 2006

Hi

I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.

I also need to adjust rows, say mth and nth that meet condition , say y.

My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)

This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.

Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:

ID
Int1
Type1
Ideal Rank by Type

1
1
1
1

2
1
1
2

3
2
1
3

4
3
1
4

5
5
1
5

6
8
2
1

7
13
1
6

8
21
1
7

9
34
1
8

10
55
2
2

11
89
1
9

12
144
1
10

13
233
1
11

14
377
1
12

15
610
1
13

16
987
2
3

17
1597
1
14

18
2584
1
15

19
4181
1
16

20
6765
1
17

The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...

I hope this posting is clear, albeit long.

Thanks in advance

Greg

PS The code:

USE

master

GO

CREATE DATABASE CertainRowsToChange

GO

USE CertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1 )

SELECT 2

INSERT INTO RowsToChange (Int1 )

SELECT 4

INSERT INTO RowsToChange (Int1 )

SELECT 8

INSERT INTO RowsToChange (Int1 )

SELECT 16

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 2

INSERT INTO AllRows (Int1 )

SELECT 3

INSERT INTO AllRows (Int1 )

SELECT 5

INSERT INTO AllRows (Int1 )

SELECT 8

INSERT INTO AllRows (Int1 )

SELECT 13

INSERT INTO AllRows (Int1 )

SELECT 21

INSERT INTO AllRows (Int1 )

SELECT 34

INSERT INTO AllRows (Int1 )

SELECT 55

INSERT INTO AllRows (Int1 )

SELECT 89

INSERT INTO AllRows (Int1 )

SELECT 144

INSERT INTO AllRows (Int1 )

SELECT 233

INSERT INTO AllRows (Int1 )

SELECT 377

INSERT INTO AllRows (Int1 )

SELECT 610

INSERT INTO AllRows (Int1 )

SELECT 987

INSERT INTO AllRows (Int1 )

SELECT 1597

INSERT INTO AllRows (Int1 )

SELECT 2584

INSERT INTO AllRows (Int1 )

SELECT 4181

INSERT INTO AllRows (Int1 )

SELECT 6765

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

USE

master

GO

CREATE DATABASE ComplexCertainRowsToChange

GO

USE ComplexCertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 2, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 4, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 8, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 16, 1

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 3, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 5, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 8, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 13, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 21, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 34, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 55, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 89, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 144, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 233, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 377, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 610, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 987, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 1597, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2584, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 4181, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 6765, 1

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

View 3 Replies View Related

Using Specific Criteria For Exact Postal Codes?

Feb 20, 2012

I have to find the number of orders sent to two different postal codes but to get all of the right columns, I have to use 2 different tables.

So, I used WHERE to connect the two tables with the primary keys, but where do I use the specific criteria for the exact postal codes?

View 1 Replies View Related

Repost Along With DDL's And Sample Data.

Mar 6, 2007

Here’s a more in depth breakdown of my problem:

We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is, send that entry along with the result set, if it is not valid, Check that the dongle area code is valid, if dongle area is valid select with the result set, and if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).

I have a table named lnkPBXUser which contains the following:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lnkPBXUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lnkPBXUser]
GO

DDL:
CREATE TABLE [dbo].[lnkPBXUser] (
[RegionalID] [int] NOT NULL ,
[pbxID] [decimal](18, 0) NOT NULL ,
[userID] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

Sample data:
INSERT INTO lnkPBXUser(RegionalID, pbxID, userID)
SELECT 0, 543, 2 UNION ALL
SELECT 0, 10961, 6 UNION ALL
SELECT 0, 1012, 17 UNION ALL
SELECT 0, 16499, 26 UNION ALL
SELECT 0, 14061, 36 UNION ALL
SELECT 0, 16499, 2

I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):

PBXID DailupDT DongleAccessNum CLI RegionalID RegionalDialup
83 8/8/2006 8:58:11 AM T2 UQ 28924 0132493700 0 0800003554
543 8/8/2006 8:55:44 AM T0 UA 33902 0123623500 0 0800003554
1219 8/8/2006 8:59:03 AM T3 ZD 02031 0152958095 0 0800003554
1012 8/8/2006 9:02:54 AM T0 UA 41261 0173011050 0 0800003554
1331 8/8/2006 8:59:57 AM T0 UA 01938 0124604627 0 0800003554
1979 8/8/2006 9:02:52 AM T0 UA 09836 0163751210 0 0800003554
1903 8/8/2006 8:58:41 AM T0 UA 26009 0147175356 0 0800003554
1522 8/8/2006 8:58:54 AM T3 MB 94595 0573912871 0 0800004249
319 8/8/2006 8:51:28 AM T2 ZD 32892 0543375100 0 0800004249
3270 8/8/2006 9:04:26 AM T2 MB 87331 0 0800004249

DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDialupLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDialupLog]
GO

CREATE TABLE [dbo].[tblDialupLog] (
[RegionalID] [int] NOT NULL ,
[PBXID] [int] NULL ,
[DialupDT] [datetime] NULL ,
[DongleAccessNum] [varchar] (64) NULL ,
[CLI] [varchar] (64) NULL ,
[RegionalDialup] [varchar] (50) NULL
) ON [PRIMARY]
GO

Sample data:
INSERT INTO tblDialupLog(PBXID,DailupDT ,DongleAccessNum,CLI,RegionalID,RegionalDialup )
SELECT 83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554' UNION ALL
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2006 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800003554' UNION ALL
SELECT 16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'

You see that the DongleAccessNumber is actually made up of three parts, and it is the middle part (ie.UQ) which i will use to check that the tbldongleArea.DongleAreaCode is valid for that region

I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :

CodeID RegionalID ExtName SubsNDCD LocCD UpdateStatus RegionDesc
7973 1 PRETORIA 012 362 0 NORTH EASTERN REGION
7974 1 HARTEBEESHOEK 012 3012 0 NORTH EASTERN REGION
7975 1 HARTEBEESHOEK 012 3013 0 NORTH EASTERN REGION
7976 1 PRETORIA 017 3014 0 NORTH EASTERN REGION
7977 1 PRETORIA 012 3015 0 NORTH EASTERN REGION

DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCodes]
GO

CREATE TABLE [dbo].[tblCodes] (
[CodeID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[ExtName] [varchar] (64) NULL ,
[SubsNDCD] [varchar] (10) NULL ,
[LocCD] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionDesc] [varchar] (255) NULL
) ON [PRIMARY]
GO

Sample Data:

INSERT INTO tblCodes(CodeID ,RegionalID ,ExtName , SubsNDCD ,LocCD ,UpdateStatus,RegionDesc)
SELECT 7973,1, 'PRETORIA', '012', '362', 0 ,'NORTH EASTERN REGION' UNION ALL
SELECT 7974,1, 'HARTEBEESHOEK ', '012', '3012', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7975,1, 'HARTEBEESHOEK ', '012', '3013', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7976,1, 'PRETORIA', '012', '3014', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7977,1, 'PRETORIA', '017', '3015', 0,'NORTH EASTERN REGION'

I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):

AreaID RegionalID DongleAreaCode AreaDesc UpdateStatus
12 1 UA Oumashoop 0
13 1 UB Pietersburg 0
14 1 UC Warmbad 0 1
15 1 UD Nylstroom 0
16 1 UE Potgietersrus 0
27 1 UF Louis Trichardt 0
28 1 UG Messina 0

DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDongleArea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDongleArea]
GO

CREATE TABLE [dbo].[tblDongleArea] (
[AreaID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[DongleAreaCode] [varchar] (5) NULL ,
[AreaDesc] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL
) ON [PRIMARY]
GO

Sample Data:
INSERT INTO tblDongleArea(AreaID,RegionalID,DongleAreaCode,AreaDesc,UpdateStatus)
SELECT 12,1, 'UA', Oumashoop, 0 UNION ALL
SELECT 13, 1, 'UB', 'Pietersburg', 0 UNION ALL
SELECT 14, 1 ,'UC' ,'Warmbad', 0 UNION ALL
SELECT 15, 1, 'UD', 'Nylstroom', 0 UNION ALL
SELECT 16, 1, 'UE', 'Potgietersrus', 0 UNION ALL
SELECT 27, 1, 'UF', 'Louis Trichardt', 0 UNION ALL
SELECT 28, 1, 'UG', 'Messina', 0

I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:

RegionalID RegionalDialup Region UpdateStatus RegionCode LocalRegion
1 0800003554 North Eastern 0 1 0
2 0800005027 Gauteng 0 2 0
3 0800006194 Eastern 0 3 0
4 0800004249 Central 0 4 0
5 0800201859 Southern 0 5 0
6 0800201989 Western 0 6 0
7 0800113515 HO 1 0 1
8 0800222204 Tellumat 0 7 0

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegionNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegionNumbers]
GO

CREATE TABLE [dbo].[tblRegionNumbers] (
[RegionalID] [int] NOT NULL ,
[RegionalDialup] [varchar] (255) NULL ,
[Region] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionCode] [int] NULL ,
[LocalRegion] [int] NULL ,
) ON [PRIMARY]
GO

INSERT INTO tblRegionNumbers(RegionalID ,RegionalDialup,Region,UpdateStatus,RegionCode,LocalRegion)
SELECT 1,'0800003554', 'North Eastern', 0, 1, 0 UNION ALL
SELECT 2, '0800005027' ,'Gauteng', 0 ,2, 0 UNION ALL
SELECT 3, '0800006194','Eastern', 0, 3, 0 UNION ALL
SELECT 4, '0800004249' ,'Central', 0, 4, 0 UNION ALL
SELECT 5, '0800201859','Southern', 0 ,5, 0 UNION ALL
SELECT 6, '0800201989' ,'Western' 0, 6, 0 UNION ALL
SELECT 0, '0800113515', 'HO', 1, 0, 1 UNION ALL
SELECT 8, '0800222204', 'Tellumat', 0, 7, 0

Ok, I am dealing with the lnkPBXUser table at the moment,

I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)

If it does exist, then it is part of the fourth region and should be returned in the result set.

If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.

If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.

If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.

So from the above tables an expected result would be:

RegionalID pbxID userID
0 1012 17
0 543 2

Please assist, it would be greatly appreciated.
Regards
SQLJunior

View 20 Replies View Related

Extract Specific Word From String

Oct 15, 2015

I have a values a column like this

Morning(07:00-16:30)
Morning(09:30-18:30)
Morning(11:00-20:00)
Afternoon(14:00-23:00)
Afternoon(16:00-01:00)
Morning(08:00-17:00)
Morning(10:00-19:00)
Morning(09:00-18:00)

So i just only need 07:00-16:30 this kind of part from above all these string in sql server.

View 4 Replies View Related

Transact SQL :: Extract ID And Specific Part Of A String From Column

Jun 10, 2015

I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.

I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)

The row sample looks like the following

I have to extract the Id and a part of the column from mystring.

Id      MyString
1      ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|

[Code] ....

The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following

Id      DesiredString
1      Extracts^This^String1
2      Extracts^This^String2
3      Extracts^This^String3

Is there a way to extract this either using TSQL or SSIS.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,

[Code] ....

View 4 Replies View Related

Extract Specific Words From A Free Format String

Feb 27, 2008

I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.

My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.

The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.

So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.

I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.

I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.

View 4 Replies View Related

Transact SQL :: Sequence Of Characters - Extract Some Specific Values

May 13, 2015

I've the following data:

<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>

And my goals is to the extract the following example:

11,12, 15, 18

How Can I do That?

View 6 Replies View Related

SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?

Jun 30, 2015

We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.

Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?

View 2 Replies View Related

Assign Specific Data To Specific Users

Mar 21, 2008

I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?

View 1 Replies View Related

Recurring Meeting On Multiple Days UGH!

Nov 10, 2004

Ugh!

I need to create some SQL that will generate all the dates for a recurring meeting on multiple days per week.

For example:
a meeting on Monday , Wednesday and Friday at 10:00 am - 10:30 am from December 1st 2004 to Feb 28th 2005.

I'm not sure what to do here.

Any Ideas?

Regards
Russ

View 1 Replies View Related

Best Practice Question: JOIN Criteria Vs. WHERE Criteria

May 24, 2004

For example, consider the following queries:

DECLARE @SomeParam INT
SET @SomeParam = 44

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam

Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?

Thanks.

View 3 Replies View Related

CD -- RePost

Aug 4, 2006

I posted the following back in May and received the reply below.

I asked again in July, but got no response so I assume that wasn't the way to handle it ...

So -- here is the RePost & Reply.

Question is still open: Is the CD ready yet ?/

Roger

===========================================

Can I get: SQL Express with Advanced Services on a CD (or DVD)

I'm on DialUp so a download would take 24hr+.

I was able to get the MS .NET Framework 2.0 on DVD because an MVP took pity on me and was kind enough to give me the "Secret" URL to the correct Order Desk.

I'm hoping one of you knows the URL for this CD/DVD

Roger

=========================================

Hi Roger,

We're working on CD images for SQL Server Express with Advanced Services in all the languages that SQL Server usually ships in. The English version is in final testing and should be available in a couple of weeks.

Thanks

Lead Program Manager, Microsoft SQL Server Storage Engine

View 1 Replies View Related

T-SQL XML Experience??? (repost)

Nov 14, 2007

Hi all,
I have posted a thread on the XML forum but its not getting much traction there so I'm posting a link to it from here hoping that more people will pick it up. Hope that's OK.

I have a problem with a T-SQL query involving XML that is taking FAR too long to run.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2404607&SiteID=1&mode=1

-Jamie

View 1 Replies View Related

Extract Outlook Contacts Data(on Public Directory) Directly In Data Flow

Jun 13, 2006

Hi everyone,

I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?

Need help desperatly !!!

View 3 Replies View Related

Data Flow Task Error To Extract Data From Sql Server To Excel

Mar 28, 2008

Hi All,

I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.

The SQL datable fileds are
i) ID - Int

ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)

While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.

After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.

Please do help me out.

thanks
Sanra

View 4 Replies View Related

Repost : Using Forms With Sql Server

Jul 23, 2005

Howdy,Does anyone know of any packages that you can create a form and via aODBC connection save the data to a table ?If it is possible to compile the form as I don't want clients to changethe forms ?Any idea/thoughts would be most welcome ?RegardsAndrew

View 4 Replies View Related

Excluding Data For Certain Criteria Only

Feb 6, 2012

I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.

Here's an example of my data:

No code has to be inserted here.
No code has to be inserted here.

No code has to be inserted here.
No code has to be inserted here.

Right now my SQL is this:

Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF

INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd

WHERE PR04PTF.EmployeeID=441

View 3 Replies View Related

REPOST: New SQL Server Registration Failure

Feb 23, 2004

Hi,

I have 3 SQL Servers running here. Here are their configurations:

SERVER 1:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 2:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4

SERVER 3:
OS: Windows XP Professionnal
SQL: SQL Server 2000 SP 3

Now, as you can see, SERVER 1 and SERVER 2 have identical configurations. Plus, both will accept Windows Authentication and SQL Authentication.

If I try to add a new Registration from SERVER 3 to SERVER 1, it works fine but from SERVER 3 to SERVER 2 it doesn't. I always get an error (SQL Server does not exist or access denied). But, using the exact same user name and password, a connection can be established from SERVER 1 to SERVER 2 and vice-versa. Only when trying to connect froms SERVER 3 to SERVER 2 fails.

Any ideas?

Thanks,

Skip.

View 1 Replies View Related

Retrieving Data Based On Criteria

May 31, 2008

Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;

dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.

dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.

dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1

dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2

What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.

Code:

SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM

View 1 Replies View Related

Retrieving Data Based On Criteria

May 31, 2008

Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;

dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.

dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.

dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1

dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2

What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.

SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM

View 2 Replies View Related

Get Ditinct Data If All 3 Criteria Is Satisfied?

Feb 11, 2008

Hi

I have table with 25 colums. 3 of the colums(Chkflag,BMCHECK,UPDATED) have yes/no data type.
What I am trying to do :
If chkflag is No value (i.e 0) and BMCHECK or UPDATED has no value then bring one of the field from BMCHECK.

quote:SELECT DISTINCT BMCHECK FROM FEEDER WHERE Chkflag = 0 AND BMCHECK = 0 OR UPDATED = 0

I am using the above query in vb.net to look for any of above field is blank or not ticked if the query brings any data THEN
---run the other queries
else (There is no data)
---do this---

Advance thanks

View 5 Replies View Related

Data Type Mismatch On Criteria

Oct 8, 2007

I have a data flow that is updating an Access database using an OLD DB Command control. I am getting this error and have narrowed it down to a column the Access table called CreateDate. I don't think this is a reserved word, but even surrounding it in [] did not resolve the problem. The column from SQL Server is called order_date and is a datetime and the destination column createdate is a datetime in Access. When I remove this column fromt he insert command, it works fine but when included, it gives the data type mismatch on criteria error. Any ideas?

View 3 Replies View Related

Repost: DTS -- Insert Into Oracle Is SLOW. Any Tips?

Dec 16, 2001

Originally posted by Jeremy at 12/10/2001 11:39:38 AM

Hello all,

I've written a simple dts job that uses oracle (8.x) as a source and oracle (8.x) as a destination. I'm using SQL 2000 and Microsoft's oledb provider for oracle as the two connections. I've chosen "Transform Data Task" with the following SQL "SELECT * FROM REPORTER_STATUS
WHERE LASTOCCURRENCE > TRUNC(SYSDATE)". As you can see, it's very simple, however it's very very very slow. (averages about 1000 rows per minute). In my column transformations, I've selected many to many versus the one to one. There are no activex scripts or anything along those lines. Just a simple push of the data from one oracle box to the other. The table schemas are identical etc... I've had this problem before with writing to Oracle and I can't imagine that it's really supposed to be this slow. If you need more details, please just let me know.

Thank you,

Jeremy

-----------------------------More -------------------------------

The official response from microsoft is that dts only allows for single inserts... not bulk or bcp for oracle. There must be someone out there who has figured out how to configure / modify / call (something) from a dts pacakage to insert millions of records into Oracle in a decent time frame...

thnx again..

View 2 Replies View Related

Repost: 00000 Display On Upgrade To Windows XP

Jul 20, 2005

I posted this under 'microsoft.public.sqlserver.client' but got no reply.Any help with this problem would be greatly appreciated---------------------I developed a database under SQL Server 2000, with Access 2000 on Windows2000 as client. This had been running fine for several years. The client isnow upgrading to Windows XP, and has come across a display problem on thereports. The figures are correct but are now displayed with lots of trailingzeros e.g365.00000I have built a test system but cannot duplicate the problem :-( . Also theproblem varies between computers, and even between different users on thesame computer. One solution that seems to work is to explicitly cast alloutput from the server e.g.CAST ( TonsLoaded AS INT) TonsLoadedbut this is a pita since there are 141 stored procedures.Has anyone seen this problem? I haven't found anything in the newsgroups orany Knowlege Base article. It looks like it is to do with Windows XP as theclient ...David

View 5 Replies View Related

Truncate All Raw Files In A Given Folder (Repost As Requested)

Mar 30, 2007

I am looking for a way to truncate raw files without losing the metadata. The metadata of the raw file should be automatically detected at run-time. The result will be a raw file with the same metadata as the original file, but no data in it.

There are two reasons I would like such a tool. First, I want to erase the potentially sensitive data stored in the raw files. Second, I want to keep Validation enabled so that development is simpler.

Is anyone aware of such a tool?

Thanks,

View 4 Replies View Related

Data Type Mismatch In Criteria Expression, Help~

Feb 20, 2005

Error in Explorer:
Data type mismatch in criteria expression.

Following codings:

Dim lowestPrice As Double
Dim highestPrice As Double

lowestPrice = FormatCurrency(txtLow.Text, 2)
highestPrice = FormatCurrency(txtHigh.Text, 2)

lblLabel.Text = lowestPrice

.
.

"UNION " & _
"SELECT p.ProductID, p.ProductTitle FROM Product p " & _
"WHERE (p.Price > '" & FormatCurrency(lowestPrice, 2) & "' AND p.Price < '" & FormatCurrency(highestPrice, 2) & "') " & _
"ORDER BY p.ProductTitle"

I don't know where the error goes wrong in here.. previously because of the union missing one spacing that resulted in syntax error, after i inserted a space to it.. it shows me Data type mismatch the criteria expression. Is it because in my sql coding i cant use FormatCurrency for ASP.net? please give me a hand.. thank you

Contact me at: ryuichi_ogata86@hotmail.com
ICQ me at: 18750757

View 1 Replies View Related

Data Type Mismatch In Criteria Expression In ASP

Jun 20, 2007

Hi,

I need help in ASP for this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/advice generation/testdateprint.asp, line 371

Code is as :

crtdt = #27/04/2007# ' date in DD/MM/YYYY format

and in database its date format is MM/DD/yyyy

set objrs=server.CreateObject("ADODB.Recordset")

set unitrs=server.CreateObject("ADODB.Recordset")

set userrs=server.CreateObject("ADODB.Recordset")

tsql = "SELECT * From advice_register WHERE "

tsql = tsql & " user_id ='" & Session("UserID") & "'"

tsql = tsql & " and fin_year ='" & CStr(Request.QueryString("fin_year")) & "' and "

tsql = tsql & "created_on ='" & (crtdt) &"'"

objrs.Open tsql,objconn, 1,3

View 7 Replies View Related

Data Type Mismatch In Criteria Expression. Please Help

Mar 14, 2008

<% @codepage=950%>


<%
sid = "1"
Dim Connect, RS, Query
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "81231888-katiga"
Set RS = Server.CreateObject("ADODB.Recordset")

Query = "SELECT * FROM lunch_other where LOid = '"& sid &"'"

response.write Query

RS.Open Query, Connect, adOpenDynamic, adLockOptimistic

'if rs.eof then

response.write "Testing OK!!"

'Else
response.write trim(rs("LOid"))
response.write trim(rs("LMenu"))

connect.close
end if
%>

Error Result

SELECT * FROM lunch_other where LOid = '1'

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/lunchset/test3.asp, line 16

View 4 Replies View Related

Extract Data From DB2

Jan 22, 2008

I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV

SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
FROM table1
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM

View 2 Replies View Related