I try to connect to a LDAP server from SQL query analyzer with the following string
(SELECT * FROM OPENQUERY
(ADSI,'SELECT * FROM ''LDAP://DC=test-02,DC=test,CD=no'' '))
This resulted in a error meesage:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [Non-interface error: OLE DB provider ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
What is the problem ? It is something wrong with my db-setup, the query or what ? I try to do the same thing in a VB-program and that worked fine.Did you setup the linked server?
exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
instead of select * try and name them to see if you can at least return something
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://DC=test-02,DC=test,CD=no'' ')
also, if objects are in containers you can specify the container name by CN= or if in OU's - OU=
if you are only wanting users you can specify
WHERE objectCategory = ''Person'' AND objectClass = ''user''
HTHsql
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Query - SUM multi-column
I use a query to get customer unpaid amount into different colomns according to due day as the following:
Select c.credit_controller,c.customer,c.name,i.unall_amount,
CASE /*cash due*/
when i.kind='CSH' then i.unall_amount
else 0
end as amtcash,
CASE /*sales_item.due_date - today < 0 */
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate()) < 0)
THEN i.unall_amount
ELSE 0
END AS amtcur,
CASE /*sales_item.due_date - today > 0 & <= 30*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 0 AND 30) THEN i.unall_amount
ELSE 0
END AS amt30,
CASE /*sales_item.due_date - today > 30 & <= 60*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 31 AND 60) THEN i.unall_amount
ELSE 0
END AS amt60,
CASE /*sales_item.due_date - today > 60 & <= 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 61 AND 90) THEN i.unall_amount
ELSE 0
END AS amt90,
CASE /*sales_item.due_date - today > 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
> 90) THEN i.unall_amount
ELSE 0
END AS amt120,
c.analysis_codes
From scheme.slcustm c INNER JOIN scheme.slitemm i ON c.customer=i.customer
I want to SUM those records and GROUP BY c.credit_controller & c.customer in a SELECT query.
If I use SUM(i.unall_amount) instead of i.unall_amount, there're errors as the following:
Column 'c.cusomter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'i.kind' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column c.analaysis_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How to SUM in this query?
Thanks!
yabing
When you use GROUP BY statetment, then any column used in select list must be also GROUP BY list or under some aggregation function.
So if you want GROUP BY by c.credit_controller, c.customer you also need include c.name into GROUP BY, then use SUM(i.unall_amount) and SUM() around each CASE statement:
Code Snippet
Select c.credit_controller,c.customer,c.name, SUM(i.unall_amount),
SUM( CASE /*cash due*/
when i.kind='CSH' then i.unall_amount
else 0
end) as amtcash,
SUM( CASE /*sales_item.due_date - today < 0 */
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate()) < 0)
THEN i.unall_amount
ELSE 0
END )AS amtcur,
SUM( CASE /*sales_item.due_date - today > 0 & <= 30*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 0 AND 30) THEN i.unall_amount
ELSE 0
END )AS amt30,
SUM( CASE /*sales_item.due_date - today > 30 & <= 60*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 31 AND 60) THEN i.unall_amount
ELSE 0
END )AS amt60,
SUM( CASE /*sales_item.due_date - today > 60 & <= 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 61 AND 90) THEN i.unall_amount
ELSE 0
END )AS amt90,
SUM( CASE /*sales_item.due_date - today > 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
> 90) THEN i.unall_amount
ELSE 0
END ) AS amt120
From scheme.slcustm c INNER JOIN scheme.slitemm i ON c.customer=i.customer
GROUP BY c.credit_controller,c.customer,c.name
|||Hi Konstantin,
Thanks for your replay. It works.
I understand "When you use GROUP BY statetment, then any column used in select list must be also GROUP BY list or under some aggregation function." But I only want to group value by two columns - c.credit_controller and c.customer, not c.name and c.analysis_codes. When using "GROUP BY c.credit_controller,c.customer,c.name,c.analysis_codes", how it groups columns? does it group the columns one by one for all the four columns in the list in the GROUP BY clause? does it aggregate values for everty possible combination of the columns in the GROUP BY clause?
Though it seems it doesn't group that way when testing. But why?
Thanks,
yabing
|||
If you use "GROUP BY c.credit_controller,c.customer,c.name,c.analysis_codes" its group and aggregate for every possible combinations. So if you have few different values of c.name,c.analysis_codes for each unique pair of c.credit_controller,c.customer you will get additional lines.
But for example if you have following values:
c.credit_controller c.customer c.name
1 1 A
1 1 B
and use GROUP BY c.credit_controller,c.customer plus c.name in SELECT list. What do you want see in your result set? A or B?
You must include c.name in aggregation. For example you could use MIN or MAX or (if you have SQL Server 2005) write you own User-Defined Aggregate.This UDAG could concat values or do something else.
|||Dear Konstantin.
I have one question of GROUP BY,
When I use GROUP BY and use SUM around each CASE statement, it take very long query time.
Can we set a condition eg, query for c.name='A' only?
Thanks
pps1
|||You could write WHERE for filtering. GROUP BY works after WHEREQuery - pop-up menu for user data entry
Using the following syntax:
Select fname, lastn
From list
Where fname like 'jones'
I need the syntax that the user can enter a name to perform a query (the
user will enter a name on a pop-up menu before the query is performed).Roy,
You didn't say in what context you would be using this, so the below-code
assumes you are using a Stored Procedure:
CREATE STORED PROCEDURE [dbo].[usp_FindName]
@.LName varchar(100)=''
AS
IF @.LName<>''
BEGIN
Select fname, lastn
From list
Where fname like @.LName
END
GO
Hope thi assists,
Tony
"ROY A. DAY" wrote:
> Using the following syntax:
> Select fname, lastn
> From list
> Where fname like 'jones'
> I need the syntax that the user can enter a name to perform a query (the
> user will enter a name on a pop-up menu before the query is performed).
Select fname, lastn
From list
Where fname like 'jones'
I need the syntax that the user can enter a name to perform a query (the
user will enter a name on a pop-up menu before the query is performed).Roy,
You didn't say in what context you would be using this, so the below-code
assumes you are using a Stored Procedure:
CREATE STORED PROCEDURE [dbo].[usp_FindName]
@.LName varchar(100)=''
AS
IF @.LName<>''
BEGIN
Select fname, lastn
From list
Where fname like @.LName
END
GO
Hope thi assists,
Tony
"ROY A. DAY" wrote:
> Using the following syntax:
> Select fname, lastn
> From list
> Where fname like 'jones'
> I need the syntax that the user can enter a name to perform a query (the
> user will enter a name on a pop-up menu before the query is performed).
Query
Hi Friends,
I have the following query how can solve this any help........
i want to do my rowcount round and not the flot and i can only change in query and not in program
I have the following query how can solve this any help........
i want to do my rowcount round and not the flot and i can only change in query and not in program
set ROWCOUNT 5.1
select * from Test1
AshutoshI am really not sure what you are asking here. Could you please explain further?
Terri|||ROWCOUNT must be an integer. You cannot return 5.1 rows from your query...|||... how are you going to return .1 of a query?
Query
Hi, I have the following tables-
How do I answer this query?
EMPLOYEE
ssn ename dnumber
DEPARTMENT
dnumber dname
PROJECT
pnumber dnumber
WORKS_ON
ssn pnumber hours
For each employee, find the department he works in and the total hours he spent on projects not controlled by the department he is working on.
Ie, he works on some extra projects not controlled by his department.
Thanks.This should get you going
SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
(SELECT e.ssn, e.ename, d.dname, e.dnumber
FROM employee e, department d
WHERE e.dnumber = d.dnumber) ed,
(SELECT ssn, dnumber, hours
FROM works_on w, project p
WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;|||Hi,
SELECT e.ename
, e.dname
, sum(p.hours)
FROM
employee e
, department d
, project p
, works_on w
WHERE
e.dnumber = d.dnumber
and e.ssn = w.ssn
and w.pnumber = p.pnumber
GROUP BY e.ename
;|||Originally posted by gannet
This should get you going
SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
(SELECT e.ssn, e.ename, d.dname, e.dnumber
FROM employee e, department d
WHERE e.dnumber = d.dnumber) ed,
(SELECT ssn, dnumber, hours
FROM works_on w, project p
WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;
Thanks,
That was a great solution!!
:-)
How do I answer this query?
EMPLOYEE
ssn ename dnumber
DEPARTMENT
dnumber dname
PROJECT
pnumber dnumber
WORKS_ON
ssn pnumber hours
For each employee, find the department he works in and the total hours he spent on projects not controlled by the department he is working on.
Ie, he works on some extra projects not controlled by his department.
Thanks.This should get you going
SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
(SELECT e.ssn, e.ename, d.dname, e.dnumber
FROM employee e, department d
WHERE e.dnumber = d.dnumber) ed,
(SELECT ssn, dnumber, hours
FROM works_on w, project p
WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;|||Hi,
SELECT e.ename
, e.dname
, sum(p.hours)
FROM
employee e
, department d
, project p
, works_on w
WHERE
e.dnumber = d.dnumber
and e.ssn = w.ssn
and w.pnumber = p.pnumber
GROUP BY e.ename
;|||Originally posted by gannet
This should get you going
SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
(SELECT e.ssn, e.ename, d.dname, e.dnumber
FROM employee e, department d
WHERE e.dnumber = d.dnumber) ed,
(SELECT ssn, dnumber, hours
FROM works_on w, project p
WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;
Thanks,
That was a great solution!!
:-)
Monday, March 26, 2012
Query
I have two tables
Table 1 is as following
Id
1
2
3
Table 2 is as following. Here Id is the foreign key.
Id Name
1 David
1 James
1 Larry
2 Smith
2 Will
How can i write a query joing two tables, which can return result as followi
ng
Id Name
1 David,James,Larry
2 Smith,Will
ThanksCheck whether the below given script gives ur desired output or not
create table sample_name(
name_id int primary key)
go
insert into sample_name values(1)
insert into sample_name values(2)
insert into sample_name values(3)
go
create table sample_name_sub(
name_id int foreign key references sample_name(name_id),
cus_name varchar(50)
)
go
insert into sample_name_sub values(1,'David')
insert into sample_name_sub values(1,'James')
insert into sample_name_sub values(1,'Kelly')
insert into sample_name_sub values(2,'smith')
insert into sample_name_sub values(2,'Welly')
go
create function fn_return_names(@.id int)
returns varchar(500)
as
Begin
declare @.name varchar(500)
set @.name = ''
select @.name = @.name + cus_name + ','
from sample_name_sub where name_id = @.id
if len(@.name) >= 1
select @.name = substring(@.name,1,len(@.name)-1)
return @.name
End
go
select name_id,
dbo.fn_return_names(name_id)
from sample_name
Regards
Sudarshan
"mvp" wrote:
> I have two tables
> Table 1 is as following
> Id
> 1
> 2
> 3
> Table 2 is as following. Here Id is the foreign key.
> Id Name
> 1 David
> 1 James
> 1 Larry
> 2 Smith
> 2 Will
> How can i write a query joing two tables, which can return result as follo
wing
> Id Name
> 1 David,James,Larry
> 2 Smith,Will
> Thanks|||Also refer this to know why you need function
[url]http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true[/
url]
Madhivanan
Table 1 is as following
Id
1
2
3
Table 2 is as following. Here Id is the foreign key.
Id Name
1 David
1 James
1 Larry
2 Smith
2 Will
How can i write a query joing two tables, which can return result as followi
ng
Id Name
1 David,James,Larry
2 Smith,Will
ThanksCheck whether the below given script gives ur desired output or not
create table sample_name(
name_id int primary key)
go
insert into sample_name values(1)
insert into sample_name values(2)
insert into sample_name values(3)
go
create table sample_name_sub(
name_id int foreign key references sample_name(name_id),
cus_name varchar(50)
)
go
insert into sample_name_sub values(1,'David')
insert into sample_name_sub values(1,'James')
insert into sample_name_sub values(1,'Kelly')
insert into sample_name_sub values(2,'smith')
insert into sample_name_sub values(2,'Welly')
go
create function fn_return_names(@.id int)
returns varchar(500)
as
Begin
declare @.name varchar(500)
set @.name = ''
select @.name = @.name + cus_name + ','
from sample_name_sub where name_id = @.id
if len(@.name) >= 1
select @.name = substring(@.name,1,len(@.name)-1)
return @.name
End
go
select name_id,
dbo.fn_return_names(name_id)
from sample_name
Regards
Sudarshan
"mvp" wrote:
> I have two tables
> Table 1 is as following
> Id
> 1
> 2
> 3
> Table 2 is as following. Here Id is the foreign key.
> Id Name
> 1 David
> 1 James
> 1 Larry
> 2 Smith
> 2 Will
> How can i write a query joing two tables, which can return result as follo
wing
> Id Name
> 1 David,James,Larry
> 2 Smith,Will
> Thanks|||Also refer this to know why you need function
[url]http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true[/
url]
Madhivanan
Query
Hi,
I have an Audit table that has the following fields and values
FieldName ChangedValue
FirstName Scott
LastName Hello
how am i able to create a query to show the above in one line
FirstName LastName
Scott Hello
Thanks
EdHi
Unless you can identify how to pair your records then you may get spurious
results
If say there was an identifier column (called id) you can link using that
SELECT a.ChangedValue as [Firstname], b.ChangedValue as [LastName]
FROM dbo.MyAudit A
JOIN dbo.MyAudit B on A.id = B.Id
WHERE a.FieldName = 'FirstName'
b.FieldName = 'LastName'
This previous post may also help:
http://tinyurl.com/6rhsj
John
"Ed" wrote:
> Hi,
> I have an Audit table that has the following fields and values
> FieldName ChangedValue
> FirstName Scott
> LastName Hello
> how am i able to create a query to show the above in one line
> FirstName LastName
> Scott Hello
> Thanks
> Ed
>
>
I have an Audit table that has the following fields and values
FieldName ChangedValue
FirstName Scott
LastName Hello
how am i able to create a query to show the above in one line
FirstName LastName
Scott Hello
Thanks
EdHi
Unless you can identify how to pair your records then you may get spurious
results
If say there was an identifier column (called id) you can link using that
SELECT a.ChangedValue as [Firstname], b.ChangedValue as [LastName]
FROM dbo.MyAudit A
JOIN dbo.MyAudit B on A.id = B.Id
WHERE a.FieldName = 'FirstName'
b.FieldName = 'LastName'
This previous post may also help:
http://tinyurl.com/6rhsj
John
"Ed" wrote:
> Hi,
> I have an Audit table that has the following fields and values
> FieldName ChangedValue
> FirstName Scott
> LastName Hello
> how am i able to create a query to show the above in one line
> FirstName LastName
> Scott Hello
> Thanks
> Ed
>
>
query
Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this
?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this
?one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this
?> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
Friday, March 23, 2012
query
Can I have conatins clause like following
where contains(col_name ,'%')
where contains (col_name , '/*')
basically looking for special characters in text columnsHi.
Perhaps you want to look at this example
CREATE TABLE #TEST
(
COL VARCHAR(50)
)
INSERT INTO #TEST VALUES ('FF')
INSERT INTO #TEST VALUES ('F%F')
INSERT INTO #TEST VALUES ('FF%')
INSERT INTO #TEST VALUES ('NNFF')
INSERT INTO #TEST VALUES ('F88F')
INSERT INTO #TEST VALUES ('*FF')
SELECT * FROM #TEST WHERE CHARINDEX('%',COL)>0
<anonymous@.discussions.microsoft.com> wrote in message
news:085e01c39dfe$ff0d6db0$a501280a@.phx.gbl...
> Can I have conatins clause like following
>
> where contains(col_name ,'%')
> where contains (col_name , '/*')
> basically looking for special characters in text columns
>
where contains(col_name ,'%')
where contains (col_name , '/*')
basically looking for special characters in text columnsHi.
Perhaps you want to look at this example
CREATE TABLE #TEST
(
COL VARCHAR(50)
)
INSERT INTO #TEST VALUES ('FF')
INSERT INTO #TEST VALUES ('F%F')
INSERT INTO #TEST VALUES ('FF%')
INSERT INTO #TEST VALUES ('NNFF')
INSERT INTO #TEST VALUES ('F88F')
INSERT INTO #TEST VALUES ('*FF')
SELECT * FROM #TEST WHERE CHARINDEX('%',COL)>0
<anonymous@.discussions.microsoft.com> wrote in message
news:085e01c39dfe$ff0d6db0$a501280a@.phx.gbl...
> Can I have conatins clause like following
>
> where contains(col_name ,'%')
> where contains (col_name , '/*')
> basically looking for special characters in text columns
>
query
Hi
I have a following problem;
Lets say i have table with names and value in this table "håkan"
Now user enters text "hakan" for search and query must return alsow
"håkan".
Is in SQL any "special" function for this :)?
one option is create own function, but there is toooo many different options
a-ä, o-ö aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI = @.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "håkan"
> Now user enters text "hakan" for search and query must return alsow
> "håkan".
> Is in SQL any "special" function for this :)?
> one option is create own function, but there is toooo many different
> options a-ä, o-ö aso aso.
>
> Regards;
> Meelis
>
I have a following problem;
Lets say i have table with names and value in this table "håkan"
Now user enters text "hakan" for search and query must return alsow
"håkan".
Is in SQL any "special" function for this :)?
one option is create own function, but there is toooo many different options
a-ä, o-ö aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI = @.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "håkan"
> Now user enters text "hakan" for search and query must return alsow
> "håkan".
> Is in SQL any "special" function for this :)?
> one option is create own function, but there is toooo many different
> options a-ä, o-ö aso aso.
>
> Regards;
> Meelis
>
query
Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this
?one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this
?> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>
Wednesday, March 21, 2012
Queries mixing two tables (beginner)
I have following two tables in my database:
cars(regnr, brand, owner, inspected)
owner(ssNr, name, adress)
And i would like a queiry with SQL that is the returning the name of all who is owning a Saab. How would that look?
Something like:
SELECT name,
FROM cars, owner WHERE brand = Saab
??
And how do u return regnr and brand of all cars that has not been inspected since 2006-02-22?
Very thankful for your help!
DanneAs I said in your other thread...
You need some form of primary key/foreign key relationship between the two tables. This allows you to join the table together.
What field in the owners table does the owner field in the cars table correspond to?|||What field in the owners table does the owner field in the cars table correspond to?
my guess:
cars(regnr, brand, owner, inspected)
owner(ssNr, name, adress)|||select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB"
something like that?
thank u for all fast answers :)|||no, not quite, but you're close
sorry, but i cannot continue just giving you the answer, i suspect this is homework and you have to try harder
:)|||yes i gotta specify that only the names shall be written out...
after some googling i came to this conclusion??
select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB" order by owner.name|||select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB"
something like that?
Test it!
What are your expected results and do they match the returned values? :D|||Technically, I think it will return all the records Danne expects, but
the join is not quite right.|||it works quite alright :)
RedNeckGeek, i wonder what is wrong with the join?|||I suggest you look at the different types of joins available in SQL.
Google is a source of endless knowledge ;)|||The following stepwise procedure to construct your SELECT qurey might help with this kind of questions:
Step 1. In which table(s) is the information to be found?
==> answer: in CARS and OWNER
==> put down the following part of your query:
FROM cars, owner
Keep in mind that this asks for a "Cartesian product" of the two tables:
any row of CARS is combined with any row of OWNER.
Step 2. Which horizontal restriction do you want to apply to those rows?
==> answer: (a) only those combinations where the field CARS.OWNER equals the OWNER.SSNR field (since other combinations are meaningless)
(b) and from these, only the rows that have CARS.BRAND = 'Saab'
==> put down the following part of your query:
WHERE cars.owner = owner.ssnr AND cars.brand = 'Saab'
Step 3. Do you want to see individual rows, or summary information?
==> answer: individual information
==> so do NOT put any GROUP BY or HAVING clauses.
Step 4. What fields (attributes) do I want to see from each of the remaining rows?
==> answer: the name of the car owner.
==> put down the following part of your query (before the FROM):
SELECT owner.name
In summary, this gives the following:SELECT owner.name
FROM cars, owner
WHERE cars.owner = owner.ssnr AND cars.brand = 'Saab'
As a final check, a useful rule-of-thumb, make sure that there are n-1 "join conditions" in the WHERE clause, where "n" is the number of tables in the FROM clause.
A join condition links a column from one table to a column of an other table.
Here it's the condition cars.owner = owner.ssnr
With too few join conditions, meaningless combinations will be kept (typically visible by the fact that too many rows show up in the end result).
A better way to write the above query, and one where you cannot forget join conditions, is by using the "... INNER JOIN ... ON ... " syntax, but the ingredients and the steps remain the same:SELECT owner.name
FROM cars INNER JOIN owner ON cars.owner = owner.ssnr
WHERE cars.brand = 'Saab'
Using the same procedure could actually lead to a completely other solution (one using a subquery):
Step 1. I need only information from the table OWNER.
==> FROM owner
Step 2. I want to see only those owners that appear in the CARS table with a certain condition there.
==> WHERE ssnr IN (SELECT owner FROM cars WHERE .... )
Step 3: no summary.
Step 4: just the column NAME.
==> SELECT name
Now we are left with the task to create a list of "owner" values from table CARS:
Step 1: which table(s)?
==> FROM cars
Step 2: which horizontal restriction?
==> WHERE cars.brand = 'Saab'
Putting it all together:SELECT name
FROM owner
WHERE ssnr IN (SELECT owner
FROM cars
WHERE brand = 'Saab')
Additional advantage (or disadvantage?) of the latter solution is that persons having several Saabs will only be shown once, while in the first solution they will be shown as many times as their number of Saabs.
(Adding a "DISTINCT" after SELECT would "solve" that, but at the cost of a must slower query than the one with the subquery.)
cars(regnr, brand, owner, inspected)
owner(ssNr, name, adress)
And i would like a queiry with SQL that is the returning the name of all who is owning a Saab. How would that look?
Something like:
SELECT name,
FROM cars, owner WHERE brand = Saab
??
And how do u return regnr and brand of all cars that has not been inspected since 2006-02-22?
Very thankful for your help!
DanneAs I said in your other thread...
You need some form of primary key/foreign key relationship between the two tables. This allows you to join the table together.
What field in the owners table does the owner field in the cars table correspond to?|||What field in the owners table does the owner field in the cars table correspond to?
my guess:
cars(regnr, brand, owner, inspected)
owner(ssNr, name, adress)|||select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB"
something like that?
thank u for all fast answers :)|||no, not quite, but you're close
sorry, but i cannot continue just giving you the answer, i suspect this is homework and you have to try harder
:)|||yes i gotta specify that only the names shall be written out...
after some googling i came to this conclusion??
select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB" order by owner.name|||select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB"
something like that?
Test it!
What are your expected results and do they match the returned values? :D|||Technically, I think it will return all the records Danne expects, but
the join is not quite right.|||it works quite alright :)
RedNeckGeek, i wonder what is wrong with the join?|||I suggest you look at the different types of joins available in SQL.
Google is a source of endless knowledge ;)|||The following stepwise procedure to construct your SELECT qurey might help with this kind of questions:
Step 1. In which table(s) is the information to be found?
==> answer: in CARS and OWNER
==> put down the following part of your query:
FROM cars, owner
Keep in mind that this asks for a "Cartesian product" of the two tables:
any row of CARS is combined with any row of OWNER.
Step 2. Which horizontal restriction do you want to apply to those rows?
==> answer: (a) only those combinations where the field CARS.OWNER equals the OWNER.SSNR field (since other combinations are meaningless)
(b) and from these, only the rows that have CARS.BRAND = 'Saab'
==> put down the following part of your query:
WHERE cars.owner = owner.ssnr AND cars.brand = 'Saab'
Step 3. Do you want to see individual rows, or summary information?
==> answer: individual information
==> so do NOT put any GROUP BY or HAVING clauses.
Step 4. What fields (attributes) do I want to see from each of the remaining rows?
==> answer: the name of the car owner.
==> put down the following part of your query (before the FROM):
SELECT owner.name
In summary, this gives the following:SELECT owner.name
FROM cars, owner
WHERE cars.owner = owner.ssnr AND cars.brand = 'Saab'
As a final check, a useful rule-of-thumb, make sure that there are n-1 "join conditions" in the WHERE clause, where "n" is the number of tables in the FROM clause.
A join condition links a column from one table to a column of an other table.
Here it's the condition cars.owner = owner.ssnr
With too few join conditions, meaningless combinations will be kept (typically visible by the fact that too many rows show up in the end result).
A better way to write the above query, and one where you cannot forget join conditions, is by using the "... INNER JOIN ... ON ... " syntax, but the ingredients and the steps remain the same:SELECT owner.name
FROM cars INNER JOIN owner ON cars.owner = owner.ssnr
WHERE cars.brand = 'Saab'
Using the same procedure could actually lead to a completely other solution (one using a subquery):
Step 1. I need only information from the table OWNER.
==> FROM owner
Step 2. I want to see only those owners that appear in the CARS table with a certain condition there.
==> WHERE ssnr IN (SELECT owner FROM cars WHERE .... )
Step 3: no summary.
Step 4: just the column NAME.
==> SELECT name
Now we are left with the task to create a list of "owner" values from table CARS:
Step 1: which table(s)?
==> FROM cars
Step 2: which horizontal restriction?
==> WHERE cars.brand = 'Saab'
Putting it all together:SELECT name
FROM owner
WHERE ssnr IN (SELECT owner
FROM cars
WHERE brand = 'Saab')
Additional advantage (or disadvantage?) of the latter solution is that persons having several Saabs will only be shown once, while in the first solution they will be shown as many times as their number of Saabs.
(Adding a "DISTINCT" after SELECT would "solve" that, but at the cost of a must slower query than the one with the subquery.)
Monday, March 12, 2012
QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)
I am getting the following message when I attempt to Export data in SQL Management Studio from my workstation. The error occurs as soon as I click Next to go to the Source Data Sources page of the Export wizzard. I am using SQL Server 2005 Management Studio. I get a similar error in Visual Studio 2005 when I attempt to open a SSIS project. When I click on the link in the error message the page at Micorsoft Help says there is no explanation. Any ideas on what might be wrong or what settings I am missing?
The error text is:
This wizard will close because it encountered the following error: (Microsoft SQL Server)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476
===================================
QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)
Program Location:
at System.Runtime.InteropServices.CustomMarshalers.EnumeratorToEnumVariantMarshaler.MarshalNativeToManaged(IntPtr pNativeData)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSDBProviderInfos90.GetEnumerator()
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)
Review this thread http://groups-beta.google.com/group/microsoft.public.dotnet.framework.setup/browse_thread/thread/a2ed96d68cb349f3/db810a1bcf8f66e4?q=qi+ienumvariant&_done=%2Fgroups%3Fhl%3Den%26lr%3Dlang_en%26safe%3Doff%26c2coff%3D1%26q%3Dqi+ienumvariant%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#db810a1bcf8f66e4 in other groups that might help to resolve the issue.
HTH
|||This may be due to InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)
I am getting the following message when I attempt to Export data in SQL Management Studio from my workstation. The error occurs as soon as I click Next to go to the Source Data Sources page of the Export wizzard. I am using SQL Server 2005 Management Studio. I get a similar error in Visual Studio 2005 when I attempt to open a SSIS project. When I click on the link in the error message the page at Micorsoft Help says there is no explanation. Any ideas on what might be wrong or what settings I am missing?
The error text is:
This wizard will close because it encountered the following error: (Microsoft SQL Server)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476
===================================
QI for IEnumVARIANT failed on the unmanaged server. (CustomMarshalers)
Program Location:
at System.Runtime.InteropServices.CustomMarshalers.EnumeratorToEnumVariantMarshaler.MarshalNativeToManaged(IntPtr pNativeData)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSDBProviderInfos90.GetEnumerator()
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)
Review this thread http://groups-beta.google.com/group/microsoft.public.dotnet.framework.setup/browse_thread/thread/a2ed96d68cb349f3/db810a1bcf8f66e4?q=qi+ienumvariant&_done=%2Fgroups%3Fhl%3Den%26lr%3Dlang_en%26safe%3Doff%26c2coff%3D1%26q%3Dqi+ienumvariant%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#db810a1bcf8f66e4 in other groups that might help to resolve the issue.
HTH
|||This may be due to InstallShield. Here is their solution: http://support.installshield.com/kb/view.asp?articleid=q106194Friday, March 9, 2012
QA returns different records from SQL Server EM
SQL Server 7.0
If I run the following in Query Analyzer I get no records returned:
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'
If, however, I run either :
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'
or
exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'
I get a single record returned in each case (which is what I would
expect).
The SQL for GetLeadsOutcome_Dealer is:
CREATE PROCEDURE GetLeadsOutcome_Dealer
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.OutcomeTypeID integer,
@.DealerCode varchar(8000)
AS
DECLARE @.TotalLeads integer
BEGIN
SELECT @.TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE
(tblLeads.fldDealerID IS NOT NULL)
SELECT DISTINCT (dbo.tblDealers.fldDealerName),
COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@.TotalLeads AS TotalLeads
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (((dbo.tblLeads.fldEntered) BETWEEN @.FromDate And @.ToDate)) AND
tblOutcome.fldOutcomeID=@.OutcomeTypeID AND
(dbo.tblDealers.fldDealerCode in (@.DealerCode))
GROUP BY dbo.tblDealers.fldDealerName
ORDER BY dbo.tblDealers.fldDealerName
END
GO
However, if I open EM, open a table in query view, and paste this into
the SQL window,
SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) AS
LeadCount
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND
(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,
183))
GROUP BY tblDealers.fldDealerName
ORDER BY tblDealers.fldDealerName
I get two records returned.
What is happening?
TIA
Edward
TABLE DEFS:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealers
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINT
FK_tblSystemUsers_tblDealers
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINT
FK_tblLeadNotes_tblLeads
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegion
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblDealers]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblLeads]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOutcome]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblRegion]
GO
CREATE TABLE [dbo].[tblDealers] (
[fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDealerCode] [varchar] (50) NOT NULL ,
[fldDealerName] [varchar] (50) NULL ,
[fldDealerTel] [varchar] (20) NULL ,
[fldDealerEmail] [varchar] (100) NULL ,
[fldDealerContact] [varchar] (50) NULL ,
[fldRegionID] [int] NULL ,
[fldDealerActive] [smallint] NOT NULL ,
[fldHeadOffice] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLeads] (
[fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,
[fldAccountNo] [varchar] (50) NULL ,
[fldDealerID] [int] NULL ,
[fldStatusID] [int] NOT NULL ,
[fldOutcomeID] [int] NOT NULL ,
[fldContacted] [smallint] NOT NULL ,
[fldDateContacted] [smalldatetime] NULL ,
[fldAppointment] [smallint] NULL ,
[fldShowRoom] [smallint] NOT NULL ,
[fldTestDrive] [smallint] NOT NULL ,
[fldSalesPersonID] [int] NULL ,
[fldCustomerName] [varchar] (50) NULL ,
[fldCHouseNo] [varchar] (50) NULL ,
[fldCStreet] [varchar] (50) NULL ,
[fldCDistrict] [varchar] (50) NULL ,
[fldCTown] [varchar] (40) NULL ,
[fldCCounty] [varchar] (50) NULL ,
[fldCPostcode] [varchar] (50) NULL ,
[fldPhoneInd] [varchar] (20) NULL ,
[fldCTel] [varchar] (50) NULL ,
[fldNewBusinessDate] [smalldatetime] NULL ,
[fldAgreementType] [varchar] (50) NULL ,
[fldCashPrice] [smallmoney] NULL ,
[fldBalanceFin] [smallmoney] NULL ,
[fldCustRate] [varchar] (10) NULL ,
[fldOrigTerm] [int] NULL ,
[fldPPPType] [varchar] (10) NULL ,
[fldBalloonValue] [smallmoney] NULL ,
[fldMonthlyInstal] [smallmoney] NULL ,
[fldRegNo] [varchar] (10) NULL ,
[fldRegDate] [smalldatetime] NULL ,
[fldModel] [varchar] (50) NULL ,
[fldDescription] [varchar] (50) NULL ,
[fldTheoPIFDate] [smalldatetime] NULL ,
[fldMonthsToGo] [int] NULL ,
[fldBalanceOS] [smallmoney] NULL ,
[fldLeadPrinted] [smallint] NULL ,
[fldMergeFile] [varchar] (255) NULL ,
[fldTPS] [varchar] (10) NULL ,
[fldMPS] [varchar] (10) NULL ,
[fldUpdated] [smalldatetime] NULL ,
[fldUpdatedBy] [int] NULL ,
[fldEntered] [smalldatetime] NULL ,
[fldReasonLeadNotProgressed] [varchar] (5000) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblOutcome] (
[fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,
[fldOutcomeCode] [int] NULL ,
[fldOutcome] [varchar] (100) NULL ,
[fldConvertedSales] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblRegion] (
[fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,
[fldRegionCode] [varchar] (10) NULL ,
[fldRegion] [varchar] (50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED
(
[fldDealerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED
(
[fldLeadID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED
(
[fldOutcomeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED
(
[fldRegionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR
[fldDealerActive],
CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR
[fldHeadOffice],
CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED
(
[fldDealerCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR
[fldOutcomeID],
CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],
CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR
[fldAppointment],
CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],
CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],
CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR
[fldLeadPrinted],
CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],
CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]
GO
ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR
[fldConvertedSales]
GO
ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED
(
[fldRegionCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDealers] ADD
CONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY
(
[fldRegionID]
) REFERENCES [dbo].[tblRegion] (
[fldRegionID]
)
GO
ALTER TABLE [dbo].[tblLeads] ADD
CONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY
(
[fldDealerID]
) REFERENCES [dbo].[tblDealers] (
[fldDealerID]
)
GO
alter table [dbo].[tblLeads] nocheck constraint
[FK_tblLeads_tblDealers]
GOEdward (teddysnips@.hotmail.com) writes:
> If I run the following in Query Analyzer I get no records returned:
> exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'
>...
> The SQL for GetLeadsOutcome_Dealer is:
> ...
> (dbo.tblDealers.fldDealerCode in (@.DealerCode))
Apparently, you dont have any dealer with the code '176, 183'.
> However, if I open EM, open a table in query view, and paste this into
> the SQL window,
>...
> (tblDealers.fldDealerCode IN (176, 183))
Here you have two different values. In the procedure you have only one.
Looks like you need a procedure that unpacks a list of values into
a table. Look at
http://www.algonet.se/~sommar/array...-of-string-proc.
(This brings you to a procedure which is for SQL7. But you have have
to read some other parts of the long text to make sense of it.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||teddysnips@.hotmail.com (Edward) wrote in message news:<25080b60.0312020231.449732c@.posting.google.com>...
> SQL Server 7.0
> If I run the following in Query Analyzer I get no records returned:
> exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'
> If, however, I run either :
> exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'
> or
> exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'
> I get a single record returned in each case (which is what I would
> expect).
<snip
Your first SQL statement essentially comes down to this:
select * from table where column in ('183, 176')
In other words, return the columns with a value of '183, 176'. Since
there aren't any, you get no rows. What you are trying to achieve is
this - return columns with a value of '183' or '176':
select * from table where column in ('183', '176')
When you use a single value for the variable, you get a valid query,
and hence a single result:
select * from table where column in ('183')
You're getting results in EM because you've correctly typed in the
values manually.
See here for a fuller discussion:
http://www.algonet.se/~sommar/dynamic_sql.html#List
Simon
Wednesday, March 7, 2012
q; please check this trigger
Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,
Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>
|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
q; please check this trigger
Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the original
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if the
> compatibility level is equal to 70. The text, ntext, and image values in the
> inserted and deleted tables cannot be accessed. To retrieve the new value in
> either an INSERT or UPDATE trigger, join the inserted table with the original
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update of
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
> > Apparently I could not insert a text field to another table from INSERTED in
> > a trigger.
> > It seems the following is working, do you see any problem joining INSERTED
> > to the mySrcTable which is the table that has this trigger
> >
> > INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
> > SELECT i.myTrID, i.myFirstName, p.myBigText
> > FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> > WHERE (i.myTrType = 'In')
> >
> > Thanks,
> >
q; please check this trigger
Apparently I could not insert a text field to another table from INSERTED in
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the origina
l
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED
in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigT
ext)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if th
e
> compatibility level is equal to 70. The text, ntext, and image values in t
he
> inserted and deleted tables cannot be accessed. To retrieve the new value
in
> either an INSERT or UPDATE trigger, join the inserted table with the origi
nal
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update o
f
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
>
a trigger.
It seems the following is working, do you see any problem joining INSERTED
to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')
Thanks,Hi Jim
From Books Online "Create Trigger" topic
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
ntext, or image column references in the inserted and deleted tables if the
compatibility level is equal to 70. The text, ntext, and image values in the
inserted and deleted tables cannot be accessed. To retrieve the new value in
either an INSERT or UPDATE trigger, join the inserted table with the origina
l
update table. When the compatibility level is 65 or lower, null values are
returned for inserted or deleted text, ntext, or image columns that allow
null values; zero-length strings are returned if the columns are not
nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of
text, ntext, or image columns through the INSTEAD OF trigger on tables or
views.
If you use an instead of trigger your logic will have to be different as
mentioned in one of your earlier posts. If you are not going to hold more
than 8000 characters in this column change it to varchar.
John
"JIM.H." wrote:
> Apparently I could not insert a text field to another table from INSERTED
in
> a trigger.
> It seems the following is working, do you see any problem joining INSERTED
> to the mySrcTable which is the table that has this trigger
> INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigT
ext)
> SELECT i.myTrID, i.myFirstName, p.myBigText
> FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
> WHERE (i.myTrType = 'In')
> Thanks,
>|||Thanks John.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> From Books Online "Create Trigger" topic
> In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> ntext, or image column references in the inserted and deleted tables if th
e
> compatibility level is equal to 70. The text, ntext, and image values in t
he
> inserted and deleted tables cannot be accessed. To retrieve the new value
in
> either an INSERT or UPDATE trigger, join the inserted table with the origi
nal
> update table. When the compatibility level is 65 or lower, null values are
> returned for inserted or deleted text, ntext, or image columns that allow
> null values; zero-length strings are returned if the columns are not
> nullable.
> If the compatibility level is 80 or higher, SQL Server allows the update o
f
> text, ntext, or image columns through the INSTEAD OF trigger on tables or
> views.
> If you use an instead of trigger your logic will have to be different as
> mentioned in one of your earlier posts. If you are not going to hold more
> than 8000 characters in this column change it to varchar.
> John
> "JIM.H." wrote:
>
Saturday, February 25, 2012
q; Linked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server =
N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server =
> N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
> N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =
> N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>
|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
> N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>
>
|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server =
N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server =
> N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
> N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =
> N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>
|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
> N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>
>
|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
q; Linked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server => N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword => N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> >
> > Hi
> > I use the following SQL statements to create a link server.
> > RemoteServerName
> > is an SQL2000 and I am executing this in another machine which is SQL2005.
> >
> > Though the link server is created successfully, I am not able to se the
> > tables under it so could not query anything.
> >
> > --
> >
> > USE [master]
> > EXEC master.dbo.sp_addlinkedserver @.server => > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation compatible', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'data access', @.optvalue=N'true'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc',@.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc out', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'connect timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation name', @.optvalue=null
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'query timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'use remote collation', @.optvalue=N'true'
> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => > N'False',
> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
> >
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>> Jim
>> First of all , have you enabled remote connections on SQL Server 2005
>> server?
>> I did just testing on my machine and it works fine.
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
>> @.srvproduct=N'SQL Server'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> compatible', @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
>> access', @.optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc
>> out',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> name', @.optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use
>> remote
>> collation', @.optvalue=N'true'
>> GO
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
>> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
>> =>> N'blblbl'
>> GO
>>
>> --usage
>> select * from ServerName.dtabasename.dbo.tablename
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>> >
>> > Hi
>> > I use the following SQL statements to create a link server.
>> > RemoteServerName
>> > is an SQL2000 and I am executing this in another machine which is
>> > SQL2005.
>> >
>> > Though the link server is created successfully, I am not able to se the
>> > tables under it so could not query anything.
>> >
>> > --
>> >
>> > USE [master]
>> > EXEC master.dbo.sp_addlinkedserver @.server =>> > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation compatible', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'data access', @.optvalue=N'true'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc',@.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc out', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'connect timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation name', @.optvalue=null
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'query timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'use remote collation', @.optvalue=N'true'
>> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =>> > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =>> > N'False',
>> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>> >
>>
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server => N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword => N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> >
> > Hi
> > I use the following SQL statements to create a link server.
> > RemoteServerName
> > is an SQL2000 and I am executing this in another machine which is SQL2005.
> >
> > Though the link server is created successfully, I am not able to se the
> > tables under it so could not query anything.
> >
> > --
> >
> > USE [master]
> > EXEC master.dbo.sp_addlinkedserver @.server => > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation compatible', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'data access', @.optvalue=N'true'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc',@.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc out', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'connect timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation name', @.optvalue=null
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'query timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'use remote collation', @.optvalue=N'true'
> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => > N'False',
> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
> >
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>> Jim
>> First of all , have you enabled remote connections on SQL Server 2005
>> server?
>> I did just testing on my machine and it works fine.
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
>> @.srvproduct=N'SQL Server'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> compatible', @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
>> access', @.optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc
>> out',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> name', @.optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use
>> remote
>> collation', @.optvalue=N'true'
>> GO
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
>> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
>> =>> N'blblbl'
>> GO
>>
>> --usage
>> select * from ServerName.dtabasename.dbo.tablename
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>> >
>> > Hi
>> > I use the following SQL statements to create a link server.
>> > RemoteServerName
>> > is an SQL2000 and I am executing this in another machine which is
>> > SQL2005.
>> >
>> > Though the link server is created successfully, I am not able to se the
>> > tables under it so could not query anything.
>> >
>> > --
>> >
>> > USE [master]
>> > EXEC master.dbo.sp_addlinkedserver @.server =>> > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation compatible', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'data access', @.optvalue=N'true'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc',@.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc out', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'connect timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation name', @.optvalue=null
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'query timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'use remote collation', @.optvalue=N'true'
>> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =>> > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =>> > N'False',
>> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>> >
>>
Subscribe to:
Posts (Atom)