Showing posts with label example. Show all posts
Showing posts with label example. Show all posts

Wednesday, March 28, 2012

I dont know How can i upload my sqlserver file to the server

I don't know How can i upload my sqlserver file to the server
could you help me?
could you write an example code in query analyser for
uploading sql server file to databaseDo you have access to the SQL Server on the server? That is, can you connect to the server from the machine with your SQL Server? If so, you can use DTS (in internet explorer, right click on the database and select Import (or export, depending upon the database you right click on).

i don′t understand

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

Monday, March 26, 2012

I can't understand the meaning of a prediction query

Dear friends,
I'm reading Wiley's Data mining with SQL Server 2005... There are MANY things I can't understand about MovieClick example (Chapter 3).
I hope someone is going to help me with this troubles...

WARNING (1): I'm a dummy both with sql server and data mining.
WARNING (2): My English is not good at all.

Just two questions for now:

1) When I create the model to predict the number of bedrooms for homeowners, the book says to check BEDROOMS as Predictable... question: is it also an INPUT for the model, or PREDICTABLE only?

2) I'd like to keep this model (number of bedrooms.......) and make a prediction query.

- Query builder
- select case table -> Homeowners
- Drag the Customer ID column from the Homeowners table and drop it on the grid
- Drag the BEDROOMS column from the mining model and drop it on the grid.
- On the last row: Source=PredictionFunction, Field=PredictProbability
- Drag the BEDROOMS column from the mining model and drop it into Criteria/Argument
- Add (i.e.) 'Two or Three' to the field Criteria/Argument

I execute the query and I obtain many rows in a table with the following colums: CustomerID, BEDROOMS and Expression: WHAT DOES THIS MEAN?
WHICH INFO DO I GET FROM THOSE NUMBERS? WHAT CAN I LEARN FROM THEM?

Thanx a lot in advance, please help me!

1: It won't actually matter if you make it INPUT as well. When you mark something as PREDICTABLE, it generally means that the algorithm will try to learn about the data in that column. When you mark it as INPUT, it generally means that the algorithm will consider this column as information about the predictable columns. For trees, for example, the algorithm will create a tree for each predictable column, and the trees can have splits on the input columns. If you have a column that is marked INPUT and PREDICTABLE, in general, it only matters if you have more than one PREDICTABLE, since the columns don't act as inputs for themselves, for example, the tree for # of bedrooms isn't going to have a split on # of bedrooms, but if you also make Home Ownership predictable, than # of bedrooms could be a condition in the tree for Home Ownership. (the one exception is for clustering, but that's a longer explanation).

2: What do the numbers mean? The first column is just the customer ID from the input - this could also be the customer name, for example. The second column is the # of bedrooms predicted by the algorithm - that is, the count of bedrooms that has the highest probability of all options given the input. The last column is the probability that the # of bedrooms would be 'Two or Three'.

What can you use this for? Take for example you were building a model to predict whether people would buy a product. You would want to predict which people would buy or not buy. In addition, you would probably want to contact those people with the highest probability of buying - therefore you need to use the PredictProbability function with a specified value. As you learn to use the product more, you will see that you can use the Profit Chart to determine the maximum profit for a marketing campaign using a Mining Model. The chart will give you a probability threshold. You want to use this threshold in a DMX query to select all customers who have a probability higher than that threshold in order to achieve maximum profit.

Hope this helps

-Jamie

Friday, March 9, 2012

i am geeting an idea

any one please help me
In our company we maitain a table for cheques issued for
example the description is
cheques_issued(chq_no int, reciever_name varchar(30))
we ha ve so many number of records in this table with
defferent chq numbers.
one day our senio manager came to me and given a list of
100 numbers collected from the recievers and asked me to
find out how many of the numbers in the list of 100 given
by him are not there in the table.
is there any method to find it direectly trough a querry?
if i create a temp table i hve to use 100 insert
statements to send 100 values into temparary table and
compare the temp table with cheques_issued table. This is
problematic for me.
please give me any solution.Once you have your 100 numbers in a #temp table (called #temp100 here),
there are plenty of ways. Let's assume this DDL and sample data:
CREATE TABLE cheques_issued(chq_no INT)
INSERT cheques_issued VALUES(1)
INSERT cheques_issued VALUES(2)
INSERT cheques_issued VALUES(3)
INSERT cheques_issued VALUES(4)
CREATE TABLE #temp100(chq_no INT)
INSERT #temp100 VALUES(1)
INSERT #temp100 VALUES(2)
INSERT #temp100 VALUES(3)
INSERT #temp100 VALUES(4)
INSERT #temp100 VALUES(5)
INSERT #temp100 VALUES(6)
INSERT #temp100 VALUES(7)
You could get your results using a simple NOT IN
SELECT COUNT(*) FROM #temp100 WHERE chq_no NOT IN (SELECT chq_no FROM
cheques_issued)
Or a NOT EXISTS
SELECT COUNT(*) FROM #temp100 WHERE NOT EXISTS (SELECT chq_no FROM
cheques_issued WHERE chq_no = #temp100.chq_no)
Or a LEFT JOIN
SELECT COUNT(*) FROM #temp100
LEFT OUTER JOIN cheques_issued c
ON #temp100.chq_no = c.chq_no
WHERE c.chq_no IS NULL
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"kalyana chakravarthi" <kalyannow@.yahoo.com> wrote in message
news:05d001c3c069$3a4a6900$a401280a@.phx.gbl...
> any one please help me
> In our company we maitain a table for cheques issued for
> example the description is
> cheques_issued(chq_no int, reciever_name varchar(30))
> we ha ve so many number of records in this table with
> defferent chq numbers.
> one day our senio manager came to me and given a list of
> 100 numbers collected from the recievers and asked me to
> find out how many of the numbers in the list of 100 given
> by him are not there in the table.
> is there any method to find it direectly trough a querry?
> if i create a temp table i hve to use 100 insert
> statements to send 100 values into temparary table and
> compare the temp table with cheques_issued table. This is
> problematic for me.
> please give me any solution.
>

i am a beginner, who have an example for operate the DataBase(Sql Server) in Asp.net(C#)?

i am a beginner, who have an example for operate the DataBase(Sql Server) in Asp.net(C#)?http://www.dotnetjunkies.com/quickstart/aspplus/doc/webdataaccess.aspx

Wednesday, March 7, 2012

HyperThreading and NT Fibers

We've got a customer whose system is performing below average. Example, a billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000, 2-1.5GHz, 2GB RAM, RAID5 array
, SQL 2000 SP3). We turned on the performance monitors recently and watched a bill run. The hard drives and RAM were barely used while the 4 processor instances (2 physical = 4 logical with hyperthread) were all consistently above 90%.
We deduced that the processors were the obvious bottleneck but we are unsure why a less powerful machine (my laptop) out-performs the more powerful server. The only thing I can think is that the hyper-threading or NT Fiber setting is adversely affecting t
he processors.
Has anyone had any problems with hyperthreading or NT fibers or have any ideas as to where I might start looking?
Thanks in advance,
Dean
Does the calculation use the same query plan on both systems?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>
|||You can't compare these two systems side by side. For instance your laptop
has a faster processor (even though it only has one) and most likely has
more level 1 cache. The disk in the laptop probably has more cache as well
which can make a big difference over a Raid 5. That said you can still be
running into a multi-processor issue but it is hard to say for sure. Try
running the process with MAXDOP set to 1 and see if that makes a difference.
Are you sure the disk queues are fine? Is there just one RAID 5 and is
everything (data, tempdb, logs) on the same array?
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>
|||I expect so. On my laptop I just took a copy of their database and re-ran the process.
|||We monitored the server with performance counters on the processors, RAM and drives. Yes, it is one big RAID partition but the monitors showed almost no activity on the disks and very little on the RAM while the processors were very high.
|||Look at the estimated query execution plan on both systems. Also, check the
disk queue length on our data drives. A long queue length may indicate a
bottleneck at the hardware layer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:601177D1-D700-441B-9607-A15EA29855B4@.microsoft.com...
> I expect so. On my laptop I just took a copy of their database and re-ran
the process.
|||I just resolved a performance issue related to the fact that the execution plan was different between my development & production.
It was directly related to differences in hardware, specifically the environments you describe. I wouldn't assume that the execution
plans are the same. I'd run an execution plan in both environments to confirm.
If you find that the execution plans are different (and parallelism is involved), I would suggest looking up the query hint OPTION
(MAXDOP 1) in BOL.
ChrisG
|||I would still change the MAXDOP and see what happens.
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:827EF5AF-5FC0-43FD-BFD5-BD7D7A89C3D8@.microsoft.com...
> We monitored the server with performance counters on the processors, RAM
and drives. Yes, it is one big RAID partition but the monitors showed almost
no activity on the disks and very little on the RAM while the processors
were very high.
|||Exactly, I had this issue when we were doing the migration, taking half time when we turned that off. Is that a Dell machine?
Yes, you should turn that off.
|||I'll try it and see what happens...

HyperThreading and NT Fibers

We've got a customer whose system is performing below average. Example, a bi
lling calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB R
AM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000, 2-1
.5GHz, 2GB RAM, RAID5 array
, SQL 2000 SP3). We turned on the performance monitors recently and watched
a bill run. The hard drives and RAM were barely used while the 4 processor i
nstances (2 physical = 4 logical with hyperthread) were all consistently abo
ve 90%.
We deduced that the processors were the obvious bottleneck but we are unsure
why a less powerful machine (my laptop) out-performs the more powerful serv
er. The only thing I can think is that the hyper-threading or NT Fiber setti
ng is adversely affecting t
he processors.
Has anyone had any problems with hyperthreading or NT fibers or have any ide
as as to where I might start looking?
Thanks in advance,
DeanDoes the calculation use the same query plan on both systems?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>|||You can't compare these two systems side by side. For instance your laptop
has a faster processor (even though it only has one) and most likely has
more level 1 cache. The disk in the laptop probably has more cache as well
which can make a big difference over a Raid 5. That said you can still be
running into a multi-processor issue but it is hard to say for sure. Try
running the process with MAXDOP set to 1 and see if that makes a difference.
Are you sure the disk queues are fine? Is there just one RAID 5 and is
everything (data, tempdb, logs) on the same array?
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:EE7A4018-48E7-405C-90F2-2CF0FD54F256@.microsoft.com...
> We've got a customer whose system is performing below average. Example, a
billing calculation that takes 13 minutes on my laptop (XP PRO, 2.2 GHz, 1GB
RAM, 4200RPM HD, SQL 2000 SP3) takes 28 minutes on their server (WIN2000,
2-1.5GHz, 2GB RAM, RAID5 array, SQL 2000 SP3). We turned on the performance
monitors recently and watched a bill run. The hard drives and RAM were
barely used while the 4 processor instances (2 physical = 4 logical with
hyperthread) were all consistently above 90%.
> We deduced that the processors were the obvious bottleneck but we are
unsure why a less powerful machine (my laptop) out-performs the more
powerful server. The only thing I can think is that the hyper-threading or
NT Fiber setting is adversely affecting the processors.
> Has anyone had any problems with hyperthreading or NT fibers or have any
ideas as to where I might start looking?
> Thanks in advance,
> Dean
>|||I expect so. On my laptop I just took a copy of their database and re-ran th
e process.|||We monitored the server with performance counters on the processors, RAM and
drives. Yes, it is one big RAID partition but the monitors showed almost no
activity on the disks and very little on the RAM while the processors were
very high.|||Look at the estimated query execution plan on both systems. Also, check the
disk queue length on our data drives. A long queue length may indicate a
bottleneck at the hardware layer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:601177D1-D700-441B-9607-A15EA29855B4@.microsoft.com...
> I expect so. On my laptop I just took a copy of their database and re-ran
the process.|||I just resolved a performance issue related to the fact that the execution p
lan was different between my development & production.
It was directly related to differences in hardware, specifically the environ
ments you describe. I wouldn't assume that the execution
plans are the same. I'd run an execution plan in both environments to confir
m.
If you find that the execution plans are different (and parallelism is invol
ved), I would suggest looking up the query hint OPTION
(MAXDOP 1) in BOL.
ChrisG|||I would still change the MAXDOP and see what happens.
Andrew J. Kelly
SQL Server MVP
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:827EF5AF-5FC0-43FD-BFD5-BD7D7A89C3D8@.microsoft.com...
> We monitored the server with performance counters on the processors, RAM
and drives. Yes, it is one big RAID partition but the monitors showed almost
no activity on the disks and very little on the RAM while the processors
were very high.|||Exactly, I had this issue when we were doing the migration, taking half time
when we turned that off. Is that a Dell machine?
Yes, you should turn that off.|||I'll try it and see what happens...