I have been using MS Viso (the one that integrates with Visual Studio .NET)
to generate ddl script for my database. It uses the following update trigger
code to enforce the referential integrity, but will only execute the code fo
r
one row updates, it will throw an error for multi-row updates. I am wonderin
g
why it only allows the one row, as it appears (to me) that the code will wor
k
fine for multi-row updates as well.
UPDATE "ReferedTable"
SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
FROM inserted, deleted, "ReferedTable"
WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
I understand that it is updating the refered key column in the related
tables whenever the primary key column of the parent table is changed, but I
do not understand why it only allows one row at a time to be updated.
Can someone please explain this for me? And can it actually be used for
multi-row updates? If not then what would be a good way of doing it? Thanks--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
DELETE CASCADE methods of a Foreign Key? E.g.:
create table t (
a char(1) primary key,
c char(2) not null
)
create table s (
a char(1) not null references t (a)
on update cascade on delete cascade,
d datetime not null
)
Whenever t.a is changed s.a will reflect the changes and all rows in s.a
will be updated.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQj4zo4echKqOuFEgEQKRgACg3GMmYjF9+Igx
UGwWwRMF3YJL5msAn0Le
4XoP+70vYIkNaJh/nfxGf6Nj
=Wx8M
--END PGP SIGNATURE--
Gary K wrote:
> I have been using MS Viso (the one that integrates with Visual Studio .NET
)
> to generate ddl script for my database. It uses the following update trigg
er
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am wonder
ing
> why it only allows the one row, as it appears (to me) that the code will w
ork
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it? Thanks[/color
]|||"MGFoster" wrote:
> Why not dispense w/ the trigger and use the ON UPDATE CASCADE and ON
> DELETE CASCADE methods of a Foreign Key? E.g.:
>
Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
NULL/SET DEFAULT options. Also our database requirements specify before/afte
r
auditing which can only be done in INSTEAD OF triggers (due to the nature of
the tables used, which can't be changed, or at least not by me), and using
INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
restrictions.
We have borrowed from the programming structure that Viso produces, in that
while we still create foreign key references they are disabled so we can
implement our own version of referential integrity + auditing.
Personally I would prefer to use another DB package, but unfortunately to
keep things cheap and easily integratable with our MS Office products we are
stuck with SQL Server.
Thanks for the reply MG, but sorry, it's not something we can use.|||Gary K wrote:
> "MGFoster" wrote:
>
> Mainly because SQL Server does not have ON UPDATE/DELETE RESTRICTED/SET
> NULL/SET DEFAULT options. Also our database requirements specify before/af
ter
> auditing which can only be done in INSTEAD OF triggers (due to the nature
of
> the tables used, which can't be changed, or at least not by me), and using
> INSTEAD OF triggers precludes the use of UPDATE/DELETE foreign key
> restrictions.
> We have borrowed from the programming structure that Viso produces, in tha
t
> while we still create foreign key references they are disabled so we can
> implement our own version of referential integrity + auditing.
> Personally I would prefer to use another DB package, but unfortunately to
> keep things cheap and easily integratable with our MS Office products we a
re
> stuck with SQL Server.
> Thanks for the reply MG, but sorry, it's not something we can use.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
trigger):
"Usually, when an UPDATE statement that references a table attempts to
set the value of a computed, *identity*, or timestamp column, an error
is generated because the values for these columns must be determined by
Microsoft? SQL Server?. These columns must be included in the UPDATE
statement to meet the NOT NULL requirement of the column. However, if
the UPDATE statement references a view with an INSTEAD OF UPDATE
trigger, the logic defined in the trigger can bypass these columns and
avoid the error."
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQj5HZIechKqOuFEgEQJWDACePu5W/oh+PLuf3ysomu6DVtaT6IQAoLBB
y1D1g1dzEYdEoG6vh1+WNsx1
=IYDc
--END PGP SIGNATURE--|||"MGFoster" wrote:
> Is the Primary Key (PK) an Identity column? From BOL (Instead of Update
> trigger):
> "Usually, when an UPDATE statement that references a table attempts to
> set the value of a computed, *identity*, or timestamp column, an error
> is generated because the values for these columns must be determined by
> Microsoft? SQL Server?. These columns must be included in the UPDATE
> statement to meet the NOT NULL requirement of the column. However, if
> the UPDATE statement references a view with an INSTEAD OF UPDATE
> trigger, the logic defined in the trigger can bypass these columns and
> avoid the error."
>
Nope, I try to avoid those like the plague now. We use uniqueidentifiers as
ROWGUIDCOL columns, not only for 'bookmark' uses of such a column, but also
to make replication a lot easier.
The main point of my question was to confirm that the given code would
handle multi-row referential updates on a parent table primary key column
(which i have now done in a practical experiment), and to find out why the M
S
Viso designers would only let one row be updated at a time with the code.
I have confirmed that the code will perform multi-row updates and it appears
to work correctly, but as the famous quotes says, "Just because we can do
something, does it mean we SHOULD?" I am basically looking for any problems
that might arise from the use of the code.|||I should have included this in the last message, but here is the code I used
to test the multi-row update code.
use tempdb
go
-- these table testers are only so I can reuse the code if it needed any
changes (it did)
if objectproperty(object_id('tblb'), 'IsTable')=1
drop table tblb
go
if objectproperty(object_id('tbla'), 'IsTable')=1
drop table tbla
go
create table tbla ( -- our parent table in the relationship
-- (i couldn't be bothered typing in GUIDs so we use a tinyint pk)
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL
)
go
create table tblb ( -- our child table in the relationship
ii tinyint NOT NULL PRIMARY KEY,
ll varchar(50) NULL,
ia tinyint NOT NULL,
-- and this is our relationship contraint
CONSTRAINT FK_b FOREIGN KEY (ia) REFERENCES tbla (ii)
)
go
-- we are going to look after the integrity, so we disable the constraint
alter table tblb nocheck constraint FK_b
go
create trigger tbla_upd on tbla for update as
begin
-- our test trigger is only for the update condition, and since we control
what is
-- going to happen we can skip all the extra code we will be using.
-- this code will only be executed (for every child table) when the primary
key is updated
update tblb
set tblb.ia = inserted.ii
from inserted, deleted, tblb
where tblb.ia = deleted.ii
end
go
-- insert parent table values
insert tbla values (1, 'first')
insert tbla values (2, 'second')
insert tbla values (3, 'third')
-- insert child table values
insert tblb values (1, 'first/first', 1)
insert tblb values (2, 'first/second', 2)
insert tblb values (3, 'first/third', 3)
insert tblb values (4, 'second/third', 3)
insert tblb values (5, 'second/second', 2)
insert tblb values (6, 'third/second', 2)
insert tblb values (7, 'second/first', 1)
insert tblb values (8, 'third/third', 3)
go
-- what it looks like before we change things
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'second' series to a new unique value
update tbla set ii=4 where ii=2
go
-- and we see what we get (works ok!)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go
-- now we change the pk of the 'third' series to a value that is already in
use
-- (error testing, and yes it does throw an error as it is supposed to)
update tbla set ii=4 where ii=3
go
-- and then we see what we get after the change (which doesn't happen)
select * from tbla a inner join tblb b on b.ia=a.ii order by a.ll, b.ll
go|||Gary,
The code looks wrong to me, but even so, it can't be
fixed unless there is another candidate key on the table.
First off, there is no join condition between inserted and
either of the other two tables. If 10 rows are updated, which
of the 10 inserted.PrimaryKey values will be assigned to
ReferedTable.ReferedKey? The way this proprietary
SQL Server syntax works, an arbitrary one of the 10
possibilities will be used.
But the problem is worse than that. Because the primary
key is being updated, there is no way to identify the correct
correspondence between an old row and a new row.
Suppose the update was this:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 123
when 2 then 456
end
where PrimaryKey in (1,2)
Within the trigger, there is no way to distinguish
this update from a different one:
update T set
PrimaryKey =
case PrimaryKey
when 1 then 456
when 2 then 123
end
where PrimaryKey in (1,2)
At least not without it being possible to identify which
row is which on the basis of some column or columns
other than the PrimaryKey column.
If only one row is updated, the is no ambiguity.
Steve Kass
Drew University
Gary K wrote:
>I have been using MS Viso (the one that integrates with Visual Studio .NET)
>to generate ddl script for my database. It uses the following update trigge
r
>code to enforce the referential integrity, but will only execute the code f
or
>one row updates, it will throw an error for multi-row updates. I am wonderi
ng
>why it only allows the one row, as it appears (to me) that the code will wo
rk
>fine for multi-row updates as well.
>UPDATE "ReferedTable"
>SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
>FROM inserted, deleted, "ReferedTable"
>WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
>I understand that it is updating the refered key column in the related
>tables whenever the primary key column of the parent table is changed, but
I
>do not understand why it only allows one row at a time to be updated.
>Can someone please explain this for me? And can it actually be used for
>multi-row updates? If not then what would be a good way of doing it? Thanks
>|||Gary,
Check Itzik Ben-Gan's presentation & scripts on RI in SQL 7.0 and 2000 at
http://www.sql.co.il/ug/13/Thirteenth.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gary K" <GaryK@.discussions.microsoft.com> wrote in message
news:D3D0B4F4-EC82-41E6-A527-E6202A5394F7@.microsoft.com...
> I have been using MS Viso (the one that integrates with Visual Studio
.NET)
> to generate ddl script for my database. It uses the following update
trigger
> code to enforce the referential integrity, but will only execute the code
for
> one row updates, it will throw an error for multi-row updates. I am
wondering
> why it only allows the one row, as it appears (to me) that the code will
work
> fine for multi-row updates as well.
> UPDATE "ReferedTable"
> SET "ReferedTable"."ReferedKey" = inserted."PrimaryKey"
> FROM inserted, deleted, "ReferedTable"
> WHERE "ReferedTable"."ReferedKey" = deleted."PrimaryKey"
> I understand that it is updating the refered key column in the related
> tables whenever the primary key column of the parent table is changed, but
I
> do not understand why it only allows one row at a time to be updated.
> Can someone please explain this for me? And can it actually be used for
> multi-row updates? If not then what would be a good way of doing it?
Thanks
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Wednesday, March 28, 2012
Multi Row Insert
According to the CTP3 BOL the following code should work just fine, unfortunately is giving me the error message (below the code):
create table FamilyNames (
Id int identity,
[Name] varchar(30)
);
go
insert into FamilyNames values (
('Andersson'),
('Ben-Gan'),
('Carlsson'),
('Davidsen')
);
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Does anybody know why this code fails?
Hi,
Try
insert into FamilyNames values
('Andersson'),
('Ben-Gan'),
('Carlsson'),
('Davidsen');
Kind regards,
Wesley
|||Thanks.multi parameters
Hi,
I want to keyin many values in a parameter textbox, and I try the syntax
like following but it doesn't work, and response the syntax error, why?
SELECT * FROM Product
WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value +"))
Is any mistake in my syntax or how should I modify it?
Thanks!
AngiAngi,
I presume your query string is actually
="SELECT * FROM Product" +
"WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value + "))"
and that you enter values into the parameter field seperated by commas, i.e. 10,11,12
Then it should work. If you still have a problem, try using & instead of + for concatenation.
Regards
Chris McGuigan
"angi" wrote:
> Hi,
> I want to keyin many values in a parameter textbox, and I try the syntax
> like following but it doesn't work, and response the syntax error, why?
> SELECT * FROM Product
> WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value +"))
> Is any mistake in my syntax or how should I modify it?
>
> Thanks!
> Angi
>
>|||Chris,
Thanks to you first.
Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
Dataset SQL dialog or StoreProcedure?
Third, my situation like following..
If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
Excute function be disable, and in Preview, there is an error msg "syntax
error near '=' "
Really have no idea >"<
Thanks again, Chris.
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> Angi,
> I presume your query string is actually
> ="SELECT * FROM Product" +
> "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value + "))"
> and that you enter values into the parameter field seperated by commas,
i.e. 10,11,12
> Then it should work. If you still have a problem, try using & instead of +
for concatenation.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Hi,
> >
> > I want to keyin many values in a parameter textbox, and I try the syntax
> > like following but it doesn't work, and response the syntax error, why?
> >
> > SELECT * FROM Product
> > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > Parameters!Month.Value +"))
> >
> > Is any mistake in my syntax or how should I modify it?
> >
> >
> > Thanks!
> > Angi
> >
> >
> >|||Chris,
Thank you very much!
I know what's the problem with my syntax, I break(use Enter) the syntax, so
when I preview the Report appears syntax error!
With "int" data type, we can enter data value 1,2,5 directly, and I change
to "char" data type, the value must be write as '1','2','5'
Any convenience way to enter "char" value?
And I'm not VB programmer and the newbie of RS, but if there is any chance
to learn, I must to be learning more.
Thanks for your time and your kind. :)
Regards!
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> Hi Angi,
> To write dynamic queries like this, you need to be in the 'Generic Query
Designer' in the 'Data' tab of the report. By default you are in the
'Graphical Query Designer'. To switch, press the button to the left of the
'Run' button (!).
> A dynamic query is actually a SQL query built up in a Visual Basic string.
> I'm guessing you're not a VB programmer, if that's the case you don't have
to learn too much. String handling in VB is similar to string handling in
SQL. The big difference is that SQL strings are delimited by a single quote
('), in VB it's double quotes (").
> I hope that helps.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Chris,
> >
> > Thanks to you first.
> > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > Dataset SQL dialog or StoreProcedure?
> > Third, my situation like following..
> > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > Excute function be disable, and in Preview, there is an error msg
"syntax
> > error near '=' "
> >
> > Really have no idea >"<
> >
> > Thanks again, Chris.
> >
> > Angi
> >
> >
> > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
o¡Ps?D
> > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > Angi,
> > > I presume your query string is actually
> > > ="SELECT * FROM Product" +
> > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > Parameters!Month.Value + "))"
> > >
> > > and that you enter values into the parameter field seperated by
commas,
> > i.e. 10,11,12
> > >
> > > Then it should work. If you still have a problem, try using & instead
of +
> > for concatenation.
> > >
> > > Regards
> > > Chris McGuigan
> > > "angi" wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to keyin many values in a parameter textbox, and I try the
syntax
> > > > like following but it doesn't work, and response the syntax error,
why?
> > > >
> > > > SELECT * FROM Product
> > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value +"))
> > > >
> > > > Is any mistake in my syntax or how should I modify it?
> > > >
> > > >
> > > > Thanks!
> > > > Angi
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Angi,
The only way to make the entry of strings easier for the end user is to try and put the quotes in for them, when you build the SQL string.
Say the WHERE part of your SQL looks like this;
"WHERE Item IN (" + Parameters!Item.Value + ")"
Add a single quote before and after the parameter, and replace any commas with a comma surrounded by single quotes like this;
"WHERE Item IN ('" + Replace(Parameters!Item.Value, ",", "','") + "')"
Note where the single quotes are!
This would convert an entry of 1,2,3 into '1','2','3'
Just watch out for spaces around the commas, that may stop the where clause working properly. You can get round this but that will require more code.
Regards
Chris McGuigan
"angi" wrote:
> Chris,
> Thank you very much!
> I know what's the problem with my syntax, I break(use Enter) the syntax, so
> when I preview the Report appears syntax error!
> With "int" data type, we can enter data value 1,2,5 directly, and I change
> to "char" data type, the value must be write as '1','2','5'
> Any convenience way to enter "char" value?
> And I'm not VB programmer and the newbie of RS, but if there is any chance
> to learn, I must to be learning more.
> Thanks for your time and your kind. :)
> Regards!
> Angi
> "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> > Hi Angi,
> > To write dynamic queries like this, you need to be in the 'Generic Query
> Designer' in the 'Data' tab of the report. By default you are in the
> 'Graphical Query Designer'. To switch, press the button to the left of the
> 'Run' button (!).
> >
> > A dynamic query is actually a SQL query built up in a Visual Basic string.
> > I'm guessing you're not a VB programmer, if that's the case you don't have
> to learn too much. String handling in VB is similar to string handling in
> SQL. The big difference is that SQL strings are delimited by a single quote
> ('), in VB it's double quotes (").
> >
> > I hope that helps.
> > Regards
> > Chris McGuigan
> >
> > "angi" wrote:
> >
> > > Chris,
> > >
> > > Thanks to you first.
> > > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > > Dataset SQL dialog or StoreProcedure?
> > > Third, my situation like following..
> > > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > > Excute function be disable, and in Preview, there is an error msg
> "syntax
> > > error near '=' "
> > >
> > > Really have no idea >"<
> > >
> > > Thanks again, Chris.
> > >
> > > Angi
> > >
> > >
> > > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
> o¡Ps?D
> > > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > > Angi,
> > > > I presume your query string is actually
> > > > ="SELECT * FROM Product" +
> > > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value + "))"
> > > >
> > > > and that you enter values into the parameter field seperated by
> commas,
> > > i.e. 10,11,12
> > > >
> > > > Then it should work. If you still have a problem, try using & instead
> of +
> > > for concatenation.
> > > >
> > > > Regards
> > > > Chris McGuigan
> > > > "angi" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I want to keyin many values in a parameter textbox, and I try the
> syntax
> > > > > like following but it doesn't work, and response the syntax error,
> why?
> > > > >
> > > > > SELECT * FROM Product
> > > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > > Parameters!Month.Value +"))
> > > > >
> > > > > Is any mistake in my syntax or how should I modify it?
> > > > >
> > > > >
> > > > > Thanks!
> > > > > Angi
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql
I want to keyin many values in a parameter textbox, and I try the syntax
like following but it doesn't work, and response the syntax error, why?
SELECT * FROM Product
WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value +"))
Is any mistake in my syntax or how should I modify it?
Thanks!
AngiAngi,
I presume your query string is actually
="SELECT * FROM Product" +
"WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value + "))"
and that you enter values into the parameter field seperated by commas, i.e. 10,11,12
Then it should work. If you still have a problem, try using & instead of + for concatenation.
Regards
Chris McGuigan
"angi" wrote:
> Hi,
> I want to keyin many values in a parameter textbox, and I try the syntax
> like following but it doesn't work, and response the syntax error, why?
> SELECT * FROM Product
> WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value +"))
> Is any mistake in my syntax or how should I modify it?
>
> Thanks!
> Angi
>
>|||Chris,
Thanks to you first.
Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
Dataset SQL dialog or StoreProcedure?
Third, my situation like following..
If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
Excute function be disable, and in Preview, there is an error msg "syntax
error near '=' "
Really have no idea >"<
Thanks again, Chris.
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> Angi,
> I presume your query string is actually
> ="SELECT * FROM Product" +
> "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value + "))"
> and that you enter values into the parameter field seperated by commas,
i.e. 10,11,12
> Then it should work. If you still have a problem, try using & instead of +
for concatenation.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Hi,
> >
> > I want to keyin many values in a parameter textbox, and I try the syntax
> > like following but it doesn't work, and response the syntax error, why?
> >
> > SELECT * FROM Product
> > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > Parameters!Month.Value +"))
> >
> > Is any mistake in my syntax or how should I modify it?
> >
> >
> > Thanks!
> > Angi
> >
> >
> >|||Chris,
Thank you very much!
I know what's the problem with my syntax, I break(use Enter) the syntax, so
when I preview the Report appears syntax error!
With "int" data type, we can enter data value 1,2,5 directly, and I change
to "char" data type, the value must be write as '1','2','5'
Any convenience way to enter "char" value?
And I'm not VB programmer and the newbie of RS, but if there is any chance
to learn, I must to be learning more.
Thanks for your time and your kind. :)
Regards!
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> Hi Angi,
> To write dynamic queries like this, you need to be in the 'Generic Query
Designer' in the 'Data' tab of the report. By default you are in the
'Graphical Query Designer'. To switch, press the button to the left of the
'Run' button (!).
> A dynamic query is actually a SQL query built up in a Visual Basic string.
> I'm guessing you're not a VB programmer, if that's the case you don't have
to learn too much. String handling in VB is similar to string handling in
SQL. The big difference is that SQL strings are delimited by a single quote
('), in VB it's double quotes (").
> I hope that helps.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Chris,
> >
> > Thanks to you first.
> > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > Dataset SQL dialog or StoreProcedure?
> > Third, my situation like following..
> > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > Excute function be disable, and in Preview, there is an error msg
"syntax
> > error near '=' "
> >
> > Really have no idea >"<
> >
> > Thanks again, Chris.
> >
> > Angi
> >
> >
> > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
o¡Ps?D
> > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > Angi,
> > > I presume your query string is actually
> > > ="SELECT * FROM Product" +
> > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > Parameters!Month.Value + "))"
> > >
> > > and that you enter values into the parameter field seperated by
commas,
> > i.e. 10,11,12
> > >
> > > Then it should work. If you still have a problem, try using & instead
of +
> > for concatenation.
> > >
> > > Regards
> > > Chris McGuigan
> > > "angi" wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to keyin many values in a parameter textbox, and I try the
syntax
> > > > like following but it doesn't work, and response the syntax error,
why?
> > > >
> > > > SELECT * FROM Product
> > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value +"))
> > > >
> > > > Is any mistake in my syntax or how should I modify it?
> > > >
> > > >
> > > > Thanks!
> > > > Angi
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Angi,
The only way to make the entry of strings easier for the end user is to try and put the quotes in for them, when you build the SQL string.
Say the WHERE part of your SQL looks like this;
"WHERE Item IN (" + Parameters!Item.Value + ")"
Add a single quote before and after the parameter, and replace any commas with a comma surrounded by single quotes like this;
"WHERE Item IN ('" + Replace(Parameters!Item.Value, ",", "','") + "')"
Note where the single quotes are!
This would convert an entry of 1,2,3 into '1','2','3'
Just watch out for spaces around the commas, that may stop the where clause working properly. You can get round this but that will require more code.
Regards
Chris McGuigan
"angi" wrote:
> Chris,
> Thank you very much!
> I know what's the problem with my syntax, I break(use Enter) the syntax, so
> when I preview the Report appears syntax error!
> With "int" data type, we can enter data value 1,2,5 directly, and I change
> to "char" data type, the value must be write as '1','2','5'
> Any convenience way to enter "char" value?
> And I'm not VB programmer and the newbie of RS, but if there is any chance
> to learn, I must to be learning more.
> Thanks for your time and your kind. :)
> Regards!
> Angi
> "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> > Hi Angi,
> > To write dynamic queries like this, you need to be in the 'Generic Query
> Designer' in the 'Data' tab of the report. By default you are in the
> 'Graphical Query Designer'. To switch, press the button to the left of the
> 'Run' button (!).
> >
> > A dynamic query is actually a SQL query built up in a Visual Basic string.
> > I'm guessing you're not a VB programmer, if that's the case you don't have
> to learn too much. String handling in VB is similar to string handling in
> SQL. The big difference is that SQL strings are delimited by a single quote
> ('), in VB it's double quotes (").
> >
> > I hope that helps.
> > Regards
> > Chris McGuigan
> >
> > "angi" wrote:
> >
> > > Chris,
> > >
> > > Thanks to you first.
> > > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > > Dataset SQL dialog or StoreProcedure?
> > > Third, my situation like following..
> > > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > > Excute function be disable, and in Preview, there is an error msg
> "syntax
> > > error near '=' "
> > >
> > > Really have no idea >"<
> > >
> > > Thanks again, Chris.
> > >
> > > Angi
> > >
> > >
> > > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
> o¡Ps?D
> > > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > > Angi,
> > > > I presume your query string is actually
> > > > ="SELECT * FROM Product" +
> > > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value + "))"
> > > >
> > > > and that you enter values into the parameter field seperated by
> commas,
> > > i.e. 10,11,12
> > > >
> > > > Then it should work. If you still have a problem, try using & instead
> of +
> > > for concatenation.
> > > >
> > > > Regards
> > > > Chris McGuigan
> > > > "angi" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I want to keyin many values in a parameter textbox, and I try the
> syntax
> > > > > like following but it doesn't work, and response the syntax error,
> why?
> > > > >
> > > > > SELECT * FROM Product
> > > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > > Parameters!Month.Value +"))
> > > > >
> > > > > Is any mistake in my syntax or how should I modify it?
> > > > >
> > > > >
> > > > > Thanks!
> > > > > Angi
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql
Multi Parameter Question Part II
Hi,
I use the following syntax select multi parameter like 1,2,5 and if the
parameter is empty then get all data.
="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
OrderID IN (" + Parameters!ID.Value + ")")
And my problem is..
IF I want to select 3 parameters, ID, Name, Date and use that syntax,
how should I modify the Syntax? or use another way, like procedure? or the
RS can't support my situation?
Thanks!
AngiI would suggest using stored procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I use the following syntax select multi parameter like 1,2,5 and if the
> parameter is empty then get all data.
> ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> OrderID IN (" + Parameters!ID.Value + ")")
> And my problem is..
> IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> how should I modify the Syntax? or use another way, like procedure? or the
> RS can't support my situation?
> Thanks!
> Angi
>|||Stored Procedure can use @. to execute parameter, like
CREATE PROCEDURE sp_ActualVsQuota @.CalendarYear char(4)
But can Stored Procedure execute this parameter function -> (" +
Parameters!CalendarYear.Value + ")?
Cause I want to use multi parameter, and how to define it?
Thanks
Angi
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||About this issue, is any sample could offer?
Thanks!
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||Choose CommandType=StoredProcedure in the data pane of report designer,
choose the stored procedure from the dropdown, and run it. You will be
prompted for parameters.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:%23pLpmb6cEHA.1656@.TK2MSFTNGP09.phx.gbl...
> About this issue, is any sample could offer?
> Thanks!
> "Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> > I would suggest using stored procedure.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "angi" <angi@.microsoft.com> wrote in message
> > news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I use the following syntax select multi parameter like 1,2,5 and if
the
> > > parameter is empty then get all data.
> > > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > > OrderID IN (" + Parameters!ID.Value + ")")
> > >
> > > And my problem is..
> > > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > > how should I modify the Syntax? or use another way, like procedure? or
> the
> > > RS can't support my situation?
> > >
> > > Thanks!
> > > Angi
> > >
> > >
> >
> >
>
I use the following syntax select multi parameter like 1,2,5 and if the
parameter is empty then get all data.
="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
OrderID IN (" + Parameters!ID.Value + ")")
And my problem is..
IF I want to select 3 parameters, ID, Name, Date and use that syntax,
how should I modify the Syntax? or use another way, like procedure? or the
RS can't support my situation?
Thanks!
AngiI would suggest using stored procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I use the following syntax select multi parameter like 1,2,5 and if the
> parameter is empty then get all data.
> ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> OrderID IN (" + Parameters!ID.Value + ")")
> And my problem is..
> IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> how should I modify the Syntax? or use another way, like procedure? or the
> RS can't support my situation?
> Thanks!
> Angi
>|||Stored Procedure can use @. to execute parameter, like
CREATE PROCEDURE sp_ActualVsQuota @.CalendarYear char(4)
But can Stored Procedure execute this parameter function -> (" +
Parameters!CalendarYear.Value + ")?
Cause I want to use multi parameter, and how to define it?
Thanks
Angi
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||About this issue, is any sample could offer?
Thanks!
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||Choose CommandType=StoredProcedure in the data pane of report designer,
choose the stored procedure from the dropdown, and run it. You will be
prompted for parameters.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:%23pLpmb6cEHA.1656@.TK2MSFTNGP09.phx.gbl...
> About this issue, is any sample could offer?
> Thanks!
> "Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> > I would suggest using stored procedure.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "angi" <angi@.microsoft.com> wrote in message
> > news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I use the following syntax select multi parameter like 1,2,5 and if
the
> > > parameter is empty then get all data.
> > > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > > OrderID IN (" + Parameters!ID.Value + ")")
> > >
> > > And my problem is..
> > > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > > how should I modify the Syntax? or use another way, like procedure? or
> the
> > > RS can't support my situation?
> > >
> > > Thanks!
> > > Angi
> > >
> > >
> >
> >
>
Monday, March 26, 2012
multi excel files to sql server
I am following the instruction in
http://www.sqldts.com/default.aspx?6,103,246,0,1 to loop thru a directory an
d
get multi excel files to sql.
excel files: same layout, in the same folder and going to the same sql table
I am using the transform data task for excel -> sql step. the problem is
that the package can only process the first file (the initial that I use to
setup the excel connection). The package dies on the 2nd file. I can see the
2nd file being pickup by the loop because the correct file name is in the
excel connection property and the global variable is updated to reference th
e
2nd file as well.
When I goto the transform data task properties and preview source, I get an
error. Error Source: Microsoft JET Database Engine.
Error Description: 'xxx$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long
the file names are not too long. I changed it to be a.xls, b.xls, c.xls etc.
so what's wrong?a bit more info:
I noticed that there were always two selections (2 xxx$) in the transform
data task properties -> table/view dropdown. the one selected by default is
always wrong. I have to select the other one and re-do the transformation
then the whole thing would work again... but only once on one file..... I
would error back here again on the 2nd file...
How can I make the package see the correct file in the table/view dropdown'
"christy" wrote:
> I am following the instruction in
> http://www.sqldts.com/default.aspx?6,103,246,0,1 to loop thru a directory
and
> get multi excel files to sql.
> excel files: same layout, in the same folder and going to the same sql tab
le
> I am using the transform data task for excel -> sql step. the problem is
> that the package can only process the first file (the initial that I use t
o
> setup the excel connection). The package dies on the 2nd file. I can see t
he
> 2nd file being pickup by the loop because the correct file name is in the
> excel connection property and the global variable is updated to reference
the
> 2nd file as well.
> When I goto the transform data task properties and preview source, I get a
n
> error. Error Source: Microsoft JET Database Engine.
> Error Description: 'xxx$' is not a valid name. Make sure that it does not
> include invalid characters or punctuation and that it is not too long
> the file names are not too long. I changed it to be a.xls, b.xls, c.xls et
c.
> so what's wrong?
http://www.sqldts.com/default.aspx?6,103,246,0,1 to loop thru a directory an
d
get multi excel files to sql.
excel files: same layout, in the same folder and going to the same sql table
I am using the transform data task for excel -> sql step. the problem is
that the package can only process the first file (the initial that I use to
setup the excel connection). The package dies on the 2nd file. I can see the
2nd file being pickup by the loop because the correct file name is in the
excel connection property and the global variable is updated to reference th
e
2nd file as well.
When I goto the transform data task properties and preview source, I get an
error. Error Source: Microsoft JET Database Engine.
Error Description: 'xxx$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long
the file names are not too long. I changed it to be a.xls, b.xls, c.xls etc.
so what's wrong?a bit more info:
I noticed that there were always two selections (2 xxx$) in the transform
data task properties -> table/view dropdown. the one selected by default is
always wrong. I have to select the other one and re-do the transformation
then the whole thing would work again... but only once on one file..... I
would error back here again on the 2nd file...
How can I make the package see the correct file in the table/view dropdown'
"christy" wrote:
> I am following the instruction in
> http://www.sqldts.com/default.aspx?6,103,246,0,1 to loop thru a directory
and
> get multi excel files to sql.
> excel files: same layout, in the same folder and going to the same sql tab
le
> I am using the transform data task for excel -> sql step. the problem is
> that the package can only process the first file (the initial that I use t
o
> setup the excel connection). The package dies on the 2nd file. I can see t
he
> 2nd file being pickup by the loop because the correct file name is in the
> excel connection property and the global variable is updated to reference
the
> 2nd file as well.
> When I goto the transform data task properties and preview source, I get a
n
> error. Error Source: Microsoft JET Database Engine.
> Error Description: 'xxx$' is not a valid name. Make sure that it does not
> include invalid characters or punctuation and that it is not too long
> the file names are not too long. I changed it to be a.xls, b.xls, c.xls et
c.
> so what's wrong?
Friday, March 23, 2012
Mulitple stored proc parameters
Hi,
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql
MTD
I have created the following calculated member on my cube
Ancestor([Time].CurrentMember, [Time].[Month]), i want to link this to the
measures so that i can give me previous month versus the current months
sales figures, at the moment when i process the cube this member returns
nothing on the cube, how do i do this?
You'll have to explain a bit more fully because my understanding from that is
that you just want to compare 2 months side by side which is just a matter of
referencing those 2 months in your MDX statement e.g. {Time.January,
Time.February ON ROWS}. This is very easy to do so I'm guessing your problem
is a little more complex than that.
The subject of this thread leads me to think you are after MonthToDate or
similar.
Can you explain further?
Regards
Jamie Thomson
"MANDLA MKHWANAZI" wrote:
> I have created the following calculated member on my cube
> Ancestor([Time].CurrentMember, [Time].[Month]), i want to link this to the
> measures so that i can give me previous month versus the current months
> sales figures, at the moment when i process the cube this member returns
> nothing on the cube, how do i do this?
>
>
Ancestor([Time].CurrentMember, [Time].[Month]), i want to link this to the
measures so that i can give me previous month versus the current months
sales figures, at the moment when i process the cube this member returns
nothing on the cube, how do i do this?
You'll have to explain a bit more fully because my understanding from that is
that you just want to compare 2 months side by side which is just a matter of
referencing those 2 months in your MDX statement e.g. {Time.January,
Time.February ON ROWS}. This is very easy to do so I'm guessing your problem
is a little more complex than that.
The subject of this thread leads me to think you are after MonthToDate or
similar.
Can you explain further?
Regards
Jamie Thomson
"MANDLA MKHWANAZI" wrote:
> I have created the following calculated member on my cube
> Ancestor([Time].CurrentMember, [Time].[Month]), i want to link this to the
> measures so that i can give me previous month versus the current months
> sales figures, at the moment when i process the cube this member returns
> nothing on the cube, how do i do this?
>
>
MTD
I have created the following calculated member on my cube
Ancestor([Time].CurrentMember, [Time].[Month]), i want to link t
his to the
measures so that i can give me previous month versus the current months
sales figures, at the moment when i process the cube this member returns
nothing on the cube, how do i do this?You'll have to explain a bit more fully because my understanding from that i
s
that you just want to compare 2 months side by side which is just a matter o
f
referencing those 2 months in your MDX statement e.g. {Time.January,
Time.February ON ROWS}. This is very easy to do so I'm guessing your problem
is a little more complex than that.
The subject of this thread leads me to think you are after MonthToDate or
similar.
Can you explain further?
Regards
Jamie Thomson
"MANDLA MKHWANAZI" wrote:
> I have created the following calculated member on my cube
> Ancestor([Time].CurrentMember, [Time].[Month]), i want to link
this to the
> measures so that i can give me previous month versus the current months
> sales figures, at the moment when i process the cube this member returns
> nothing on the cube, how do i do this?
>
>
Ancestor([Time].CurrentMember, [Time].[Month]), i want to link t
his to the
measures so that i can give me previous month versus the current months
sales figures, at the moment when i process the cube this member returns
nothing on the cube, how do i do this?You'll have to explain a bit more fully because my understanding from that i
s
that you just want to compare 2 months side by side which is just a matter o
f
referencing those 2 months in your MDX statement e.g. {Time.January,
Time.February ON ROWS}. This is very easy to do so I'm guessing your problem
is a little more complex than that.
The subject of this thread leads me to think you are after MonthToDate or
similar.
Can you explain further?
Regards
Jamie Thomson
"MANDLA MKHWANAZI" wrote:
> I have created the following calculated member on my cube
> Ancestor([Time].CurrentMember, [Time].[Month]), i want to link
this to the
> measures so that i can give me previous month versus the current months
> sales figures, at the moment when i process the cube this member returns
> nothing on the cube, how do i do this?
>
>
Wednesday, March 21, 2012
MSSQLSvc
Hi,
I have recently been getting the following error on a
regular basis:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 23/01/2004
Time: 2:08:27:AM
User: N/A
Computer: Server
Description:
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regards
AdamMultiply Accounts with the Domain have been configured as a SQL Service acco
unt for
the Servername server. This causes both service accounts to have the same SP
N
registered.
If the accounts are known then use ADSI edit and remove then SPN on the acco
unt
that is no longer used to start the service on servername.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
-- Adam wrote: --
Hi,
I have recently been getting the following error on a
regular basis:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 23/01/2004
Time: 2:08:27:AM
User: N/A
Computer: Server
Description:
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regards
Adam
I have recently been getting the following error on a
regular basis:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 23/01/2004
Time: 2:08:27:AM
User: N/A
Computer: Server
Description:
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regards
AdamMultiply Accounts with the Domain have been configured as a SQL Service acco
unt for
the Servername server. This causes both service accounts to have the same SP
N
registered.
If the accounts are known then use ADSI edit and remove then SPN on the acco
unt
that is no longer used to start the service on servername.
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
-- Adam wrote: --
Hi,
I have recently been getting the following error on a
regular basis:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 23/01/2004
Time: 2:08:27:AM
User: N/A
Computer: Server
Description:
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regards
Adam
MSSQLSvc
Hi,
I have recently been getting the following error on a
regular basis:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 23/01/2004
Time: 2:08:27:AM
User: N/A
Computer: Server
Description:
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regards
AdamMultiply Accounts with the Domain have been configured as a SQL Service account for
the Servername server. This causes both service accounts to have the same SPN
registered.
If the accounts are known then use ADSI edit and remove then SPN on the account
that is no longer used to start the service on servername
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.ht
-- Adam wrote: --
Hi,
I have recently been getting the following error on a
regular basis
Event Type: Erro
Event Source: KD
Event Category: Non
Event ID: 1
Date: 23/01/200
Time: 2:08:27:A
User: N/
Computer: Serve
Description
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regard
Ada
I have recently been getting the following error on a
regular basis:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 23/01/2004
Time: 2:08:27:AM
User: N/A
Computer: Server
Description:
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regards
AdamMultiply Accounts with the Domain have been configured as a SQL Service account for
the Servername server. This causes both service accounts to have the same SPN
registered.
If the accounts are known then use ADSI edit and remove then SPN on the account
that is no longer used to start the service on servername
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.ht
-- Adam wrote: --
Hi,
I have recently been getting the following error on a
regular basis
Event Type: Erro
Event Source: KD
Event Category: Non
Event ID: 1
Date: 23/01/200
Time: 2:08:27:A
User: N/
Computer: Serve
Description
There are multiple accounts with name
MSSQLSvc/Server.Domain.com:1433 of type 10.
does anybody have any ideas how to resolve this or what is
causing this to occur.
Regard
Ada
MSSQLSeverADHelper
When trying to add the SQL server to the Windows 2000
Active Directory I get the following error
Error 14354: Cannot start the MSSQLSeverADHelper
Service. Verify that the service account for this SQL
Server instance has the necessary permissions to start
the MSSQLSeverADHelper service.Is the service on the machine? If not, something probably
went wrong with the install where some registry keys are
missing - reinstalling is the cleanest option in this case.
Otherwise check the service account for SQL Server to make
sure it has the appropriate permissions.
-Sue
On Fri, 15 Aug 2003 17:29:15 -0700, "Michael Luna"
<michael@.winvotes.com> wrote:
>When trying to add the SQL server to the Windows 2000
>Active Directory I get the following error
>Error 14354: Cannot start the MSSQLSeverADHelper
>Service. Verify that the service account for this SQL
>Server instance has the necessary permissions to start
>the MSSQLSeverADHelper service.
Active Directory I get the following error
Error 14354: Cannot start the MSSQLSeverADHelper
Service. Verify that the service account for this SQL
Server instance has the necessary permissions to start
the MSSQLSeverADHelper service.Is the service on the machine? If not, something probably
went wrong with the install where some registry keys are
missing - reinstalling is the cleanest option in this case.
Otherwise check the service account for SQL Server to make
sure it has the appropriate permissions.
-Sue
On Fri, 15 Aug 2003 17:29:15 -0700, "Michael Luna"
<michael@.winvotes.com> wrote:
>When trying to add the SQL server to the Windows 2000
>Active Directory I get the following error
>Error 14354: Cannot start the MSSQLSeverADHelper
>Service. Verify that the service account for this SQL
>Server instance has the necessary permissions to start
>the MSSQLSeverADHelper service.
Monday, March 19, 2012
MSSQLServerADHelper
Hi
I am getting the following error (given at the end) in the event log and the
MSSQLServerADHelper service does not start. What can I do?
Thanks
Regards
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 09/07/2004
Time: 18:41:36
User: N/A
Computer: MYSERVER
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.This also comes up.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/07/2004
Time: 19:15:36
User: N/A
Computer: MYSERVER
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572 (0xC0000064).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:ei8G85dZEHA.3092@.tk2msftngp13.phx.gbl...
> Hi
> I am getting the following error (given at the end) in the event log and
the
> MSSQLServerADHelper service does not start. What can I do?
> Thanks
> Regards
>
> Event Type: Error
> Event Source: MSSQLServerADHelper
> Event Category: None
> Event ID: 100
> Date: 09/07/2004
> Time: 18:41:36
> User: N/A
> Computer: MYSERVER
> Description:
> '0' is an invalid number of start up parameters. This service takes two
> start up parameters.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
I am getting the following error (given at the end) in the event log and the
MSSQLServerADHelper service does not start. What can I do?
Thanks
Regards
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 09/07/2004
Time: 18:41:36
User: N/A
Computer: MYSERVER
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.This also comes up.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/07/2004
Time: 19:15:36
User: N/A
Computer: MYSERVER
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572 (0xC0000064).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:ei8G85dZEHA.3092@.tk2msftngp13.phx.gbl...
> Hi
> I am getting the following error (given at the end) in the event log and
the
> MSSQLServerADHelper service does not start. What can I do?
> Thanks
> Regards
>
> Event Type: Error
> Event Source: MSSQLServerADHelper
> Event Category: None
> Event ID: 100
> Date: 09/07/2004
> Time: 18:41:36
> User: N/A
> Computer: MYSERVER
> Description:
> '0' is an invalid number of start up parameters. This service takes two
> start up parameters.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
MSSQLServerADHelper
Hi
I am getting the following error (given at the end) in the event log and the
MSSQLServerADHelper service does not start. What can I do?
Thanks
Regards
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 09/07/2004
Time: 18:41:36
User: N/A
Computer: MYSERVER
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This also comes up.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/07/2004
Time: 19:15:36
User: N/A
Computer: MYSERVER
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572 (0xC0000064).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:ei8G85dZEHA.3092@.tk2msftngp13.phx.gbl...
> Hi
> I am getting the following error (given at the end) in the event log and
the
> MSSQLServerADHelper service does not start. What can I do?
> Thanks
> Regards
>
> Event Type: Error
> Event Source: MSSQLServerADHelper
> Event Category: None
> Event ID: 100
> Date: 09/07/2004
> Time: 18:41:36
> User: N/A
> Computer: MYSERVER
> Description:
> '0' is an invalid number of start up parameters. This service takes two
> start up parameters.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
I am getting the following error (given at the end) in the event log and the
MSSQLServerADHelper service does not start. What can I do?
Thanks
Regards
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 09/07/2004
Time: 18:41:36
User: N/A
Computer: MYSERVER
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This also comes up.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/07/2004
Time: 19:15:36
User: N/A
Computer: MYSERVER
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572 (0xC0000064).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:ei8G85dZEHA.3092@.tk2msftngp13.phx.gbl...
> Hi
> I am getting the following error (given at the end) in the event log and
the
> MSSQLServerADHelper service does not start. What can I do?
> Thanks
> Regards
>
> Event Type: Error
> Event Source: MSSQLServerADHelper
> Event Category: None
> Event ID: 100
> Date: 09/07/2004
> Time: 18:41:36
> User: N/A
> Computer: MYSERVER
> Description:
> '0' is an invalid number of start up parameters. This service takes two
> start up parameters.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
MSSQLServerADHelper
Hi
I am getting the following error (given at the end) in the event log and the
MSSQLServerADHelper service does not start. What can I do?
Thanks
Regards
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 09/07/2004
Time: 18:41:36
User: N/A
Computer: MYSERVER
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.This also comes up.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/07/2004
Time: 19:15:36
User: N/A
Computer: MYSERVER
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572 (0xC0000064).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:ei8G85dZEHA.3092@.tk2msftngp13.phx.gbl...
> Hi
> I am getting the following error (given at the end) in the event log and
the
> MSSQLServerADHelper service does not start. What can I do?
> Thanks
> Regards
>
> Event Type: Error
> Event Source: MSSQLServerADHelper
> Event Category: None
> Event ID: 100
> Date: 09/07/2004
> Time: 18:41:36
> User: N/A
> Computer: MYSERVER
> Description:
> '0' is an invalid number of start up parameters. This service takes two
> start up parameters.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
I am getting the following error (given at the end) in the event log and the
MSSQLServerADHelper service does not start. What can I do?
Thanks
Regards
Event Type: Error
Event Source: MSSQLServerADHelper
Event Category: None
Event ID: 100
Date: 09/07/2004
Time: 18:41:36
User: N/A
Computer: MYSERVER
Description:
'0' is an invalid number of start up parameters. This service takes two
start up parameters.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.This also comes up.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 09/07/2004
Time: 19:15:36
User: N/A
Computer: MYSERVER
Description:
The MSSQLServerADHelper service terminated with service-specific error
3221225572 (0xC0000064).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:ei8G85dZEHA.3092@.tk2msftngp13.phx.gbl...
> Hi
> I am getting the following error (given at the end) in the event log and
the
> MSSQLServerADHelper service does not start. What can I do?
> Thanks
> Regards
>
> Event Type: Error
> Event Source: MSSQLServerADHelper
> Event Category: None
> Event ID: 100
> Date: 09/07/2004
> Time: 18:41:36
> User: N/A
> Computer: MYSERVER
> Description:
> '0' is an invalid number of start up parameters. This service takes two
> start up parameters.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
MSSQLServerAdHelper
I am unable to start this server and it returns the
following error:
Could not start the MSSQLSerevrADHelper on <server>.
Refer to service-specifc error code-1073741724You cannot manually start this service, SQL Server uses it when you register
a database or publication in AD.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Duane LaChance" <dlachance@.omniplex.org> wrote in message
news:0e7901c377a9$cb7a8ad0$a301280a@.phx.gbl...
I am unable to start this server and it returns the
following error:
Could not start the MSSQLSerevrADHelper on <server>.
Refer to service-specifc error code-1073741724|||I cdont even have the AD tab on the server properties
page, and was wondering if this was the cause
>--Original Message--
>You cannot manually start this service, SQL Server uses
it when you register
>a database or publication in AD.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Duane LaChance" <dlachance@.omniplex.org> wrote in
message
>news:0e7901c377a9$cb7a8ad0$a301280a@.phx.gbl...
>I am unable to start this server and it returns the
>following error:
>Could not start the MSSQLSerevrADHelper on <server>.
>Refer to service-specifc error code-1073741724
>
>.
>
following error:
Could not start the MSSQLSerevrADHelper on <server>.
Refer to service-specifc error code-1073741724You cannot manually start this service, SQL Server uses it when you register
a database or publication in AD.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Duane LaChance" <dlachance@.omniplex.org> wrote in message
news:0e7901c377a9$cb7a8ad0$a301280a@.phx.gbl...
I am unable to start this server and it returns the
following error:
Could not start the MSSQLSerevrADHelper on <server>.
Refer to service-specifc error code-1073741724|||I cdont even have the AD tab on the server properties
page, and was wondering if this was the cause
>--Original Message--
>You cannot manually start this service, SQL Server uses
it when you register
>a database or publication in AD.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Duane LaChance" <dlachance@.omniplex.org> wrote in
message
>news:0e7901c377a9$cb7a8ad0$a301280a@.phx.gbl...
>I am unable to start this server and it returns the
>following error:
>Could not start the MSSQLSerevrADHelper on <server>.
>Refer to service-specifc error code-1073741724
>
>.
>
MSSQLSERVER service terminated unexpectedly
Hi
For 15 days now my SQL server service is terminating with the following error
MSSQLSERVER service terminated unexpectedly. It has done this 2 Time(s).
This service terminates and restarts automatically and sometimes doesnt restart we have to mannuly start it again.
Event ID: 7034.
Version Details
Version: MSSQL Server 2000
Service Pack: 3a
Build: 8.0.0.760
I have checked many forums most of them suggest to apply the latest service pack. But the same error has been reported by sites having SP4 also.
Kindly help.
Thank you
wasimI'd guess a hardware problem.
Or...
...there is an infamous story at one of my old employers about a server that kept unexpectedly rebooting itself. It would always happen at about the same time each evening, but never the exact same time. The techs tried everything to figure out what was going on, but to no avail.
Finally one of the techs agreed to spend the night in the server room to observe the issue first-hand. All was quiet, until about the time the reboot was expected to occur. The tech watched as one of the late-shift workers entered the room to change the tapes. She was, shall we say, a short and portly woman, and as she bent over to eject the tape on one of the servers her derrier' smushed up against the suspect server, depressing its restart button.
Problem diagnosed and solved.
True story.|||Check the start-up account of the MSSQLServer service to see if its password has expired. Or if the account's profile has changed. Or if the service is running under credentials of an employee who has left the company.
For 15 days now my SQL server service is terminating with the following error
MSSQLSERVER service terminated unexpectedly. It has done this 2 Time(s).
This service terminates and restarts automatically and sometimes doesnt restart we have to mannuly start it again.
Event ID: 7034.
Version Details
Version: MSSQL Server 2000
Service Pack: 3a
Build: 8.0.0.760
I have checked many forums most of them suggest to apply the latest service pack. But the same error has been reported by sites having SP4 also.
Kindly help.
Thank you
wasimI'd guess a hardware problem.
Or...
...there is an infamous story at one of my old employers about a server that kept unexpectedly rebooting itself. It would always happen at about the same time each evening, but never the exact same time. The techs tried everything to figure out what was going on, but to no avail.
Finally one of the techs agreed to spend the night in the server room to observe the issue first-hand. All was quiet, until about the time the reboot was expected to occur. The tech watched as one of the late-shift workers entered the room to change the tapes. She was, shall we say, a short and portly woman, and as she bent over to eject the tape on one of the servers her derrier' smushed up against the suspect server, depressing its restart button.
Problem diagnosed and solved.
True story.|||Check the start-up account of the MSSQLServer service to see if its password has expired. Or if the account's profile has changed. Or if the service is running under credentials of an employee who has left the company.
MSSQLSERVER service terminated unexpectedly
This probably does not apply, but we had a similar problem and it had to do
with the following article:
KB article 822360
MS had us put in a couple of hot fixes and it seems to have taken care of
the problem.
"Martin Paterson" wrote:
> I have a couple of sites that are running MS SQL 2000 Service pack 4 that
are
> stopping (apperantly randomly) and restarting itself. This is causing gre
at
> havoc on some of my applications that are attached to this database. The
> server is dedicated for this purpose only and is hosting only on database.
> reviewing the error logs have not resulted in any explanation as to why SQ
L
> is dying. Google & MS knowledgebase has also resulted in no
> explanation/solution. Any assistance would be greatly appreciated.I have a couple of sites that are running MS SQL 2000 Service pack 4 that ar
e
stopping (apperantly randomly) and restarting itself. This is causing great
havoc on some of my applications that are attached to this database. The
server is dedicated for this purpose only and is hosting only on database.
reviewing the error logs have not resulted in any explanation as to why SQL
is dying. Google & MS knowledgebase has also resulted in no
explanation/solution. Any assistance would be greatly appreciated.|||This probably does not apply, but we had a similar problem and it had to do
with the following article:
KB article 822360
MS had us put in a couple of hot fixes and it seems to have taken care of
the problem.
"Martin Paterson" wrote:
> I have a couple of sites that are running MS SQL 2000 Service pack 4 that
are
> stopping (apperantly randomly) and restarting itself. This is causing gre
at
> havoc on some of my applications that are attached to this database. The
> server is dedicated for this purpose only and is hosting only on database.
> reviewing the error logs have not resulted in any explanation as to why SQ
L
> is dying. Google & MS knowledgebase has also resulted in no
> explanation/solution. Any assistance would be greatly appreciated.|||I looked at KB822360 and it appears to be for active directory issues. The
SQL server that is having this problem does not sit on a domain. thanks in
advance for your assistance.
"CLM" wrote:
[vbcol=seagreen]
> This probably does not apply, but we had a similar problem and it had to d
o
> with the following article:
> KB article 822360
> MS had us put in a couple of hot fixes and it seems to have taken care of
> the problem.
> "Martin Paterson" wrote:
>|||I looked at KB822360 and it appears to be for active directory issues. The
SQL server that is having this problem does not sit on a domain. thanks in
advance for your assistance.
"CLM" wrote:
[vbcol=seagreen]
> This probably does not apply, but we had a similar problem and it had to d
o
> with the following article:
> KB article 822360
> MS had us put in a couple of hot fixes and it seems to have taken care of
> the problem.
> "Martin Paterson" wrote:
>|||What does the sql errorlog say?
Martin Paterson wrote:[vbcol=seagreen]
> I looked at KB822360 and it appears to be for active directory issues. Th
e
> SQL server that is having this problem does not sit on a domain. thanks i
n
> advance for your assistance.
> "CLM" wrote:
>|||there are no errors that I can see...but here is an example of what is
displayed:
2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
(Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-06-19 07:01:01.44 server All rights reserved.
2006-06-19 07:01:01.44 server Server Process ID is 2032.
2006-06-19 07:01:01.44 server Logging SQL Server messages in file
'F:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2006-06-19 07:01:01.46 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2006-06-19 07:01:01.57 server SQL Server configured for thread mode
processing.
2006-06-19 07:01:01.60 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-06-19 07:01:02.08 server Attempting to initialize Distributed
Transaction Coordinator.
2006-06-19 07:01:05.10 spid3 Starting up database 'master'.
2006-06-19 07:01:05.24 spid3 0 transactions rolled back in database
'master' (1).
2006-06-19 07:01:05.24 spid3 Recovery is checkpointing database 'master'
(1)
2006-06-19 07:01:05.36 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-06-19 07:01:05.36 spid5 Starting up database 'model'.
2006-06-19 07:01:05.38 spid3 Server name is 'SECDB'.
2006-06-19 07:01:05.40 spid8 Starting up database 'msdb'.
2006-06-19 07:01:05.40 spid9 Starting up database 'pubs'.
2006-06-19 07:01:05.40 spid10 Starting up database 'Northwind'.
2006-06-19 07:01:05.40 spid11 Starting up database 'SSW'.
2006-06-19 07:01:05.60 server SQL server listening on www.xxx.yyy.zzz:
1433.
2006-06-19 07:01:05.60 server SQL server listening on 127.0.0.1: 1433.
2006-06-19 07:01:05.61 spid5 Clearing tempdb database.
2006-06-19 07:01:05.85 spid8 134 transactions rolled forward in database
'msdb' (4).
2006-06-19 07:01:05.91 spid8 0 transactions rolled back in database
'msdb' (4).
2006-06-19 07:01:05.93 spid8 Recovery is checkpointing database 'msdb' (
4)
2006-06-19 07:01:06.27 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2006-06-19 07:01:06.27 server SQL Server is ready for client connections
2006-06-19 07:01:07.19 spid5 Starting up database 'tempdb'.
2006-06-19 07:01:08.35 spid11 314 transactions rolled forward in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 0 transactions rolled back in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 Recovery is checkpointing database 'SSW' (7
)
2006-06-19 07:01:09.44 spid53 Using 'odsole70.dll' version '2000.80.2039'
to execute extended stored procedure 'sp_OACreate'.
"SQLPoet" wrote:
> What does the sql errorlog say?
>
> Martin Paterson wrote:
>|||Martin Paterson wrote:
> there are no errors that I can see...but here is an example of what is
> displayed:
> 2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
> (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
> Corporation.
That's the current log that was generated when SQL restarted. What does
the archived error log contain? Last 15-20 lines should be sufficient.|||there are no errors that I can see...but here is an example of what is
displayed:
2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
(Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-06-19 07:01:01.44 server All rights reserved.
2006-06-19 07:01:01.44 server Server Process ID is 2032.
2006-06-19 07:01:01.44 server Logging SQL Server messages in file
'F:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2006-06-19 07:01:01.46 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2006-06-19 07:01:01.57 server SQL Server configured for thread mode
processing.
2006-06-19 07:01:01.60 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-06-19 07:01:02.08 server Attempting to initialize Distributed
Transaction Coordinator.
2006-06-19 07:01:05.10 spid3 Starting up database 'master'.
2006-06-19 07:01:05.24 spid3 0 transactions rolled back in database
'master' (1).
2006-06-19 07:01:05.24 spid3 Recovery is checkpointing database 'master'
(1)
2006-06-19 07:01:05.36 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-06-19 07:01:05.36 spid5 Starting up database 'model'.
2006-06-19 07:01:05.38 spid3 Server name is 'SECDB'.
2006-06-19 07:01:05.40 spid8 Starting up database 'msdb'.
2006-06-19 07:01:05.40 spid9 Starting up database 'pubs'.
2006-06-19 07:01:05.40 spid10 Starting up database 'Northwind'.
2006-06-19 07:01:05.40 spid11 Starting up database 'SSW'.
2006-06-19 07:01:05.60 server SQL server listening on www.xxx.yyy.zzz:
1433.
2006-06-19 07:01:05.60 server SQL server listening on 127.0.0.1: 1433.
2006-06-19 07:01:05.61 spid5 Clearing tempdb database.
2006-06-19 07:01:05.85 spid8 134 transactions rolled forward in database
'msdb' (4).
2006-06-19 07:01:05.91 spid8 0 transactions rolled back in database
'msdb' (4).
2006-06-19 07:01:05.93 spid8 Recovery is checkpointing database 'msdb' (
4)
2006-06-19 07:01:06.27 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2006-06-19 07:01:06.27 server SQL Server is ready for client connections
2006-06-19 07:01:07.19 spid5 Starting up database 'tempdb'.
2006-06-19 07:01:08.35 spid11 314 transactions rolled forward in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 0 transactions rolled back in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 Recovery is checkpointing database 'SSW' (7
)
2006-06-19 07:01:09.44 spid53 Using 'odsole70.dll' version '2000.80.2039'
to execute extended stored procedure 'sp_OACreate'.
"SQLPoet" wrote:
> What does the sql errorlog say?
>
> Martin Paterson wrote:
>|||Martin Paterson wrote:
> there are no errors that I can see...but here is an example of what is
> displayed:
> 2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
> (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
> Corporation.
That's the current log that was generated when SQL restarted. What does
the archived error log contain? Last 15-20 lines should be sufficient.
with the following article:
KB article 822360
MS had us put in a couple of hot fixes and it seems to have taken care of
the problem.
"Martin Paterson" wrote:
> I have a couple of sites that are running MS SQL 2000 Service pack 4 that
are
> stopping (apperantly randomly) and restarting itself. This is causing gre
at
> havoc on some of my applications that are attached to this database. The
> server is dedicated for this purpose only and is hosting only on database.
> reviewing the error logs have not resulted in any explanation as to why SQ
L
> is dying. Google & MS knowledgebase has also resulted in no
> explanation/solution. Any assistance would be greatly appreciated.I have a couple of sites that are running MS SQL 2000 Service pack 4 that ar
e
stopping (apperantly randomly) and restarting itself. This is causing great
havoc on some of my applications that are attached to this database. The
server is dedicated for this purpose only and is hosting only on database.
reviewing the error logs have not resulted in any explanation as to why SQL
is dying. Google & MS knowledgebase has also resulted in no
explanation/solution. Any assistance would be greatly appreciated.|||This probably does not apply, but we had a similar problem and it had to do
with the following article:
KB article 822360
MS had us put in a couple of hot fixes and it seems to have taken care of
the problem.
"Martin Paterson" wrote:
> I have a couple of sites that are running MS SQL 2000 Service pack 4 that
are
> stopping (apperantly randomly) and restarting itself. This is causing gre
at
> havoc on some of my applications that are attached to this database. The
> server is dedicated for this purpose only and is hosting only on database.
> reviewing the error logs have not resulted in any explanation as to why SQ
L
> is dying. Google & MS knowledgebase has also resulted in no
> explanation/solution. Any assistance would be greatly appreciated.|||I looked at KB822360 and it appears to be for active directory issues. The
SQL server that is having this problem does not sit on a domain. thanks in
advance for your assistance.
"CLM" wrote:
[vbcol=seagreen]
> This probably does not apply, but we had a similar problem and it had to d
o
> with the following article:
> KB article 822360
> MS had us put in a couple of hot fixes and it seems to have taken care of
> the problem.
> "Martin Paterson" wrote:
>|||I looked at KB822360 and it appears to be for active directory issues. The
SQL server that is having this problem does not sit on a domain. thanks in
advance for your assistance.
"CLM" wrote:
[vbcol=seagreen]
> This probably does not apply, but we had a similar problem and it had to d
o
> with the following article:
> KB article 822360
> MS had us put in a couple of hot fixes and it seems to have taken care of
> the problem.
> "Martin Paterson" wrote:
>|||What does the sql errorlog say?
Martin Paterson wrote:[vbcol=seagreen]
> I looked at KB822360 and it appears to be for active directory issues. Th
e
> SQL server that is having this problem does not sit on a domain. thanks i
n
> advance for your assistance.
> "CLM" wrote:
>|||there are no errors that I can see...but here is an example of what is
displayed:
2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
(Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-06-19 07:01:01.44 server All rights reserved.
2006-06-19 07:01:01.44 server Server Process ID is 2032.
2006-06-19 07:01:01.44 server Logging SQL Server messages in file
'F:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2006-06-19 07:01:01.46 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2006-06-19 07:01:01.57 server SQL Server configured for thread mode
processing.
2006-06-19 07:01:01.60 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-06-19 07:01:02.08 server Attempting to initialize Distributed
Transaction Coordinator.
2006-06-19 07:01:05.10 spid3 Starting up database 'master'.
2006-06-19 07:01:05.24 spid3 0 transactions rolled back in database
'master' (1).
2006-06-19 07:01:05.24 spid3 Recovery is checkpointing database 'master'
(1)
2006-06-19 07:01:05.36 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-06-19 07:01:05.36 spid5 Starting up database 'model'.
2006-06-19 07:01:05.38 spid3 Server name is 'SECDB'.
2006-06-19 07:01:05.40 spid8 Starting up database 'msdb'.
2006-06-19 07:01:05.40 spid9 Starting up database 'pubs'.
2006-06-19 07:01:05.40 spid10 Starting up database 'Northwind'.
2006-06-19 07:01:05.40 spid11 Starting up database 'SSW'.
2006-06-19 07:01:05.60 server SQL server listening on www.xxx.yyy.zzz:
1433.
2006-06-19 07:01:05.60 server SQL server listening on 127.0.0.1: 1433.
2006-06-19 07:01:05.61 spid5 Clearing tempdb database.
2006-06-19 07:01:05.85 spid8 134 transactions rolled forward in database
'msdb' (4).
2006-06-19 07:01:05.91 spid8 0 transactions rolled back in database
'msdb' (4).
2006-06-19 07:01:05.93 spid8 Recovery is checkpointing database 'msdb' (
4)
2006-06-19 07:01:06.27 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2006-06-19 07:01:06.27 server SQL Server is ready for client connections
2006-06-19 07:01:07.19 spid5 Starting up database 'tempdb'.
2006-06-19 07:01:08.35 spid11 314 transactions rolled forward in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 0 transactions rolled back in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 Recovery is checkpointing database 'SSW' (7
)
2006-06-19 07:01:09.44 spid53 Using 'odsole70.dll' version '2000.80.2039'
to execute extended stored procedure 'sp_OACreate'.
"SQLPoet" wrote:
> What does the sql errorlog say?
>
> Martin Paterson wrote:
>|||Martin Paterson wrote:
> there are no errors that I can see...but here is an example of what is
> displayed:
> 2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
> (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
> Corporation.
That's the current log that was generated when SQL restarted. What does
the archived error log contain? Last 15-20 lines should be sufficient.|||there are no errors that I can see...but here is an example of what is
displayed:
2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
(Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-06-19 07:01:01.44 server All rights reserved.
2006-06-19 07:01:01.44 server Server Process ID is 2032.
2006-06-19 07:01:01.44 server Logging SQL Server messages in file
'F:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2006-06-19 07:01:01.46 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2006-06-19 07:01:01.57 server SQL Server configured for thread mode
processing.
2006-06-19 07:01:01.60 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-06-19 07:01:02.08 server Attempting to initialize Distributed
Transaction Coordinator.
2006-06-19 07:01:05.10 spid3 Starting up database 'master'.
2006-06-19 07:01:05.24 spid3 0 transactions rolled back in database
'master' (1).
2006-06-19 07:01:05.24 spid3 Recovery is checkpointing database 'master'
(1)
2006-06-19 07:01:05.36 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-06-19 07:01:05.36 spid5 Starting up database 'model'.
2006-06-19 07:01:05.38 spid3 Server name is 'SECDB'.
2006-06-19 07:01:05.40 spid8 Starting up database 'msdb'.
2006-06-19 07:01:05.40 spid9 Starting up database 'pubs'.
2006-06-19 07:01:05.40 spid10 Starting up database 'Northwind'.
2006-06-19 07:01:05.40 spid11 Starting up database 'SSW'.
2006-06-19 07:01:05.60 server SQL server listening on www.xxx.yyy.zzz:
1433.
2006-06-19 07:01:05.60 server SQL server listening on 127.0.0.1: 1433.
2006-06-19 07:01:05.61 spid5 Clearing tempdb database.
2006-06-19 07:01:05.85 spid8 134 transactions rolled forward in database
'msdb' (4).
2006-06-19 07:01:05.91 spid8 0 transactions rolled back in database
'msdb' (4).
2006-06-19 07:01:05.93 spid8 Recovery is checkpointing database 'msdb' (
4)
2006-06-19 07:01:06.27 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2006-06-19 07:01:06.27 server SQL Server is ready for client connections
2006-06-19 07:01:07.19 spid5 Starting up database 'tempdb'.
2006-06-19 07:01:08.35 spid11 314 transactions rolled forward in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 0 transactions rolled back in database
'SiteSecure' (7).
2006-06-19 07:01:08.43 spid11 Recovery is checkpointing database 'SSW' (7
)
2006-06-19 07:01:09.44 spid53 Using 'odsole70.dll' version '2000.80.2039'
to execute extended stored procedure 'sp_OACreate'.
"SQLPoet" wrote:
> What does the sql errorlog say?
>
> Martin Paterson wrote:
>|||Martin Paterson wrote:
> there are no errors that I can see...but here is an example of what is
> displayed:
> 2006-06-19 07:01:01.44 server Microsoft SQL Server 2000 - 8.00.2039
> (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-06-19 07:01:01.44 server Copyright (C) 1988-2002 Microsoft
> Corporation.
That's the current log that was generated when SQL restarted. What does
the archived error log contain? Last 15-20 lines should be sufficient.
Saturday, February 25, 2012
MSSQL SPROC and VB6
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP
GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@.ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE @.ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @.CP int /*Len of String */
DECLARE @.SV varchar(50) /*Holds Result */
While @.ListStr<>''
Begin
Set @.CP=CharIndex(',',@.ListStr) /*Sets length of words - Instr */
If @.CP<>0
Begin
Set @.SV=Cast(Left(@.ListStr,@.CP-1) as varchar) /*Copies Portion of String*/
Set @.ListStr=Right(@.ListStr,Len(@.ListStr)-@.CP) /*Sets up next portion of string*/
End
Else
Begin
Set @.SV=Cast(@.ListStr as varchar)
Set @.ListStr=''
End
Insert into @.ListTbl Values (@.SV) /*Inserts variable into Temp Table*/
End
Select InvUnit From @.ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
Set adoConn = New ADODB.Connection
adoConn.Open connString
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
Set adoRS = .Execute
Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop
End With
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub
Any ideas?
ThanksWhat is the edition of SQL used and its memory settings?
Is SQL Server shared by other applications?|||Thanks for the reply my problem was resolved (http://vbforums.com/showthread.php?t=405134)
Here is my complete code:
USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP
GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@.ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE @.ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @.CP int /*Len of String */
DECLARE @.SV varchar(50) /*Holds Result */
While @.ListStr<>''
Begin
Set @.CP=CharIndex(',',@.ListStr) /*Sets length of words - Instr */
If @.CP<>0
Begin
Set @.SV=Cast(Left(@.ListStr,@.CP-1) as varchar) /*Copies Portion of String*/
Set @.ListStr=Right(@.ListStr,Len(@.ListStr)-@.CP) /*Sets up next portion of string*/
End
Else
Begin
Set @.SV=Cast(@.ListStr as varchar)
Set @.ListStr=''
End
Insert into @.ListTbl Values (@.SV) /*Inserts variable into Temp Table*/
End
Select InvUnit From @.ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
Set adoConn = New ADODB.Connection
adoConn.Open connString
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
Set adoRS = .Execute
Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop
End With
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub
Any ideas?
ThanksWhat is the edition of SQL used and its memory settings?
Is SQL Server shared by other applications?|||Thanks for the reply my problem was resolved (http://vbforums.com/showthread.php?t=405134)
Monday, February 20, 2012
MSSQL SELECT error
I've the following Select Statement that generates errors, could someone tells me why? :(
SELECT TOP 20 * FROM tickets WHERE ticket_ID NOT IN (SELECT TOP 20 * FROM tickets ORDER BY ticket_ID DESC) ORDER BY ticket_ID DESC;
Warning: mssql_query() [function.mssql-query]: message: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. (severity 16) in c:\Inetpub\wwwroot\schoolProject\admin\show.php on line 437Try this ??
SELECT TOP 20 * FROM tickets WHERE ticket_ID NOT IN (SELECT TOP 20 ticket_ID FROM tickets ORDER BY ticket_ID DESC) ORDER BY ticket_ID DESC
You had it checking the id against all columns - take it you are trying to get the 21st to 40th most recent??
Subscribe to:
Posts (Atom)