one more time

i′m sorry my bad english

i have a dts in sql server 2005

for example in sql server 2000 i can do that :

over DTS right-clik choose option "Scheduled pachage" and this create a scheduled what i mean is that this produces a "JOB"

what i mean is that i need to know how can to create a scheduled to DTS′s in sql 2005

Please anyone of you provide some solution for this.

Okay, I completely misunderstood what you wanted before. But, I think I can help you now.

Once you have the SSIS package (DTS packages mean something else - like packages from SQL SERVER 2000 DTS) you can use SQL Server Agent to shedule the package to be run at a certain time.

Use the SQL Server Agent to schedule your package.

|||

sorry man:

what i do in sql server 2005 is:

right-click over SQL Server Agent , choose the option "New" and choose option "Schedule" but isn't what i want, may be i am not specfic

when i do right-click over dts in sql server 2000 and choose the option schedule package this produces an scheduled and i can see it in the part of the jobs

Now how i can do that in sql server 2005? i need to see of my DTSs the jobs that i created and you said me that i can do by SQL Server Agent in SQL server 2005 and this isn′t do what i want.

Please anyone of you provide some solution for this.
Thanks :)

|||

limon26,

I merged this post into your earlier thread. If you want to reply to another reply you should use the reply button and not the new thread button. This will keep the discussion in one thread and others that can help will be able to follow the discussion more easy.

Thanks.

|||

i apologies for that

so what can i do about my DTSs in sql server 2005 ?

|||

I am not that experienced with SSIS for 2005, hopefully swells or somebody else will be able to help you further...

|||

In SQL Server 2000, you could create a schedule by right-clicking on a package in Enteprise Manager and selecting "Schedule Package." SQL Server 2000 created the SQL Server Agent job. You could find the jobs in Enterprise Manager under the Management folder, in the jobs tree.

In SQL Server 2005 there are some changes ...

1. SQL Server Agent is not automatically enabled. Make sure the service has been started.

2. You do not automatically create scheduled jobs for SSIS. You should right click on the SQL Server Agent icon in the SQL Server Object Explorer in the Management Studio and select to create a new Agent Job. When you do that, you can add a new job step to execute an SSIS package. You can also add a new schedule.

The end result is similar to 2000 - jobs are listed under the SQL Server Agent node; jobs have steps - one step executes a package; and jobs have schedules.

An important difference is that SSIS now has an Agent subsytem for more better integrated execution.

hth

Donald

|||

After create your ssis package. Here is step you have to do to make a job run....

My ssis is running every 10 min.....

Here is what I do to make it work.......

Create the proxy account, And SQL Server Agent running under your NT Acount and then the job Steps running under the "SQL Agent Service Account" and your nt account as the owner of the job. It will works perfect.

I don't know if you know how to create proxy account. Here is how to create:

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

kenny

|||Similar problems, but (so far) the credentials/proxy doesn't seem to resolve it.

We have two people who have developed some SSIS packages, myself and

another developer. The other developer's SSIS package runs fine from

BIDS, DTexec and DTexecUI, but not from SQL Server agent. My SSIS

package runs fine from BIDS, DTexec, DTexecUI and local SQLServer

Agent. I haven't had the opportunity to have it deplyed into another

environment to check if I have problems from the server.

What was interesting to me, in attempting to test the credentials, was

that the working SQL Server Agent job, it I edit it to use the proxy,

fails. If I edit the job back to using the SQL Agent Service Account

(on the RUN AS tab on the task), then it works successfully.

Now this may be because I've set up the credential incorrectly, since

it was my first time (be gentle :-)). I'm going to try and modify the

script above and see if that works any better. The credential I set up

used a local user I created that was part of the powerusers group. The

package does file operations locally (based on the current

configuration file). The 'ProtectLevel' was set to

'EncryptSenstitiveWithPassword', and this specific test job is using

SQL Server as the package source.

Nay help? The KB http://support.microsoft.com/kb/918760 states that

this is ' This behavior is by design.' - but surely it should also be

easy to use?|||using the scipr (after removing the duplicate, and adding exec's), I

generated a proxy and a credential. The user being used is the same one

I create earlier (which is a member of the power user group on my

laptop). I get the following error:

-

Date 25/07/2006 14:21:59

Log Job History (Test Excel Orchestration)

Step ID 1

Server MyLaptop\SQL2005_SP1_TEST

Job Name Test Excel Orchestration

Step Name Invoke Excel Orchestration

Duration 00:00:01

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: MyLaptop\SSISAgentJobCredUser. The package could not be loaded. The step failed.

-

this proves the credential at least worked - it did try to run with the correct user, but still no joy.

.... even less joy now, since I now get the following, trying to use this in the way that worked before:
Date 25/07/2006 14:27:45

Log Job History (Test Excel Orchestration)

Step ID 1

Server MyLaptop\RGALBRAITH\SQL2005_SP1_TEST

Job Name Test Excel Orchestration

Step Name Invoke Excel Orchestration

Duration 00:00:00

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.|||

Hey,

Today I got the same error as you, to fixed I didn't create the credentials, I changed the owner for the Scheduled Jobs instead, and it's works great!

|||

I fixed this by adding the owner of the package to the owned schema's in the agent roles in msdb.

take a look at this link:

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

One more problem with importing csv files

Hi,

I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like:

...,Bearing Double "D" Flange,...

And the result is: ... | Bearing Double | null (all following columns are null)

The code is as following:

string strCsvConn =@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";

using (OleDbConnection cn =newOleDbConnection(strCsvConn))

{

string strSQL ="SELECT * FROM " + strFileName;

OleDbCommand cmd =newOleDbCommand(strSQL, cn);

cn.Open();

using (OleDbDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

string str =Convert.ToString(dr[8]);

}

// Bulk Copy to SQL Server

//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn))

//{

// bulkCopy.DestinationTableName = strSqlTable;

// bulkCopy.WriteToServer(dr);

//}

}

}

Any idea is highly appreciated.

shz

Hello shz,

I would recomend using a TextReader for processing a .csv file.

Hope this helps

regards,

G

|||

Thanks Gonzo. However, TextReader should be the last thing I'd like to use.

shz

sql

One insert box per individual name?

Hi,

My Crystal Report has a box around each section (detail sections a&b). So, for example, John Doe might have 3 different sections and appear within 3 boxes. But now my client would like this box to group for each individual name, meaning John Doe's data should only be in one box. How would I do this? I'm new to Crystal Reports...help!

Please help as soon as possible.

Thanks so much!
LillyGroup your records on that field (click on it-> Insert->Group) place the fields you want to shown in you report into GF then suppress Details section.

One Account and one product result

As an example i have 4 columns:

Account, Product, Description, Price

I want to retrieve every account that has bought a particular product and sum up the price - I managed to do that but it lists it as:

Account, Product, Description, Price

Acc1 CD Compact Disc 50

Acc2 CD Compact Disc 50

Acc3 CD Compact Disc 50

Acc4 CD Compact Disc 50

So gives me a total for each account. I want to have the above returned back as ALL the accounts i have chosen but only list the product once with the price ie.:

Account, Product, Description, Price

Acc1,2,3,4 CD Compact Disc 200

How could this be done?

Thanks

Since you didn't provide the table DDL, I will work with only the information you supplied.

Code Snippet

SELECT
Account = substring(( SELECT ', ' + Account as [text()]
FROM MyTable m2
WHERE m2.Account = m1.Account
FOR XML path(''), elements
), 3, 1000
),
Product,
Description,
Price = sum( Price)
FROM MyTable m1
GROUP BY Product

|||

EssCee:

I am bothered by what I see in your result set:

Account Product Description Price

- -- -- --

Acc1,2,3,4 CD Compact Disc 200

What bothers me most is seing the ACCOUNT code listed as "Acc1,2,3,4". Are you really wanting the query to break the ACCOUNT column apart to get a "Base Account" component and then append a "string product" of all of the numeric pieces of all of the different values in the ACCOUNT column?

Also, I am baffeled by seeing the results of PRICE as the sum of the PRICE columns of each row. Would you also please verify if this is correct?

|||

Kent,

I didn't see your response as a 'rant', only asking deeper questions.

I didn't see the account grouping issue you raised -it wasn't apparant in the OP. The sum() issue seemed specious, but there are many, many 'strange' uses of data around. What gets me is how often we get requests to concatenate all column values into one result. I guess most folks just don't get it that presentation is a client task, not a data server task.

With the growth in SQL Server, and the apparant 'push' to move data code down to the application, we will be getting more and more 'half-backed' designs (EssCee, I don't know enough of your design to make that assumption, please don't take it personally.) Say after me: "Developers are not Database Professionals, They are trying to solve a user related problem. Their prime objective is to make the end user happy. A DBA's prime objective is to protect the data at all costs." Repeat as often as necessary -THEN bang you head on your desk.

|||

I know it doesn't answer your question directly...but....

Probably you could get by with a COUNT of the number of accounts that have purchased that product:

Code Snippet

select Count(*) as [AccountCount]

, Product

, Description

, sum(Price) as [GrossRevenue]

from <MyTable>

where Product = 'CD'

group by Product, Description

This will get you exactly what you asked for, but it's two hits to the table:

Code Snippet

declare @.AccountList nvarchar(4000)

select @.AccountList = isnull(@.AccountList + ',' + Account,Account)

from <MyTable>

where Product = 'CD'

select @.AccountList as [Accounts]

, Product

, Description

, sum(Price) as [GrossRevenue]

from <MyTable>

where Product = 'CD'

group by Product, Description

|||

Thanks everyone for your input. Just to clarify

The database is not designed by me. Another company who dont provide this support but im trying to develop a solution for some of these problems. Reason i need it in this format is because two companies have merged together and i need one total - hopefully that makes sense.

Ill try out all of these suggestions. Thanks to you all.

Once only delivery (high availibiltity) ?

Hi There

I was wondering if someone could elaborate (or provide a link) on how the once only delivery works for servcie broker.

For example you have DB1 that sends messages to DB2. DB1 has corruption at 1pm, you retore the database to 12:30pm, this will have all the messages that were in this instance at 12:30 , however between 12:30 AND 1PM several successful messages were sent. These messages are in the restored DB1 queue, they get sent again?

How does service borker ensure that these messages are not processed again. The only thing i can think of is that the inititator or target keep a complete history of messages processed. But surely this "table" would get huge and slow down servcie broker if it had to check this tale every time a message is sent or received.

I cannot find much in BOL on this topic, or maybe i cannot find the topic.

Thanx

This question has no easy answer. On a typical database application you deal with the potential data loss of 'back-in-time' recovery by inspecting the application data and comparing it with some other record (web logs, or a paper trail like invoices or receipts etc) and you can probably recreate the lost data. If the data is critical for you business, you take steps to ensure such event cannot happen (e.g. you run with synchronous database mirroring, or you enforce some hardware disk mirroring).

On a Service Broker application the situation is more compl,ex, because you are now faced with a distributed state between the services involved. As you noticed, a back-in-time restore will put some conversation out of sync. Unless you roll back in time the other database as well (by restoring to an apropiate LSN), you cannot continue those conversations. The most difficult problem is when DB1 has sent a message between 12:30 and 1, DB2 has received the said message and acted upon (e.g. had printed and mailed a paper invoice). If you restore DB1 at 12:30, DB2 now has a message that DB1 had not sent. There is no possible 'history table' that can recover this situation.

How to act on such situations really depends on the business meaning of each conversation. After a restore, DB1 broker will be disabled. One would have to inspect the state of pending conversations in DB1 and compare them with the state of DB2 to see if a conversation can be allowed to continue or it has to be errored or event completely cleaned up. One possible action is to error out all restored conversations in DB1 (using ALTER DATABASE ... SET ERROR_BROKER_CONVERSATIONS) and then enable back the broker in DB1. This way any conversation between DB1 and DB2 that was still pending will be errored (and the application has to deal with the error appropiately). Any conversation that was started by DB1 between 12:30 and 1 and now only exists on DB2 will eventualy time out and error (and this is another reason why one should use the LIFETIME option in BEGIN DIALOG).

And again, if the business loss from loosing those conversations is critical, you must deploy a solution that simply does not allow this to happen (like a mirrored database solution).

HTH,
~ Remus

on "uniqueidentifier"

When your database is distributed. For example: you have it on one central
server but some people also have it on their laptops and then syncronize
with the central server (through the sql srv technique called "replication")
when they have the connection (like once in a w). IDENTITY is not very
suitable for a key because there is no way to see how much records are in
the table right now.. GUID column will be a key unique in the whole system.
Hope I'm clear in my point.
Milan
P.S. I don't say that's not possible to achieve with IDENTITY - it would be
possible to tweak replication how to deal with such fields - but this
process is not straightforward if you have foreign keys...
"Martin Arvidsson" <yeahright@.ms.com> wrote in message
news:%23CUfIuGRGHA.5900@.tk2msftngp13.phx.gbl...
> Any good example on when to use the unique identifier?
> In the AdventureWorks database it's used frequently
> Doesnt the CustomerID for example represent a unique identifier?
> Regards
> Martin
>Hi!
I think so.
You can use for example
CustomerID and set it as a primary key, and use GUID to use for replication,
so that the system can keep track of the replication, is that correct?
Regards
Martin
"Milan Kosanovic" <milan@.REMOVETHISsoftlakecity.com> skrev i meddelandet
news:%23klfBVURGHA.3192@.TK2MSFTNGP09.phx.gbl...
> When your database is distributed. For example: you have it on one central
> server but some people also have it on their laptops and then syncronize
> with the central server (through the sql srv technique called
> "replication") when they have the connection (like once in a w).
> IDENTITY is not very suitable for a key because there is no way to see how
> much records are in the table right now.. GUID column will be a key unique
> in the whole system.
> Hope I'm clear in my point.
> Milan
> P.S. I don't say that's not possible to achieve with IDENTITY - it would
> be possible to tweak replication how to deal with such fields - but this
> process is not straightforward if you have foreign keys...
> "Martin Arvidsson" <yeahright@.ms.com> wrote in message
> news:%23CUfIuGRGHA.5900@.tk2msftngp13.phx.gbl...
>

Ole DB Example For Windows Mobile 5.0

Dear All:

Is there a tutorial for WM5.0 to connect to SQL CE 3.0 using Ole DB using the headers:

#include "ca_merge30.h"

#include "ssceoledb30.h"

#include "ssceerr30.h"

The example Northwind at "Sql Mobile 5.0" doesn't works with WM5.0 SDK, due to imcompatibility issues. It works with the WM2003 sdk, but i'm programming in native c++ for WM5 devices.

I'm using the header from SQL Everywhere.

Thanks.

Alfredo Mendiola Loyola

Lima Perú

Please look at the KB articles

http://support.microsoft.com/default.aspx?scid=kb;en-us;914174&sd=rss&spid=2852

and the following post on this forum

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=313150&SiteID=1

Thanks

Arun

OLe DB Command and Using the value of an input column more than once

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

anydobbo wrote:

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

Yeah, that's what you have to do!

anydobbo wrote:

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

I may be wrong but I don't think you can do that. The OLE DB Command accepts a DML statement but not a statement block (as far as I know).

-Jamie

|||Thanks Jamie

OLE DB - ADO .NET

Does anybody know why in some cases you can use both connection types, in others you can't. Like for example, you start a data flow task with a Datareader Source which uses an ADO .NET connection manager, next you want to do a lookup and seems like the only type of connections you can use in a lookup table is the OLE DB.

So OLE DB is placed hard coded in a lookup task, and I thought things changed since the coming of OOP ...

I think ole db is ok

just when i use script component,i use ado.net

|||Your observation is correct -- Lookup currently only supports OLE DB connections. We hope to add ADO.NET connection support in the future.|||Ok

Old join syntaxis its support by SQL Server 2000?

A question...
The old join syntaxis works in SQL Server 2000??...
I mean, this example works in SQL Server 2000??...
SELECT *
FROM Employee e, Departmend d
WHERE d.departmentId *= e.departmentIdI would think most of the previous SQL 6.5 etc. should be
upward compatible. I use INNER JOINS etc. and haven't
run across problems between the versions. What is the
previous version of SQL are you referring to?

BobbyJ

Originally posted by ericka
A question...

The old join syntaxis works in SQL Server 2000??...
I mean, this example works in SQL Server 2000??...

SELECT *
FROM Employee e, Departmend d
WHERE d.departmentId *= e.departmentId|||I'm using SQL Server 6.5 but I want to migrate to SQL Server 7.0 and I want to left like that my joins...; but I want to know if this joins (=*, *=) it'll in Sql Server 2000...

Originally posted by BobbyJ
I would think most of the previous SQL 6.5 etc. should be
upward compatible. I use INNER JOINS etc. and haven't
run across problems between the versions. What is the
previous version of SQL are you referring to?

BobbyJ|||Ok. Don't quote me but I think it should. Let's see what others respond
with.....|||use

select *
from employee e
left join departmet d on (d.departmentid = e.departmentid)

radzi.

"BobbyJ" wrote in message
news:2430436.1043361575@.dbforums.com...|||For Books Online:

Transact-SQL Joins
In earlier versions of Microsoft SQL Server 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either the FROM or WHERE clause. Inner joins specified in the WHERE clause do not have the same problems with ambiguity as the Transact-SQL outer join syntax.

All this to say that the Inner/Outer Right/Left syntax is more reliable and at somepoint the "*" syntax will no longer be supported.|||'ambiguous" cauze the user is id***ot and does not know what he is doing...

ORACLE 8 does not know RIGHT JOIN and uses only *=

did I get any "ambiguous" joins with Oracle? NO!

LEFT JOIN is more powerful than *= but that's it. It is not different or ambiguous, it can do just more.... NOT LESS nor DIFFERENT!

To your question... if your SQL works with =* then it WILL work just fine....

jiri|||RE:
Q1 [Does the old join syntax work in SQL Server 2000?]
A1 As others have already noted, for the most part, such older syntax is (still) supported.

Some additional points that maybe helpful:

i Consider carefully checking for any potentially problematic changes in behavior between versions. There are not very many, however if any do apply they may present some issues for your environment. Several relate to character handling and related function differences e.g.(empty string 6.x literals ' ' interpreted as a space, CHARINDEX and PATINDEX processing to generate null results, etc.).

ii Often it is possible to have the Enterprise Manager query building functionality, (not query analyzer), 'rewrite' older *= joins in the newer format (by pasting in the old format sql and clicking on verify syntax).

iii If troublesome version related behavior issues (mentioned in i above) are found to exist, you may wish to consider temporarily implementing a 6x db compatibility setting (the setting may be easily changed). This may allow significant use of 7.0 features / advantages while allowing 6.x issues to be addressed (and conveniently tested / verified) over a longer time frame.

For Example:

-- To view the current dbcmptlevel of Pubs
exec sp_dbcmptlevel
@.dbname = 'Pubs'
Go

-- To set to 6.5:
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = 65
Go

-- Check the dbcmptlevel change to 6.5 settings
exec sp_dbcmptlevel
@.dbname = 'Pubs'
Go|||P.S. Was the timestamp access issue (RE: 6.5 timestamp column access in 7.0) resolved satisfactorily?