Help on code in a SP

Hi,
I'm currently auditing some SQL servers to have an overview on what's
running/existing in the different databases.
On one of the server, I found User's Stored Procedures in MSDB databases
(which, I think is already not a good idea). My problem is that I can't get
the usage of the SPs.
All are built on the same model : all the code is written on 1 line only.
Here is the full code of one the SP:
-- beginning of the code --
create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,@.ShowCollections)
GO
-- end of the code --
If someone would have any clue abotu what's this SP is doing...
Thanks,
ChrisYou are trying to insert into the RTblClassExtension with the values passed.
What is that you would like to know here
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm currently auditing some SQL servers to have an overview on what's
> running/existing in the different databases.
> On one of the server, I found User's Stored Procedures in MSDB databases
> (which, I think is already not a good idea). My problem is that I can't
get
> the usage of the SPs.
> All are built on the same model : all the code is written on 1 line only.
> Here is the full code of one the SP:
> -- beginning of the code --
> create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,@.ShowCollections)[vbc
ol=seagreen]
> GO
> -- end of the code --
> If someone would have any clue abotu what's this SP is doing...
> Thanks,
> Chris
>[/vbcol]|||I'm trying to understand what are theses procedures I have into MSDB.
On your opinion, what could be the usage of such insert ?
(I'm not, far from that, expert in SQL. so, any help will be appreciated)
Thx,
Chris
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cohev5$avs$1@.news01.intel.com...
> You are trying to insert into the RTblClassExtension with the values
passed.
> What is that you would like to know here
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Chris V." <tophe_news@.hotmail.com> wrote in message
> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> get
only.
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,@.ShowCollections)[vbc
ol=seagreen]
>|||Like you I don't know what its used for either, however I
have in my database which sort of means that its actually
a Microsoft SP, and not a user one.
Sorry I can't be much of a help here, except to lay your
mind at rest.
Peter
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill

>--Original Message--
>I'm trying to understand what are theses procedures I
have into MSDB.
>On your opinion, what could be the usage of such insert ?
>(I'm not, far from that, expert in SQL. so, any help will
be appreciated)
>Thx,
>Chris
>"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in
message
>news:cohev5$avs$1@.news01.intel.com...
with the values[vbcol=seagreen]
>passed.
http://www.microsoft.com/sql/techin...tdoc/2000/books
.asp[vbcol=seagreen]
overview on what's[vbcol=seagreen]
Procedures in MSDB databases[vbcol=seagreen]
problem is that I can't[vbcol=seagreen]
written on 1 line[vbcol=seagreen]
>only.
-[vbcol=seagreen]
(8),@.Z_BranchID_Z int,@.Z_VS_Z[vbcol=seagreen]
int=null,@.ShowCollections[vbcol=seagreen
]
RTblClassExtension values[vbcol=seagreen]
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.I
conLibrary,@.IconID,
@.ShowCollections)
is doing...[vbcol=seagreen]
>
>.
>

Help on code in a SP

Hi,
I'm currently auditing some SQL servers to have an overview on what's
running/existing in the different databases.
On one of the server, I found User's Stored Procedures in MSDB databases
(which, I think is already not a good idea). My problem is that I can't get
the usage of the SPs.
All are built on the same model : all the code is written on 1 line only.
Here is the full code of one the SP:
-- beginning of the code --
create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,@.ShowCollections)
GO
-- end of the code --
If someone would have any clue abotu what's this SP is doing...
Thanks,
ChrisYou are trying to insert into the RTblClassExtension with the values passed.
What is that you would like to know here
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm currently auditing some SQL servers to have an overview on what's
> running/existing in the different databases.
> On one of the server, I found User's Stored Procedures in MSDB databases
> (which, I think is already not a good idea). My problem is that I can't
get
> the usage of the SPs.
> All are built on the same model : all the code is written on 1 line only.
> Here is the full code of one the SP:
> -- beginning of the code --
> create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,@.ShowCollections)
> GO
> -- end of the code --
> If someone would have any clue abotu what's this SP is doing...
> Thanks,
> Chris
>|||I'm trying to understand what are theses procedures I have into MSDB.
On your opinion, what could be the usage of such insert ?
(I'm not, far from that, expert in SQL. so, any help will be appreciated)
Thx,
Chris
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cohev5$avs$1@.news01.intel.com...
> You are trying to insert into the RTblClassExtension with the values
passed.
> What is that you would like to know here
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> "Chris V." <tophe_news@.hotmail.com> wrote in message
> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I'm currently auditing some SQL servers to have an overview on what's
> > running/existing in the different databases.
> >
> > On one of the server, I found User's Stored Procedures in MSDB databases
> > (which, I think is already not a good idea). My problem is that I can't
> get
> > the usage of the SPs.
> > All are built on the same model : all the code is written on 1 line
only.
> >
> > Here is the full code of one the SP:
> >
> > -- beginning of the code --
> > create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> > int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> > bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
> >
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,@.ShowCollections)
> > GO
> >
> > -- end of the code --
> >
> > If someone would have any clue abotu what's this SP is doing...
> >
> > Thanks,
> > Chris
> >
> >
>|||Like you I don't know what its used for either, however I
have in my database which sort of means that its actually
a Microsoft SP, and not a user one.
Sorry I can't be much of a help here, except to lay your
mind at rest.
Peter
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill
>--Original Message--
>I'm trying to understand what are theses procedures I
have into MSDB.
>On your opinion, what could be the usage of such insert ?
>(I'm not, far from that, expert in SQL. so, any help will
be appreciated)
>Thx,
>Chris
>"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in
message
>news:cohev5$avs$1@.news01.intel.com...
>> You are trying to insert into the RTblClassExtension
with the values
>passed.
>> What is that you would like to know here
>> --
>> HTH,
>> Vinod Kumar
>> MCSE, DBA, MCAD, MCSD
>> http://www.extremeexperts.com
>> Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books
.asp
>> "Chris V." <tophe_news@.hotmail.com> wrote in message
>> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I'm currently auditing some SQL servers to have an
overview on what's
>> > running/existing in the different databases.
>> >
>> > On one of the server, I found User's Stored
Procedures in MSDB databases
>> > (which, I think is already not a good idea). My
problem is that I can't
>> get
>> > the usage of the SPs.
>> > All are built on the same model : all the code is
written on 1 line
>only.
>> >
>> > Here is the full code of one the SP:
>> >
>> > -- beginning of the code --
-
>> > create procedure <SP_Name> (@.IntID binary
(8),@.Z_BranchID_Z int,@.Z_VS_Z
>> > int,@.IconLibrary varchar(255)=null,@.IconID
int=null,@.ShowCollections
>> > bit=null,@.Z_VE_Z int=2147483647) as insert
RTblClassExtension values
>> >
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary,@.IconID,
@.ShowCollections)
>> > GO
>> >
>> > -- end of the code --
>> >
>> > If someone would have any clue abotu what's this SP
is doing...
>> >
>> > Thanks,
>> > Chris
>> >
>> >
>>
>
>.
>sql

Help on code in a SP

Hi,
I'm currently auditing some SQL servers to have an overview on what's
running/existing in the different databases.
On one of the server, I found User's Stored Procedures in MSDB databases
(which, I think is already not a good idea). My problem is that I can't get
the usage of the SPs.
All are built on the same model : all the code is written on 1 line only.
Here is the full code of one the SP:
-- beginning of the code --
create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,@.ShowCollections)
GO
-- end of the code --
If someone would have any clue abotu what's this SP is doing...
Thanks,
Chris
You are trying to insert into the RTblClassExtension with the values passed.
What is that you would like to know here
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm currently auditing some SQL servers to have an overview on what's
> running/existing in the different databases.
> On one of the server, I found User's Stored Procedures in MSDB databases
> (which, I think is already not a good idea). My problem is that I can't
get
> the usage of the SPs.
> All are built on the same model : all the code is written on 1 line only.
> Here is the full code of one the SP:
> -- beginning of the code --
> create procedure <SP_Name> (@.IntID binary(8),@.Z_BranchID_Z int,@.Z_VS_Z
> int,@.IconLibrary varchar(255)=null,@.IconID int=null,@.ShowCollections
> bit=null,@.Z_VE_Z int=2147483647) as insert RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,@.ShowCollections)
> GO
> -- end of the code --
> If someone would have any clue abotu what's this SP is doing...
> Thanks,
> Chris
>
|||I'm trying to understand what are theses procedures I have into MSDB.
On your opinion, what could be the usage of such insert ?
(I'm not, far from that, expert in SQL. so, any help will be appreciated)
Thx,
Chris
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cohev5$avs$1@.news01.intel.com...
> You are trying to insert into the RTblClassExtension with the values
passed.[vbcol=seagreen]
> What is that you would like to know here
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
> "Chris V." <tophe_news@.hotmail.com> wrote in message
> news:u0Lybtr1EHA.1152@.TK2MSFTNGP14.phx.gbl...
> get
only.
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,@.ShowCollections)
>
|||Like you I don't know what its used for either, however I
have in my database which sort of means that its actually
a Microsoft SP, and not a user one.
Sorry I can't be much of a help here, except to lay your
mind at rest.
Peter
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill

>--Original Message--
>I'm trying to understand what are theses procedures I
have into MSDB.
>On your opinion, what could be the usage of such insert ?
>(I'm not, far from that, expert in SQL. so, any help will
be appreciated)
>Thx,
>Chris
>"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in
message[vbcol=seagreen]
>news:cohev5$avs$1@.news01.intel.com...
with the values[vbcol=seagreen]
>passed.
http://www.microsoft.com/sql/techinf...doc/2000/books
..asp[vbcol=seagreen]
overview on what's[vbcol=seagreen]
Procedures in MSDB databases[vbcol=seagreen]
problem is that I can't[vbcol=seagreen]
written on 1 line[vbcol=seagreen]
>only.
-[vbcol=seagreen]
(8),@.Z_BranchID_Z int,@.Z_VS_Z[vbcol=seagreen]
int=null,@.ShowCollections[vbcol=seagreen]
RTblClassExtension values
>
(@.IntID,@.Z_BranchID_Z,@.Z_VS_Z,@.Z_VE_Z,@.IconLibrary ,@.IconID,
@.ShowCollections)[vbcol=seagreen]
is doing...
>
>.
>

Help needed with Primary Key and Identity

I have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc...:

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

End If

' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()

End If

End Subds.Tables(0).Columns("YourPrimaryKey").IncrementSeed = 1
ds.Tables(0).Columns("YourPrimaryKey")... other properties you need to set to make it an identity.|||Right now I'm using an "ID" field as the primary key and it is setup in SQL as an identity but when I try to add a row...it says..

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'CustomerInfo' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.OrderEdit_aspx.DataGrid_Update(Object Sender, DataGridCommandEventArgs E

Would I need to remove the identity setting in SQL and create the identity through my code or is there a way to specify that my "ID" field is a primary key and it needs to be incremented by 1 whenever a new row is added?

Thanks for the help!!

Help needed with Instead of Update trigger on a View

I have an application where we are replacing a subsystem including portions
of the database. In order to minimize the code impact on the existing
application, we have decided to create a few "compatibility views" - i.e.
database Views that produce the same result and with the same names as the
old tables. Also in order to allow existing code to continue to function,
we are implementing INSTEAD OF triggers on the views. Even though all our
database accesses are encapsulated in stored procs, we have around 1500 of
them, and one of the tables we needed to reengineer this way is the "main"
table for the entire app. To make sure this isn't trivial, we have a new
"master" entity table with an Identity column that is referenced by the
reengineered "main" table.
At this point, we are only aware of performance impacts - everything appears
to work OK:
1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
statement) is part of an index, we loose the use of the index as a result of
having to use NULLIF() or COALESCE() on those columns in the view. For the
same reason, we can't index those view(s).
2. (This is where the question comes in:) The INSTEAD OF UPDATE trigger
appears to require a large number of separate UPDATE statements against the
base tables, or building a dynamic SQL statement. We are looking for
guidance...
Now to my question:
In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
table. If I understand correctly, since the triggering update statement may
only update one column, I cannot use an UPDATE statement against the base
table that updates all the columns with the values from the 'updated'
pseudo-table. Instead, I will have to check if each column is updated, and
if so, either update it separately or build a dynamic SQL UPDATE statement
including those columns that have been updated.
What is the recommended approach to this?
TIA,
Tore.On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
(snip)
>1. If a NON-NULL IDENTITY (or other non-required column in an INSERT
>statement) is part of an index, we loose the use of the index as a result o
f
>having to use NULLIF() or COALESCE() on those columns in the view.
Hi Tore,
I don't think I understand what you're saying here. Where are you using
NULLIF() or COALESCE() and why? Coould you post a simplified sample of
your code?

> For the
>same reason, we can't index those view(s).
And neither should you. If you index the views, you'll create a complete
copy of your data. I don't think that yoou should do that in your
scenario.
(snip)
>In the INSTEAD OF UPDATE trigger, I have about 90 member columns from one
>table. If I understand correctly, since the triggering update statement ma
y
>only update one column, I cannot use an UPDATE statement against the base
>table that updates all the columns with the values from the 'updated'
>pseudo-table.
You undersatnd incorrectly. There is no 'updated' pseudo-table. The
'deleted' and 'inserted' pseudo-tables contain the complete before and
after image of the updated rows, including all columns that are not
affected by the update.
If your compatibility view translates to one new table, just perform the
modification in a single UPDATE statement. If your compatibility view
translates to more than one new table, use
IF UPDATE(col1) OR UPDATE(col2) ....
to find out which table(s) need updating, then use a single UPDATE
statement for all rows in each of the tables.
Sure, you'll be setting columns to the same value they already had. The
added cost of that is much less than the cost of finding out which
columns to update and executing up to 90 (!) consecutive UPDATE
statements against the same set of rows.

> Instead, I will have to check if each column is updated, and
>if so, either update it separately or build a dynamic SQL UPDATE statement
>including those columns that have been updated.
Using the dynamic SQL is even a worse option - it forces you to give
every user update permissions to the table. (And it will be slow because
of the extra recompiles).
www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP|||Thanks Hugo,
I'll be looking at this tomorrow.
Tore.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p75762lfnlmg789b20fv9jk1jfjrhs2it0@.
4ax.com...
> On Thu, 11 May 2006 14:30:01 -0400, "Tore" <tbostrup at agfirst> wrote:
> (snip)
of
> Hi Tore,
> I don't think I understand what you're saying here. Where are you using
> NULLIF() or COALESCE() and why? Coould you post a simplified sample of
> your code?
>
> And neither should you. If you index the views, you'll create a complete
> copy of your data. I don't think that yoou should do that in your
> scenario.
> (snip)
may
> You undersatnd incorrectly. There is no 'updated' pseudo-table. The
> 'deleted' and 'inserted' pseudo-tables contain the complete before and
> after image of the updated rows, including all columns that are not
> affected by the update.
> If your compatibility view translates to one new table, just perform the
> modification in a single UPDATE statement. If your compatibility view
> translates to more than one new table, use
> IF UPDATE(col1) OR UPDATE(col2) ....
> to find out which table(s) need updating, then use a single UPDATE
> statement for all rows in each of the tables.
> Sure, you'll be setting columns to the same value they already had. The
> added cost of that is much less than the cost of finding out which
> columns to update and executing up to 90 (!) consecutive UPDATE
> statements against the same set of rows.
>
statement
> Using the dynamic SQL is even a worse option - it forces you to give
> every user update permissions to the table. (And it will be slow because
> of the extra recompiles).
> www.sommarskog.se/dynamic_sql.html
> --
> Hugo Kornelis, SQL Server MVP

Help needed to generate a "sort code"

I have been puzzled by this over the last 2 days, maybe an SQL expert here can help me out.

I have the following table:
==============
Pref
==============
UID int
SMOKE bit
LIVESMOKE bit
PET bit
LIVEPET bit
==============

I have a record with the following preferences:
smoke: 1
live with smoker: 1
pet: 0
live with pet: 0

how can I write an SQL statement to pull up all rows in the table sorted by the number of matching preferences to the row indicated above?

My initial thought was to xnor the pref which will return 1 on each matching pref but then I still need to count the number of on bit on the xnor result.

Have anyone done this before? any idea or suggestion?order
by case when SMOKE = 1 then 1 else 0 end
+case when LIVESMOKE = 1 then 1 else 0 end
+case when PET = 0 then 1 else 0 end
+case when LIVEPET = 0 then 1 else 0 end
desc|||I'm not used to the BIT datatype. So you can't do this then?

order by SMOKE+LIVESMOKE+PET+LIVEPET

i.e. BITs are not implicitly converted to INTEGERS by an expression like that?|||not implicitly, no, you actually have to use CAST on them

however, that won't help here, as the problem was to find rows with most matches to the particular combination 1, 1, 0, 0|||I should have read more carefully!

help needed on XML

using the BOL and looking through some of the posts on here ive managed to
come up with the following code in T-SQl
DECLARE @.TESTXML varchar(8000)
SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
<BDocument>,
<Data>
<InputReport>
<Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
reportSequenceNumber="000121" userNumber="123456">
<ProducedOn time="19:21:22" date="2004-09-30"/>
<ProcessingDate date="2004-10-01"/>
</Header>
</InputReport>
</Data>
</BDocument>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
SELECT *
FROM
OPENXML(@.hDoc, '/BDocument')
EXEC sp_xml_removedocument @.hDoc
I need help on expanding this.
1. Would i be right in assuming that to load the contants of a XML file
into the variable @.TESTXML, i would need to use something like actixex in a
DTS.
2. how can i in this case just do a select on a specific field ie
'reportnumber'
3. some of the reports i will be recieving will have the same field names in
different sections, for example
- <AccountTotals>
- <DebitEntry>
<AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
</DebitEntry>
</AccountTotal>
- <CreditEntry>
<AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
<AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
</CreditEntry>
As you can see the field 'numberOf' is used several times, how can i
differanciate between each one in each section
See below.
Best regards
Michael
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3A396C18-0271-4DD4-B2B5-C74505CE1156@.microsoft.com...
> using the BOL and looking through some of the posts on here ive managed to
> come up with the following code in T-SQl
> DECLARE @.TESTXML varchar(8000)
> SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BDocument>,
> <Data>
> <InputReport>
> <Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
> reportSequenceNumber="000121" userNumber="123456">
> <ProducedOn time="19:21:22" date="2004-09-30"/>
> <ProcessingDate date="2004-10-01"/>
> </Header>
> </InputReport>
> </Data>
> </BDocument>'
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> SELECT *
> FROM
> OPENXML(@.hDoc, '/BDocument')
> EXEC sp_xml_removedocument @.hDoc
> I need help on expanding this.
> 1. Would i be right in assuming that to load the contants of a XML file
> into the variable @.TESTXML, i would need to use something like actixex in
> a
> DTS.
Not necessarily. Any client side API that allows you to pass a parameter to
a stored proc should work. Just copy the file content over as parameter
value.

> 2. how can i in this case just do a select on a specific field ie
> 'reportnumber'
The OpenXML above results in an edge table.
To get the reportNumber for every header, you would replace your select
with:
select *
from OpenXML(@.hDoc, '/BDocument/Data/InputReport/Header') WITH (rno int
'@.reportNumber')

> 3. some of the reports i will be recieving will have the same field names
> in
> different sections, for example
> - <AccountTotals>
> - <DebitEntry>
> <AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
> </DebitEntry>
> </AccountTotal>
> - <CreditEntry>
> <AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
> <AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
> </CreditEntry>
>
> As you can see the field 'numberOf' is used several times, how can i
> differanciate between each one in each section
The following will give you only AcceptedRecords:
select * from OpenXML(@.hDoc, '//AcceptedRecords') with
(numberOf int, valueOf real, currency nvarchar(5))
The following will give you numberOf and the name of its element:
select * from OpenXML(@.hDoc, '//DebitEntry/*') with
(recname nvarchar(40) '@.mp:localname', numberOf int)
HTH
Michael

Help Needed on Next() function of Crystal Reports

Can any of you help me by providing an Alternate Code Snippet in
reportingsvcs(SSRS) for Crystal Report Next() function.
Thanks in Advance.
Regards,
Nivasp74My CR knowledge is really rusty now, but I guess Next in CR is the
opposite of Previous in RS?
There isn't a Next function as such in RS. You'll need to do a bit of
lateral thinking.
If you are affecting formatting or showing or hiding rows/cells based
on the next value, try and switch your logic so you can use previous
instead.
If that's not possible in your scenario, try incorporating the next
value onto each row from the dataset, i.e.
Select a.id, a.value, b.id, b.value
From MyTable A
Left Join MyTable B On B.Id = (A.Id + 1)
A good piece of advice when switching from CR to RS; try not to think
how you would do it in CR first - this will only cause you frustration!
RS is quite a different product in the way it works, as soon as you can
break the habit of thinking in Crystal terms, you'll find RS get's
easier.
--
Regards
Chris
nivasp wrote:
> Can any of you help me by providing an Alternate Code Snippet in
> reportingsvcs(SSRS) for Crystal Report Next() function.
> Thanks in Advance.
> Regards,
> Nivasp74|||Not sure if you will find this useful for your situation or not, but I am in
the process of converting our reports from Crystal Reports to SQL Server
Reporting Services and I came up with the following alternative for the NEXT
function in our situation.
Many of our reports consist of multiple groups with subtotals and grand
totals in the group footers. On some of the CR reports, we have a
conditional page break which forces a page break only if the NEXT group value
is equal to the current value. For example: {@.Bank_Name/Bank_HEADER} = Next
({@.Bank_Name/Bank_HEADER})
This prevents a group footer from appearing on a single page by itself.
My SSRS solution is to place all the group footer information in the
innermost group footer, but within their own individual table rows with the
innermost group footer's Page break at End property checked. Then I define
the table row's hidden property to an expression which evaluates the row
number of the group with the row count of the group, such as
=(RowNumber("table1_Group2")<>CountRows("table1_Group2")).
My Group 1 and Report Footers row hidden properties have the following
respective expressions:
=(RowNumber("table1_Group1")<>CountRows("table1_Group1"))
=(RowNumber("table1")<>CountRows("table1"))
As for the aggregate functions in each of the table rows, I apply the same
scope values to achieve the Group 2, Group 1, and report totals as in:
=Count(Fields!Formula_Acct_Num.Value, "table1_Group2")
=Count(Fields!Formula_Acct_Num.Value, "table1_Group1")
=Count(Fields!Formula_Acct_Num.Value, "table1")
= Sum(Fields!Formula_Balance.Value, "table1_Group2")
= Sum(Fields!Formula_Balance.Value, "table1_Group1")
= Sum(Fields!Formula_Balance.Value, "table1")
Enjoy!
"Chris McGuigan" wrote:
> My CR knowledge is really rusty now, but I guess Next in CR is the
> opposite of Previous in RS?
> There isn't a Next function as such in RS. You'll need to do a bit of
> lateral thinking.
> If you are affecting formatting or showing or hiding rows/cells based
> on the next value, try and switch your logic so you can use previous
> instead.
> If that's not possible in your scenario, try incorporating the next
> value onto each row from the dataset, i.e.
> Select a.id, a.value, b.id, b.value
> From MyTable A
> Left Join MyTable B On B.Id = (A.Id + 1)
> A good piece of advice when switching from CR to RS; try not to think
> how you would do it in CR first - this will only cause you frustration!
> RS is quite a different product in the way it works, as soon as you can
> break the habit of thinking in Crystal terms, you'll find RS get's
> easier.
> --
> Regards
> Chris
>
> nivasp wrote:
> > Can any of you help me by providing an Alternate Code Snippet in
> > reportingsvcs(SSRS) for Crystal Report Next() function.
> >
> > Thanks in Advance.
> > Regards,
> > Nivasp74
>

Help needed for SSRS 2005(Regarding html code)

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in this.
We are using an ASP.net application in which user fills a form. Some
fields contain text fields and we are entering all the fields in our
database through application.
But when we are entering values from application then our database
contains some html code in some fields with data.
Now I want to show those fields in my report, Values coming from
database in my report but they are in html format. So it is not
readable in report.
Now you people tell me how to solve this issue in my reports. I want
that data in my report as user entered in the application form.
Any help will be appreciated.
Regards
DineshDinesh,
The only thing I can think of is writing a function which strips out the
HTML tags and then call that function for each field in your report. You
could make this function a Report Code Block or even better, put it in an
assembly referenced by the Report.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1176467678.190725.177080@.y80g2000hsf.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005, and I am facing a problem in this.
> We are using an ASP.net application in which user fills a form. Some
> fields contain text fields and we are entering all the fields in our
> database through application.
> But when we are entering values from application then our database
> contains some html code in some fields with data.
> Now I want to show those fields in my report, Values coming from
> database in my report but they are in html format. So it is not
> readable in report.
> Now you people tell me how to solve this issue in my reports. I want
> that data in my report as user entered in the application form.
> Any help will be appreciated.
> Regards
> Dinesh
>

help me....

hi everyone

i got 2 questions plz

1- how to use the datadiff function ....plz write the code

2- is there a Trim function or statment that cuts the unneeded spaces before and after the word

thanx ...

Hi there,
1. http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_5vxi.asp?frame=true
samples imcluded :-)
2. What do you mean by unneeded ? You can trail characters, by using the function which I wrote some time ago:
CREATE FUNCTION dbo.fn_removetrailingchars
(
@.strValue VARCHAR(200),
@.TrailingChar VARCHAR(200),
@.RemoveLeading BIT
)
--Coded by Jens Suessmeyer, 2005 Available on http://www.sqlserver2005.de
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @.intCount int
SET @.intCount = 0

WHILE @.intCount <= LEN(@.strValue)
BEGIN
SET @.intCount = @.intCount +1
IF SUBSTRING(@.strValue, @.intCount, 1) NOT LIKE @.TrailingChar
BREAK
ELSE
CONTINUE
END
IF @.RemoveLeading = 1
SET @.strValue =
REVERSE(dbo.fn_removetrailingchars(REVERSE(RIGHT(@.strValue,
LEN(@.strValue) - @.intCount +1 )),@.TrailingChar,0))
ELSE
SET @.strValue = RIGHT(@.strValue, LEN(@.strValue) - @.intCount +1
)

RETURN @.strValue
END

Could be called by: Select dbo.fn_removetrailingchars (SomeColumn,CHAR(32),1) FROM Sometable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanx for answering

but what i mean is a function or starment Called trim which removes the spaces ....and i think it should be used in the insertion

for an example lets say wa have a string " hi " i want to remove the spaces before and after hi

so how can i use it ?

|||Hi,

if you don't want to use my function, simply use LTRIM(RTRIM(Expression))

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Help me!!!

sSQL = "INSERT into order (, [pass], [pass1], [brands], [models], [attention], [address], [town], [code], [county], [country], [dp1], [p2], [fx], [email]) values ('" & i & "', '" & pass & "', '" & pass1 & "', '" &_
brands & "', '" & models & "', '" & attention & ", '" & address & "', '" & town & "', '" & code & "', '" & county & "', '" & country & "', '" & dp1 & "', '" & p2 & ", '" & fx & ",'" & email & "')"
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\worldcar\car.mdb "
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
connection.Mode = 0
'Open the connection to the database
connection.Open(sConnString)
connection.execute(sSQL)<<<<line44

response.redirect ("E:\car1\welcome.htm")
'Done. Close the connection object
connection.Close

this is my asp coding and i keep on getting this problem

Syntax error in INSERT INTO statement.
/worldcar/add_to_database1.asp, line 44

It looks like the code pasted above is missing snigle-quotes at a few places:

...

& attention & ", '"

...

& p2 & ", '"

& fx & ",'"

If firther troubleshooting is needed, I suggest you print the sSQL string before the failing line to examine the string for correctness.

As a side-note, I would recommend looking into parameters instead of string concatenation to avoid exposure to SQL-injection.

Help me!!!

sSQL = "INSERT into order (, [pass], [pass1], [brands], [models], [attention], [address], [town], [code], [county], [country], [dp1], [p2], [fx], [email]) values ('" & i & "', '" & pass & "', '" & pass1 & "', '" &_
brands & "', '" & models & "', '" & attention & ", '" & address & "', '" & town & "', '" & code & "', '" & county & "', '" & country & "', '" & dp1 & "', '" & p2 & ", '" & fx & ",'" & email & "')"
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\worldcar\car.mdb "
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")
connection.Mode = 0
'Open the connection to the database
connection.Open(sConnString)
connection.execute(sSQL)<<<<line44

response.redirect ("E:\car1\welcome.htm")
'Done. Close the connection object
connection.Close

this is my asp coding and i keep on getting this problem

Syntax error in INSERT INTO statement.
/worldcar/add_to_database1.asp, line 44

It looks like the code pasted above is missing snigle-quotes at a few places:

...

& attention & ", '"

...

& p2 & ", '"

& fx & ",'"

If firther troubleshooting is needed, I suggest you print the sSQL string before the failing line to examine the string for correctness.

As a side-note, I would recommend looking into parameters instead of string concatenation to avoid exposure to SQL-injection.

Help me resolve this error."Missing parameter field current value." Code Attach

Hi ,
am getting this error."Missing parameter field current value." When i Use this line of code
crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues() it says "ApplyCurrentValues is not a member of CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition".How do i get rid of this error.
Also is there anyway that without Exporting , i Can Print the Report from vb.net application directly using default Printer.
Code is:
Private Sub Btn_Export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Export.Click
Try
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = "WASA00150"
.DatabaseName = "iCalls"
.UserID = "sa"
.Password = "courage"
End With
Me.CrystalReportViewer1.ParameterFieldInfo.Clear()
If Me.txtSTdate.Text.Trim.Length > 0 Then
Me.CrystalReportViewer1.ReportSource = Server.MapPath("iCalls_CrystalReport_Department.rpt")
Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
Dim Per As New ParameterField
Per.ParameterFieldName = "Period"
Dim Period_Value As New ParameterRangeValue
Period_Value.StartValue = Me.txtSTdate.Text
Period_Value.EndValue = Me.txtCLdate.Text
Per.CurrentValues.Add(Period_Value)
ParamFields.Add(Per)
End If
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
Me.CrystalReportViewer1.RefreshReport()
CrystalReportViewer1.Visible = True
Dim exportPath As String = "D:\SampleCrystalReports\iCalls_Export\test1.pdf"
Dim crExportOptions As ExportOptions
Dim crDestOptions As New DiskFileDestinationOptions
crDestOptions.DiskFileName = exportPath
crExportOptions = crReportDocument.ExportOptions
crExportOptions.DestinationOptions = crDestOptions
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()
crReportDocument.Export()
Catch ex As Exception
lblmsg.Text = ex.Message.ToString
End Try
End Sub
Many Thanks.Not sure if this is it, but looking at the code this line.

'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()

has a ' at the begining, is that supposed to be there ?

help me plz!

I am pretty new to the subject...i would really appreciate if you could
help me with this doubt...

I need to write a C# code which performs the following:

a) connects to the sql server database( I dont know the procedure to
connect to the data base)

b) on a web page i need to display the list of table names and their
associated indexes in a tree view ( i.e. parent node should be the name
of the table and the child nodes should be the index names)

Plz do help me

Thanks a lotHi

You don't give which version of SQL Server you are using, but you should
find most of what you want to do in parts of the Samples that come with the
product, for instance with SQL 2005 there is a dependency explorer and other
SMO examples which will do something similar to what you require. For SQL
2000 check out the DMO examples.

John

"frien" <001frien@.gmail.comwrote in message
news:1167166884.988685.252620@.73g2000cwn.googlegro ups.com...

Quote:

Originally Posted by

>I am pretty new to the subject...i would really appreciate if you could
help me with this doubt...
>
I need to write a C# code which performs the following:
>
a) connects to the sql server database( I dont know the procedure to
connect to the data base)
>
b) on a web page i need to display the list of table names and their
associated indexes in a tree view ( i.e. parent node should be the name
of the table and the child nodes should be the index names)
>
Plz do help me
>
Thanks a lot
>

help me Please!

Hi There!!

I have a code that runs from a different machine and a different site and i am facing a problem that i can not log in to the database.I installed SQL Server on my machine and I created a user "fadila" and SQL Server Authentication and the password is "fadil1977" and the database is "otters" and it is installed as tables and stored procedures but there is not data on these tables.

I used the method provided in the code so, i only change the "connStr" in one place rather than in 20 places and my code is as below. Can you please Help me to connect to the database. I Really..Really appreciate it if you help me to solve it as it causeing me a big head-ache and still get the error message "SQL Server does not exist or access denied" .. please help!!!!

 Friend Shared ReadOnly Property connStr() As String
 Get
 Return String.Format( _
 "Data Source={0};Initial Catalog=Otters;User ID=fadila;Password=fadil1977", _
 DatabaseMachine)
 End Get
 End Property

your help is appreciated!!!!!!Sure you can connect to the sql server?

Possible reasons:

* Firewall in between.
* Stupid admin. One who did not read that syou need a special service pack (SP3?) to run on 2003, and Winwodows Server 2003 actively blocks SQL Server ports unless this sp is installewd (at least).
* Bad setup for the network, not allowing name resolution.
* Bad database machine name?

Do you homework - there is nothing we can do here to help you, simply because we have no clue what the error is, simply because you do not provide enough info.|||Hi Thona!!

Thank you very much for your help and answering my question. You just said there is no enough information provided, can you please tell me what information is needed so i can post up for you. I just want to make one point, I used before in the previous project the dataset and connect to the database without any problem so, is that what you told me, it does not apply to the dataset and SqlAdaptor as it is a different way toconnect so, i will not have this problem.

I will speak to the IT admin about the you told me and get back to you so, please keep looking to this thread until you solve it for me or you get fed up :)

Thanks again

Help me please with SQL!!!

I was wondering if anybody could help me with this problem for access97 SQL

I want this code to inquiry records that are 3 yrs and older, with a year_type of 1. Can somebody help me code this please!!!!!!!!!!!!

Here's what i have so far:

DELETE [ArchBOILER].[Inspection_Date], (DateDiff("d",[Inspection_Date],Now()))/365 AS Differance, Year_Type
FROM [ArchBOILER]
WHERE ( ( ( ( DateDiff ("d", [Inspection_Date], Now() ) ) / 365 )>=3 )are you using DELETE to to inquiry records ?|||yes

Originally posted by Bigced_21
I was wondering if anybody could help me with this problem for access97 SQL

I want this code to inquiry records that are 3 yrs and older, with a year_type of 1. Can somebody help me code this please!!!!!!!!!!!!

Here's what i have so far:

DELETE [ArchBOILER].[Inspection_Date], (DateDiff("d",[Inspection_Date],Now()))/365 AS Differance, Year_Type
FROM [ArchBOILER]
WHERE ( ( ( ( DateDiff ("d", [Inspection_Date], Now() ) ) / 365 )>=3 )

HELP me in this datediff() function....

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.

My code :

datediff(Minute,'01:00:00','23:00:00')

The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)...

Can anybody help ?

Thanks in advance...

You have to try this: datediff(Minute,'23:00:00','01:00:00')|||

suigion:

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.

My code :

datediff(Minute,'01:00:00','23:00:00')

The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)...

Can anybody help ?

Thanks in advance...

Try the link below for how to get correct hours from the SQL Server DateDiff function. Hope this helps.

http://www.stanford.edu/~bsuter/sql-datecomputations.html