Microsoft Reporting Technologies

It seems there are several current reporting mechanisms related to .net and Visual Studio. There is Crystal Reports, an integrated reporting system in Visual Studio and a somewhat related reporting system in SQL Server. The last two seem to use similar .xml bases but it's unclear how or if they are related. This does not include (or maybe is does) the Sharepoint reporting system.

I have eliminated the Crystal Report for various reasons after trying it for awhile. Therefore is there some good introductions and/or comparisons between the Visual Studio reporting system and the SQL Server or BI reporting systems since they both seem to use .rdlc and .rdl type definitions. How or are they related?

Try this link where you can find the answers for your questions

http://msdn2.microsoft.com/en-us/library/ms170246.aspx

sql

Microsoft OLE DB Provider for ODBC Drivers error 8007000e

Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
[Microsoft][ODBC Microsoft Access Driver] System resource exceeded

I recieve this error every time I execute SQL statement form ASP page on an Access database.
Could any one help me diagnosing this problem and how to avoid seeing such error messages.

ThanksI FOUND IT!
I FOUND THE SOLUTION.

If there is any one interested in this subject or suffering from it, contact me and I will tell him about the million dollar problem solution.

:p|||Originally posted by Khalid
I FOUND IT!
I FOUND THE SOLUTION.

If there is any one interested in this subject or suffering from it, contact me and I will tell him about the million dollar problem solution.

:p

I have this problem (about access database sending "Resources Exceeded Error") my largest table has 350,000+ Registers. Might this be causing the problem? I tried deleteing 10,000 registers aout of the table but it didn't work.

It just happened after more than 1 year of mu application running perfectly fine.

You can see the error in: http://www.citaris.com/checarplayer.jhtml

Please let me know if you need any other information.

Best regards and thank you very much,.

Alexis.
acastanares@.yahoo.com|||Originally posted by Khalid
I FOUND IT!
I FOUND THE SOLUTION.

If there is any one interested in this subject or suffering from it, contact me and I will tell him about the million dollar problem solution.

:p Khalid, can you help me out? I've been getting this error for a few weeks now. Thanks|||Khalid,
It would be most helpful if you'd post the solution here, rather than make everyone who has this problem email you for it.

VRWL :rolleyes:

Originally posted by Khalid
I FOUND IT!
I FOUND THE SOLUTION.

If there is any one interested in this subject or suffering from it, contact me and I will tell him about the million dollar problem solution.

:p|||I received lately a lot of emails asking about this famous error problem.
this is my solution I have discovered:

ASP is full of bugs, as I discovered lately, but these bugs can be solved by writing code.

My problem was due to Database connections, it seem to me I was doing the perfect thing, opening connections, recordsets, executing queries and finally I destroy every object I create.

THE PROBLEM
===========
If you open only one connection (as I used to do), and run more than one query or recordsets at the same time, what is happening? ASP does not allow you to run more than one query through the same connection at the same time, (though it seems that it is working) so how come? Since ASP does not allow that, it creates its own connections to run the further queries you are executing, and after you are done with them, ASP is not destroying the connections it creates. If you are not destroying your connection objects as well, so you are helping the problem to come up.

WHAT IS HAPPENING
==================
unclosed objects will create a resource leak in memory, and as the times goes, more objects are not closed, and more memory resources are consumed until it reaches to the maximum. And guess what, the famous message will show up! :rolleyes:

SOLUTION
=========
-Define a connection for every query by its own.
-Create you connection object and destroy them your self, don't let ASP do any thing by its self. :D

Hope this information be helpful for every body.

Cheers

;)|||Yes but... Why if i have two servers, one with a single PIII 500 Mhz and 256 Mb Ram and other with Dual PIII 1 ghz an 1 Gb Ram... with the same web page and with the same Operatin System, I got the problem in the Dual PIII and not in the oldest server?|||Hi,
after reading an article, I am using a jdbc:odbc bridge with an excel file setup as a user dsn. This is to import and export from our java applciation and jdbc database to excel. On XP and 2003 server machines, there is no problem. However, on 2000 server machines, they all get 'system resources exceeded' message and fail to do anything.

Can anyone help with identifying the underlying problem and recommend a solution ? Is there a a buggy MS component or something that needs updating? HELP!!!

thanks in advance,
Neil|||iamneil
As I have described previously, you are getting this message because your application is causing memory leak. This happens due to many causes, I have discovered that in my ASP application, IIS is opening more connections when I try to run more than SQL statement at the same time on one connections, so IIS is creating its own connections and it is not closing those connections when done with them.
If the application is working on heavy traffic those unclosed connections will be consuming the resource of the system untill it reaches the maxium.

So try to find out where is your bug that causes a memory leak.

Check out your application and tell me more about it.

Good luck|||Need Help with this issue.

Pretty Sure that the Problem is that there is a Memory Leak within the Page. It randomly just started Popping Up Non-Stop about Temporary Disk Space.

But here's where I need help - I'm a new webmaster at one of AAA Auto Clubs Divisions, and the Previous webmaster loved ASP Scripting. I personally favor PHP, because I have more experience with it.

But The Employees Here need to get this working up and running ASAP -

It's Pretty Obvious that The Script Used is from ASP Calendar -- But I can't just simply Download another Version of ASP Calendar and Overwrite the File because the Files Have Been Modified to Work Better for the Employees Uses.

Here Is the Exact Error Message I Am Receiving

Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
[Microsoft][ODBC Microsoft Access Driver] Not enough space on temporary disk.
/Calendar/calendar.asp, line 222

Here is the Coding For calendar.asp



--
6 hours trying to find what this error code was from, how it was happening, and trying to find the resource to fix it was Extremely Frustrating.

So if you can help in anyway, it will be greatly appreciated. Plus if you throw in some Educational snippits (comments in the code) of What this part of the script does and what this part of the script does. It would help tremondously!|||<!-- #include file ="dsn.asp" -->
<!-- #include file="adovbs.inc" -->
<%
' Name: ASP Calendar
' Author: Manohar Kamath
' Version: 1.0
' Copyright Manohar Kamath. Original calendar copyright Wrox Press
%>
<%
' catch department and status name from login page
Dim Dept, intStat, strEvents
Dept=Request("Dept")
intStat=Request("intStat")
strEvents=""
%>
<%
'------------------
' This function finds the last date of the given month
'------------------
Function GetLastDay(intMonthNum, intYearNum)
Dim dNextStart
If CInt(intMonthNum) = 12 Then
dNextStart = CDate( "1/1/" & intYearNum)
Else
dNextStart = CDate(intMonthNum + 1 & "/1/" & intYearNum)
End If
GetLastDay = Day(dNextStart - 1)
End Function

'----------------------
' This routine prints the individual table divisions for days of the month
'----------------------
Sub Write_TD(sValue, sClass)
Response.Write " <TD ALIGN='RIGHT' WIDTH=14% HEIGHT=80 VALIGN='top' CLASS='" & sClass & "'> " & sValue & "</TD>" & vbCrLf
End Sub

' Constants for the days of the week
Const cSUN = 1, cMON = 2, cTUE = 3, cWED = 4, cTHU = 5, cFRI = 6, cSAT = 7

' Get the name of this file
sScript = Request.ServerVariables("SCRIPT_NAME")

' Check for valid month input
If IsEmpty(Request("MONTH")) OR NOT IsNumeric(Request("MONTH")) Then
datToday = Date()
intThisMonth = Month(datToday)
ElseIf CInt(Request("MONTH")) < 1 OR CInt(Request("MONTH")) > 12 Then
datToday = Date()
intThisMonth = Month(datToday)
Else
intThisMonth = CInt(Request("MONTH"))
End If

' Check for valid year input
If IsEmpty(Request("YEAR")) OR NOT IsNumeric(Request("YEAR")) Then
datToday = Date()
intThisYear = Year(datToday)
Else
intThisYear = CInt(Request("YEAR"))
End If

strMonthName = MonthName(intThisMonth)
datFirstDay = DateSerial(intThisYear, intThisMonth, 1)
intFirstWeekDay = WeekDay(datFirstDay, vbSunday)
intLastDay = GetLastDay(intThisMonth, intThisYear)

' Get the previous month and year
intPrevMonth = intThisMonth - 1
If intPrevMonth = 0 Then
intPrevMonth = 12
intPrevYear = intThisYear - 1
Else
intPrevYear = intThisYear
End If

' Get the next month and year
intNextMonth = intThisMonth + 1
If intNextMonth > 12 Then
intNextMonth = 1
intNextYear = intThisYear + 1
Else
intNextYear = intThisYear
End If

' Get the last day of previous month. Using this, find the sunday of
' last week of last month
LastMonthDate = GetLastDay(intLastMonth, intPrevYear) - intFirstWeekDay + 2
NextMonthDate = 1

' Initialize the print day to 1
intPrintDay = 1

%>

<html>
<head>
<meta HTTP-EQUIV="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Vacation Schedule</title>
<style TYPE="text/css">
TD.NON {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#C0C0C0; font-weight :normal;}
TD.TOP {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#0000FF; font-weight :bold;}
TD.Some {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#000000; font-weight :normal;}

A.NOEVENT:Link {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#000000; font-weight :normal; text-decoration: none;}
A.NOEVENT:Visited {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#000000; font-weight :normal; text-decoration: none;}

A.EVENT:Link {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#000000; font-weight :bold; text-decoration: none;}
A.EVENT:Visited {font-family :Tahoma, Verdana, Arial; font-size :12px; color :#000000; font-weight :bold; text-decoration: none;}

A.NORMAL:Link {font-family :Verdana, Arial; font-size :12px; color :#0000FF; font-weight :normal; text-decoration: none;}
A.NORMAL:Visited {font-family :Verdana, Arial; font-size :12px; color :#0000FF; font-weight :normal; text-decoration: none;}
</style>
</head>

<body LEFTMARGIN="3" BGCOLOR="#FFFFFF" TEXT="#000000">
<p> </p>
<h2 align="center">Vacation Schedule Calendar</h2>
<h3 align="center"><%=Dept %></h3>
<p align=center><a href="http://links.10026.com/?link=default.asp">Choose a different department/branch</a></p>
<FORM ACTION="<% =sScript%>?Dept=<% =Dept %>&intStat=<% =intStat %>" METHOD=GET >
<font face="Verdana, Arial" size="2">

<div align="center">Select month and year:
<SELECT NAME=MONTH>
<OPTION VALUE=1 SELECTED>Jan
<OPTION VALUE=2>Feb
<OPTION VALUE=3>Mar
<OPTION VALUE=4>Apr
<OPTION VALUE=5>May
<OPTION VALUE=6>Jun
<OPTION VALUE=7>Jul
<OPTION VALUE=8>Aug
<OPTION VALUE=9>Sep
<OPTION VALUE=10>Oct
<OPTION VALUE=11>Nov
<OPTION VALUE=12>Dec
</SELECT>
<SELECT NAME=YEAR>
<option value="2006" selected>2006</option>
</SELECT>
<input type="hidden" name="Dept" value=<% =Dept %>>
<input type="hidden" name="intStat" value=<% =intStat %>>
<input type="submit" name="Submit" value="Go!">
<! INPUT TYPE="Image" NAME="" src="http://pics.10026.com/?src=images/go.gif" BORDER="0" WIDTH="35" HEIGHT="20">
</div>
</font>
</FORM>
<table ALIGN="CENTER" BORDER="1" CELLSPACING="0" CELLPADDING="2" BGCOLOR="White" BORDERCOLOR="Gray" width="75%">
<tr><td>
<table BORDER="1" CELLPADDING="1" CELLSPACING="0" BGCOLOR="#FFFFFF" width="100%">
<tr HEIGHT="18" BGCOLOR="Silver">
<td WIDTH="14%" HEIGHT="18" ALIGN="LEFT" VALIGN="MIDDLE">
<div align="center"><a href="http://links.10026.com/?link=<% =sScript%>?month=<% =IntPrevMonth %>&year=<% =IntPrevYear %>&Dept=<% =Dept %>&intStat=<% =intStat %>"><img src="http://pics.10026.com/?src=images/prev.gif" WIDTH="14" HEIGHT="14" BORDER="0" ALT="Previous Month"></a></div>
</td>
<td WIDTH="*" COLSPAN="5" ALIGN="CENTER" VALIGN="MIDDLE" CLASS="SOME"><font size=+1><b>
<% = strMonthName & " " & intThisYear %>
</b> </font></td>
<td WIDTH="14%" HEIGHT="18" ALIGN="RIGHT" VALIGN="MIDDLE">
<div align="center"><a href="http://links.10026.com/?link=<% =sScript %>?month=<% =IntNextMonth %>&year=<% =IntNextYear %>&Dept=<% =Dept %>&intStat=<% =intStat %>"><img src="http://pics.10026.com/?src=images/next.gif" WIDTH="14" HEIGHT="14" BORDER="0" ALT="Next Month"></a></div>
</td>
</tr>
<tr>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Sunday</td>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Monday</td>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Tuesday</td>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Wednesday</td>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Thursday</td>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Friday</td>
<td ALIGN="center" CLASS="SOME" WIDTH="14%" HEIGHT="15" VALIGN="BOTTOM">Saturday</td>
</tr>

<%

' Initialize the end of rows flag to false
EndRows = False
Response.Write vbCrLf

' Loop until all the rows are exhausted
Do While EndRows = False

' Start a table row
Response.Write " <TR>" & vbCrLf
' This is the loop for the days in the week
For intLoopDay = cSUN To cSAT
' If the first day is not sunday then print the last days of previous month in grayed font
If intFirstWeekDay > cSUN Then
Write_TD LastMonthDate, "NON"
LastMonthDate = LastMonthDate + 1
intFirstWeekDay = intFirstWeekDay - 1
' The month starts on a sunday
Else
' If the dates for the month are exhausted, start printing next month's dates
' in grayed font
If intPrintDay > intLastDay Then
Write_TD NextMonthDate, "NON"
NextMonthDate = NextMonthDate + 1
EndRows = True
Else
' If last day of the month, flag the end of the row
If intPrintDay = intLastDay Then
EndRows = True
End If

dToday = CDate(intThisMonth & "/" & intPrintDay & "/" & intThisYear)

' Open a record set of schedules
Set Rs = Server.CreateObject("ADODB.RecordSet")

sSQL = "SELECT * FROM tEvents WHERE " & _
"Start_Date =#" & dToday & "# " & _
"AND (Dept='" & Dept & _
"' OR Dept='all')" & _
" ORDER BY Start_Date"

' Open the RecordSet with a static cursor. This cursor provides bi-directional navigation
Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

If NOT Rs.EOF Then

bEvents = True
Do While NOT Rs.EOF

' build list of events for day
'If logged in as manager or during initial entry period, include delete option, else display only names
If intStat = 2 AND Rs("Dept") <> "all" Then
strEvents = strEvents & Rs("Event_Title") & "<a href='delete.asp?Event_ID=" & Rs("Event_ID") & "&Dept=" & Dept & "&intStat=" & intStat & "'><font size=-2 color=red>DEL</font></a><br>"
Else
strEvents = strEvents & Rs("Event_Title") & "<br>"
End If

Rs.MoveNext

Loop

Else

bEvents = False

End If

Rs.Close
Set Rs = Nothing

' If the event flag is not raise for that day, print it in a plain font
If bEvents = False Then
Write_TD intPrintDay, "SOME"
Else
Write_TD intPrintDay & "<br>" & strEvents, "SOME"
strEvents = ""

End If

End If


' Increment the date. Done once in the loop.

intPrintDay = intPrintDay + 1
'clear list of events
strEvents = ""
End If

' Move to the next day in the week

Next
Response.Write " </TR>" & vbCrLf

Loop

%>
</table>
</td></tr>
</table>|||I FOUND IT!
I FOUND THE SOLUTION.

If there is any one interested in this subject or suffering from it, contact me and I will tell him about the million dollar problem solution.

:p

I experiencing the same problem I can not find the solution. Please let me know how to solve

Microsoft OLE DB Provider for ODBC Drivers

I am using an system ODBC created to access data on an AS/400.
I can access the data fine, using the following query.
SELECT F0101.* FROM CRPDTA.PILGRIM.CRPDTA.F0101 as F0101
where the first CRPDTA is the DSN name, PILGRIM is the AS/400 catalog,
the second CRPDTA is the SCHEMA, and F0101 is the file name.
When I try to save this query as a view, I receive the following error.
The operation could not be performed because the OLE DB provider
'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC
Driver (32-bit)]Option type out of range.]
OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a]
Can anyone help?
PMGBoy
On several occasion a bug in the ODBC driver causes all sorts of problem
in the client. Try using any third party software, such as WinSQL
(http://synametrics.com/winsql) and see if you get the same behavior.
This would eliminate the possibility of a buggy driver.
jeff.jones@.papermagic.com wrote:
> I am using an system ODBC created to access data on an AS/400.
> I can access the data fine, using the following query.
> SELECT F0101.* FROM CRPDTA.PILGRIM.CRPDTA.F0101 as F0101
> where the first CRPDTA is the DSN name, PILGRIM is the AS/400 catalog,
> the second CRPDTA is the SCHEMA, and F0101 is the file name.
> When I try to save this query as a view, I receive the following error.
> The operation could not be performed because the OLE DB provider
> 'MSDASQL' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC
> Driver (32-bit)]Option type out of range.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]
> Can anyone help?
> PMGBoy
>

Microsoft OLE DB Provider for ODBC Drivers

I am using an system ODBC created to access data on an AS/400.
I can access the data fine, using the following query.
SELECT F0101.* FROM CRPDTA.PILGRIM.CRPDTA.F0101 as F0101
where the first CRPDTA is the DSN name, PILGRIM is the AS/400 catalog,
the second CRPDTA is the SCHEMA, and F0101 is the file name.
When I try to save this query as a view, I receive the following error.
The operation could not be performed because the OLE DB provider
'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [IBM][Client Access Express O
DBC
Driver (32-bit)]Option type out of range.]
OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x8004d00a]
Can anyone help?
PMGBoyOn several occasion a bug in the ODBC driver causes all sorts of problem
in the client. Try using any third party software, such as WinSQL
(http://synametrics.com/winsql) and see if you get the same behavior.
This would eliminate the possibility of a buggy driver.
jeff.jones@.papermagic.com wrote:
> I am using an system ODBC created to access data on an AS/400.
> I can access the data fine, using the following query.
> SELECT F0101.* FROM CRPDTA.PILGRIM.CRPDTA.F0101 as F0101
> where the first CRPDTA is the DSN name, PILGRIM is the AS/400 catalog,
> the second CRPDTA is the SCHEMA, and F0101 is the file name.
> When I try to save this query as a view, I receive the following error.
> The operation could not be performed because the OLE DB provider
> 'MSDASQL' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: [IBM][Client Access Express
ODBC
> Driver (32-bit)]Option type out of range.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]
> Can anyone help?
> PMGBoy
>sql

Metadata Detail

Hi all
I want to get some metadata information for my tables
Some column properties I can get using System tabes and Information Schema
but I can not get some information as below
I want to get information of column properties Identity Seed, Identity
Increment, Is Rowguid , Formula value, Description for my any column which
this property applicable.
Any help will be highly appreciated
Thanks> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT. For
"Description" use system function fn_listextendedproperty and for "Formula
value" check system table syscomments.
Example:
use northwind
go
create table dbo.t (
colA int not null identity,
colB uniqueidentifier ROWGUIDCOL not null default(newid()),
colC as power(2, colA),
colD as colA % 10
)
go
select
ordinal_position,
column_name,
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else 'No'
end as [IsIdentity],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_seed(table_name)) else '' end as [ident_seed],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_incr(table_name)) else '' end as [ident_incr],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_current(table_name)) else '' end as [ident_current],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else 'No
'
end as [IsRowGuidCol],
coalesce(sc.[text], '') as [Formula]
from
information_schema.columns as c
left join
syscomments as sc
on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
sc.[id] and sc.number = c.ordinal_position
where
table_name = 't'
order by
ordinal_position
go
drop table t
go
AMB
"AM" wrote:

> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information Schem
a
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>
>|||Thanks
It helps me a lot
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
which
> Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT.
For
> "Description" use system function fn_listextendedproperty and for "Formula
> value" check system table syscomments.
> Example:
> use northwind
> go
> create table dbo.t (
> colA int not null identity,
> colB uniqueidentifier ROWGUIDCOL not null default(newid()),
> colC as power(2, colA),
> colD as colA % 10
> )
> go
> select
> ordinal_position,
> column_name,
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else
'No'
> end as [IsIdentity],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_seed(table_name)) else '' end as [ident_seed],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_incr(table_name)) else '' end as [ident_incr],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_current(table_name)) else '' end as [ident_current],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else
'No'
> end as [IsRowGuidCol],
> coalesce(sc.[text], '') as [Formula]
> from
> information_schema.columns as c
> left join
> syscomments as sc
> on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
> sc.[id] and sc.number = c.ordinal_position
> where
> table_name = 't'
> order by
> ordinal_position
> go
> drop table t
> go
>
> AMB
>
> "AM" wrote:
>
Schema
which|||See if this helps:
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"AM" wrote:

> Thanks
> It helps me a lot
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
> which
> For
> 'No'
> 'No'
> Schema
> which
>
>|||Also check out following link for " Schema: How do I show the description
property of a column? ":
http://www.aspfaq.com/show.asp?id=2244
"AM" <anonymous@.examnotes.net> wrote in message
news:%23WseefgOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information
> Schema
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column
> which
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>

Metadata cleanup has deleted metadata at the Subscriber incorrectly?

Hi,

We have Microsoft SQL Server 2005 SP1 based merge replication application. This system consist of 1 publisher/distributor and about 700 subscribers. Publisher is MS SQL Server 2005 standard SP1 and subscribers are mostly MS SQL Server Express SP1s.

We started this system in September last year with migrating data as region based with one area at time. Everything went well until January 2007 when over 500 users were using new system. What is even strange that most errors comes on areas which were moved to new system on week number 2 on 2007 (8.1.2007->)

I have double and cross checked that these publications have exact the same parameteters as working publications.

The error message which comes on subscriber is following:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199401) Get help: http://help/MSSQL_REPL-2147199401

When this error comes client subscription should not be expired yet. So subscription is expiring incorrectly earlier than expected. After reinit subcriber can syncronize without any errors. For now machines suffering of this error have been different machines so there is no certain information if this error is repeatable on same machine.

Real bad thing is that if user has't syncronized in regular basis there is possibility to that data may be lost because the reinit must be done without upload as error message says.

Regards,

Jouko

We have now installed SP2 to over 650 machines and we are still getting this error. So it seems obvious that SP2 do NOT fix this error. We have now open second time support case to Microsoft about this issue. First time we got answer to apply SP2 to all machines that are involved with replication.

Jouko

Messages stuck in sys.transmission_queue

Hi,

At my company, we're trying to use service broker to create a client-server system where there is a head office machine and multiple outlets registered with that head office. My problem is that sometimes when a branch sends a message to the head office, it just seems to sit in the transmission queue and never gets sent. If I run a script that forcibly ends the conversations on the client machine (with cleanup), storing the message bodies and then resend them, they seem to get through fine.

The way that we send messages is by calling a t-sql stored procedure from a c# application using SqlCommand (don't know if this should make any difference).

If I monitor the Head Office machine and one of the Outlets while this is happening, on the HO I get three events in a row:

Broker: Message Classify (1 - Local) Audit Broker Conversation (2 - No Certificate) Broker: Message Undeliverable (1 - Sequenced Message)
The TextData contained in the third event is: This message could not be delivered because the security context could not be retrieved.

The RoleName of the server is Initiator, and the TargetUserName is the name of the service on the Outlet.

On the Outlet I get the following event repeatedly (presumably as it continues to try sending the message) - Broker: Remote Message Acknowledgement (1 - Message With Acknowledgement Sent).

On the client the RoleName also appears to be Initiator, and the TargetUserName is blank.

This would make me suspect that certificates were missing or something, except that if I remove messages from the queue and resend them they seem to get through, and also I've checked both databases and they have the correct certificates.

Any ideas?

Thanks in advance,

AdamThanks for the detailed info, it is always helpfull to have the full details when trying to diagnose an issue.
The most likely culprit in such cases is an account that has more certificates to be picked from when encrypting/signing a message. Whenever SSB has more than one certificates 'valid' to be used for a particular database principal, it will pick the one with the latest expiration date. So sometimes even though the configuration 'seems' correct, SSB will pick the 'wrong' certificate simply because it has multiple choices. The typical account prone to this problems is 'dbo', as certs are being created for dbo for various reasons and by default they are all valid for SSB to pick.
To confirm this problem, the 2nd event in your post (the Audit Broker Conversation one) actually tells which certificate was not found: it has the issuer name and serial number of the cert 'not found' on two of the columns (I can't remember now which ones and the BOL 'ommits' this detail). You can then look into the message sender's database to see why that particular cert was picked.
Certs can actualy be controled whether they are available for SSB to 'pick' by turning off the ACTIVE_FOR_BEGIN_DIALOG option on them (the option refers to certs used for the reverse path from target to initiator as well, despite the name 'begin_dalog'...)|||Thanks for the reply. What you're saying makes sense, and seems possible to me (we re-registered one of the outlets recently which could have caused this). The only problem is that the Audit Broker Conversation event doesn't appear to have the issuer or serial number in any of the columns. Maybe I'm using profiler wrong or something, but I would have thought that just selecting the event would have been enough. Any idea what I'm doing wrong?

Adam

|||

IssuerName is DBUserName and SerialNumber is in TargetLoginName.

Make sure the columns above are selected when defining the trace.

|||I had worked out that the IssuerName was DBUserName, but hadn't noticed the serial number. Thanks for explaining that. The thing is that the IssuerName isn't much use to me because the way I generated the certificates, they all have the same name .

But anyhow, I found a solution to the problem. Basically we had an old database on the same server that had broker enabled as well, and I'm guessing that since they both have the same service names in them, that was causing the problems. I disabled service broker on the old db and everything seems to be working again. The only thing I'm worried about now is how the broker became enabled on the database - but that's not really a service broker issue.

Thanks for the help.

Adam
|||

Whenever SSB finds multiple instances of the same service it assumes a load balancing scenario and will hash the incomming conversations to the available service instances. Specifying a broker_instance in the begin dialog from the initiator would make the application stick to a specific service (database), even if more instances of the service show up on the target.

message queues

I having a fight with what I think is the message queue system when
running stored procedures. If I have this as a sp
print 'Handling First File'
(do some processing on the first file)
print 'Handling Second File'
(do some processing on the second file)
print 'Handling Third File'
(do some processing on the third file)
print 'Done'
I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
'ServerMessage' functions to use print commands as part of my front end
but what happens is that while all the processing is done my dialog
filled by 'ServerMessage.Message' is blank and then all my print
commands come together - London bus style. (Query Analyser does the
same).
This presumably is down to Message Queuing ? One help file I saw says
something like "when you create a Stored Procedure (with queuing
enabled)...." suggesting that you can create a sp with queing
disabled? but I cant find anything in the syntax to stipulate that.
Is there a way round this so that messages are forwarded by the agent
as they are reached in the script and not queued.?
Thanks
GlennIt really has nothing to do with queuing per say it is that the packet does
not get sent to the client until the batch is done or the buffer is full.
In a nutshell since the packets are around 4K in size it doesn't waste many
round trips each time a little bit of info is placed in the buffer. It
waits until it is full until it sends it or when the batch is done. Here is
an example to show this. Comment out the replicate statement and you will
see it doesn't print until the batch is done.
DECLARE @.X INT
SET @.X = 1
WHILE @.X < 100
BEGIN
PRINT CAST(@.X AS VARCHAR(20))
PRINT REPLICATE(' ',8000)
SET @.X = @.X + 1
waitfor delay '00:00:01'
END
Andrew J. Kelly SQL MVP
<glenn.hughes@.luk.net> wrote in message
news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>I having a fight with what I think is the message queue system when
> running stored procedures. If I have this as a sp
> print 'Handling First File'
> (do some processing on the first file)
> print 'Handling Second File'
> (do some processing on the second file)
> print 'Handling Third File'
> (do some processing on the third file)
> print 'Done'
> I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
> 'ServerMessage' functions to use print commands as part of my front end
> but what happens is that while all the processing is done my dialog
> filled by 'ServerMessage.Message' is blank and then all my print
> commands come together - London bus style. (Query Analyser does the
> same).
> This presumably is down to Message Queuing ? One help file I saw says
> something like "when you create a Stored Procedure (with queuing
> enabled)...." suggesting that you can create a sp with queing
> disabled? but I cant find anything in the syntax to stipulate that.
> Is there a way round this so that messages are forwarded by the agent
> as they are reached in the script and not queued.?
> Thanks
> Glenn
>|||You can 'cheat' the messages out faster by raising low-level errors...
RAISERROR('Hack!!!',0,1) WITH NOWAIT
HTH,
Ben
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SqYsHxNGHA.1676@.TK2MSFTNGP09.phx.gbl...
> It really has nothing to do with queuing per say it is that the packet
> does not get sent to the client until the batch is done or the buffer is
> full. In a nutshell since the packets are around 4K in size it doesn't
> waste many round trips each time a little bit of info is placed in the
> buffer. It waits until it is full until it sends it or when the batch is
> done. Here is an example to show this. Comment out the replicate
> statement and you will see it doesn't print until the batch is done.
> DECLARE @.X INT
> SET @.X = 1
> WHILE @.X < 100
> BEGIN
> PRINT CAST(@.X AS VARCHAR(20))
> PRINT REPLICATE(' ',8000)
> SET @.X = @.X + 1
> waitfor delay '00:00:01'
> END
>
> --
> Andrew J. Kelly SQL MVP
>
> <glenn.hughes@.luk.net> wrote in message
> news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>|||Thanks to both of you. Ben, I just love that word "Cheat" its what
programming is all about. The error level idea sounds good. I'll try
that
Cheers
Glenn