Outlook Data as a Source

We have a few legacy Crystal Reports that we have written that group,
calculate, etc. Outlook Calendar and conctact information. We are looking to
replace these with SQL Reports, but when I looked at my connection info under
SQL Reporting, I did not see anything that can effectively query Outlook
Calendar and Contact information. Does anyone know how I can effectively
connect to Outlook Calendar and Contact information as a source for my
reports?Hi,
You can use an OLEDB connection string to connect to Outlook/Exchange:
<code>
Outlook
9.0;MAPILEVEL="";Provider=Microsoft.Jet.OLEDB.4.0;TABLETYPE=0;DATABASE=C:\Temp\
</code>
With this connection you can simple query the contacts: <code>select * from
Contacts</code>. I haven't been able to query the calendar yet, but you can
try using MS Access to create a link to Outlook and look for that properties.
Hope this would help you.
Jan Pieter Posthuma
"Jack Bender" wrote:
> We have a few legacy Crystal Reports that we have written that group,
> calculate, etc. Outlook Calendar and conctact information. We are looking to
> replace these with SQL Reports, but when I looked at my connection info under
> SQL Reporting, I did not see anything that can effectively query Outlook
> Calendar and Contact information. Does anyone know how I can effectively
> connect to Outlook Calendar and Contact information as a source for my
> reports?

outer join

Hello,

I am having problems with an outer join statement.
I have written a procedure that tests a table for missing and corrupt data and
to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did.
To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:

select o.*,'#',k.* from repaired o right outer join copy k on
(str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) )
where
o.D<>k.D or
o.E<>k.E or
o.F<>k.F or
...

I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy".
Before that it looked like:

... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...

In table "copy" is a record missing that is in table "repaired".
Why is my statement not printingout that missing record?
Shouldn't be an outer join exactly what I have to use for finding missing data?

I anybody can help me, I would be very happy.

SvenIn table "copy" is a record missing that is in table "repaired".try LEFT OUTER JOIN instead ;)|||Your first join attempt: ... on (o.a=k.a and o.b=o.b and o.c = k.c) where ... joins o.b on itself. This sort of typo happens with the gratuitous use of table aliases.
Use a left outer join, as Rudy has suggested.
Try using the binary_checksum value rather than listing all your columns in the where clause:select repaired.*,
'#',
copy.*
from --repaired
(select binary_checksum(*) checkvalue,
repaired.*
from repaired) repaired
left outer join --copy
(select binary_checksum(*) checkvalue,
repaired.*
from copy) copy
on repaired.a = copy.a
and repaired.b = copy.b
and repaired.c = copy.c
where repaired.checkvalue <> copy.checkvalue

Our Application dosn't work with Sql Service pack 3

Hi , My name is Tomas
I work for a Software Developing Company ,
Our applications has been written and developed by
Power Builder 5.0 , and they can work with Sql Server 2000
normally , but when I install service pack 3.0 on our sql
server after that our application can't work with sql
server correctly , for example some queries doesn't run
and there is no result for them after installing service
pack 3,,
Please Help MeHi
First thing you need to do is run profiler on the server to see what queries
are being submitted. then pick some of those queries and run them in Query
Analyzer. In that situation, you will be able to tell if your code is
running with errors.
If your application is using more than 1 database at the same time, look at
the "Cross-Database Ownership Chaining" in books online. This was a change
introduced by SP3.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Tomas" <anonymous@.discussions.microsoft.com> wrote in message
news:01a101c3d429$41ba6c70$a001280a@.phx.gbl...
> Hi , My name is Tomas
> I work for a Software Developing Company ,
> Our applications has been written and developed by
> Power Builder 5.0 , and they can work with Sql Server 2000
> normally , but when I install service pack 3.0 on our sql
> server after that our application can't work with sql
> server correctly , for example some queries doesn't run
> and there is no result for them after installing service
> pack 3,,
> Please Help Me
>|||you should really consider upgrading powerbuilder. when did 5.0 come
out, the mid 90's?
Tomas wrote:
> Hi , My name is Tomas
> I work for a Software Developing Company ,
> Our applications has been written and developed by
> Power Builder 5.0 , and they can work with Sql Server 2000
> normally , but when I install service pack 3.0 on our sql
> server after that our application can't work with sql
> server correctly , for example some queries doesn't run
> and there is no result for them after installing service
> pack 3,,
> Please Help Me|||Especially since PB5 is no longer supported much less supported for use with
SQL2000. We're using PB9 and it works great with SQL2K.
Mike Kruchten
"ch" <ch@.dontemailme.com> wrote in message
news:3FFB0C2E.A65CF347@.dontemailme.com...
> you should really consider upgrading powerbuilder. when did 5.0 come
> out, the mid 90's?
>
> Tomas wrote:
> > Hi , My name is Tomas
> > I work for a Software Developing Company ,
> > Our applications has been written and developed by
> > Power Builder 5.0 , and they can work with Sql Server 2000
> > normally , but when I install service pack 3.0 on our sql
> > server after that our application can't work with sql
> > server correctly , for example some queries doesn't run
> > and there is no result for them after installing service
> > pack 3,,
> > Please Help Me
>

other possibilities than cursor

I've written a set of stored procedures that currently use a cursor to pull
data from a staging table into two separate tables. The two destination
tables are linked by a Foreign Key relationship on the primary key (can't
change this design), which is why I need to use a cursor to be able to inser
t
records into both tables.
I was wondering whether there is any other possible solution to using a
cursor to insert records into the two tables?Another thing I forgot to mention is that I'm generating new
uniqueidentifiers within the cursor for each new record as primary keys for
the inserted records (there are no primary keys within the staging table)
such that destination_table1.accountId = destination_table2.accountId
"EL" wrote:

> I've written a set of stored procedures that currently use a cursor to pul
l
> data from a staging table into two separate tables. The two destination
> tables are linked by a Foreign Key relationship on the primary key (can't
> change this design), which is why I need to use a cursor to be able to ins
ert
> records into both tables.
> I was wondering whether there is any other possible solution to using a
> cursor to insert records into the two tables?|||Can you give the columns in the input tables
and what all needs to go to the first and second table?
--
"EL" wrote:
> Another thing I forgot to mention is that I'm generating new
> uniqueidentifiers within the cursor for each new record as primary keys fo
r
> the inserted records (there are no primary keys within the staging table)
> such that destination_table1.accountId = destination_table2.accountId
> "EL" wrote:
>|||Can you post CREATE TABLE statements, and a couple of lines of sample
data; that would be helpful for those of us who want to copy and paste
the code into our own query analyzers to test. Just off the top of my
head, can you not use temp tables or table variables to pre-generate
the uniqueidentifiers on the parent and child tables, and then insert
the parent rows followed by the child rows?
Without trying to open up a whole can of worms on the issue of
surrogate vs natural keys, why are you using uniqueidentifiers?
Although there are times when the uniqueidentifier is necessary, it has
some consequences.
Stu|||Thanks for youre reply omnibuzz.
I also forgot to mention that I would be inserting into 3 separate tables.
I'll explain them later in this message.
Here's a subset of the columns that exist in the single staging table:
TTC_Agency_Id int null,
Agency_Name varchar(150) NULL,
Agency_Addr1 varchar(150) NULL,
Agency_Addr2 varchar(150) NULL,
Agency_Addr3 varchar(150) NULL,
Agency_City varchar(150) NULL,
Agency_State varchar(150) NULL,
Agency_Zip varchar(150) NULL,
Agency_Country varchar (150) NULL,
Agency_IATA_Number varchar(25) NULL,
Agency_Email varchar(100) NULL,
Agency_Barred varchar(10) NULL,
Agency_Fax varchar (50) NULL,
Agency_Phone varchar(50) NULL
This staging table contains various other columns used by other stored
procedures which are more or less similar in their function. If I was
currently running the procedure that pulls all the data contained in the
Agency_... columns from the staging table then the procedure would be doing
the following (pseudo code):
create cursor from query that pulls Agency_... column data from staging tabl
e
Within Cursor:
Create uniqueidentifier value (Primary Key) -- call this newid
Create uniqueidentifier value (Address Primary Key) -- call this
addressId
Insert into table1 values (newid, Agency_Name, Agency_IATA,
ttc_agency_id)
Insert into table2 values (newid, Agency_Email, Agency_Barred,
Agency_Fax, Agency_Phone)
Insert into table3 values(addressId, newid, Agency_Addr1,
Agency_Addr2, Agency_Addr3, Agency_City, Agency_State, Agency_Zip,
Agency_Country)
close cursor
Table1 and table2 are the main destination tables with a foreign key
relationship on the primary keys (table1.accountId = table2.accountId) which
is why I create a new id for the agency inside the cursor.
Table3 is another table that holds address details (in this case holds the
agency address details)
I thought about putting a uniqueidetifier column within my source / staging
table that gets populated when data is loading into that table and use that
value for the primary keys of the destination tables (table1 and table2), bu
t
that would mean that I would no longer be able to perform bulk inserts into
that table.
"Omnibuzz" wrote:
> Can you give the columns in the input tables
> and what all needs to go to the first and second table?
> --
>
>
> "EL" wrote:
>|||Here's the script for the staging table that I use (simplified down)
Create table TempBookings (
Agency_Name varchar(150) NULL,
Agency_Addr1 varchar(150) NULL,
Agency_Addr2 varchar(150) NULL,
Agency_Addr3 varchar(150) NULL,
Agency_City varchar(150) NULL,
Agency_State varchar(150) NULL,
Agency_Zip varchar(150) NULL,
Agency_Country varchar (150) NULL,
Agency_IATA_Number varchar(25) NULL,
Agency_Email varchar(100) NULL,
Agency_Barred varchar(10) NULL,
Agency_Fax varchar (50) NULL,
Agency_Phone varchar(50) NULL,
Agency_Code varchar (30) NULL,
TTC_Agency_Id int NULL
)
Here's some insert sql code for inserting values into the staging table
insert into TempBookings values ('Test 1', 'Test_1_Addr1', 'Test_1_Addr2',
'Test_1_Addr3', 'Test_1_City', 'Test_1_State', 'Test_1_Zip',
'Test_1_Country', 'Test_1_IATA', 'Test_1_Email@.Test.com', '1', '12345678',
'12345689', 'Test_1_Code', 50)
insert into TempBookings values ('Test 2', 'Test_2_Addr1', 'Test_2_Addr2',
'Test_2_Addr3', 'Test_2_City', 'Test_2_State', 'Test_2_Zip',
'Test_2_Country', 'Test_2_IATA', 'Test_2_Email@.Test.com', '1', '12345678',
'12345689', 'Test_2_Code', 52)
insert into TempBookings values ('Test 3', 'Test_3_Addr1', 'Test_3_Addr2',
'Test_3_Addr3', 'Test_3_City', 'Test_3_State', 'Test_3_Zip',
'Test_3_Country', 'Test_3_IATA', 'Test_3_Email@.Test.com', '1', '12345678',
'12345689', 'Test_3_Code', 65)
insert into TempBookings values ('Test 4', 'Test_4_Addr1', 'Test_4_Addr2',
'Test_4_Addr3', 'Test_4_City', 'Test_4_State', 'Test_4_Zip',
'Test_4_Country', 'Test_4_IATA', 'Test_4_Email@.Test.com', '1', '12345678',
'12345689', 'Test_4_Code', 67)
insert into TempBookings values ('Test 5', 'Test_5_Addr1', 'Test_5_Addr2',
'Test_5_Addr3', 'Test_5_City', 'Test_5_State', 'Test_5_Zip',
'Test_5_Country', 'Test_5_IATA', 'Test_5_Email@.Test.com', '1', '12345678',
'12345689', 'Test_5_Code', 68)
insert into TempBookings values ('Test 6', 'Test_6_Addr1', 'Test_6_Addr2',
'Test_6_Addr3', 'Test_6_City', 'Test_6_State', 'Test_6_Zip',
'Test_6_Country', 'Test_6_IATA', 'Test_6_Email@.Test.com', '1', '12345678',
'12345689', 'Test_6_Code', 69)
insert into TempBookings values ('Test 7', 'Test_7_Addr1', 'Test_7_Addr2',
'Test_7_Addr3', 'Test_7_City', 'Test_7_State', 'Test_7_Zip',
'Test_7_Country', 'Test_7_IATA', 'Test_7_Email@.Test.com', '1', '12345678',
'12345689', 'Test_7_Code', 70)
insert into TempBookings values ('Test 8', 'Test_8_Addr1', 'Test_8_Addr2',
'Test_8_Addr3', 'Test_8_City', 'Test_8_State', 'Test_8_Zip',
'Test_8_Country', 'Test_8_IATA', 'Test_8_Email@.Test.com', '1', '12345678',
'12345689', 'Test_8_Code', 71)
insert into TempBookings values ('Test 9', 'Test_9_Addr1', 'Test_9_Addr2',
'Test_9_Addr3', 'Test_9_City', 'Test_9_State', 'Test_9_Zip',
'Test_9_Country', 'Test_9_IATA', 'Test_9_Email@.Test.com', '1', '12345678',
'12345689', 'Test_9_Code', 72)
insert into TempBookings values ('Test 10', 'Test_10_Addr1',
'Test_10_Addr2', 'Test_10_Addr3', 'Test_10_City', 'Test_10_State',
'Test_10_Zip', 'Test_10_Country', 'Test_10_IATA', 'Test_10_Email@.Test.com',
'1', '12345678', '12345689', 'Test_10_Code', 73)
The scripts below have been generated by scripting the destination tables
involved in one of the storedprocedures
CREATE TABLE [dbo].[AccountBase] (
[AccountId] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (160) COLLATE Latin1_General_CI_AS NULL ,
[AccountNumber] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[EMailAddress1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Telephone1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Fax] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[AccountExtensionBase] (
[AccountId] [uniqueidentifier] NOT NULL ,
[New_AccountStatus] [int] NULL ,
[New_TTC_Id] [int] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountBase] WITH NOCHECK ADD
CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED
(
[AccountId]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD
CONSTRAINT [PK_AccountExtensionBase] PRIMARY KEY CLUSTERED
(
[AccountId]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [ndx_for_cascaderelationship_account_par
ent_account] ON
[dbo].[AccountBase]([ParentAccountId]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAddressBase] (
[ParentId] [uniqueidentifier] NOT NULL ,
[CustomerAddressId] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressNumber] [int] NULL ,
[Name] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Line1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Line2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Line3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[StateOrProvince] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PostalCode] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddressBase] WITH NOCHECK ADD
CONSTRAINT [cndx_PrimaryKey_CustomerAddress] PRIMARY KEY CLUSTERED
(
[CustomerAddressId]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [ndx_for_cascaderelationship_Contact_Cus
tomerAddress] ON
[dbo].[CustomerAddressBase]([ParentId]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
"Stu" wrote:

> Can you post CREATE TABLE statements, and a couple of lines of sample
> data; that would be helpful for those of us who want to copy and paste
> the code into our own query analyzers to test. Just off the top of my
> head, can you not use temp tables or table variables to pre-generate
> the uniqueidentifiers on the parent and child tables, and then insert
> the parent rows followed by the child rows?
> Without trying to open up a whole can of worms on the issue of
> surrogate vs natural keys, why are you using uniqueidentifiers?
> Although there are times when the uniqueidentifier is necessary, it has
> some consequences.
> Stu
>|||Here's my stored procedure (rather the cursor) that I use to insert the
records into the destination tables from the staging table. I've simplified
it down quite heavily just for this example, and so it may not work correctl
y
declare @.Name varchar(150)
declare @.AddressLine1 varchar(50)
declare @.AddressLine2 varchar(50)
declare @.AddressLine3 varchar(50)
declare @.City varchar(50)
declare @.State varchar(50)
declare @.Zip varchar(50)
declare @.Country varchar(50)
declare @.IATA varchar(30)
declare @.AgencyEmail varchar(100)
declare @.New_AccountStatusNew varchar(5)
declare @.NewId uniqueidentifier
declare @.AddressId uniqueidentifier
declare TravelAgenciesCursor Cursor LOCAL FAST_FORWARD for
select distinct Agency_Name, Agency_Addr1, Agency_Addr2, Agency_addr3,
Agency_City, Agency_State, Agency_Zip, Agency_Country,
Agency_IATA_Number, Agency_Email, Agency_Barred Agency_Fax, TTC_Agency_Id,
Agency_Phone
from TempBooking
open TravelAgenciesCursor
Fetch next from TravelAgenciesCursor into @.Name, @.AddressLine1,
@.AddressLine2, @.AddressLine3, @.City,
@.State, @.Zip, @.Country, @.IATA, @.AgencyEmail, @.New_AccountStatusNew,
@.AgencyFax, @.TTC_Id, @.Phone
While (@.@.Fetch_Status <> -1)
Begin
if (@.@.Fetch_Status <> -2)
Begin
-- Set a new Consortium Id every time new insert and get agency type value
and the current date
set @.NewId = NewId()
-- Inserts sets for inserting Agencies
Insert into AccountBase (AccountId, Name, AccountNumber, EMailAddress1,
Telephone1, Fax) values
(@.NewId, @.Name, @.IATA, @.AgencyEmail, @.Phone, @.Fax)
insert into AccountExtensionBase (AccountId, New_AccountStatus,
New_TTC_Id) values
(@.NewId, 1, @.TTC_Id)
-- insert a new record for the customer
insert into CustomerAddressBase (ParentId, CustomerAddressId,
AddressNumber, Name, Line1, Line2, Line3,
City, StateOrProvince, Country, PostalCode) values
(@.NewId, NewId(), 1, @.Name, @.AddressLine1, @.AddressLine2, @.AddressLine3,
@.City, @.State, @.Country, @.Zip)
End
Fetch next from TravelAgenciesCursor into @.Name, @.AddressLine1,
@.AddressLine2, @.AddressLine3, @.City,
@.State, @.Zip, @.Country, @.IATA, @.AgencyEmail, @.New_AccountStatusNew,
@.AgencyFax, @.TTC_Id, @.Phone
End
Close TravelAgenciesCursor
deallocate TravelAgenciesCursor
As regards the use of the temp tables of table variables, I did write a
version of the same stored procedure using a table variable instead of a
cursor, but in most of the cases the query optimizer either showed either
very little or no improvement or worse query performance using the table
variable.
I'm guessing the use of a uniqueidentifier as the primary key was to
maintain global uniqueness for each record, which in this situation isn't
really necessary. I would have wanted to use an int value instead but making
any changes to the table or database design is out of the question.
"EL" wrote:
> Here's the script for the staging table that I use (simplified down)
> Create table TempBookings (
> Agency_Name varchar(150) NULL,
> Agency_Addr1 varchar(150) NULL,
> Agency_Addr2 varchar(150) NULL,
> Agency_Addr3 varchar(150) NULL,
> Agency_City varchar(150) NULL,
> Agency_State varchar(150) NULL,
> Agency_Zip varchar(150) NULL,
> Agency_Country varchar (150) NULL,
> Agency_IATA_Number varchar(25) NULL,
> Agency_Email varchar(100) NULL,
> Agency_Barred varchar(10) NULL,
> Agency_Fax varchar (50) NULL,
> Agency_Phone varchar(50) NULL,
> Agency_Code varchar (30) NULL,
> TTC_Agency_Id int NULL
> )
> Here's some insert sql code for inserting values into the staging table
> insert into TempBookings values ('Test 1', 'Test_1_Addr1', 'Test_1_Addr2',
> 'Test_1_Addr3', 'Test_1_City', 'Test_1_State', 'Test_1_Zip',
> 'Test_1_Country', 'Test_1_IATA', 'Test_1_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_1_Code', 50)
> insert into TempBookings values ('Test 2', 'Test_2_Addr1', 'Test_2_Addr2',
> 'Test_2_Addr3', 'Test_2_City', 'Test_2_State', 'Test_2_Zip',
> 'Test_2_Country', 'Test_2_IATA', 'Test_2_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_2_Code', 52)
> insert into TempBookings values ('Test 3', 'Test_3_Addr1', 'Test_3_Addr2',
> 'Test_3_Addr3', 'Test_3_City', 'Test_3_State', 'Test_3_Zip',
> 'Test_3_Country', 'Test_3_IATA', 'Test_3_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_3_Code', 65)
> insert into TempBookings values ('Test 4', 'Test_4_Addr1', 'Test_4_Addr2',
> 'Test_4_Addr3', 'Test_4_City', 'Test_4_State', 'Test_4_Zip',
> 'Test_4_Country', 'Test_4_IATA', 'Test_4_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_4_Code', 67)
> insert into TempBookings values ('Test 5', 'Test_5_Addr1', 'Test_5_Addr2',
> 'Test_5_Addr3', 'Test_5_City', 'Test_5_State', 'Test_5_Zip',
> 'Test_5_Country', 'Test_5_IATA', 'Test_5_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_5_Code', 68)
> insert into TempBookings values ('Test 6', 'Test_6_Addr1', 'Test_6_Addr2',
> 'Test_6_Addr3', 'Test_6_City', 'Test_6_State', 'Test_6_Zip',
> 'Test_6_Country', 'Test_6_IATA', 'Test_6_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_6_Code', 69)
> insert into TempBookings values ('Test 7', 'Test_7_Addr1', 'Test_7_Addr2',
> 'Test_7_Addr3', 'Test_7_City', 'Test_7_State', 'Test_7_Zip',
> 'Test_7_Country', 'Test_7_IATA', 'Test_7_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_7_Code', 70)
> insert into TempBookings values ('Test 8', 'Test_8_Addr1', 'Test_8_Addr2',
> 'Test_8_Addr3', 'Test_8_City', 'Test_8_State', 'Test_8_Zip',
> 'Test_8_Country', 'Test_8_IATA', 'Test_8_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_8_Code', 71)
> insert into TempBookings values ('Test 9', 'Test_9_Addr1', 'Test_9_Addr2',
> 'Test_9_Addr3', 'Test_9_City', 'Test_9_State', 'Test_9_Zip',
> 'Test_9_Country', 'Test_9_IATA', 'Test_9_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_9_Code', 72)
> insert into TempBookings values ('Test 10', 'Test_10_Addr1',
> 'Test_10_Addr2', 'Test_10_Addr3', 'Test_10_City', 'Test_10_State',
> 'Test_10_Zip', 'Test_10_Country', 'Test_10_IATA', 'Test_10_Email@.Test.com'
,
> '1', '12345678', '12345689', 'Test_10_Code', 73)
>
> The scripts below have been generated by scripting the destination tables
> involved in one of the storedprocedures
> CREATE TABLE [dbo].[AccountBase] (
> [AccountId] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (160) COLLATE Latin1_General_CI_AS NULL ,
> [AccountNumber] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [EMailAddress1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
> [Telephone1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Fax] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AccountExtensionBase] (
> [AccountId] [uniqueidentifier] NOT NULL ,
> [New_AccountStatus] [int] NULL ,
> [New_TTC_Id] [int] NULL ,
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AccountBase] WITH NOCHECK ADD
> CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED
> (
> [AccountId]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD
> CONSTRAINT [PK_AccountExtensionBase] PRIMARY KEY CLUSTERED
> (
> [AccountId]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE INDEX [ndx_for_cascaderelationship_account_par
ent_account] ON
> [dbo].[AccountBase]([ParentAccountId]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerAddressBase] (
> [ParentId] [uniqueidentifier] NOT NULL ,
> [CustomerAddressId] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [AddressNumber] [int] NULL ,
> [Name] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Line1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Line2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Line3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [City] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [StateOrProvince] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Country] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [PostalCode] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerAddressBase] WITH NOCHECK ADD
> CONSTRAINT [cndx_PrimaryKey_CustomerAddress] PRIMARY KEY CLUSTERED
> (
> [CustomerAddressId]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
> CREATE INDEX [ndx_for_cascaderelationship_Contact_Cus
tomerAddress] ON
> [dbo].[CustomerAddressBase]([ParentId]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
> "Stu" wrote:
>|||The design and your specification doesn't give much hope of preserving
integrity. If you are forced to accept it you may just have to make the
best of a bad job. The following is an example although probably not a
very good one. A good solution depends on whether there is some
uniqueness in the source data that can be preserved even despite the
missing keys. From your sample data I can only guess
(Agency_IATA_Number for example?).
I didn't read your code to identify which columns mapped to which, I
just guessed. Hopefully this will give you some ideas.
What I'd also do is document the fact that the model may make a
nonsense of the data. Unfortunately it's often not until the business
customers suffer that IT departments take note of data quality issues.
INSERT INTO dbo.AccountBase
(AccountId, Name, AccountNumber, EMailAddress1, Telephone1, Fax)
SELECT NEWID(), MAX(Agency_Name), Agency_IATA_Number,
MAX(Agency_Email), MAX(Agency_Phone), MAX(Agency_Fax)
FROM dbo.TempBookings
GROUP BY Agency_IATA_Number ;
INSERT INTO dbo.AccountExtensionBase (AccountId, New_AccountStatus,
New_TTC_Id)
SELECT A.AccountId, 0, MIN(T.TTC_Agency_Id)
FROM dbo.TempBookings AS T
JOIN dbo.AccountBase AS A
ON T.Agency_IATA_Number = A.AccountNumber
GROUP BY A.AccountId ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||try this.. hope this helps.
-- setting up the staging table for my example
create table #staging (a int, b int)
insert into #staging values(1,1)
insert into #staging values(1,3)
insert into #staging values(2,1)
insert into #staging values(1,2)
insert into #staging values(2,3)
insert into #staging values(3,3)
-- till here was the setup to the staging table
alter table #staging add c uniqueidentifier not null constraint df1 default
newid()
--do you bulk insert here.. now that you have the guid
-- get your staging table back to its original form
alter table #staging drop constraint df1
alter table #staging drop column c
-- this is for my example :)
drop table #staging|||OK,
So based off your stored procedure, all of your relationships between
entities are 1-to-1. You have two choices; either alter your staging
table AFTER your bulk insert in order to add a uniqueidentifier for
your data, OR move the data from your tempstaging table to a second
staging table with the uniqueidentifier already inserted.
Assuming that you want to do the former (should perform the best), you
can do something like this (sorry for the pseudo-code; running out the
door to work):
CREATE proc...
BEGIN TRANSACTION
ALTER TABLE TempStaging
ADD UNID uniqueidentifier DEFAULT newid WITH VALUES
INSERT INTO table1
SELECT table 1 columns
INSERT INTO table2...
INSERT INTO table3...
ALTER TABLE TempStaging
DROP COLUMN UNID
END TRANSACTION
Maybe that will get you started.
HTH,
Stu

OSQL returns error code of 1 - intermittently

Hello,
I've written an program that runs OSQL to set up a database. I got a weird
situation yesterday where OSQL returns an error level of 1 and didn't
generate any output file at all. The command I use is:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
Normally, this command runs OK and produces a .log file. Why would it
return 1 and no log file?
I call OSQL from a Borland Builder C++ program.
Any ideas?
Regards,
Robert
PS: I ran the OSQL command straight after I installed MSDE and started up
SQL Server.Hi,
you are assuming some things:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
-E are you really able to use integrated authentication, if not you
have to provide a user name and a password. Perhaps you are running
your program under a special account which isn=B4t priviledged in the
database ?!
-i Are you in the right folder that SOQL can find the query file while
executing the file from the program ?
(missing -S) If you don=B4t specify a server, the default behaviour is
to go to the local server, the default instance, to the default port
1433. If this is not valid, you have to define the server/indtance,port
e=2Eg. Servername\instancename,Port.
HTH, Jens Suessmeyer.|||Hi,
Thanks for your reply.
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
-E are you really able to use integrated authentication, if not you
have to provide a user name and a password. Perhaps you are running
your program under a special account which isnt priviledged in the
database ?!
I think so. I am running as (machine) administrator when I execute OSQL
-i Are you in the right folder that SOQL can find the query file while
executing the file from the program ?
The SetupRTEDatabase.sql script is in the same directory as the calling
program.
(missing -S) If you dont specify a server, the default behaviour is
to go to the local server, the default instance, to the default port
1433. If this is not valid, you have to define the server/indtance,port
e.g. Servername\instancename,Port.
This is OK. I run OSQL pretty much straight after I do an install of SQL
Server. I don't change any of the default settings.
Regards,
Robert|||I've fixed the problem.
As it turns out, I was running the program in the wrong folder after all.
OSQL was being run (indirectory) from a Setup program which ran on a network
drive.
I did a ChangeDirectory to the path with the SetupRTEDatabase.sql script and
everything worked fine.
"Robert Wheadon" <robert.wheadon@.monitorbm.co.nz> wrote in message
news:uC$01qrTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Thanks for your reply.
> OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
> -E are you really able to use integrated authentication, if not you
> have to provide a user name and a password. Perhaps you are running
> your program under a special account which isnt priviledged in the
> database ?!
> I think so. I am running as (machine) administrator when I execute OSQL
> -i Are you in the right folder that SOQL can find the query file while
> executing the file from the program ?
> The SetupRTEDatabase.sql script is in the same directory as the calling
> program.
> (missing -S) If you dont specify a server, the default behaviour is
> to go to the local server, the default instance, to the default port
> 1433. If this is not valid, you have to define the server/indtance,port
> e.g. Servername\instancename,Port.
> This is OK. I run OSQL pretty much straight after I do an install of SQL
> Server. I don't change any of the default settings.
> Regards,
> Robert
>

os_oa* run exe written in vb6

I need to launch an exe application written in VB6 from stored procedure or
trigger. I know I have to use os_oa* but I cannot find any document to help
me build one. Many examples that I found have something to do with com but I
only have exe. I know this is not the way to go but I was told to do it. Can
any one help me by pointing me in the right directions? I know nothing about
this so I am learning from scratch for os_oa*.
The exe was developed to run silent in the background with no user
interface.Search for xp_cmdshell in BOL, it may be useful.
Francesco Anti
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>|||Do you mean sp_oa* ' If so, here is everything you need:
1. Compile your VB6 class into a DLL. You better have good error handling in
it otherwise you risk crashing your SQL Server.
2. Place that DLL into the WIN32 directory on your SQL Server, and REGISTER
it on your SQL Server by using RegSvr32.exe
3. Call methods of your VB6 class/dll from any stored procedure, using
something like the following (some of the names have been changed to protect
the innocent):
BEGIN
blah blah blah
DECLARE @.Object int -- holds a reference to your object instantiated
from the vb6 class.
SET @.MethodToCall = 'CalculatePayment(' + @.RatePercent + ', ' +
@.RateIncrease + ', ' + @.Months + ', ' + @.Fees + ')'
--Instantiate an instance of our DataConversion class and put it's
reference in @.object
EXEC sp_OACreate 'Your_VB_Class.DataConversion', @.object OUT
--Run the CalculatePayment method of our DataConversion class - and
place it's output into @.Return
EXEC sp_OAMethod @.object, @.MethodToCall, @.return OUT
-- Destroy the instance of our DataConversion class now that we're
done with it.
EXEC sp_OADestroy @.object
blah blah blah
END
4. Any time you need to upgrade your VB6 DLL, be sure to Unregister the old
one (using RegSvr32.exe... -U), then replace the old DLL with the new one,
and then register the new one like in step 2 above. No need to restart your
SQL Server.
That's it - really quite straight-forward, actually.
-HTH
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>|||Hi,
Use the following :
EXEC xp_cmdshell 'your vb program.exe'
you want to grant the right to execute xp_cmdshell to the SQL login
LimitedUser.
You'll need an NT account to execute the program. Here's the script:
use master
go
xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser's password>'
go
-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go
-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go
grant exec on xp_cmdshell to LimitedUser
go
Thanks,
Tarek ghazali
"UGH" <nospam@.noSPam.com> wrote in message
news:eRxL9tFaFHA.1368@.tk2msftngp13.phx.gbl...
>I need to launch an exe application written in VB6 from stored procedure or
>trigger. I know I have to use os_oa* but I cannot find any document to help
>me build one. Many examples that I found have something to do with com but
>I only have exe. I know this is not the way to go but I was told to do it.
>Can any one help me by pointing me in the right directions? I know nothing
>about this so I am learning from scratch for os_oa*.
>
> The exe was developed to run silent in the background with no user
> interface.
>

orthodromy calcul with custom paging in sql

***the sql-instruction has been modified a lot, so whas was written here is now useless***

Edit

The reason is Float converts to Char better than Varchar because Char is a variable length while Varchar is fixed length and NVarchar is multi bytes. All your quantitative functions are in Float because that is where T-SQL implemented them but you can cast Float to decimal more stable and decimal to Varchar. If you don't have any reason for using int then it should be decimal. I know LOG is not included in your code but if you need to use LOG you have to tell SQL Server LOG(n) or LOG10(n), if not SQL Server will give you algebraic numbers when you are looking for calculus calculations. Run a search for cast,convert and LOG compare in SQL Server BOL(books online) Hope this helps.

|||

sorry if im not too quick, but what you mean is that I have to change some of my type for it to work? because I have tried all the combinaison of char, varchar, nvarchar, int and float, and every times it give me the same result (well not exactly the same, since like I said the result always change at page 2...). Maybe I just didnt catch what you mean.

On a side-note, when I try to do a filter on the dist_km field (ie: dist_km > 200), this doesnt work at all...Maybe the answer lie within your post, but im still kinda confused...By the way, thanks a lot for your answer! And if you have any others suggestions, I'll be glad to hear them.

|||ok, I've made many, many tests, and I've come to the conclusion that the problem is not in the orthodromic formula and conversion itself. For my test, I just tried to sort my records on some INT fields, and guess what, at page 2, the same problem occur. Of course for my test, is use something like this in my sql instruction (Select *, dist_km = ad_viewed......ad_viewed being an INT field). So with that in light, does anybody know what is the problem...im sure its not just a conversion problem now... thanks again for your time|||

You are using Unicode and Ascii in one code you cannot do that, you also have date as varchar500, why do you need that much for date? I also see Charindex why do you need a string function in a quantitative function stored proc? Look at it you will see what I am talking about try cast instead of convert if it is explicit in SQL Server it will work. Change all your float converts to decimal instead of Nvarchar, change int to Numeric and try the link below and download the first file there is a converter, it is the exe. If you are still having problems send me an email I have detailed Descriptive statistics files including linear regression, it may make your code mile long but it may help you. Hope this helps

http://www.novicksoftware.com/transact-sql-user-defined-functions.htm

|||

ok, maybe the nvarchar for the date parameter was kinda high (even though it was 50 not 500), But I really need the charindex part. This is not a quantitative stored procedure only, the orthodromic part is only a small portion of the big picture. And I did try cast, but the effect was exactly the same. As you can see in my previous post (well, previous depending when the admin will posts it), I figured out that the problem was not the orthodromic formula itself.

But all these info really help me, since it will surely improve my stored procedure logic. So thanks a lot again for all your dedication. But before I made all these modifications to my sotored procedure, I need to know why it doesnt sort my record correctly...

|||

Yes I understand you now try TOP10 to 20 play with it. Test drive this expensive tool it was created by the best minds in T-SQL. Hope this helps

http://shop.bmc.com/product_moreinfo.cfm?id=1E61087

|||

Try this link , his paging is different. Hope this helps.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=633&lngWId=5

|||well thanks for the link. Well, as I can see theres two paging methods on this page. The first one is too simple to satisfy my need (I now used a method that looks like this one, but have some difference). And as for the second one, its a method using subqueries. Now this was the one I was using when I write this topic for the first time. And it ended up that it was the real problem, because alias column CANT be used for sorting in the inner most subquery, but this is where I need it to sort. So this one is perfect, but only if you have a table without any alias colum to sort on. I found that out on an other topic I've made, and now im using another method of custom paging that works fine now. But thanks a lot for the feedback, and for all the previous help too!!!