How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks
|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to exectue sql Function in asp.net

I want to exectue sql user define function in my asp.net application
I have one user define function in sql server 2000 that give good logic for my database access now i want to execute that function in asp.net

check this article

How to enter manually a "uniqueIdentifier" type value

I created a DB and a table in Visual Studio .NET 2003. For one of the column
I choose the data type uniqueidentifier.
How can I add manually a unique ID value in the table value editor?
OK, figured it out: select newID() as the default value.
"Frank" <someone@.nospam.com> wrote in message
news:c5f6j4$dpe$1@.news.mch.sbs.de...
> I created a DB and a table in Visual Studio .NET 2003. For one of the
column
> I choose the data type uniqueidentifier.
> How can I add manually a unique ID value in the table value editor?
>

How to Encrypt or Hide parameters in URL

Please help, i have been struggling for a solution could'nt able find any info. on the net.

I am currently using SSRS 2000 updated with SP2.

Is it possible to hide or encrypt the parameters in URL.

Please help, Thank you very much for the information.

are you using any front end application to pass the parameters?

|||

Yes, i am using a front end to pass the parameters via URL

http://servername/reportserver?/ttsReports/StatusReport&eStatus=Open&UserName=myname

But want to hide the parameters information completely from the URL, using SQL2K RS

PLease Please Help, if any one has the info. That will be a gret great help for me.

Thank you all.

How to enable remote connections by code?

How to enable remote connections by code without "SQL Server Surface Area Configuration" ,implement by .net framework?

Thats pretty easy using SMO:

instance.ServerProtocols[protocol].IsEnabled = true;

Whereas protocol is the protocol you want to enable (like "tcp" or "np") ans instance is the server instance you want to configure. I wrote an application, the RemoteconnectionsEnabler which does all this using the SMO library.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to enable range Date in Calender?

Hi,

I am using Asp.net Calender in my application and i have two date (i.e StartDate and EndDate). I need to enable dates between these two date in calender.

So the user can choose date with in this set of date.

Can any one give me idea to solve this problem

Thanks,

Hi,

Do you want to pass these dates to a report?

If so, you need to create 2 parameters and pass the start and end date to the report.
If not, you should probably need to post your question in a different forum.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

how to enable auditing in SQL (or how to maintain session state variables)

My application is developed in VB.NET and uses a single/same connection
string for all users (all
installations), which uses SQL Server 2000 as the data store.
But I need to keep track of who inserted a record and who modified a record
(along with the dates and times).
Using triggers, I can easily setup the dates when a record was added and
last modified.
But how can i determine what users is connected to the application and is
making the changes.
As this information is stored in the application.
Is there a way that i can inform SQL what user is connecting to the server
(database), or set this information in SQL Server such that this information
is stored for each session. I.e. is there some technology similar to session
states in websites in Sql server. where i can store session variables for
each session on the website, thus enabling me to store session variables in
sql server for each session (connection).
any help on this matter would be great.LeAnne wrote:
> My application is developed in VB.NET and uses a single/same connection
> string for all users (all
> installations), which uses SQL Server 2000 as the data store.
> But I need to keep track of who inserted a record and who modified a recor
d
> (along with the dates and times).
> Using triggers, I can easily setup the dates when a record was added and
> last modified.
> But how can i determine what users is connected to the application and is
> making the changes.
> As this information is stored in the application.
> Is there a way that i can inform SQL what user is connecting to the server
> (database), or set this information in SQL Server such that this informati
on
> is stored for each session. I.e. is there some technology similar to sessi
on
> states in websites in Sql server. where i can store session variables for
> each session on the website, thus enabling me to store session variables i
n
> sql server for each session (connection).
> any help on this matter would be great.
Just pass in the user name or ID as a parameter to the stored procedure
that performs the insert or update. In a two-tier application procs
should certainly be used for all data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
There are SYSTEM_USER,HOST_NAME() ,USER_NAME ...system functions
If I remember well you can specify a workstationname within a connection
string to identify an user.
Anne" <Anne@.bogusemail.com> wrote in message
news:eBbc$FpRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> My application is developed in VB.NET and uses a single/same connection
> string for all users (all
> installations), which uses SQL Server 2000 as the data store.
> But I need to keep track of who inserted a record and who modified a
> record
> (along with the dates and times).
> Using triggers, I can easily setup the dates when a record was added and
> last modified.
> But how can i determine what users is connected to the application and is
> making the changes.
> As this information is stored in the application.
> Is there a way that i can inform SQL what user is connecting to the server
> (database), or set this information in SQL Server such that this
> information
> is stored for each session. I.e. is there some technology similar to
> session
> states in websites in Sql server. where i can store session variables for
> each session on the website, thus enabling me to store session variables
> in
> sql server for each session (connection).
> any help on this matter would be great.
>
>

how to empty a stored procedure in ms sql server management studio express

hi everyone,

I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id′s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?

Thanks in advance, best regards

bg

hi bg,

stored procedures can only be executed and not "filled" like "tables"... so they can not be emptied (is that english? )

so what are you trying to do? if you want to empty a specific table, you can open SQL Server Management Studio Express, select the table, access the "Open table" feature, select all rows and delete them (if not referenced in foreign key constraint) or do the same in a query window executing

TRUNCATE TABLE schema_name.table_name

or

DELETE FROM schema_name.table_name WHERE filter_criterion..

regards

sql

How to embed Activex contol in SSRS

How to embed Activex contol in SSRS. I have created one custom library in VB.NET and tried to reference and previewed (Report -> Report Properties-> Reference), but I got following errors. Same library is working fine for other .NET applications. Is there any limitations to use .dll in SSRS, please let me know
1) "Error while loading code module:"
2) "Error in class instance declaration"
Any chance??

How to email one set of data from app database.

Hi,
My vb.net app will use msde for database needs. The app is a tool in which the app user (companyA) will do some planning for their clients. So basically, the database will have information about all the clients (Sam, Julia, Peter, Nancy etc) of our cust
omer (companyA). Now suppose there is some error happening in the application for user Nancy, and we want the companyA to send us only Nancy's data so that we can duplicate the error and debug it. How do we do that...
In previous versions of our app where we used the flat files for data storage, we will just tell the companyA to email us Nancy.ourFile so that we can have a look at the data which is causing the problem. How to extract only Nancy's data from the whole d
atabase...
Thanks
dev
No replies anyone... Andrea/Aaron where are you all...
Thanks
dev
"dev_kh" wrote:

> Hi,
> My vb.net app will use msde for database needs. The app is a tool in which the app user (companyA) will do some planning for their clients. So basically, the database will have information about all the clients (Sam, Julia, Peter, Nancy etc) of our cu
stomer (companyA). Now suppose there is some error happening in the application for user Nancy, and we want the companyA to send us only Nancy's data so that we can duplicate the error and debug it. How do we do that...
> In previous versions of our app where we used the flat files for data storage, we will just tell the companyA to email us Nancy.ourFile so that we can have a look at the data which is causing the problem. How to extract only Nancy's data from the whole
database...
> Thanks
> dev
sql

How to edit a asp.net2.0/C# project in VS2005, Plz HELP

Hi,

How can i run or edit my ASP.NET 2.0/C# project in VS2005. I have source code files (including database) but it doesn't have .sln file. Please guide me how can i edit or run this in VS2005

Hi,

if you are missing the sln file, you should create a new web project and import the existing files to your solution.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the help.

which files are necessary, while sending ASP.NET 2.0/C# project (for further development) to the developer, like .sln files, database.

please write the name of other files. and how to open that project in VS2005.

Thanks

vkkv

|||Every file except *user files as they store user specific information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to duplicate a database?

Hi:
I have a database for my asp.net application and now I want to create
another database with everything same, including the data, except the
database name(this database will be used by another asp.net application).
How can I do this programmingly? I am using C#.
Thank you very much!
Quentin H.You can restore from a recent backup and specify the new name. Also, you can
detach, copy the mdf file, and then re-attach under a new name. How to do
this from C# ? You could use data management objects (DMO), but I would
suggest just writing a stored procedure and calling the SP from C#.
"Quentin Huo" <q.huo@.manyworlds.com> wrote in message
news:%23QcXgaiZFHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have a database for my asp.net application and now I want to create
> another database with everything same, including the data, except the
> database name(this database will be used by another asp.net application).
> How can I do this programmingly? I am using C#.
> Thank you very much!
> Quentin H.
>|||Thank you for your quick reply!
Also, I want to ask how I can do if I want to duplicate the database, but
without any data. Do I need to use DMO? I tried this before by DTS, but
everytime when I did this, some definition of fields were changed. For
example, there is a field named "adddate" which default value is
"getDate()". However, after I duplicate it from DTS, the default value
(getdate()) was lost. Maybe I lost something when I did DTS?
Thanks
Q.
"JT" <someone@.microsoft.com> wrote in message
news:%23fQMymiZFHA.3364@.TK2MSFTNGP12.phx.gbl...
> You can restore from a recent backup and specify the new name. Also, you
> can
> detach, copy the mdf file, and then re-attach under a new name. How to do
> this from C# ? You could use data management objects (DMO), but I would
> suggest just writing a stored procedure and calling the SP from C#.
> "Quentin Huo" <q.huo@.manyworlds.com> wrote in message
> news:%23QcXgaiZFHA.3328@.TK2MSFTNGP09.phx.gbl...
>|||The easiest way of doing this is the restore from a recent backup ! You
can restore from a backup using DMO or TSQL with ADO !
Your best bet if you use DTS is to use copy object job. Try the copy
database wizard and reuse the DTS made by it !
Pollus Brodeur

how to dump database to MSDE

Hi

In my app. i'm using sql server 2000 now I want to move to MSDE2000. I have two Queres. my application environment is Vb.net

1.how to dump my sqlserver database which i was already using to theMSDE and

2.can i connect this MSDE to MSSqlServer2000

hi,

GRK wrote:

Hi

In my app. i'm using sql server 2000 now I want to move to MSDE2000. I have two Queres. my application environment is Vb.net

1.how to dump my sqlserver database which i was already using to theMSDE and

you can "move" the database from the SQL Server 2000 to the MSDE instance in 2 easy ways..

1. you can backup (full backup) the database on the original machine, move the backup to the MSDE machine, restore the database onto the MSDE instance..

2. detach the database from the SQL Server 2000 instance, copy all database files to the MSDE machine, attach the database to the MSDE instance (and obviously reattach the original database to the original SQL Server 2000 instance)..

you obtain the very same result, and you have to deal with the very same issues.. eventual "Orphaned Users"..

2.can i connect this MSDE to MSSqlServer2000

the question is not clear to me.. if you mean to "access MSDE from the SQL Server 2000" instance as a linked server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp as well), then yes, this is possible... a "commercial" issue is present as this connection has to be properly licensed...

regards

|||

Hi Andrea ,

Thank you verymuch.

-GRK

How to drop database table if it exits in sql mobile version?

Hi,

I am using VB.net and Visual Studio 2005 to compile a program for a Pocket PC with Microsoft? Windows Mobile? 2003 Second Edition. I am using sql mobile also.

What I want to complete is like one of the following two queries. But when I tried them in the sql mobile, errors happened. So how can I complete the same function in sql Mobile? Thanks.

USE DBa

if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[Table_a]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop Table Table_a

or

USE DBa

IF OBJECT_ID('dbo.Table_a) IS NOT NULL

DROP TABLE dbo.Table_a

You should issue a query to check if table exists and if it does issue another query to delete it.

Please see SQL CE/Mobile books online for queries (hint: look for Information_Schema.Tables and Drop Table).

|||Thanks a lot, and your suggestion is great

How to do with xml file -> schema file (funcion in . NET)

I use a BulkLoad to import file.xml to my base MS Server 2000.
To import this xml file I need schema file. Mayby you know how to do this
file mechanicy f.g. mayby somebody have some script in .net who generate
this schema.
for any help Thanks
======== My xml file =================================
<ROOT>
<Customers>
<CustomerId>5555</CustomerId>
<CompanyName>Jagoda1</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>0200</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>199</CustomerId>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>
=================================================
My schema . How to do this schema mechanicy? Becouse in this time I must do
this schema ofcourse writing.
============================== MY Schema file ===============
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="CustomerId" dt:type="int" />
<ElementType name="CompanyName" dt:type="string" />
<ElementType name="City" dt:type="string" />
<ElementType name="ROOT" sql:is-constant="1">
<element type="Customers" />
</ElementType>
<ElementType name="Customers" sql:relation="Customer">
<element type="CustomerId" sql:field="CustomerId" />
<element type="CompanyName" sql:field="CompanyName" />
<element type="City" sql:field="City" />
</ElementType>
</Schema>
================================================== ======
Do you have any idea ?
Sorry for my english
Luk
ljag@.wp.pl
Hi
couple of options:
1. http://apps.gotdotnet.com/xmltools/xsdinference/
2. look at the (Select) 'FOR XML' options with SQL Srvr (some options can
gen schema)
3. if you don't have VS.NET, probably your best (freebie) is XML Fox
(XML/XSD Editor). (Only 1 I could find that would actually load in the XML
files I work with most of the time. (30+ MB) This includes DreamWeaver,
VS.NET, Eclipse, JBuilder, etc.)
4. if you have VS.NET, open your XML file and there's a 'create Schema'
option, under the XML Menu option
Rob
"Szaki" wrote:

> I use a BulkLoad to import file.xml to my base MS Server 2000.
> To import this xml file I need schema file. Mayby you know how to do this
> file mechanicy f.g. mayby somebody have some script in .net who generate
> this schema.
> for any help Thanks
> ======== My xml file =================================
> <ROOT>
> <Customers>
> <CustomerId>5555</CustomerId>
> <CompanyName>Jagoda1</CompanyName>
> <City>NY</City>
> </Customers>
> <Customers>
> <CustomerId>0200</CustomerId>
> <CompanyName>Tom Johnston</CompanyName>
> <City>LA</City>
> </Customers>
> <Customers>
> <CustomerId>199</CustomerId>
> <CompanyName>Institute of Art</CompanyName>
> </Customers>
> </ROOT>
> =================================================
> My schema . How to do this schema mechanicy? Becouse in this time I must do
> this schema ofcourse writing.
> ============================== MY Schema file ===============
> <?xml version="1.0" ?>
> <Schema xmlns="urn:schemas-microsoft-com:xml-data"
> xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
> xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
> <ElementType name="CustomerId" dt:type="int" />
> <ElementType name="CompanyName" dt:type="string" />
> <ElementType name="City" dt:type="string" />
> <ElementType name="ROOT" sql:is-constant="1">
> <element type="Customers" />
> </ElementType>
> <ElementType name="Customers" sql:relation="Customer">
> <element type="CustomerId" sql:field="CustomerId" />
> <element type="CompanyName" sql:field="CompanyName" />
> <element type="City" sql:field="City" />
> </ElementType>
> </Schema>
> ================================================== ======
>
> Do you have any idea ?
> Sorry for my english
> --
> Luk
> ljag@.wp.pl
>
>
|||Thanks
Uytkownik "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> napisa w
wiadomoci news:A489D834-8451-4E04-B489-FEFACB3E6CEB@.microsoft.com...[vbcol=seagreen]
> Hi
> couple of options:
> 1. http://apps.gotdotnet.com/xmltools/xsdinference/
> 2. look at the (Select) 'FOR XML' options with SQL Srvr (some options can
> gen schema)
> 3. if you don't have VS.NET, probably your best (freebie) is XML Fox
> (XML/XSD Editor). (Only 1 I could find that would actually load in the XML
> files I work with most of the time. (30+ MB) This includes DreamWeaver,
> VS.NET, Eclipse, JBuilder, etc.)
> 4. if you have VS.NET, open your XML file and there's a 'create Schema'
> option, under the XML Menu option
> Rob
> "Szaki" wrote:
this[vbcol=seagreen]
do[vbcol=seagreen]

How to do search on two or three data tables at once!

Hi
Looking at the vb.net code below how can I modify the code so that when using SQL Servers
Full Text Search it will search two or three data tables at once Jobs Jobs2 and jobs3 all
tables are in the same database aspnetjobs and share the same job_id column. I can perform a search on one table but not two. Please look
at the vb.net code below and help if you can.

thanks!

sub button_click(s as object, e as eventargs)

Dim conaspnetjobs as sqlconnection
Dim strsearch as string
Dim cmdsearch as sqlcommand
Dim dtrsearch as sqldatareader

conaspnetjobs = new sqlconnection ( "server=mark\mark;uid=sa;pwd=clr_fcl;database=aspnetjobs" )

strsearch ="select job_fulldesc from Jobs, jobs2 where freetext( job_fulldesc, @.searchphrase )"
cmdsearch = new sqlcommand( strsearch, conaspnetjobs )
cmdsearch.parameters.add( "@.searchphrase", txtsearchphrase.text )

conaspnetjobs.open()
dtrsearch = cmdsearch.executereader()

while dtrsearch.read
lblresults.text &= "<li><br>" & dtrsearch( "job_fulldesc" )
end while

conaspnetjobs.close
end subHi,

Can't you simply join the other job tables in you query?

select job_fulldesc from Jobs a join jobs2 b on a.job_id = b.job_id where freetext( job_fulldesc, @.searchphrase )"

:-D

JB|||I think you need a UNION ALL. This will search each of the tables and combine the results into one resultset. I added a "source" column so you'd know which table the match come from; you may not need this.

"select job_fulldesc from Jobs, source="Jobs" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs2, source="Jobs2" where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc from Jobs3, source="Jobs3" where freetext( job_fulldesc, @.searchphrase )"

Terri|||Hi
I tryed the UNION ALL as above and recieved error message "UNION" is not declared!

please help!|||Something ran amok with the code I suggested. No wonder it didn't work. This is what I meant:

"select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase )
UNION ALL
select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

I tried this out by creating a duplicate of Products called Products2 on Northwind, and then creating a full text index on the ProductName field. Then I ran this successfully:

DECLARE @.searchphrase varchar(20)
SET @.searchphrase = 'Ale'
SELECT productname, source='Jobs' FROM products WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs2' FROM products2 WHERE FREETEXT(productname, @.searchphrase )
UNION ALL
SELECT productname, source='Jobs3' FROM products WHERE FREETEXT(productname, @.searchphrase )

|||I get this error message "statement is not valid inside a method"

Declare @.sreachphrase varchar(20 )

Please Help!|||That bottom block of code I posted was strictly for Query Analyzer use as proof of concept, not to copy and paste into your ASP.NET page.

This block of code is what should work for you:

strsearch ="select job_fulldesc, source='Jobs' from Jobs where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs2' from Jobs2 where freetext( job_fulldesc, @.searchphrase ) UNION ALL select job_fulldesc, source='Jobs3' from Jobs3 where freetext( job_fulldesc, @.searchphrase )"

Terri

How to do certain task in SQL?

Hi Everyone,
I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and
I'm new to SQL. I've been experimenting with VB.Net and MSDE for a few
months now and I'm very impressed. I got the go ahead to convert a
major xBase application to SQL/VB.Net.
I am not sure how to do certain tasks in SQL. I need to know how to do
the following...
Move to the last record in a table (in xBase, it is "go bottom")
Move to the first record in a table (in xBase, it is "go top")
Append a new blank record to a table (in xBase, it is "append blank")
Move to the next record in a table
In the DBF world, some tasks require filtering a file (say all records
belonging to invoice <n>) and then processing each record in a loop.
This involved moving a record pointer with either a "goto" or "skip"
command. I suspect the SQL equivalent is to use the "SELECT" statement
to filter the records and then to use the "UPDATE" or "DELETE" commands
to edit the table. Do I have the right idea?
Thanks.
Richard
Hi Richard,
Don't take this personally, but from the questions you are asking, you have
a bit of a learning curve ahead of you. First you need to stop looking at
SQL data as a list of sequential records. SQL data is retrieved and
manipulated as sets of data. There really is no equivalent to "move to last
record" or first record. If you need to update or select a particular record
then you need to define your records with either a primary key or some other
unique contraint on the data and provide the SQL statement that will extract
a distinct record. I suggest that you find a good general book on SQL and
study up on relational database theory. As a recommendation "Data &
Databases: Concepts In Practice" by Joe Celko is a very good book that lays
down the basics of relational database design.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:u%23WzFEO$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and I'm
> new to SQL. I've been experimenting with VB.Net and MSDE for a few months
> now and I'm very impressed. I got the go ahead to convert a major xBase
> application to SQL/VB.Net.
> I am not sure how to do certain tasks in SQL. I need to know how to do
> the following...
> Move to the last record in a table (in xBase, it is "go bottom")
> Move to the first record in a table (in xBase, it is "go top")
> Append a new blank record to a table (in xBase, it is "append blank")
> Move to the next record in a table
> In the DBF world, some tasks require filtering a file (say all records
> belonging to invoice <n>) and then processing each record in a loop. This
> involved moving a record pointer with either a "goto" or "skip" command.
> I suspect the SQL equivalent is to use the "SELECT" statement to filter
> the records and then to use the "UPDATE" or "DELETE" commands to edit the
> table. Do I have the right idea?
> Thanks.
> Richard
|||Hi Jim,
I'm not taking it personally, I appreciate you comments
I had a feeling that I'd have to use the "select" command to filter the
record(s) that I want to work with and then to think in terms of 'a set
of records'.
I have already defined primary keys for my imported DBF files. I know
how to 'update' information in existing records, but I was browsing the
'SQL Book Online' and couldn't find any references on how to combine
records from multiple tables. I know about the 'join' command, but my
request is a bit different. Say I had a existing table with 1000
records and I had the user input information into a similar table with
15 new records (exact same layout) and I wanted to merge the two tables
into one table with 1015 records, how would I do this?
Thanks for the recommendation, I'll check it out. Is it a general book
or one specific to MS SQL?
Richard
Jim Young wrote:
> Hi Richard,
> Don't take this personally, but from the questions you are asking, you have
> a bit of a learning curve ahead of you. First you need to stop looking at
> SQL data as a list of sequential records. SQL data is retrieved and
> manipulated as sets of data. There really is no equivalent to "move to last
> record" or first record. If you need to update or select a particular record
> then you need to define your records with either a primary key or some other
> unique contraint on the data and provide the SQL statement that will extract
> a distinct record. I suggest that you find a good general book on SQL and
> study up on relational database theory. As a recommendation "Data &
> Databases: Concepts In Practice" by Joe Celko is a very good book that lays
> down the basics of relational database design.
> Jim
|||I do not question why you have the extra table with exactly the same layout
(columns, I guess). It is very simple the do what you want. Assume, tableA
contains the 15 (or any number of) rows of user inputs and you want to add
all of them of some of them into tableB. You could use this SQL statement:
INERT INTO tableB (Col1,Col2,Col3...)
SELECT Filed1,Field2,Field3...
FROM tableA
WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
being transfered to tableB.
You also can see from above SQL statement, tableA does not have to be the
same structure as tableB. You only need to make sure the fields selected in
SELECT...clause match the fields (field count and data type) those in INSERT
INTO clause.
Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
to stduy/investigete T-SQL, rather than browse SQL Book on-line for
particular processing.
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad#LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set
> of records'.
> I have already defined primary keys for my imported DBF files. I know
> how to 'update' information in existing records, but I was browsing the
> 'SQL Book Online' and couldn't find any references on how to combine
> records from multiple tables. I know about the 'join' command, but my
> request is a bit different. Say I had a existing table with 1000
> records and I had the user input information into a similar table with
> 15 new records (exact same layout) and I wanted to merge the two tables
> into one table with 1015 records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book
> or one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
have[vbcol=seagreen]
at[vbcol=seagreen]
last[vbcol=seagreen]
record[vbcol=seagreen]
other[vbcol=seagreen]
extract[vbcol=seagreen]
and[vbcol=seagreen]
lays[vbcol=seagreen]
|||The Celko book is about general relational database theory (very little
SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
"Inside SQL Server 2000" is an essential book for anyone that is in the
business of working with SQL Server.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad%23LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set of
> records'.
> I have already defined primary keys for my imported DBF files. I know how
> to 'update' information in existing records, but I was browsing the 'SQL
> Book Online' and couldn't find any references on how to combine records
> from multiple tables. I know about the 'join' command, but my request is
> a bit different. Say I had a existing table with 1000 records and I had
> the user input information into a similar table with 15 new records (exact
> same layout) and I wanted to merge the two tables into one table with 1015
> records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book or
> one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
|||Hi Norman,
That's exact what I was looking for, thanks!
I moved over all the DBF files into SQL. That extra table was the
template user entered data into before 'posting' the transaction.
I think I'll take yours (any others) advice and read a T-SQL specific
book. The book on-line I see are a great reference AFTER one becomes
more familiar with T-SQL.
I was intending to spend at least a week or two experimenting with
various database operations using small scale example, but I think I'll
order the book first
Thanks again.
Richard
Norman Yuan wrote:
> I do not question why you have the extra table with exactly the same layout
> (columns, I guess). It is very simple the do what you want. Assume, tableA
> contains the 15 (or any number of) rows of user inputs and you want to add
> all of them of some of them into tableB. You could use this SQL statement:
> INERT INTO tableB (Col1,Col2,Col3...)
> SELECT Filed1,Field2,Field3...
> FROM tableA
> WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
> being transfered to tableB.
> You also can see from above SQL statement, tableA does not have to be the
> same structure as tableB. You only need to make sure the fields selected in
> SELECT...clause match the fields (field count and data type) those in INSERT
> INTO clause.
> Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
> to stduy/investigete T-SQL, rather than browse SQL Book on-line for
> particular processing.
>
|||Hi Jim,
Thanks for the recommendations. Those two sound more like what I am
looking for. I've been using relational databases (dBase, Clipper,
Paradox, FoxPro, etc) for years, but I agree that I should get one of
the T-SQL books.
My clients are all small businesses and use SBS 2000. In otherwords, I
don't want to spend time learning about larger enterprise systems, just
small, single server systems with 5-15 users.
I went to some seminars and they gave out SQL books but they were for
large organizations (multiple servers, load balancing, forest, trees,
advance security. etc).
Are either of these books geared for the small business? Is there one
you'd think suites my needs better?
Richard
p.s. Is the MS Press author is "Delaney"? I'd like to double check.
Jim Young wrote:

> The Celko book is about general relational database theory (very little
> SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
> Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
> "Inside SQL Server 2000" is an essential book for anyone that is in the
> business of working with SQL Server.
> Jim
>
|||None of these books are written to address a specific implementation. They
will serve you well, no matter what your deployment size is. There have been
a couple of books written to address MSDE specifically. One that I have is
"MSDE Bible" by IDG Books. But, MSDE is so much like SQL Server that any
book that is useful for SQL Server will do for MSDE also. Most, if not all,
of the MSDE specific information can be found in the Books Online.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:%23VBz2KZ$EHA.1260@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> Thanks for the recommendations. Those two sound more like what I am
> looking for. I've been using relational databases (dBase, Clipper,
> Paradox, FoxPro, etc) for years, but I agree that I should get one of the
> T-SQL books.
> My clients are all small businesses and use SBS 2000. In otherwords, I
> don't want to spend time learning about larger enterprise systems, just
> small, single server systems with 5-15 users.
> I went to some seminars and they gave out SQL books but they were for
> large organizations (multiple servers, load balancing, forest, trees,
> advance security. etc).
> Are either of these books geared for the small business? Is there one
> you'd think suites my needs better?
> Richard
> p.s. Is the MS Press author is "Delaney"? I'd like to double check.
> Jim Young wrote:

how to do Alternative color row?

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..
Simon
Zean,
See:
http://www.sqlservercentral.com/colu...formatting.asp
HTH
Jerry
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:7p6dnQpaQ9zGz8TeRVn-hQ@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
> I can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
> Simon
>

how to do Alternative color row?

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..
SimonZean,
See:
http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp
HTH
Jerry
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:7p6dnQpaQ9zGz8TeRVn-hQ@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
> I can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
> Simon
>