Anybody seen this error? - 37000 - Storage Allocation record not available

Hi all,
I have a problem with a SQL Server database throwing an error I have
never seen before, and cannot remember ever seeing anywhere else either
(even a google search doesn't throw anything up).
Here's the exact text...
37000
[Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
record not available
The error was originally caused by the database server running out of
space on the system volume (containing the log files), but that has now
been rectified, and we have confirmed that the growth rate of the log
files are smaller than the amount of free space.
What on earth does it mean anyway - "storage allocation record not
available" ?Hi Jonathan
I am not familiar with the error message, but is there any more information
in the SQL Server error log. Does Stopping/Starting SQL server help? Have you
tried defragmenting the disc? Are you using a fixed value for file growth on
all the databases (including tempdb).
John
"jonathan.beckett" wrote:
> Hi all,
> I have a problem with a SQL Server database throwing an error I have
> never seen before, and cannot remember ever seeing anywhere else either
> (even a google search doesn't throw anything up).
> Here's the exact text...
> 37000
> [Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
> record not available
> The error was originally caused by the database server running out of
> space on the system volume (containing the log files), but that has now
> been rectified, and we have confirmed that the growth rate of the log
> files are smaller than the amount of free space.
> What on earth does it mean anyway - "storage allocation record not
> available" ?
>|||First, what version of SQL server are you using?
2nd, is the the error still recurring?
I never seen it before but i expect it has to do wit the SQL engine- which
generates this message when the log was full.
Greetz,
Fling Dutch men.|||> I am not familiar with the error message, but is there any more information
> in the SQL Server error log. Does Stopping/Starting SQL server help? Have you
> tried defragmenting the disc? Are you using a fixed value for file growth on
> all the databases (including tempdb).
> John
It's SQL Server 7 on NT.
Stopping and starting SQL Server does not resolve the problem.
Defragmentation is not an issue either. We're really stumped, and have
opened a call with the makers of the software that is reporting the
error from SQL Server.
> "jonathan.beckett" wrote:
> > Hi all,
> >
> > I have a problem with a SQL Server database throwing an error I have
> > never seen before, and cannot remember ever seeing anywhere else either
> > (even a google search doesn't throw anything up).
> >
> > Here's the exact text...
> > 37000
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
> > record not available
> >
> > The error was originally caused by the database server running out of
> > space on the system volume (containing the log files), but that has now
> > been rectified, and we have confirmed that the growth rate of the log
> > files are smaller than the amount of free space.
> >
> > What on earth does it mean anyway - "storage allocation record not
> > available" ?
> >
> >|||Hate_orphaned_users wrote:
> First, what version of SQL server are you using?
> 2nd, is the the error still recurring?
> I never seen it before but i expect it has to do wit the SQL engine- which
> generates this message when the log was full.
SQL Server 7 on NT
The error is still recurring, but only for a specific operation within
the software that uses the database. Everything else appears to be
fine.
It is very, very strange - we have opened a call with the makers of the
software that is reporting the error (annoyingly it does not appear in
their knowledgebase, and Microsoft have no information on this specific
error from SQL Server either).|||Hi Jonathan,
This is a memory storage allocation error. You can get more details by
running the DBCC MEMORYSTATUS command. For more info go to:
http://support.microsoft.com/kb/907877/en-us
The first I'd check is to make sure you haven't run out if space on
TempDB.
On 27 Dec 2006 03:08:11 -0800, "jonathan.beckett"
<jonathan.beckett@.gmail.com> wrote:
>Hi all,
>I have a problem with a SQL Server database throwing an error I have
>never seen before, and cannot remember ever seeing anywhere else either
>(even a google search doesn't throw anything up).
>Here's the exact text...
>37000
>[Microsoft][ODBC SQL Server Driver][SQL Server]Storage Allocation
>record not available
>The error was originally caused by the database server running out of
>space on the system volume (containing the log files), but that has now
>been rectified, and we have confirmed that the growth rate of the log
>files are smaller than the amount of free space.
>What on earth does it mean anyway - "storage allocation record not
>available" ?

Anybody seen this error? - 37000 - Storage Allocation record not a

> I am not familiar with the error message, but is there any more information
> in the SQL Server error log. Does Stopping/Starting SQL server help? Have you
> tried defragmenting the disc? Are you using a fixed value for file growth on
> all the databases (including tempdb).
> John
It's SQL Server 7 on NT.
Stopping and starting SQL Server does not resolve the problem.
Defragmentation is not an issue either. We're really stumped, and have
opened a call with the makers of the software that is reporting the
error from SQL Server.
[vbcol=seagreen]
> "jonathan.beckett" wrote:
Hi Jonathan
You should be able to run SQL profiler to find out what commands are being
sent to SQL Server and possibly track down what is causing it.
John
"jonathan.beckett" wrote:

>
> It's SQL Server 7 on NT.
> Stopping and starting SQL Server does not resolve the problem.
> Defragmentation is not an issue either. We're really stumped, and have
> opened a call with the makers of the software that is reporting the
> error from SQL Server.
>
>
>

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?
Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:

>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:

>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:
>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

Any one help me out!

Hi

I have a requirement in SSRS where in i need to generate a report in which initially there will be one record (say, a manager of a department and some values corresponding to him ) now i need to provide a drill-down on this employee(mamager) such that the next level of employees reporting to him should be displayed with all the parameters and this has to continue till the leaf level employee.

Thanks in advance.

will the number of levels be known in advance or is it parent-child?

RS is not very good at dealing with an arbitary number of grouping levels.

One way to do it would be define a report with a manager parameter that renders a table of subordiantes. Then in a master report, nest the subreport in a table for all expected levels passing the employee id to the subreport. Problem with that is that it wouldn't export very well.

The other ooption would be to PIVOT the parent-child table i.e. flatten it out into columns per each level, then build your table based on that.

Either way you'll need to know the number of levels in advance.

|||

Yes the number of levels will be known in advance.

i'm a novice in SSRS and does not much about it so can you throw some light on what you have just said.

|||

Please tell me the structure of the data and we'll take it from there.

Is it SQL or OLAP? A table or a dimension? what are the columns or levels?

|||

Hi adam,

I'll get an employee id as a parameter to the stored procedure and then i'll get a dataset which will contain all the data for me for all the employees.

Then from this data set i need to generate the drill down report as

(the root level employee)

(then his subordinates)

(then for each employee in the above level we need to get the lower levels here.)

............................................

And this hierarchy goes on till the last employee.

Thanks in advance.|||

ok, I'll ask the question again.

In format does the data come back? What are the columns?

is it employee_id, manager_id (where manager _id is the employee_id of the manager)

OR

is it employee_level_1_id, employee_level_2_id, employee_level_3_id,......

The latter is what you want to end up with to structure your report.

|||

Hi adam,

The data is in the following format

EmployeeID ManagerID Level(the level of the employee)

Thanks in advance.

|||

OK what about the top level manager, is his manager_id = employee_id or is it NULL?

Does the Level column representative of the number of levels in the hierarchy? In it numeric or a textual descriptor?

I assume you also have employee_name an well as some other employee information.

Can you post a little bit of dummy data?

|||

hi

top level managertop level manager is NULL and you will have columns like

EMP_NO , EMP_NAME , MGR_NO , LEVEL(int) , and some other fields............................

12345 xyz 32141 1

level ranges from 1-some known value

|||

The following is a code example of my implementation. Copy the xml into a file and save as .RDL.

You may need to modify the connection as locally I have SQL 2005 installed as a named instance.

The code flattens a table by performing a join onto itself for every level in the hierarchy. I'll try to come up with a neater solution because it feels like I should be able to use the new PIVOT functionality of SQL.

[code]

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.\sql2005;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>008a7b74-651a-4e19-ad0d-a1af34c884ef</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<DataSetName>dst_employee</DataSetName>
<Top>0.91429cm</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_level">
<rd:DefaultName>l1_employee_level</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_id">
<rd:DefaultName>l1_employee_id</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_name">
<rd:DefaultName>l1_employee_name</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l1_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l1_employee_level">
<GroupExpressions>
<GroupExpression>=Fields!l1_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l1_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_level">
<rd:DefaultName>l2_employee_level</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SlateGray</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_id">
<rd:DefaultName>l2_employee_id</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_name">
<rd:DefaultName>l2_employee_name</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l2_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l2_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l2_employee_id.Value, CStr(Fields!l2_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l2_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l2_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_level">
<rd:DefaultName>l3_employee_level</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#8fa0b0</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_id">
<rd:DefaultName>l3_employee_id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_name">
<rd:DefaultName>l3_employee_name</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l3_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l3_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l3_employee_id.Value, CStr(Fields!l3_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l3_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>7.61904cm</Width>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.55873cm</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
</TableColumns>
<Height>2.15872cm</Height>
</Table>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.91429cm</Height>
<Value>Report3</Value>
</Textbox>
</ReportItems>
<Height>3.60635cm</Height>
</Body>
<rd:ReportID>0b7fa61b-6ddf-4332-bf17-5b42a10a7437</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_employee">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH employee AS
(
SELECT employee_id = 1, employee_name = 'Manager 1', manager_id = NULL, level_no = 1
UNION ALL
SELECT employee_id = 2, employee_name = 'Sub Manager 1', manager_id = 1, level_no = 2
UNION ALL
SELECT employee_id = 3, employee_name = 'Leaf Employee 1', manager_id = 2, level_no = 3
UNION ALL
SELECT employee_id = 4, employee_name = 'Leaf Employee 2', manager_id = 1, level_no = 2
)
SELECT l1_employee_id = l1.employee_id
, l1_employee_name = l1.employee_name
, l1_employee_level = l1.level_no

, l2_employee_id = l2.employee_id
, l2_employee_name = l2.employee_name
, l2_employee_level = l2.level_no

, l3_employee_id = l3.employee_id
, l3_employee_name = l3.employee_name
, l3_employee_level = l3.level_no

FROM employee l1

LEFT JOIN employee l2
ON l1.employee_id = l2.manager_id

LEFT JOIN employee l3
ON l2.employee_id = l3.manager_id

WHERE ISNULL(l1.level_no, 1) = 1
AND ISNULL(l2.level_no, 2) = 2
AND ISNULL(l3.level_no, 3) = 3</CommandText>
<DataSourceName>ds_master</DataSourceName>
</Query>
<Fields>
<Field Name="l1_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_id</DataField>
</Field>
<Field Name="l1_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l1_employee_name</DataField>
</Field>
<Field Name="l1_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_level</DataField>
</Field>
<Field Name="l2_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_id</DataField>
</Field>
<Field Name="l2_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l2_employee_name</DataField>
</Field>
<Field Name="l2_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_level</DataField>
</Field>
<Field Name="l3_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_id</DataField>
</Field>
<Field Name="l3_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l3_employee_name</DataField>
</Field>
<Field Name="l3_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_level</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>22.85714cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

[/code]

any easy way to modify string in dbase

HI I have a database with a string as one of the fields and I need to modify
only a portion of the string. For example below is a record in the database
old string
server1/folder1
want to change to
server2/folder1
Paul G
Software engineer.
REPLACE is one good option. Here is an example:
create table #test111
(scode int,
sdesc varchar(30))
insert into #test111 values (1,'server1/folder1')
insert into #test111 values (2,'server1/folder1')
update #test111 set sdesc = replace(sdesc,'server1/','server2/')
where scode = 1
select * from #test111
"Paul" wrote:

> HI I have a database with a string as one of the fields and I need to modify
> only a portion of the string. For example below is a record in the database
> old string
> server1/folder1
> want to change to
> server2/folder1
> --
> Paul G
> Software engineer.
|||ok thanks for the information
Paul G
Software engineer.
"Absar Ahmad" wrote:
[vbcol=seagreen]
> REPLACE is one good option. Here is an example:
> create table #test111
> (scode int,
> sdesc varchar(30))
> insert into #test111 values (1,'server1/folder1')
> insert into #test111 values (2,'server1/folder1')
> update #test111 set sdesc = replace(sdesc,'server1/','server2/')
> where scode = 1
> select * from #test111
> "Paul" wrote:

any easy way to modify string in dbase

HI I have a database with a string as one of the fields and I need to modify
only a portion of the string. For example below is a record in the database
old string
server1/folder1
want to change to
server2/folder1
--
Paul G
Software engineer.REPLACE is one good option. Here is an example:
create table #test111
(scode int,
sdesc varchar(30))
insert into #test111 values (1,'server1/folder1')
insert into #test111 values (2,'server1/folder1')
update #test111 set sdesc = replace(sdesc,'server1/','server2/')
where scode = 1
select * from #test111
"Paul" wrote:

> HI I have a database with a string as one of the fields and I need to modi
fy
> only a portion of the string. For example below is a record in the databa
se
> old string
> server1/folder1
> want to change to
> server2/folder1
> --
> Paul G
> Software engineer.|||ok thanks for the information
--
Paul G
Software engineer.
"Absar Ahmad" wrote:
[vbcol=seagreen]
> REPLACE is one good option. Here is an example:
> create table #test111
> (scode int,
> sdesc varchar(30))
> insert into #test111 values (1,'server1/folder1')
> insert into #test111 values (2,'server1/folder1')
> update #test111 set sdesc = replace(sdesc,'server1/','server2/')
> where scode = 1
> select * from #test111
> "Paul" wrote:
>

any easy way to modify string in dbase

HI I have a database with a string as one of the fields and I need to modify
only a portion of the string. For example below is a record in the database
old string
server1/folder1
want to change to
server2/folder1
--
Paul G
Software engineer.REPLACE is one good option. Here is an example:
create table #test111
(scode int,
sdesc varchar(30))
insert into #test111 values (1,'server1/folder1')
insert into #test111 values (2,'server1/folder1')
update #test111 set sdesc = replace(sdesc,'server1/','server2/')
where scode = 1
select * from #test111
"Paul" wrote:
> HI I have a database with a string as one of the fields and I need to modify
> only a portion of the string. For example below is a record in the database
> old string
> server1/folder1
> want to change to
> server2/folder1
> --
> Paul G
> Software engineer.|||ok thanks for the information
--
Paul G
Software engineer.
"Absar Ahmad" wrote:
> REPLACE is one good option. Here is an example:
> create table #test111
> (scode int,
> sdesc varchar(30))
> insert into #test111 values (1,'server1/folder1')
> insert into #test111 values (2,'server1/folder1')
> update #test111 set sdesc = replace(sdesc,'server1/','server2/')
> where scode = 1
> select * from #test111
> "Paul" wrote:
> > HI I have a database with a string as one of the fields and I need to modify
> > only a portion of the string. For example below is a record in the database
> >
> > old string
> > server1/folder1
> > want to change to
> > server2/folder1
> > --
> > Paul G
> > Software engineer.

Ansi_Padding Question

Hi all,
How do you determine if ansi_padding was set to on or off when a table
was created? Aside from inserting a record with trailing blanks, I
can't seem to find an answer.
Thanks,
Terryif (@.@.OPTIONS & 16 = 16)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Terry" <wondermutt@.sbcglobal.net> wrote in message
news:6eee4aa9.0310031300.468ccb15@.posting.google.com...
> Hi all,
> How do you determine if ansi_padding was set to on or off when a table
> was created? Aside from inserting a record with trailing blanks, I
> can't seem to find an answer.
> Thanks,
> Terry|||@.@.options only helps you figure out the current value of ANSI_PADDING.
To check the value in effect when a column was created, use COLUMNPROPERTY
SELECT COLUMNPROPERTY(table_id, column_name, 'UsesANSITrim')
Please see Books Online for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Terry" <wondermutt@.sbcglobal.net> wrote in message
news:6eee4aa9.0310031300.468ccb15@.posting.google.com...
> Hi all,
> How do you determine if ansi_padding was set to on or off when a table
> was created? Aside from inserting a record with trailing blanks, I
> can't seem to find an answer.
> Thanks,
> Terry