I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserIDint
AddressIDint
Address
AddressIDint
Address1varchar(100)
Address2varchar(100)
Address3varchar(100)
Address4varchar(100)
Citynvarchar(100)
PostalCodevarchar(10)
Countrysmallint
AddressTypeint
CreateDatesmalldatetime
CreateUserIDint
Updatedatesmalldatetime
UpdateUserIDint
ProvinceTypeint
Inactivebit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
Stacy
On Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle
Showing posts with label address. Show all posts
Showing posts with label address. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Cascading address returns in query
I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle
Cascading address returns in query
I have a table with users. These users are joined to an address table
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle
by an intermediary table called user_address, which maps the
addressids to the userids. A user can have multiple addresses,
denoted in the Address table by an int identifying the type of
address.
Here's my issue...
I need to return a list of users with their addresses. but... I need
only to see the user's name once. If they have an address type = 1,
then i need that returned. If they don't have an addresstype 1, then
I need the address for the addresstype 2, etc. for four types of
addresses (head office, physical, billing, other).
Here's what the tables look like:
User_Address
UserID int
AddressID int
Address
AddressID int
Address1 varchar(100)
Address2 varchar(100)
Address3 varchar(100)
Address4 varchar(100)
City nvarchar(100)
PostalCode varchar(10)
Country smallint
AddressType int
CreateDate smalldatetime
CreateUserID int
Updatedate smalldatetime
UpdateUserID int
ProvinceType int
Inactive bit
The Users table has the standard user info, as well as a companyid. I
basically need all the contacts for a company, with the cascading
if... else looping statement for the address.
I haven't been able to find anything online about this... any help is
greatly appreciated.
StacyOn Feb 28, 8:22 pm, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I have a table with users. These users are joined to an address table
> by an intermediary table called user_address, which maps the
> addressids to the userids. A user can have multiple addresses,
> denoted in the Address table by an int identifying the type of
> address.
> Here's my issue...
> I need to return a list of users with their addresses. but... I need
> only to see the user's name once. If they have an address type = 1,
> then i need that returned. If they don't have an addresstype 1, then
> I need the address for the addresstype 2, etc. for four types of
> addresses (head office, physical, billing, other).
> Here's what the tables look like:
> User_Address
> UserID int
> AddressID int
> Address
> AddressID int
> Address1 varchar(100)
> Address2 varchar(100)
> Address3 varchar(100)
> Address4 varchar(100)
> City nvarchar(100)
> PostalCode varchar(10)
> Country smallint
> AddressType int
> CreateDate smalldatetime
> CreateUserID int
> Updatedate smalldatetime
> UpdateUserID int
> ProvinceType int
> Inactive bit
> The Users table has the standard user info, as well as a companyid. I
> basically need all the contacts for a company, with the cascading
> if... else looping statement for the address.
> I haven't been able to find anything online about this... any help is
> greatly appreciated.
> Stacy
Can you see if this works for you?
Declare @.User table(UserID int, UserName varchar(50))
Declare @.UserAddress table(UserID int, AddressType int, Address
varchar(50))
insert into @.User values(1,'Emp1')
insert into @.User values(2,'Emp2')
insert into @.User values(3,'Emp3')
insert into @.User values(4,'Emp4')
insert into @.User values(5,'Emp5')
insert into @.UserAddress values(1,1,'Addr11')
insert into @.UserAddress values(1,2,'Addr12')
insert into @.UserAddress values(1,3,'Addr13')
insert into @.UserAddress values(1,4,'Addr14')
insert into @.UserAddress values(2,1,'Addr21')
insert into @.UserAddress values(2,2,'Addr22')
insert into @.UserAddress values(2,3,'Addr23')
insert into @.UserAddress values(3,2,'Addr32')
insert into @.UserAddress values(3,3,'Addr33')
insert into @.UserAddress values(4,1,'Addr41')
insert into @.UserAddress values(4,3,'Addr43')
insert into @.UserAddress values(4,4,'Addr44')
insert into @.UserAddress values(5,4,'Addr54')
Select T.UserID, T.AddressType, U.UserName, A.Address from
(Select Distinct(A.UserID) as UserID, min(A.AddressType)as AddressType
from @.UserAddress A
Group by A.UserID) T
Inner Join @.User U ON U.UserID=T.UserID
INNER JOIN @.UserAddress A ON (A.AddressType = T.AddressType AND
T.UserID = A.UserID)
Thanks
-Mahesh
Seattle
Wednesday, March 7, 2012
Carriage Returns and Line Breaks
I have an address field that is coming back as a single field with carriage returns and line breaks and I would like to have it properly wrap in a single box, but the wrapping is all off. How can I get this to properly break? ThanksMake sure you have text box property "cangrow" to true.|||
Nope. That is not the issue. It is cangrow = true.
What I want is to take a field that comes out as:
John Smith CR LB 123 Main Street CR LB Anytown, MA 01888 CR LB USA
as
John Smith
123 Main Street
Anytown, MA 01888
USA
What I am getting is:
John Smith 123
Main Street Anytown,
MA 01888 USA
So I am looking how to read the CR and LBs and maybe replace them with BR tags, not sure.
|||Also... what is being returned to indicate the line break are char(13)s. I am recreating a report that I had lost and was able to resolve this at one time, but forget how I got around it.|||try select field1 + char(13) + char(10) + field2
Carriage returns
I'm migrating from Crystal. When I inserted address fields which contained
carriage returns, Crystal seemed to recognise them automatically, putting
the second line beneath the first line, etc. RS doesn't do this - obviously
I have to do something more explicit. Cam anybody help?
ThanksFirst: You need SP1. Second: I've only had success with carriage returns
when there physically was a CRLF pair. With the HTML output, the <BR> tags
are not rendered unless said CRLF pair exist.
--
TIM ELLISON
"Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
> I'm migrating from Crystal. When I inserted address fields which contained
> carriage returns, Crystal seemed to recognise them automatically, putting
> the second line beneath the first line, etc. RS doesn't do this -
obviously
> I have to do something more explicit. Cam anybody help?
> Thanks
>|||Seem to have got this sorted with an expression:
=replace((Fields!AD_ADDRESS.Value),chr(13),vbcrlf)
Thanks for the help anyway, will get SP1 installed pronto...
"Tim" <TimmyDotNet@.direcway.com> wrote in message
news:eINaxi24EHA.1976@.TK2MSFTNGP09.phx.gbl...
> First: You need SP1. Second: I've only had success with carriage returns
> when there physically was a CRLF pair. With the HTML output, the <BR>
> tags
> are not rendered unless said CRLF pair exist.
> --
> TIM ELLISON
> "Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
> news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
>> I'm migrating from Crystal. When I inserted address fields which
>> contained
>> carriage returns, Crystal seemed to recognise them automatically, putting
>> the second line beneath the first line, etc. RS doesn't do this -
> obviously
>> I have to do something more explicit. Cam anybody help?
>> Thanks
>>
>
carriage returns, Crystal seemed to recognise them automatically, putting
the second line beneath the first line, etc. RS doesn't do this - obviously
I have to do something more explicit. Cam anybody help?
ThanksFirst: You need SP1. Second: I've only had success with carriage returns
when there physically was a CRLF pair. With the HTML output, the <BR> tags
are not rendered unless said CRLF pair exist.
--
TIM ELLISON
"Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
> I'm migrating from Crystal. When I inserted address fields which contained
> carriage returns, Crystal seemed to recognise them automatically, putting
> the second line beneath the first line, etc. RS doesn't do this -
obviously
> I have to do something more explicit. Cam anybody help?
> Thanks
>|||Seem to have got this sorted with an expression:
=replace((Fields!AD_ADDRESS.Value),chr(13),vbcrlf)
Thanks for the help anyway, will get SP1 installed pronto...
"Tim" <TimmyDotNet@.direcway.com> wrote in message
news:eINaxi24EHA.1976@.TK2MSFTNGP09.phx.gbl...
> First: You need SP1. Second: I've only had success with carriage returns
> when there physically was a CRLF pair. With the HTML output, the <BR>
> tags
> are not rendered unless said CRLF pair exist.
> --
> TIM ELLISON
> "Microsoft" <mitchellpaul@.blueyonder.co.uk> wrote in message
> news:%23Kkhi714EHA.524@.TK2MSFTNGP09.phx.gbl...
>> I'm migrating from Crystal. When I inserted address fields which
>> contained
>> carriage returns, Crystal seemed to recognise them automatically, putting
>> the second line beneath the first line, etc. RS doesn't do this -
> obviously
>> I have to do something more explicit. Cam anybody help?
>> Thanks
>>
>
Carriage return in column values
Hi,
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>
Carriage Contron in field
I am trying to split an address field which contains carriage controls. The address field can have 2 carriage controls in it, ie 3 address lines. I want to split the address into 3 seperate colomns based on the control carriage. I know how to combine 3 co
lumns into 1 but not how to split it out again.
Chard.
hi chard,
I assume you have carriage return in the string data which is represented by
char(13). See following example.
--sample data
create table t(col1 varchar(400))
insert into t values ('address1' + char(13)+ 'address2' + char(13) +
'address3')
insert into t values ('address4' + char(13)+ 'address5' + char(13) +
'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6xbbhghhw')
--query
select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
substring (col1, charindex (char(13), col1) + 1,
(charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
(char(13), col1))) '2nd_address',
right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
from t
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Hi Vishal,
Exactly what I was after. Thanks very much.
Chard.
"Vishal Parkar" wrote:
> hi chard,
> I assume you have carriage return in the string data which is represented by
> char(13). See following example.
> --sample data
> create table t(col1 varchar(400))
> insert into t values ('address1' + char(13)+ 'address2' + char(13) +
> 'address3')
> insert into t values ('address4' + char(13)+ 'address5' + char(13) +
> 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6xbbhghhw')
>
> --query
> select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
> substring (col1, charindex (char(13), col1) + 1,
> (charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
> (char(13), col1))) '2nd_address',
> right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
> from t
>
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
lumns into 1 but not how to split it out again.
Chard.
hi chard,
I assume you have carriage return in the string data which is represented by
char(13). See following example.
--sample data
create table t(col1 varchar(400))
insert into t values ('address1' + char(13)+ 'address2' + char(13) +
'address3')
insert into t values ('address4' + char(13)+ 'address5' + char(13) +
'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6xbbhghhw')
--query
select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
substring (col1, charindex (char(13), col1) + 1,
(charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
(char(13), col1))) '2nd_address',
right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
from t
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Hi Vishal,
Exactly what I was after. Thanks very much.
Chard.
"Vishal Parkar" wrote:
> hi chard,
> I assume you have carriage return in the string data which is represented by
> char(13). See following example.
> --sample data
> create table t(col1 varchar(400))
> insert into t values ('address1' + char(13)+ 'address2' + char(13) +
> 'address3')
> insert into t values ('address4' + char(13)+ 'address5' + char(13) +
> 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6xbbhghhw')
>
> --query
> select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
> substring (col1, charindex (char(13), col1) + 1,
> (charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
> (char(13), col1))) '2nd_address',
> right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
> from t
>
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
carraige return
This is an easy question, I have two address fields and I have combine them
together but I want address_2 to show up on the next line instead of have a
sapce between address1 and address2
Any Idea
Thank in Advance
=Fields!BILLING_ADDRESS.Value & " " Fields!BILLING_ADDRESS_2.Value=Fields!BILLING_ADDRESS.Value & ControlChars.CrLf &
Fields!BILLING_ADDRESS_2.Value
--
Med Bouchenafa
"superQ" <superQ@.discussions.microsoft.com> a écrit dans le message de news:
5E9BA72C-7B82-447F-BA54-4F8251A3E9A8@.microsoft.com...
> This is an easy question, I have two address fields and I have combine
> them
> together but I want address_2 to show up on the next line instead of have
> a
> sapce between address1 and address2
> Any Idea
> Thank in Advance
> =Fields!BILLING_ADDRESS.Value & " " Fields!BILLING_ADDRESS_2.Value
together but I want address_2 to show up on the next line instead of have a
sapce between address1 and address2
Any Idea
Thank in Advance
=Fields!BILLING_ADDRESS.Value & " " Fields!BILLING_ADDRESS_2.Value=Fields!BILLING_ADDRESS.Value & ControlChars.CrLf &
Fields!BILLING_ADDRESS_2.Value
--
Med Bouchenafa
"superQ" <superQ@.discussions.microsoft.com> a écrit dans le message de news:
5E9BA72C-7B82-447F-BA54-4F8251A3E9A8@.microsoft.com...
> This is an easy question, I have two address fields and I have combine
> them
> together but I want address_2 to show up on the next line instead of have
> a
> sapce between address1 and address2
> Any Idea
> Thank in Advance
> =Fields!BILLING_ADDRESS.Value & " " Fields!BILLING_ADDRESS_2.Value
Sunday, February 19, 2012
Capture MAC address in trigger
I have a simple trigger that writes out records to another table when a user
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>
Capture IP Address
Does anyone know how to configure profiler or a way to capture an IP address
of a Failed Login Attempt user to a SQL Server 2000?
Thanks in advance.
JohnJohn,
If you are getting hack in attempts, just go to the command prompt and
type
netstat -n
The SQL attacks will be on 1433 and will be listed as time wait (assuming
you check when you are being hacked).
Russ Stevens|||Keeping up to date with database security can get so stressful makes me sick
to gut.
Thank you very much Russ for your helpful post. I really appreciate it.
-J
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> John,
> If you are getting hack in attempts, just go to the command prompt and
> type
> netstat -n
> The SQL attacks will be on 1433 and will be listed as time wait (assuming
> you check when you are being hacked).
> Russ Stevens
>|||Hmmm...I apologize if this is a novice observation but I just noticed
something interesting that I thought I would share...
I have some MS Access front end databases linked to SQL Server 2000
databases as the backend (tables) on our internal network. I create new SQL
logins for every user that requests access to the database and capture every
user action through SQL Profiler. In analyzing the Profiler trace logs I
noticed some Login Failed attempts to our Master database which really
raised my concern. The interesting part is that the Login Failed attempt
kept saying user 'Admin'. I know that we don't have a specific user name to
any of our databases named 'Admin' especially to our Master. I just did a
test and went to open my linked table through MS Access to SQL Server 2000
and that the Profiler logged the event as a Login Failed attempt to the
Master database even though the linked table is to one of my other defined
databases. Then the dsn odbc login pop up comes up and then I log in with
my valid specified user name and password which is not 'Admin' and am able
to login successfully. All of the Login Failed attempts with 'Admin'
throughout my trace logs have a successful login immediately after with a
valid user name. So it appears that when accessing a SQL Server table
through a link from MS Access it by default tries to access the Master
database with the default user name of 'Admin' and then prompts the user for
the valid login name and password. So maybe these weren't hack attempts
(which I am truly hoping)?
Would be interested if someone could confirm to me if this whole process is
accurate?
Thanks in advance.
-J
"John" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23pr5wns7FHA.3808@.TK2MSFTNGP10.phx.gbl...
> Keeping up to date with database security can get so stressful makes me
> sick to gut.
> Thank you very much Russ for your helpful post. I really appreciate it.
> -J
> "Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
> news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
of a Failed Login Attempt user to a SQL Server 2000?
Thanks in advance.
JohnJohn,
If you are getting hack in attempts, just go to the command prompt and
type
netstat -n
The SQL attacks will be on 1433 and will be listed as time wait (assuming
you check when you are being hacked).
Russ Stevens|||Keeping up to date with database security can get so stressful makes me sick
to gut.
Thank you very much Russ for your helpful post. I really appreciate it.
-J
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> John,
> If you are getting hack in attempts, just go to the command prompt and
> type
> netstat -n
> The SQL attacks will be on 1433 and will be listed as time wait (assuming
> you check when you are being hacked).
> Russ Stevens
>|||Hmmm...I apologize if this is a novice observation but I just noticed
something interesting that I thought I would share...
I have some MS Access front end databases linked to SQL Server 2000
databases as the backend (tables) on our internal network. I create new SQL
logins for every user that requests access to the database and capture every
user action through SQL Profiler. In analyzing the Profiler trace logs I
noticed some Login Failed attempts to our Master database which really
raised my concern. The interesting part is that the Login Failed attempt
kept saying user 'Admin'. I know that we don't have a specific user name to
any of our databases named 'Admin' especially to our Master. I just did a
test and went to open my linked table through MS Access to SQL Server 2000
and that the Profiler logged the event as a Login Failed attempt to the
Master database even though the linked table is to one of my other defined
databases. Then the dsn odbc login pop up comes up and then I log in with
my valid specified user name and password which is not 'Admin' and am able
to login successfully. All of the Login Failed attempts with 'Admin'
throughout my trace logs have a successful login immediately after with a
valid user name. So it appears that when accessing a SQL Server table
through a link from MS Access it by default tries to access the Master
database with the default user name of 'Admin' and then prompts the user for
the valid login name and password. So maybe these weren't hack attempts
(which I am truly hoping)?
Would be interested if someone could confirm to me if this whole process is
accurate?
Thanks in advance.
-J
"John" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23pr5wns7FHA.3808@.TK2MSFTNGP10.phx.gbl...
> Keeping up to date with database security can get so stressful makes me
> sick to gut.
> Thank you very much Russ for your helpful post. I really appreciate it.
> -J
> "Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
> news:u3mohmk7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
Friday, February 10, 2012
Cant update a record using a DetailsView and SqlDataSource
Hi,
I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property.
In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE ... WHERE ID = ?".
In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()
But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out?
Thanks,
Wild Thing
Does your ASP page looks like in this example from .Net help:
This section contains two code examples. The first code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a Microsoft SQL Server database using theGridView control. The second code example demonstrates how to update data in an ODBC database using theGridView control.
The following code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a SQL Server database using theGridView control. TheGridView automatically populates theUpdateParameters collection, inferring the parameters from theBoundField objects, and calls theUpdate method when theUpdate link on the editableGridView is selected. This example also includes some post-processing: after a record is updated, a notification e-mail message is sent.
<%@.Page Language="VB" %><%@.Import Namespace="System.Web.Mail" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><SCRIPT runat="server"> Sub OnDSUpdatedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs) If e.AffectedRows > 0 Then ' Perform any additional processing, ' such as setting a status label after the operation. Label1.Text = Request.LogonUserIdentity.Name & _ " changed user information successfully!" Else Label1.Text = "No data updated!" End If End Sub 'OnDSUpdatedHandler</SCRIPT><HTML> <BODY> <FORM runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" UpdateCommand="Update Employees SET FirstName=@.FirstName,LastName=@.LastName,Title=@.Title WHERE EmployeeID=@.EmployeeID" OnUpdated="OnDSUpdatedHandler"> </asp:SqlDataSource> <asp:GridView id="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" AutoGenerateEditButton="True" DataSourceID="SqlDataSource1"> <columns> <asp:BoundField HeaderText="First Name" DataField="FirstName" /> <asp:BoundField HeaderText="Last Name" DataField="LastName" /> <asp:BoundField HeaderText="Title" DataField="Title" /> </columns> </asp:GridView> <asp:Label id="Label1" runat="server"> </asp:Label> </FORM> </BODY></HTML>
Hi,
Thanks for your help! I'll go through the code you've attached.
I managed to solve the problem by binding the detailsview to the data source from scratch, and asking it to retrieve the ID parameter for the select statement from the Session Field. I don't know if it's the best alternative, but it's working!
Thanks!
Wild Thing
Subscribe to:
Posts (Atom)