SQL server connection: based

Showing posts with label based. Show all posts

Tuesday, March 20, 2012

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableYou can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableThis is a multi-part message in MIME format.
--000602000304030604090506
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
--000602000304030604090506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can "update" a view in certain restricted conditions (like
there can't be any joins in the view for instance - see BOL for the
complete list of restrictions).Â Alternately, you can write an INSTEAD
OF trigger on the view so that the trigger code handles the updating of
the underlying tables when a user runs an UPDATE statement against the
view.Â You can use this method when the basic updatable view
requirements cannot be met. 
 
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view). 
 
Cheers, 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
KathyV wrote:
<blockquote cite="mid282DBEFC-5100-4936-A1EA-A11561D1DDD4@.microsoft.com"
type="cite">
<pre wrap="">How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
</pre>
</blockquote>
</body>
</html>
--000602000304030604090506--

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?
A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>
sql

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
--
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>

another trigger question

Is trigger based on table level or can I define it on certain fields level
against a table?A trigger is defined on an INSERT, UPDATE or DELETE statement against a
table (in fact you can write a special kind (INSTEAD OF trigger) against
views now too). INSERTs & DELETEs work on an entire row so it doesn't make
much sense to do stuff at the column level there, but within a trigger on an
UPDATE operation you can define specific behaviour if certain columns on the
table are changed with the IF UPDATE() clause (it works with INSERT triggers
too but makes less sense). For example,
CREATE TRIGGER MyInsertTrigger on dbo.MyTable
FOR UPDATE AS
-- Check to see if a specific column has changed
if UPDATE(MyCol)
BEGIN
-- Do some SQL stuff in here because the column we're interested
in has changed
END
-- Do other general UPDATE trigger stuff
GO
SQL Books Online has more info on it in the "Creating and Maintaining
Databases" | "Enforcing Business Rules with Triggers" | "Creating a Trigger"
| "Programming Triggers" section.
HTH.
Cheers,
Mike
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:ud8HZU20EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Is trigger based on table level or can I define it on certain fields level
> against a table?
>

Monday, March 19, 2012

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I get
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position by
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to do
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
Maury
I tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury
|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades to
> date. I want to generate a row for every month where there was any activity.
> Some of the trades have null shares, however. So I did this (and before I get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stock,
> don't generate a row. That's because I was generating the opening position by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I get
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position by
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to do
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
MauryI tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades to
> date. I want to generate a row for every month where there was any activity.
> Some of the trades have null shares, however. So I did this (and before I get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stock,
> don't generate a row. That's because I was generating the opening position by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I ge
t
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position b
y
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to d
o
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
MauryI tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades t
o
> date. I want to generate a row for every month where there was any activit
y.
> Some of the trades have null shares, however. So I did this (and before I
get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stoc
k,
> don't generate a row. That's because I was generating the opening position
by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code]
)]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to
do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

Another SQL Server 2k Setup failed to configure the server error. Please help

Hi all,
I realised that there are many postings regarding this error, however,
I have tried all possible proposed solutions based on the replies for
the past 24 hours trying to solve this problem, but failed. Frustrating
it is, but I hope you guys can help me with this.
Problem arises when I tried to install SQL Server 2k Developer's
Edition on a Windows XP Professional. I managed to get it installed
until towards the end when it tried to configure the server, it failed
miserably, with a message:
"Setup failed to configure the server. Refer to the server error logs
and sqlstp.log for more information."
Here is the snippet of the error log file (sqlstp.log)
10:32:59 C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
###############################################################################
Starting Service ...
Latin1_General_CI_AS
-m -Q -T4022 -T3659
Connecting to Server ...
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
SQL Server configuration failed.
###############################################################################
Please Help. Thanks.
Cheers
AlvinHave you read this article?
http://support.microsoft.com/kb/306345/EN-US/
Is it given you an idea?
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ###############################################################################
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ###############################################################################
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi Alvin,
It sounds to me that you may have a problem with your MDAC installation
which is causing SQLserver not to boot correctly.
I would suggest you try to either reinstall the current version of MDAC on
you server or install the latest version.
You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
to determine your current MDAC version.
Then try to perform the installation again.
Not sure of previous suggestions, so apologise if I'm susggesting something
you may have already tried?
Regards
Immy
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ###############################################################################
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ###############################################################################
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi
Thanks for responding to my queries, greatly appreciated. However, I
have tried installing MDAC separately and yet it still failed when
reinstalling SQL Server 2000. I have sorta given up on this problem,
but tried again on another machine and it worked fine. So again I don't
know what was wrong with the settings in the first PC. No resolution =(
Cheers
Alvin
Immy wrote:
> Hi Alvin,
> It sounds to me that you may have a problem with your MDAC installation
> which is causing SQLserver not to boot correctly.
> I would suggest you try to either reinstall the current version of MDAC on
> you server or install the latest version.
> You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
> to determine your current MDAC version.
> Then try to perform the installation again.
> Not sure of previous suggestions, so apologise if I'm susggesting something
> you may have already tried?
> Regards
> Immy
>
> "alvinyyt" <alvinyyt@.gmail.com> wrote in message
> news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> > Hi all,
> >
> > I realised that there are many postings regarding this error, however,
> > I have tried all possible proposed solutions based on the replies for
> > the past 24 hours trying to solve this problem, but failed. Frustrating
> > it is, but I hope you guys can help me with this.
> >
> > Problem arises when I tried to install SQL Server 2k Developer's
> > Edition on a Windows XP Professional. I managed to get it installed
> > until towards the end when it tried to configure the server, it failed
> > miserably, with a message:
> >
> > "Setup failed to configure the server. Refer to the server error logs
> > and sqlstp.log for more information."
> >
> > Here is the snippet of the error log file (sqlstp.log)
> >
> > 10:32:59 C:\Program Files\Microsoft SQL
> > Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> > MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> > ###############################################################################
> >
> > Starting Service ...
> > Latin1_General_CI_AS
> > -m -Q -T4022 -T3659
> > Connecting to Server ...
> > driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> > default driver specified
> > driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> > default driver specified
> > driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> > [Microsoft][ODBC Driver Manager] Data source name not found and no
> > default driver specified
> > SQL Server configuration failed.
> >
> > ###############################################################################
> >
> > Please Help. Thanks.
> >
> > Cheers
> >
> > Alvin
> >

Another SQL Server 2k Setup failed to configure the server error. Please help

Hi all,
I realised that there are many postings regarding this error, however,
I have tried all possible proposed solutions based on the replies for
the past 24 hours trying to solve this problem, but failed. Frustrating
it is, but I hope you guys can help me with this.
Problem arises when I tried to install SQL Server 2k Developer's
Edition on a Windows XP Professional. I managed to get it installed
until towards the end when it tried to configure the server, it failed
miserably, with a message:
"Setup failed to configure the server. Refer to the server error logs
and sqlstp.log for more information."
Here is the snippet of the error log file (sqlstp.log)
10:32:59 C:\Program Files\Microsoft SQL
Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
########################################
####################################
###
Starting Service ...
Latin1_General_CI_AS
-m -Q -T4022 -T3659
Connecting to Server ...
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
SQL Server configuration failed.
########################################
####################################
###
Please Help. Thanks.
Cheers
AlvinHave you read this article?
http://support.microsoft.com/kb/306345/EN-US/
Is it given you an idea?
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ########################################
##################################
#####
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ########################################
##################################
#####
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi Alvin,
It sounds to me that you may have a problem with your MDAC installation
which is causing SQLserver not to boot correctly.
I would suggest you try to either reinstall the current version of MDAC on
you server or install the latest version.
You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
to determine your current MDAC version.
Then try to perform the installation again.
Not sure of previous suggestions, so apologise if I'm susggesting something
you may have already tried?
Regards
Immy
"alvinyyt" <alvinyyt@.gmail.com> wrote in message
news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...
> Hi all,
> I realised that there are many postings regarding this error, however,
> I have tried all possible proposed solutions based on the replies for
> the past 24 hours trying to solve this problem, but failed. Frustrating
> it is, but I hope you guys can help me with this.
> Problem arises when I tried to install SQL Server 2k Developer's
> Edition on a Windows XP Professional. I managed to get it installed
> until towards the end when it tried to configure the server, it failed
> miserably, with a message:
> "Setup failed to configure the server. Refer to the server error logs
> and sqlstp.log for more information."
> Here is the snippet of the error log file (sqlstp.log)
> 10:32:59 C:\Program Files\Microsoft SQL
> Server\80\Tools\Binn\cnfgsvr.exe -F "C:\WINDOWS\sqlstp.log" -I
> MSSQLSERVER -V 1 -M 0 -Q "Latin1_General_CI_AS" -H 2360174 -U sa -P
> ########################################
##################################
#####
> Starting Service ...
> Latin1_General_CI_AS
> -m -Q -T4022 -T3659
> Connecting to Server ...
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> driver={sql server};server=AU-D01095;UID=sa;PWD=;database=master
> [Microsoft][ODBC Driver Manager] Data source name not found and no
> default driver specified
> SQL Server configuration failed.
> ########################################
##################################
#####
> Please Help. Thanks.
> Cheers
> Alvin
>|||Hi
Thanks for responding to my queries, greatly appreciated. However, I
have tried installing MDAC separately and yet it still failed when
reinstalling SQL Server 2000. I have sorta given up on this problem,
but tried again on another machine and it worked fine. So again I don't
know what was wrong with the settings in the first PC. No resolution =(
Cheers
Alvin
Immy wrote:[vbcol=seagreen]
> Hi Alvin,
> It sounds to me that you may have a problem with your MDAC installation
> which is causing SQLserver not to boot correctly.
> I would suggest you try to either reinstall the current version of MDAC on
> you server or install the latest version.
> You can obtain a utility from MS (http://support.microsoft.com/kb/301202/)
> to determine your current MDAC version.
> Then try to perform the installation again.
> Not sure of previous suggestions, so apologise if I'm susggesting somethin
g
> you may have already tried?
> Regards
> Immy
>
> "alvinyyt" <alvinyyt@.gmail.com> wrote in message
> news:1154481632.633873.163050@.m79g2000cwm.googlegroups.com...

Sunday, March 11, 2012

Another Question SP

Hi,
I have this I need to be able to update the information based on custid.
DECLARE @.sql1 Varchar(8000)
DECLARE @.custid varchar (10)
DECLARE @.custname varchar (80)
DECLARE @.doc varchar(8000)
SET @.custid='000900'
SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid='
+ @.custid Is this wrong?
SET @.custname = @.sql1
SET @.doc ='
<STORE>
<ADDRESS>
<CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
<CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variable
here based on the custid,
<ADDR_1>@.address1</ADDR_1>
<ADDR_2>@.address2</ADDR_2>
<ADDR_CITY>@.city</ADDR_CITY>
<ADDR_STATE>@.region</ADDR_STATE>
<ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
</ADDRESS>
When I run this I am getting NULL what is wrong with this?
Thanks
DibTry,
Select @.custname = c.custname From dbo.tblArCust c where c.custid = @.custid
...
AMB
"Dib" wrote:

> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid
='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variab
le
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>
>|||Thanks, it worked. How can I handle NULL value here
Dib
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CF2BF93E-091C-494D-A625-19D36E041E9C@.microsoft.com...
> Try,
>
> Select @.custname = c.custname From dbo.tblArCust c where c.custid =
@.custid
> ...
>
> AMB
> "Dib" wrote:
>
c.custid='
variable|||Doesn't look like you have initialised @.sql1. Set it to a blank space and
try again.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where
> c.custid='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a
> variable
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>|||This is not working
This is what I did
SET @.custid = ''
SET @.custname = ''
SET @.address1 = ''
SET @.address2 = ''
SET @.city = ''
SET @.region = ''
SET @.zipcode = ''
SET @.doc =''
SET @.custid='000900'
SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.doc ='
It worked fine at first when I changed the Custid to 00500 it returned NULL
now nothing works.
Thanks
Dib
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uLcdeFWjFHA.708@.TK2MSFTNGP09.phx.gbl...
> Doesn't look like you have initialised @.sql1. Set it to a blank space and
> try again.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
> news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 20 Jul 2005 15:51:26 -0400, Dib wrote:

>This is not working
>This is what I did
>SET @.custid = ''
>SET @.custname = ''
>SET @.address1 = ''
>SET @.address2 = ''
>SET @.city = ''
>SET @.region = ''
>SET @.zipcode = ''
>SET @.doc =''
>
>SET @.custid='000900'
>SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End Fro
m
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.doc ='
>
>It worked fine at first when I changed the Custid to 00500 it returned NULL
>now nothing works.
>Thanks
>Dib
Hi Dib,
There are several problems with your code.
1. comparing to NULL with the = operator will allways fail - no value is
equal to NULL, not even NULL itself. (This is by design - NULL is
regarded as unknown values, so comparing NULL to NULL is like comparing
two unknown values; you can never say if two unknown values are equal or
unequal). Use IS NULL instead of = NULL for correct results.
2. The CASE statement lacks an ELSE part. If the WHEN part is not true
(which it never is, due to the reason above), and there is no ELSE part,
the result will default to NULL.
3. It is highly inefficient to execute the same query six consecutive
times. Instead, use the Transact-SQL extension to the SELECT statement
to assign values to all variables at once.
Here's some code to replace the snippet above. I also replaced the CASE
expressions with COALESCE expressions, that are (in this case)
equivalent. Oh, and since I use SELECT instead of SET, there's no need
to initialize the variables first - they'll always be assigned a value
in this statement!
SET @.custid = '000900'
SELECT @.custname = COALESCE(custname, ''),
@.address1 = COALESEC(Addr1, ''),
@.address2 = COALESCE(Addr2, ''),
@.city = COALESCE(City, ''),
@.region = COALESCE(Region, ''),
@.zipcode = COALESCE(PostalCode, '')
FROM dbo.tblArCust
WHERE custid = @.custid
(untested, since you didn't provide CREATE TABLE and INSERT statements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Thursday, February 16, 2012

Analysis Services: Deploy a cube

I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.

Would appreciate any suggestion for my enlightenment. ThanksJodJim,

I am assuming you properly specified target server name. Next steps:
1. Make sure that SQL Server Analysis Services service is running. It is not the same service as SQL service. You can do this in Adminitrator Tools ->Services. This is most likely cause why you cannot deploy
2. Make sure you are member of administrators local group on SSAS server. This will give you admin right required to deploy database.

If that does not work:
Start SQL Server Management Studio and connect to analysis services. Any problems? Any messages?

Vidas Matelis
http://www.ssas-info.com
http://www.ssas-info.com/VidasMatelisBlog

Sunday, February 12, 2012

Analysis Services Cube using SQL2000 database

Hi All
I'm trying to build my first Analysis Services (2005) cube based on fact and
dimension tables on a SQL200 database on a different server.
I'm having problems setting up the data sources - I'm assuming I need two:
one for the source SQL2000 tables and one for the target SQL2005 cube. I've
done this but when it comes to deploying the cube it looks like it's
expecting the source tables on my SQL2005 data source
I think I'm missing something fundamental. Any help or suggestions most
welcome.
Thanks
PeterHello Peter,
If I understand you correctly, you have two data sources in the data
source folder in your Analysis Services project. Can you confirm that
the data for your cube is only based on the SQL2000 data source?
If this is the case, then you should delete SQL2005 data source, as you
do not need a connection to your SQL server 2005. Make sure the data
source view is only base on the SQL2000 connection.
To deploy your cube to Analysis services you only need to set up the
deployment location in the project. This most like will be already
setup for you if you are working with everything on one PC.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Thursday, February 9, 2012

Analysis Services 2005 Design Challenge

Hi to all, I’m designing a SSAS 2005 cube based on an Oracle data source

We have a classical star schema with several millions of records into the fact table.

Now everything goes fine since dimensions are quite small (the greatest one has 100000 records), but users are asking me the possibility of view also more detailed data such order_number, client fiscal code, etc…and this data it has been stored into the fact table as a fact degenerate dimension.

Now I don’t want to load into the cube this degenerate dimension because it contains as I said too many records.

I was thinking to drill-through functionality (achieved setting the storage mode of the degenerate dimension to ROLAP) but after several tests it seems very very slow (it launches queries that incredibly big grouping all the dimensions and all the fields of the fact table or other times launches several heavy queries catching all the distinct values of order_number, client fiscal code, etc…) and some times the client itself (OWC) hangs and I have to kill it.

Note that these tests are done on the development environment where the fact table contains more or less only 70000 records!

I tried also to query directly this degenerate dimension (without drill-through) but the results are the same.

Any suggestions?

Thanks.

Alberto

Try using MOLAP dimensions. They should scale pretty well in AS2005.

You should be able to handle several milion members with MOLAP dimensions.

Hope that helps.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 20, 2012

Another version of SQL Permission Question

Another version of SQL Permission Question

Another version of SQL Permission Question

another trigger question

another trigger question

another trigger question

Monday, March 19, 2012

Another thorny summing problem

Another thorny summing problem

Another thorny summing problem

Another SQL Server 2k Setup failed to configure the server error. Please help

Another SQL Server 2k Setup failed to configure the server error. Please help

Sunday, March 11, 2012

Another Question SP

Thursday, February 16, 2012

Analysis Services: Deploy a cube

Sunday, February 12, 2012

Analysis Services Cube using SQL2000 database

Thursday, February 9, 2012

Analysis Services 2005 Design Challenge

SQL server connection

Blog Archive

About Me