Help with SQL Query - "The multi-part identifier "alias field Name" could not

Hi Everyone
This is the query and I am getting follwoing error message

"The multi-part identifier "InvDate.Account Reference" could not be bound."

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC

By the way its SQL Server 2005 Environment.
Mitesh
Well how about getting rid of:
- (select * from customer) -- just use a simple join to customer
- get rid of the collate statements in your where clauses.

Also, you'll need to add INVDATE.[LASTORDERDATE] to your group by statement.

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]

FROM CUSTOMERCONTACTNOTES AS CCN,

CUSTOMER AS CC,

(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]

GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE]
ORDER BY CC.COMPANY ASC|||Thanks Phill,

Your solution was just spot on.

Just out of curosity, how do you read any SQL Query, for e.g. like mine and find what is wrong.

Mitesh|||Experience, I guess. When you work with it enough, you can just "read" SQL and understand what's going on.

I really don't think you need the "select distinct [account reference] from invoicedata" query in your where clause though. You already have a distinct list from the INVDATE query in your FROM statement. Your where clause should probably be:

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]sql

Help with sp code - cursor

Friends,
When I step through the following code in QA it works fine - when I alter
the proc and run it, I get an error (you should be able to copy the code as
it is into your own QA for testing) - Anybody know where I am making the
mistke? Thanks in advance for your help ... Bill Morgan
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values
(@.dater)
set @.dater = @.dater + 1
end
update #main
set ca = 'a'
select * from #main
set nocount off
returnYour table #main doesn't have a column "ca" in it, just a column "Dates":

> update #main
> set ca = 'a'
"bill_morgan" <bill_morgan@.discussions.microsoft.com> wrote in message
news:7F945501-89B0-4B6C-81BC-4767C92B0464@.microsoft.com...
> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code
> as
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Look at where you create the #Main table, there is no "ca" column defined in
it, only a "dates" column... Therefore, later on where you try to update the
'ca' column, it fails... Can;t begin to suggest a fix until I know what
Stored Proc is SUpposed t odo...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Sorry, Now I see what you're doing...
Wat's wrong is that You have an Open uncommitted transaction
just delete the Begin Tran line and try it again... If you need the tran,
then you have to put in a corresponding Commit tran...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Here is the explanation of what is happening.
http://groups.google.ca/groups?selm...FTNGP11.phx.gbl
Also, you have a begin transaction without a matching commit / rollback.
Example:
-- I commented the begin transaction
use northwind
go
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
--begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values (@.dater)
set @.dater = @.dater + 1
end
exec ('update #main set ca = ''a''')
select * from #main
set nocount off
return
go
exec tester
go
drop procedure tester
go
AMB
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Bill, What is going on is that SQL7/2000 has what is called delayed
verification o(or something like that) which basically does NOT check the
column names of tables whoch ddo not currently exist when you create the
Stored Proc. It waits until run time... Then it checks again, BEFORE The
stored Proc runs, to make sure that every column and table exists...
So what's going on here is that the compiler sees that you're going to
create the #Main table, and that it will have a column named 'dates', but it
doesn't (no way it can) see that you're going to alter the table and add all
those state name columns, so the Update #Main Set CA = 'a' line fails the
compiler test...
If you comment that line out, (and fx the Open Transaction issue), the code
will work.
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||my apologies for the begin tran statement - i was monkeying with the
procedure and forgot to take that out - once it's removed you can step
through the procedure, but trying to run it all at once fails ...
"CBretana" wrote:
> Sorry, Now I see what you're doing...
> Wat's wrong is that You have an Open uncommitted transaction
> just delete the Begin Tran line and try it again... If you need the tran,
> then you have to put in a corresponding Commit tran...
> "bill_morgan" wrote:
>|||Thanks for the guidance ... my apologies for the begin tran - i forgot to
take that out before I posted this question (I thought the begin tran /
commit tran) might fix things ... I am visiting the sight you suggest ...
thanks ...
"Alejandro Mesa" wrote:
> Here is the explanation of what is happening.
> [url]http://groups.google.ca/groups?selm=uxV68C33DHA.3468%40TK2MSFTNGP11.phx.gbl[/url
]
> Also, you have a begin transaction without a matching commit / rollback.
> Example:
> -- I commented the begin transaction
> use northwind
> go
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> --begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values (@.dater)
> set @.dater = @.dater + 1
> end
> exec ('update #main set ca = ''a''')
> select * from #main
> set nocount off
> return
> go
> exec tester
> go
> drop procedure tester
> go
>
> AMB
>
> "bill_morgan" wrote:
>|||Yes ..!! I created a new proc to handle that final update ... Proc 1 calls
Proc 2 and it works great ... thank you for the new knowledge ...
"CBretana" wrote:
> Bill, What is going on is that SQL7/2000 has what is called delayed
> verification o(or something like that) which basically does NOT check the
> column names of tables whoch ddo not currently exist when you create the
> Stored Proc. It waits until run time... Then it checks again, BEFORE The
> stored Proc runs, to make sure that every column and table exists...
> So what's going on here is that the compiler sees that you're going to
> create the #Main table, and that it will have a column named 'dates', but
it
> doesn't (no way it can) see that you're going to alter the table and add a
ll
> those state name columns, so the Update #Main Set CA = 'a' line fails the
> compiler test...
> If you comment that line out, (and fx the Open Transaction issue), the cod
e
> will work.
> "bill_morgan" wrote:
>sql

Help with SP

Hello,
I have the following two tables
tb_ProductIssue
issueID productID statusID Name
========================================
====
1 10 1 Error 256
2 12 2 Can't install
3 10 3 Constant Reboot
tb_status
statusID status_name
1 Open
2 Closed
3 Pending
I want to write a stored procedure that will retreive the issueID, productID
, status_name, and name
for a row in the tb_ProductIssue table. The stored procedure will have one
parameter, the
productID, to select any records in the tb_ProductIssue that correspond to t
he product. Here is
what I have so far, but I just stuck! Any help will be greatly appreciated
create procedure dbo.p_ProductIssuesGet
(
@.ProductID int
)
SELECT issueID, productID, statusID, name FROM tb_ProductIssue WHERE product
ID = @.ProductIDTry this
Exec('SELECT issueID, productID, statusID, name FROM tb_ProductIssue
WHERE productID = '+@.ProductID )|||Thanks for the help...but I found a way to do it with Inner Joins!
Ed_P. wrote:
> Hello,
> I have the following two tables
> tb_ProductIssue
> issueID productID statusID Name
> ========================================
====
> 1 10 1 Error 256
> 2 12 2 Can't install
> 3 10 3 Constant Reboot
> tb_status
> statusID status_name
> 1 Open
> 2 Closed
> 3 Pending
> I want to write a stored procedure that will retreive the issueID,
> productID, status_name, and name for a row in the tb_ProductIssue
> table. The stored procedure will have one parameter, the productID, to
> select any records in the tb_ProductIssue that correspond to the
> product. Here is what I have so far, but I just stuck! Any help will be
> greatly appreciated
> create procedure dbo.p_ProductIssuesGet
> (
> @.ProductID int
> )
> SELECT issueID, productID, statusID, name FROM tb_ProductIssue WHERE
> productID = @.ProductID|||Try this
Exec('SELECT issueID, productID, statusID, name FROM tb_ProductIssue
WHERE productID = '+@.ProductID )
Madhivanan

Help with SOAP Lite client error problems - CGI code

Hi,

I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.

I get basic errors throughout, starting with:
Error - SOAP::Transport::HTTP::Client::send_receive: POST
It seems as if the user access information is not carried through as it should, but I truly have no idea what the problem is.

Here is what I have put together so far.

Thanks,
Mark

sub procgetmemberinfo {

my $pin = "$form{'lPin'}";
my $password = "$form{'sPassword'}";

my $soap = SOAP::Lite
-> uri('https://xmlsql.XXXXX.xxx:441')
-> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } )
-> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');

my @.params = (
SOAP::Data->name(lPin => $pin),
SOAP::Data->name(sPassword => $password)
);

my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});

my $result = $soap->call($method => @.params);
unless ($result->fault) {

my $title = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/TITLE');
my $firstname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/FIRST');
my $middlename = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/MIDDLE');
my $lastname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/LAST');
my $suffix = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/SUFFIX');
my $address1 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STREET_ADDRESS');
my $address2 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ADDRESS2');
my $city = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/CITY');
my $stateprovince = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STATE_PROV');
my $postalcode = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ZIP');
my $country = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/COUNTRY');
my $homephone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/HOME_PHONE');
my $workphone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/WORK_PHONE');
my $emailaddress = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/EMAIL');

print "<font face=arial size=2><b>PIN Number:</b> $pin</font><br>";
print "<font face=arial size=2><b>Password:</b> $password</font><br>";
print "<font face=arial size=2><b>Title:</b> $title</font><br>";
print "<font face=arial size=2><b>First Name:</b> $firstname</font><br>";
print "<font face=arial size=2><b>Middle Name:</b> $middlename</font><br>";
print "<font face=arial size=2><b>Last Name:</b> $lastname</font><br>";
print "<font face=arial size=2><b>Suffix:</b> $suffix</font><br>";
print "<font face=arial size=2><b>Address1:</b> $address1</font><br>";
print "<font face=arial size=2><b>Address2:</b> $address2</font><br>";
print "<font face=arial size=2><b>City:</b> $city</font><br>";
print "<font face=arial size=2><b>State:</b> $stateprovince</font><br>";
print "<font face=arial size=2><b>ZIP/Postal Code:</b> $postalcode</font><br>";
print "<font face=arial size=2><b>Country:</b> $country</font><br>";
print "<font face=arial size=2><b>Home Phone:</b> $homephone</font><br>";
print "<font face=arial size=2><b>Work Phone:</b> $workphone</font><br>";
print "<font face=arial size=2><b>Email Address:</b> $emailaddress</font>";

}

else {
print join ', ',
$result->faultcode,
$result->faultstring,
$result->faultdetail;
}

}

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

3. On the web service machine run c:\ssltrace.cmd to start tracing, then hit your web service with your POST. Once you are finished testing press the spacebar to in the ssltrace.cmd command window to stop tracing and display the trace file. You should see everything coming in and going out along with error codes, etc...

|||

Hello Matt,

your Message was helpfull to solve a problem with tracing.

Could you please tell me where you get the information

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

from?

Thanks

str01014

|||

Actually I updated my batch file to inline everything (i've included it below).

To get a list of trace providers, run this ->

C:\>logman query providers

Provider GUID
-
...

IIS: SSL Filter {1fbecc45-c060-4e7c-8a0e-0dbd6116181b}
...
HTTP Service Trace {dd5ef90a-6398-47a4-ad34-4dcecdef795f}.
...

This will list each trace provider and it's GUID. The 2nd and 3rd values are flags passed to the trace provider to turn on and off various traces. How these values are used are provider specific. However if you just want to turn on all the flags pass in 0xFFFFFFFF and 255 like in my example below. Search MSDN for HTTP Service Trace and SSL Filter for information on what these flags do.

Here is my updated batch file (note it requires logparser tool which is downloadable from Microsoft) ->

rem START SSL TRACE DEMO
if exist ssltrace.guids del ssltrace.guids
echo {1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0xFFFFFFFF 255 IIS: SSL Filter >> ssltrace.guids
echo {dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0xFFFFFFFF 255 HTTP Service Trace >> ssltrace.guids
logman start ssltrace -pf ssltrace.guids -o out.etl -ets
rem PRESS <SPACEBAR> TO STOP SSL+HTTP TRACING AND VIEW RESULTS
pause
logman stop ssltrace -ets
if exist ssltrace.guids del ssltrace.guids
if exist ssltrace.log del ssltrace.log
logparser -i:ETW "select * from out.etl" -e:ON >> ssltrace.log
start notepad ssltrace.log
rem END SSL TRACE DEMO

Help with SOAP Lite client error problems - CGI code

Hi,

I have been working on a soap client project for over a week now and can not figure out what I am doing wrong. I am not an expert using Perl or SOAP so any help would be "greatly" appreciated.

I get basic errors throughout, starting with:
Error - SOAP::Transport::HTTP::Client::send_receive: POST
It seems as if the user access information is not carried through as it should, but I truly have no idea what the problem is.

Here is what I have put together so far.

Thanks,
Mark

sub procgetmemberinfo {

my $pin = "$form{'lPin'}";
my $password = "$form{'sPassword'}";

my $soap = SOAP::Lite
-> uri('https://xmlsql.XXXXX.xxx:441')
-> on_action( sub { join '/', 'https://xmlsql.XXXXX.xxx:441', $_[1] } )
-> proxy('https://xmlsql.XXXXX.xxx:441/service.asmx?WSDL');

my @.params = (
SOAP::Data->name(lPin => $pin),
SOAP::Data->name(sPassword => $password)
);

my $method = SOAP::Data->name('GetMemberInfo')->attr({xmlns => 'https://xmlsql.XXXXX.xxx:441/'});

my $result = $soap->call($method => @.params);
unless ($result->fault) {

my $title = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/TITLE');
my $firstname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/FIRST');
my $middlename = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/MIDDLE');
my $lastname = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/LAST');
my $suffix = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/SUFFIX');
my $address1 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STREET_ADDRESS');
my $address2 = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ADDRESS2');
my $city = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/CITY');
my $stateprovince = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/STATE_PROV');
my $postalcode = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/ZIP');
my $country = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/COUNTRY');
my $homephone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/HOME_PHONE');
my $workphone = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/WORK_PHONE');
my $emailaddress = $result->valueof('//GetMemberInfoResult/diffgram/NewDataSet/MEMBERS/EMAIL');

print "<font face=arial size=2><b>PIN Number:</b> $pin</font><br>";
print "<font face=arial size=2><b>Password:</b> $password</font><br>";
print "<font face=arial size=2><b>Title:</b> $title</font><br>";
print "<font face=arial size=2><b>First Name:</b> $firstname</font><br>";
print "<font face=arial size=2><b>Middle Name:</b> $middlename</font><br>";
print "<font face=arial size=2><b>Last Name:</b> $lastname</font><br>";
print "<font face=arial size=2><b>Suffix:</b> $suffix</font><br>";
print "<font face=arial size=2><b>Address1:</b> $address1</font><br>";
print "<font face=arial size=2><b>Address2:</b> $address2</font><br>";
print "<font face=arial size=2><b>City:</b> $city</font><br>";
print "<font face=arial size=2><b>State:</b> $stateprovince</font><br>";
print "<font face=arial size=2><b>ZIP/Postal Code:</b> $postalcode</font><br>";
print "<font face=arial size=2><b>Country:</b> $country</font><br>";
print "<font face=arial size=2><b>Home Phone:</b> $homephone</font><br>";
print "<font face=arial size=2><b>Work Phone:</b> $workphone</font><br>";
print "<font face=arial size=2><b>Email Address:</b> $emailaddress</font>";

}

else {
print join ', ',
$result->faultcode,
$result->faultstring,
$result->faultdetail;
}

}

Here is a nice easy way to debug SSL issue with Windows 2003 that I discovered while working on SOAP projects.

1. Create batch file c:\ssltrace.cmd with following contents:

logman start http_ssl_trace -pf c:\guids.txt -o out.etl -ets
pause
logman stop http_ssl_trace -ets
tracerpt /y out.etl
notepad dumpfile.csv

2. Create file c:\guids.txt with following contents:

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

3. On the web service machine run c:\ssltrace.cmd to start tracing, then hit your web service with your POST. Once you are finished testing press the spacebar to in the ssltrace.cmd command window to stop tracing and display the trace file. You should see everything coming in and going out along with error codes, etc...

|||

Hello Matt,

your Message was helpfull to solve a problem with tracing.

Could you please tell me where you get the information

{1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0x000000FF 5 IIS: SSL Filter
{dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0x000000FF 5 HTTP Service Trace

from?

Thanks

str01014

|||

Actually I updated my batch file to inline everything (i've included it below).

To get a list of trace providers, run this ->

C:\>logman query providers

Provider GUID
-
...

IIS: SSL Filter {1fbecc45-c060-4e7c-8a0e-0dbd6116181b}
...
HTTP Service Trace {dd5ef90a-6398-47a4-ad34-4dcecdef795f}.
...

This will list each trace provider and it's GUID. The 2nd and 3rd values are flags passed to the trace provider to turn on and off various traces. How these values are used are provider specific. However if you just want to turn on all the flags pass in 0xFFFFFFFF and 255 like in my example below. Search MSDN for HTTP Service Trace and SSL Filter for information on what these flags do.

Here is my updated batch file (note it requires logparser tool which is downloadable from Microsoft) ->

rem START SSL TRACE DEMO
if exist ssltrace.guids del ssltrace.guids
echo {1fbecc45-c060-4e7c-8a0e-0dbd6116181b} 0xFFFFFFFF 255 IIS: SSL Filter >> ssltrace.guids
echo {dd5ef90a-6398-47a4-ad34-4dcecdef795f} 0xFFFFFFFF 255 HTTP Service Trace >> ssltrace.guids
logman start ssltrace -pf ssltrace.guids -o out.etl -ets
rem PRESS <SPACEBAR> TO STOP SSL+HTTP TRACING AND VIEW RESULTS
pause
logman stop ssltrace -ets
if exist ssltrace.guids del ssltrace.guids
if exist ssltrace.log del ssltrace.log
logparser -i:ETW "select * from out.etl" -e:ON >> ssltrace.log
start notepad ssltrace.log
rem END SSL TRACE DEMO

sql

help with server replacement

i'm replacing a server in the next few weeks.
the old server has all database files, log files, and error files on
f:\sql which is a drive on a san.
sql2000 program files are on c: and d:.
the new server will have the same name and same IP as the old server.
the only difference is new one is win2003 and old one is win2000.
i'm wondering if this will work.
create disk partition on new server f:
install sql2000 program files on c: and d:.
install sql2000 data, log, and error files in f:\sql
shut down sql2000.
delete f:
shut down old server.
connect new server to network.
attach the san drive to new server as f: (has all the databases from old
server).
start sql server.
think that will work?
or will i have to install master, model, msdb, tempdb on e: and then
detach the ones on e: and then attach the ones on f:?
last time i did that, it was a nightmare. i couldn't get sql server to
start for like an hour because the kb article had faulty instructions.Hi,
There are several factors you need to consider. I suggest you go through
these articles, list down each of the activities (something like checklist)
and execute it.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
Thanks
GYKsql

help with server replacement

i'm replacing a server in the next few weeks.
the old server has all database files, log files, and error files on
f:\sql which is a drive on a san.
sql2000 program files are on c: and d:.
the new server will have the same name and same IP as the old server.
the only difference is new one is win2003 and old one is win2000.
i'm wondering if this will work.
create disk partition on new server f:
install sql2000 program files on c: and d:.
install sql2000 data, log, and error files in f:\sql
shut down sql2000.
delete f:
shut down old server.
connect new server to network.
attach the san drive to new server as f: (has all the databases from old
server).
start sql server.
think that will work?
or will i have to install master, model, msdb, tempdb on e: and then
detach the ones on e: and then attach the ones on f:?
last time i did that, it was a nightmare. i couldn't get sql server to
start for like an hour because the kb article had faulty instructions.
Hi,
There are several factors you need to consider. I suggest you go through
these articles, list down each of the activities (something like checklist)
and execute it.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
Thanks
GYK

help with server replacement

i'm replacing a server in the next few weeks.
the old server has all database files, log files, and error files on
f:\sql which is a drive on a san.
sql2000 program files are on c: and d:.
the new server will have the same name and same IP as the old server.
the only difference is new one is win2003 and old one is win2000.
i'm wondering if this will work.
create disk partition on new server f:
install sql2000 program files on c: and d:.
install sql2000 data, log, and error files in f:\sql
shut down sql2000.
delete f:
shut down old server.
connect new server to network.
attach the san drive to new server as f: (has all the databases from old
server).
start sql server.
think that will work?
or will i have to install master, model, msdb, tempdb on e: and then
detach the ones on e: and then attach the ones on f:?
last time i did that, it was a nightmare. i couldn't get sql server to
start for like an hour because the kb article had faulty instructions.Hi,
There are several factors you need to consider. I suggest you go through
these articles, list down each of the activities (something like checklist)
and execute it.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
--
Thanks
GYK

Help with Sending file to server using ftp task

Doe anyone know how to do this. I keep getting an error remote path missing "/" but it doesn't contain a "/".

Mike

If you could give more detail, that would help.|||

I'm trying to connect to a FTP server running on z/OS and has MvS operating system. The remoter parameters In the FTP task editor in ssis calls for a "/" at the beginning of the remote path but the remote path I am uploading to starts out with a single quote and not a forward slash. Is there a way around this?

thanks,

Mike

|||Did you try putting a "/" in front?|||

I figured it out using a ftp script.

|||That's what I have had to resort to at times also. Some of the FTP task functionality just doesn't seem to function like it should.

Help With Scheduled Reports

When i set up a subscription i get the following error
Failure sending mail: The Report Server has encountered a configuration
error; more details in the log files
I have found the following on MSDN
http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
The above details fixes for 2000 and 2003 domains however we are on a NT
domain
When i change the account to a domain account (even administrator) when it
is time for the subscription to run it does not execute
does any body have any ideas on how to fix this
thanks
RegWe had the same issue awhile back. Make sure you're up to date on your
service packs. Also, you can check the log file (ReportServerService) for
more error messages and details.
"Reg Besseling" wrote:
> When i set up a subscription i get the following error
> Failure sending mail: The Report Server has encountered a configuration
> error; more details in the log files
> I have found the following on MSDN
> http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
> The above details fixes for 2000 and 2003 domains however we are on a NT
> domain
> When i change the account to a domain account (even administrator) when it
> is time for the subscription to run it does not execute
> does any body have any ideas on how to fix this
> thanks
> Reg
>|||It appears that applying MS05-043 , MS05-038 and MS05-039 caused the issue
to start as all was working before
"daw" <daw@.discussions.microsoft.com> wrote in message
news:CA6823E7-640E-4E86-B59F-65049301D885@.microsoft.com...
> We had the same issue awhile back. Make sure you're up to date on your
> service packs. Also, you can check the log file (ReportServerService) for
> more error messages and details.
> "Reg Besseling" wrote:
>> When i set up a subscription i get the following error
>> Failure sending mail: The Report Server has encountered a configuration
>> error; more details in the log files
>> I have found the following on MSDN
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;842423
>> The above details fixes for 2000 and 2003 domains however we are on a NT
>> domain
>> When i change the account to a domain account (even administrator) when
>> it
>> is time for the subscription to run it does not execute
>> does any body have any ideas on how to fix this
>> thanks
>> Reg
>>

Help with permission error

Hi,
I created a user and when logged in under that user name I get the foll erro
r
Server: Msg 3704, Level 16, State 1, Line 2
User does not have permission to perform this operation on table 'dbo.sale'
when I try to execute
truncate table dbo.sale
I grant select,update,delete permission on the table to the user.
What did I miss?
ThanksTruncate table is a special operation and only table owner, dbo, symin ca
n perform the operation.
It is not grantable.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:B898FE02-EE00-4734-BE0C-9F3B4447A3C4@.microsoft.com...
> Hi,
> I created a user and when logged in under that user name I get the foll er
ror
> Server: Msg 3704, Level 16, State 1, Line 2
> User does not have permission to perform this operation on table 'dbo.sale
'
> when I try to execute
> truncate table dbo.sale
> I grant select,update,delete permission on the table to the user.
> What did I miss?
> Thanks|||Hi,
Then what's the fast way to delete from a table. I have a sp that uses a
table to dump a lot of data then after the sp is completed it truncates the
table. I didn't use a temp table because I need the data in the table if the
proc fails.
Thanks
"Tibor Karaszi" wrote:

> Truncate table is a special operation and only table owner, dbo, symin
can perform the operation.
> It is not grantable.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:B898FE02-EE00-4734-BE0C-9F3B4447A3C4@.microsoft.com...
>

help with myspace

can anyone help me solve this problem.when i open up an artists myspace i cant get any music to play from the standalone player just get an error loading xml document.i have installed the latest flash player from adobe but still have the problem

hello. You may have installed a security update from microsoft that can create conflicts with myspace and other sites with flash content. I had the same problem and uninstalled an update and myspace content was working again. I am running win 2000 pro.

help with myspace

can anyone help me solve this problem.when i open up an artists myspace i cant get any music to play from the standalone player just get an error loading xml document.i have installed the latest flash player from adobe but still have the problem

hello. You may have installed a security update from microsoft that can create conflicts with myspace and other sites with flash content. I had the same problem and uninstalled an update and myspace content was working again. I am running win 2000 pro.

help with my UPDATE query

I'm receiving this error: Incorrect syntax near the keyword 'SET'

CODE:

Code Snippet

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE dbo.pnpcart_Customer_Details
SET dbo.pnpcart_Customer_Details.Prefix = @.Prefix,
SET dbo.pnpcart_Customer_Details.FirstName = @.FirstName,
SET dbo.pnpcart_Customer_Details.MiddleName = @.MiddleName,
SET dbo.pnpcart_Customer_Details.LastName = @.LastName,
SET dbo.pnpcart_Customer_Details.Address = @.Address,
SET dbo.pnpcart_Customer_Details.City = @.City,
SET dbo.pnpcart_Customer_Details.State = @.State,
SET dbo.pnpcart_Customer_Details.Zip = @.Zip,
SET dbo.pnpcart_Customer_Details.HomePhone = @.HomePhone,
SET dbo.pnpcart_Customer_Details.CellPhone = @.CellPhone,
SET dbo.pnpcart_Customer_Details.Email = @.Email
END

Thanks for the help in advanced!

-Thanks,
Rich

Use the following query...(are you forget your where clause, the current query will update all the records in the table)

UPDATE dbo.pnpcart_Customer_Details
SET Prefix = @.Prefix,
FirstName = @.FirstName,
MiddleName = @.MiddleName,
LastName = @.LastName,
Address = @.Address,
City = @.City,
State = @.State,
Zip = @.Zip,
HomePhone = @.HomePhone,
CellPhone = @.CellPhone,
Email = @.Email

Where (logical expression)

|||GREAT! That works perfectly for now. I'm going to have to revamp my code to work with a JOIN along with asp.net grid control.

Thanks,
Rich

Help with MS Access Error Trap

Please excuse if you feel this is posted in wrong group (I have posted in
Access groups as well)... I do know there is at least some some cross-over
base here that may help...
I am trying to establish links from Ms Access to tables in a SQL server
database. There is an Access table (tblLinkMaster) that contain all the
required linkage information. If
the database is attached and the network connection is good, the code below
works fine and all is
well with the world.
However, when I detach the database and test to see what the user would see,
I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
rejected the
connection; Access to selected databse has been denied. If I then click OK
and cancel, the program errors
out in a messy manner.
(Program stops HERE
' Then get new link info
db1.Execute ("qapptblLinkTable"))
If the database is not available, I would prefer that the whole procedure
stop and exit gracefully.
Any Ideas on how this can be error trapped nicely ?
Thanks !
Function GetMasterLinkData()
' Based on Data in Local table (entered via form) - tblLinkMaster /
qrytblLinkMaster
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
Dim strLinkName As String
Dim strDBName As String
Dim strTableName As String
Dim strDSNname As String
Dim strServerName As String
rs1.MoveFirst
If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
IsNull(rs1!TableName) Or rs1!TableName = "" Or _
IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
an Administrator")
Exit Function
Else
strLinkName = rs1!LinkName
strDBName = rs1!DatabaseName
strTableName = rs1!TableName
strDSNname = ""
strServerName = rs1!ServerName
Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
strServerName)
End If
' Now remove previous link info
db1.Execute ("DELETE from tblLinkTable")
' Then get new link info
db1.Execute ("qapptblLinkTable")
rs1.Close
Set rs1 = Nothing
db1.Close
Set db1 = Nothing
End Function
Public Function LinkTableDAO(strLinkName As String, strDBName As String,
strTableName As String,
strDSNname As String, strServerName As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' if link pre-exists, then delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
' Found an existing tabledef
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' No existing tabledef
' Ignore error and reset
Err.Clear
End If
' Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
' set connect and source table table name prperties to establish link
tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
";Database=" & strDBName &
";Trusted_Connection=Yes"
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
db.TableDefs.Append tdf
db.Close
End FunctionYou need to implement "On Error Goto..." error handling in your code.
See the VBA help files for more information, or post on the
Access.programming newsgroups.
--Mary
On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:

>Please excuse if you feel this is posted in wrong group (I have posted in
>Access groups as well)... I do know there is at least some some cross-over
>base here that may help...
>I am trying to establish links from Ms Access to tables in a SQL server
>database. There is an Access table (tblLinkMaster) that contain all the
>required linkage information. If
>the database is attached and the network connection is good, the code below
>works fine and all is
>well with the world.
>However, when I detach the database and test to see what the user would see
,
>I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
>rejected the
>connection; Access to selected databse has been denied. If I then click O
K
>and cancel, the program errors
>out in a messy manner.
>(Program stops HERE
>' Then get new link info
> db1.Execute ("qapptblLinkTable"))
>If the database is not available, I would prefer that the whole procedure
>stop and exit gracefully.
>Any Ideas on how this can be error trapped nicely ?
>Thanks !
>
>Function GetMasterLinkData()
>' Based on Data in Local table (entered via form) - tblLinkMaster /
>qrytblLinkMaster
> Dim db1 As DAO.Database
> Dim rs1 As DAO.Recordset
> Set db1 = CurrentDb()
> Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
> Dim strLinkName As String
> Dim strDBName As String
> Dim strTableName As String
> Dim strDSNname As String
> Dim strServerName As String
> rs1.MoveFirst
> If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
> IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
> IsNull(rs1!TableName) Or rs1!TableName = "" Or _
> IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
> MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
>an Administrator")
> Exit Function
> Else
> strLinkName = rs1!LinkName
> strDBName = rs1!DatabaseName
> strTableName = rs1!TableName
> strDSNname = ""
> strServerName = rs1!ServerName
> Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
>strServerName)
> End If
> ' Now remove previous link info
> db1.Execute ("DELETE from tblLinkTable")
> ' Then get new link info
> db1.Execute ("qapptblLinkTable")
> rs1.Close
> Set rs1 = Nothing
> db1.Close
> Set db1 = Nothing
>End Function
>
>Public Function LinkTableDAO(strLinkName As String, strDBName As String,
>strTableName As String,
>strDSNname As String, strServerName As String)
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> On Error Resume Next
> Set db = CurrentDb
> ' if link pre-exists, then delete it
> Set tdf = db.TableDefs(strLinkName)
> If Err.Number = 0 Then
> ' Found an existing tabledef
> db.TableDefs.Delete strLinkName
> db.TableDefs.Refresh
> Else
> ' No existing tabledef
> ' Ignore error and reset
> Err.Clear
> End If
> ' Create a new TableDef object
> Set tdf = db.CreateTableDef(strLinkName)
> ' set connect and source table table name prperties to establish link
> tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
>";Database=" & strDBName &
>";Trusted_Connection=Yes"
> tdf.SourceTableName = strTableName
> ' Append to the database's TableDefs collection
> ' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
> db.TableDefs.Append tdf
>db.Close
>End Function
>
>
>|||I understand I have misapplied pieces of your code (page 189) in some
manner... and must still be doing that with the error trap I attempted to
add...
When I tried the code below within "LinkTableDAO", even when the database
is attached I get an error resulting in a Quit... if I take out the error
trap and the database is attached, it all works just fine.
In addition, I did post to 2 Access newsgroups with no response, then after
looking into BOL regarding SQLSTATE errors and ODBC connections, I did feel
the topic was at least somewhat relevant to 2 sql newsgroups (programming
with SQL and ODBC), my intention is certainly not to spam these fine
newsgroups.
' Append to the database's TableDefs collection
'IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
On Error GoTo ErrHandle
db.TableDefs.Append tdf
db.Close
ErrHandle:
Quit
Best Regards
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ua3ki09utajdj3km4fhm62np3ijrek91jh@.
4ax.com...
> You need to implement "On Error Goto..." error handling in your code.
> See the VBA help files for more information, or post on the
> Access.programming newsgroups.
> --Mary
> On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
> wrote:
>
cross-over[vbcol=seagreen]
below[vbcol=seagreen]
see,[vbcol=seagreen]
Server[vbcol=seagreen]
OK[vbcol=seagreen]
contact[vbcol=seagreen]
>

Help with MS Access Error Trap

Please excuse if you feel this is posted in wrong group (I have posted in
Access groups as well)... I do know there is at least some some cross-over
base here that may help...
I am trying to establish links from Ms Access to tables in a SQL server
database. There is an Access table (tblLinkMaster) that contain all the
required linkage information. If
the database is attached and the network connection is good, the code below
works fine and all is
well with the world.
However, when I detach the database and test to see what the user would see,
I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
rejected the
connection; Access to selected databse has been denied. If I then click OK
and cancel, the program errors
out in a messy manner.
(Program stops HERE
' Then get new link info
db1.Execute ("qapptblLinkTable"))
If the database is not available, I would prefer that the whole procedure
stop and exit gracefully.
Any Ideas on how this can be error trapped nicely ?
Thanks !
Function GetMasterLinkData()
' Based on Data in Local table (entered via form) - tblLinkMaster /
qrytblLinkMaster
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
Dim strLinkName As String
Dim strDBName As String
Dim strTableName As String
Dim strDSNname As String
Dim strServerName As String
rs1.MoveFirst
If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
IsNull(rs1!TableName) Or rs1!TableName = "" Or _
IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
an Administrator")
Exit Function
Else
strLinkName = rs1!LinkName
strDBName = rs1!DatabaseName
strTableName = rs1!TableName
strDSNname = ""
strServerName = rs1!ServerName
Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
strServerName)
End If
' Now remove previous link info
db1.Execute ("DELETE from tblLinkTable")
' Then get new link info
db1.Execute ("qapptblLinkTable")
rs1.Close
Set rs1 = Nothing
db1.Close
Set db1 = Nothing
End Function
Public Function LinkTableDAO(strLinkName As String, strDBName As String,
strTableName As String,
strDSNname As String, strServerName As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' if link pre-exists, then delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
' Found an existing tabledef
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' No existing tabledef
' Ignore error and reset
Err.Clear
End If
' Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
' set connect and source table table name prperties to establish link
tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
";Database=" & strDBName &
";Trusted_Connection=Yes"
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
db.TableDefs.Append tdf
db.Close
End Function
You need to implement "On Error Goto..." error handling in your code.
See the VBA help files for more information, or post on the
Access.programming newsgroups.
--Mary
On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:

>Please excuse if you feel this is posted in wrong group (I have posted in
>Access groups as well)... I do know there is at least some some cross-over
>base here that may help...
>I am trying to establish links from Ms Access to tables in a SQL server
>database. There is an Access table (tblLinkMaster) that contain all the
>required linkage information. If
>the database is attached and the network connection is good, the code below
>works fine and all is
>well with the world.
>However, when I detach the database and test to see what the user would see,
>I get... Connection failed: SQLSTATE:'08004' SQL Server Error: 4060 Server
>rejected the
>connection; Access to selected databse has been denied. If I then click OK
>and cancel, the program errors
>out in a messy manner.
>(Program stops HERE
>' Then get new link info
> db1.Execute ("qapptblLinkTable"))
>If the database is not available, I would prefer that the whole procedure
>stop and exit gracefully.
>Any Ideas on how this can be error trapped nicely ?
>Thanks !
>
>Function GetMasterLinkData()
>' Based on Data in Local table (entered via form) - tblLinkMaster /
>qrytblLinkMaster
> Dim db1 As DAO.Database
> Dim rs1 As DAO.Recordset
> Set db1 = CurrentDb()
> Set rs1 = db1.OpenRecordset("qrytblLinkMaster")
> Dim strLinkName As String
> Dim strDBName As String
> Dim strTableName As String
> Dim strDSNname As String
> Dim strServerName As String
> rs1.MoveFirst
> If IsNull(rs1!LinkName) Or rs1!LinkName = "" Or _
> IsNull(rs1!DatabaseName) Or rs1!DatabaseName = "" Or _
> IsNull(rs1!TableName) Or rs1!TableName = "" Or _
> IsNull(rs1!ServerName) Or rs1!ServerName = "" Then
> MsgBox ("The Initial Set-up Infromation Is Incomplete - Please contact
>an Administrator")
> Exit Function
> Else
> strLinkName = rs1!LinkName
> strDBName = rs1!DatabaseName
> strTableName = rs1!TableName
> strDSNname = ""
> strServerName = rs1!ServerName
> Call LinkTableDAO(strLinkName, strDBName, strTableName, strDSNname,
>strServerName)
> End If
> ' Now remove previous link info
> db1.Execute ("DELETE from tblLinkTable")
> ' Then get new link info
> db1.Execute ("qapptblLinkTable")
> rs1.Close
> Set rs1 = Nothing
> db1.Close
> Set db1 = Nothing
>End Function
>
>Public Function LinkTableDAO(strLinkName As String, strDBName As String,
>strTableName As String,
>strDSNname As String, strServerName As String)
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> On Error Resume Next
> Set db = CurrentDb
> ' if link pre-exists, then delete it
> Set tdf = db.TableDefs(strLinkName)
> If Err.Number = 0 Then
> ' Found an existing tabledef
> db.TableDefs.Delete strLinkName
> db.TableDefs.Refresh
> Else
> ' No existing tabledef
> ' Ignore error and reset
> Err.Clear
> End If
> ' Create a new TableDef object
> Set tdf = db.CreateTableDef(strLinkName)
> ' set connect and source table table name prperties to establish link
> tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName &
>";Database=" & strDBName &
>";Trusted_Connection=Yes"
> tdf.SourceTableName = strTableName
> ' Append to the database's TableDefs collection
> ' IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
> db.TableDefs.Append tdf
>db.Close
>End Function
>
>
>
|||I understand I have misapplied pieces of your code (page 189) in some
manner... and must still be doing that with the error trap I attempted to
add...
When I tried the code below within "LinkTableDAO", even when the database
is attached I get an error resulting in a Quit... if I take out the error
trap and the database is attached, it all works just fine.
In addition, I did post to 2 Access newsgroups with no response, then after
looking into BOL regarding SQLSTATE errors and ODBC connections, I did feel
the topic was at least somewhat relevant to 2 sql newsgroups (programming
with SQL and ODBC), my intention is certainly not to spam these fine
newsgroups.
' Append to the database's TableDefs collection
'IF SQL SERVER DB NOT ATTACHED - THEN ERROR OCCURS HERE
On Error GoTo ErrHandle
db.TableDefs.Append tdf
db.Close
ErrHandle:
Quit
Best Regards
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ua3ki09utajdj3km4fhm62np3ijrek91jh@.4ax.com... [vbcol=seagreen]
> You need to implement "On Error Goto..." error handling in your code.
> See the VBA help files for more information, or post on the
> Access.programming newsgroups.
> --Mary
> On Mon, 23 Aug 2004 10:04:54 -0400, "rob" <rwc1960@.bellsouth.net>
> wrote:
cross-over[vbcol=seagreen]
below[vbcol=seagreen]
see,[vbcol=seagreen]
Server[vbcol=seagreen]
OK[vbcol=seagreen]
contact
>

Help with MAX()

Hi,
Need a query help.
Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No
Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.
How do I do it?Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

I think that should be it ...|||Sorry. That doesn't seem to give me the result I want.

These are my data.

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372873','2005-10-10','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372874','2005-10-11','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SNI', '82738278372872','2005-10-12','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372875','2005-10-13','Approved');

insert into logs (serial_no, logid, log_time, event_reason)
values ('SN2', '82738278372876','2005-10-14','Approved');

I just want the latest records for each Serial_No. In this case, I would want
the third and the fifth record.|||select * from Logs,
(Select Serial_No, MAX(Log_Time) from logs
group by Serial_No) LogMaxTime where Logs.Serial_No = LogMaxTime.Serial_No

SELECT *
FROM Logs INNER JOIN
(SELECT Serial_No, MAX(Log_Time) AS MaxOfLogs
FROM logs
GROUP BY Serial_No) LogMaxTime ON
Logs.Serial_No = LogMaxTime.Serial_No
AND Logs.Log_Time = LogMaxTime.MaxOfLogs :)|||It WORKS!!!
Thanks a lot!! :)|||Hi,
Need a query help.

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No

Gives me an error. But I want to extract the records Where Log_Time was the maximum for the corresponding Serial_No.

How do I do it?

Select Serial_No, MAX(Log_Time), * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

or

Select Serial_No, Log_Time , * from logs
group by Serial_No having Log_Time = MAX(Log_Time)

I think second is the best to use. Because it is Optimised.

Regards,
Subramanyam.|||the second is not the best to use, because it is invalid syntax

:)|||...but other than that minor drawback, it is way ultra-cool.

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"

.Connection = sqlConnection1

EndWith

cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<namespaces>

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken

Help with INSERT TRIGGER - fails with error.

I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.

TIA

Edward

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

CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable

FOR INSERT

AS

BEGIN

IF UPDATE(fldKBSubject)
BEGIN

UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID

END

IF UPDATE (fldKBDescription)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescription
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
ENDOn 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.
>Is there any alternative? I could write it in laborious code in the
>application, but I'd rather not!
>DDL for table and trigger below.

Hi Edward,

Thanks for providing the DDL!

I'll come to your problem later, but first some comments.

>CREATE TABLE [dbo].[tblMyTable] (
>[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
>[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO

You didn't declare any PRIMARY KEY in this database. I think you
intended to make the column fldCSID a PRIMARY KEY, but you didn't
declare it as such.

After that, you should also declare some other column (or combination of
columns) as UNIQUE. With this design, there's nothing to prevent you
from accidentally inserting the same data twice.

Does the fldSCID column really have to be uniqueidentifier? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifier are very rare.

Apart from the uniqueidentifier column, all your columns accept NULLs.
Do you really want to accept rows with just NULLs in your database?
Nullable columns should be the exception, not the rule.

Are you sure that all these columns need to be ntext? I can somewhat
imagine having descriptions of over 4,000 characters - but subjects? I
think that you should probably define Subject and KBSubject ar nvarchar
with an appropriate maximum length (hopefully less than 100, but I don;t
know your business of course). You might also want to rethiink the
choice of ntext/nvarchar over text/varchar - unless you really need to
store characters from non-Western alphabets or other characters that are
only available in unicode, there's no reason to use double the space
taken.

On to the trigger (I removed the empty lines for readability)

>CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
>FOR INSERT
>AS
>BEGIN
>IF UPDATE(fldKBSubject)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldSubject = i.fldKBSubject
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>IF UPDATE (fldKBDescription)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldDescription = i.fldKBDescription
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>END

In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
column in the table.

There's also no need to use two seperate update statements. You can
combine these into one and gain some performance.

But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?

Anyway, back to your question:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.

You can't reference ntext columns in the inserted column. But you can
join to the base table and get the data from there. (Or you could
convert the trigger to an instead of trigger, in which case the ntext
data *WILL* be available in the inserted table - but that's not the
easiest solution in this case).

CREATE TRIGGER PrepopulateKBFieldsFromQuery
ON dbo.tblMyTable
FOR INSERT
AS
UPDATE MyTable
SET Subject = KBSubject,
Description = KBDescription
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.CSID = MyTable.CSID)
go

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> After that, you should also declare some other column (or combination of
> columns) as UNIQUE. With this design, there's nothing to prevent you
> from accidentally inserting the same data twice.

I would guess that one of the subjects are intended to be a key of some
sort, but since it's probably a free-text column, a PK/UNIQUE constraint
only gives you half protection, as it will not catch variations due to
typos and spaces.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

Unless you are into replication. GUIDs are also popular among web
programmers, because they can save a roundtrip to get the key value.
I've seen more than one URL with GUIDs in them.

> You might also want to rethiink the choice of ntext/nvarchar over
> text/varchar - unless you really need to store characters from
> non-Western alphabets or other characters that are only available in
> unicode, there's no reason to use double the space taken.

Not sure I agree. The cost for a change when a requirement to support,
say, Japanese, comes can prove to be prohibitive.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

The trigger name says "prepopulate". I guess Edward is setting an initial
default.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis wrote:
> On 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:
[...]
> >CREATE TABLE [dbo].[tblMyTable] (
> >[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> >[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >GO
> You didn't declare any PRIMARY KEY in this database. I think you
> intended to make the column fldCSID a PRIMARY KEY, but you didn't
> declare it as such.

Weird, it IS the PK! An error with the script, or maybe I was a bit
eager on the editing.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

At one time the database was replicated.

> Apart from the uniqueidentifier column, all your columns accept NULLs.
> Do you really want to accept rows with just NULLs in your database?
> Nullable columns should be the exception, not the rule.

Couldn't agree more - not my DB design!

> Are you sure that all these columns need to be ntext? I can somewhat
> imagine having descriptions of over 4,000 characters - but subjects? I
> think that you should probably define Subject and KBSubject ar nvarchar
> with an appropriate maximum length (hopefully less than 100, but I don;t
> know your business of course). You might also want to rethiink the
> choice of ntext/nvarchar over text/varchar - unless you really need to
> store characters from non-Western alphabets or other characters that are
> only available in unicode, there's no reason to use double the space
> taken.

The ntext come from the Access upsizing wizard. The original designer
simply left the default values (it would have been memo columns in
Access)

> On to the trigger (I removed the empty lines for readability)
> >CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
> >FOR INSERT
> >AS
> >BEGIN
> >IF UPDATE(fldKBSubject)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldSubject = i.fldKBSubject
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >IF UPDATE (fldKBDescription)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldDescription = i.fldKBDescription
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >END
> In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
> an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
> column in the table.

Yes, I realise that now - thanks.

> There's also no need to use two seperate update statements. You can
> combine these into one and gain some performance.

I tend to be very "belt and braces" with my code.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

I want to do it because the underlying application is a query system.
Some queries will form part of a Knowledge Base system. The client
wants the (QUERY)Subject and Description columns to be mirrored by the
KBSubject and KBDescription fields, at least initially. Only the KB
versions will be exposed to the customer.

> Anyway, back to your question:
> >I need a trigger (well, I don't *need* one, but it would be optimal!)
> >but I can't get it to work because it references ntext fields.
> You can't reference ntext columns in the inserted column. But you can
> join to the base table and get the data from there. (Or you could
> convert the trigger to an instead of trigger, in which case the ntext
> data *WILL* be available in the inserted table - but that's not the
> easiest solution in this case).
> CREATE TRIGGER PrepopulateKBFieldsFromQuery
> ON dbo.tblMyTable
> FOR INSERT
> AS
> UPDATE MyTable
> SET Subject = KBSubject,
> Description = KBDescription
> WHERE EXISTS
> (SELECT *
> FROM inserted AS i
> WHERE i.CSID = MyTable.CSID)
> go

And that is absolutely spot on! Many thanks.

Edward