please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

please help

Author

Topic

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-01 : 18:31:11

Please don't make fun of , if it is very easy thing I am asking..

Exec master.dbo.some_xp @FolderPath='Contacts',
@matchFileAs='jeff smith',

@Phone='454-555-5555,
@Street='this and that',
@City='some city', @State='some state', @Zip='55555'

this inside of between ' ' , can I put result from my query.
query is like:
select contactname, phone, address, city, state,zip
from mytable

I want contactname's value end up front of the matchfileas ...

how can I do that?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-01 : 18:56:35

I hope You don't feel we make fun of You !?

No You can't.

This is the way to do it:
(Note , the _ syntax I used for naming the variables is only for clarity, You can call them anything You want)

-- declare your variables
DECLARE @_matchFileAs VARCHAR(50)
	,@_Phone VARCHAR(50)


-- assign your variables from the values in the table
SELECT 	@_matchFileAs = contactname
	,@_Phone = phone
FROM	mytable
WHERE	<make sure only one record is selected>


-- execute the sp/xp
EXEC master.dbo.some_xp
	@FolderPath = 'Contacts'
	,@matchFileAs = @_matchFileAs
	,@Phone = @_Phone

Edit: typo.. again!

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-01 : 19:03:13

I didn't feel bad! But why can I choose more than one record? My query is really big, I even have subselect in it.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-01 : 19:20:51

Because a Variable (eg. @_Phone ) can only hold one value.
Not a whole set of values from a table.

You have to execute the sp/xp for every row of the query.

-----------------------------------------
You can theoretically do; (bad practice)
DECLARE @i INT
SELECT @i = id FROM sysobjects
-- but the variable @i will still hold only one value
SELECT @i AS [@i's value]
----------------------------------------

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-01 : 19:54:03

You are very right, I didn't know this is only giving me one value, but I need all the values , is it doable?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-01 : 19:57:44

Not doable.

What are You trying to do with that sp/xp and the table with the contactinfo ?

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-01 : 20:17:08

I need to place my contacts from sql server to outlook public folder which is in exchange server, I have been searching ways to do that week and half now, this sp/xp is good, but gives me limited parameters and one record, I have many records.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-02 : 01:12:43

You will have to loop through the records and process them 1 by one with Your sp.

rockmoose

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-02 : 11:10:57

rockmoose can you help some more about that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 12:13:18

notsosuper, check out WHILE in SQL Server Books Online. That'll allow you to loop. Try it out first, then post what you have and let us know what the problem is.

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-02 : 13:07:40

can this be done by dts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 13:13:59

Yes almost everything can be done by DTS but that would seriously overcomplicate things.

Why are you reluctant to write a WHILE loop?

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-02 : 13:24:04

i have never done in sp, I think it involves cursors, or fetch..?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 13:27:43

No, it involves WHILE. Open up SQL Server Books Online and read up on it instead of having us write it for you. We need to see that you've at least tried to work on the solution rather than us doing it.

Here's an example one that I wrote:
http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspx

Are you familiar with loops in VB or some other programming language? If so, it works just like those.

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-02 : 18:43:34

finally I have managed to create everything, but when I come to select statement, it doesn't like my alias even though i have declare them also.

like
declare c.clientname vchar(50)

...

select c.clientname .....

it doesn't like c there.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 18:44:50

declare is used for variables. And for variables you must use @.

DECLARE @i int
SET @i = 1
PRINT @i

If it doesn't like your alias, then you aren't using the alias in the FROM clause, in your JOINs, or derived tables.

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-03 : 17:16:27

here what I have
DECLARE @Contactname varchar(50),
@contmethodvalue varchar(100),
@contmethodtype VarChar(15),
@Phone VarChar(50),
@Email VarChar(50),
@billingaddress1 VarChar(50),
@billingCity VarChar(50),
@billingState VarChar(50),
@billingZip VarChar(50),
@groupid varchar(10),
@clientname varchar(50),
@status varchar(10)
--this is after
DECLARE Inserted_Cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select contactname,(select top 1 contmethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and contmethodtype = 'Phone' ORDER BY contmethodvalue) AS Phone,
(select top 1 contmethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and contmethodtype = 'Email' ORDER BY contmethodvalue) AS Email, c.billingaddress1, c.billingcity, c.billingstate, c.billingzip
From localtest.dbo.contacts as a , localtest.dbo.clients as c
where a.clientid = c.clientid and c.groupid = 'RERE'
AND a.status IS NULL OR a.status <> 'inactive'
order by a.contactname

open....

for some reason my select statement breaking connnection, I know that because if I use simple sql statement it goes through fine, I wonder this is because this statement returns 700 rows but the other simple one was only 245 rows? or because I use aliases and more than one table? THis is the error message SQL Server is terminating process 53.
Connection Broken

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-03 : 17:52:52

In order for us to help you troubleshoot this, we need DDL for all tables involved, sample data for each of the tables that represents the problem that you are having, and the expected result set. See this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Make sure to check out the link in the first post.

BTW, you should be using a WHILE loop instead of a cursor.

Tara

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-03 : 18:22:46

I have read this but still didn't understand about dll thing. I know cursors are very slow but I don't get while loops

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-03 : 19:05:18

here my code and not giving me error anymore, but it removes all the duplicate contactname and some of the clientnames, I should have 700 records I have 199 shows. I actually would like to get rid of cursors, they are very slow.

DECLARE @TestFolderPath VarChar(255)

SET @TestFolderPath = 'SQL1_Contacts'

DECLARE @Contactname varchar(50),
@contmethodvalue varchar(100),
@contmethodtype VarChar(15),
@Phone VarChar(50),
@Email VarChar(50),
@billingaddress1 VarChar(50),
@billingCity VarChar(50),
@billingState VarChar(50),
@billingZip VarChar(50),
@groupid varchar(10),
@clientname varchar(50),
@status varchar(10)

DECLARE Inserted_Cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select c.clientname,a.contactname,(select top 1 comethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and comethodtype = 'Phone' ORDER BY comethodvalue) AS Phone,(select top 1 comethodvalue from localtest.dbo.contactmethods where contactid = [a].[contactid] and comethodtype = 'Email' ORDER BY comethodvalue) AS Email, c.billingaddress1, c.billingcity, c.billingstate, c.billingzip

From localtest.dbo.contacts as a , localtest.dbo.clients as c
where a.clientid = c.clientid and c.groupid = 'RERE'
AND a.stat IS NULL OR a.stat <> 'inactive'
and a.contactname <> ''

OPEN Inserted_Cursor

FETCH NEXT FROM Inserted_Cursor INTO @clientname,
@contactname,
@Phone,
@Email,
@billingaddress1,
@billingCity,
@billingState,
@billingZip

WHILE @@FETCH_STATUS = 0 -- see if there are any more rows--
BEGIN

EXEC master.dbo.xp_mycontacts @FolderPath = @TestFolderPath,
@matchFileAs = @contactname,
@firstname = @clientname,
@Phone = @Phone,
@Email = @Email,
@Street = @billingaddress1,
@City = @billingCity,
@State = @billingState,
@Zip = @billingZip

FETCH NEXT FROM Inserted_Cursor INTO @contactname,
@clientname,
@Phone,
@Email,
@billingaddress1,
@billingCity,
@billingstate,
@billingZip
END

CLOSE Inserted_Cursor; DEALLOCATE Inserted_Cursor

notsosuper
Posting Yak Master

190 Posts

Posted - 2005-08-04 : 12:13:10

anybody can help how can I replace this with while loop?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources