get an arithmetic overflow with count(*)

Doing a select count(*) on a table that I believe has more rows than the max
value of an int gives me an arithmetic overflow.
How can I rewrite my query to find the no. of rows ?Hi
Look up COUNT_BIG in Books online.
John
"Hassan" wrote:

> Doing a select count(*) on a table that I believe has more rows than the m
ax
> value of an int gives me an arithmetic overflow.
> How can I rewrite my query to find the no. of rows ?
>
>

Get active directory users from SQL Query Analyser

Get active directory users from SQL Query Analyser
--
I am trying to do a select in SQL Query Analyser to get the list of users in
active directory.
I use the following code:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')
But appears the following message error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
What could be?
Tks.Try moving the ORDER BY clause outside of OPENQUERY. There is also something
a little strange in the outside SELECT; maybe a typo?
SELECT [Name],SN -- Why was this here? :[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
DC=domainname,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
ORDER BY Name
--
"Rui Oliveira" wrote:

> Get active directory users from SQL Query Analyser
> --
> I am trying to do a select in SQL Query Analyser to get the list of users
in
> active directory.
> I use the following code:
> sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
> 'ADSDSOObject', 'adsdatasource'
> SELECT [Name],SN[Last Name]
> FROM OPENQUERY( ADSI,
> 'SELECT Name,SN FROM ''LDAP://servername.domainname.com/CN=Users,
> DC=domainname,DC=com''
> WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
> name')
> But appears the following message error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> What could be?
> Tks.
>

Get a timeout error when trying to export to excel

Hi I generate a report successfully in SQL reporting services . Once
the report is generated I then select the export option to export it
to excel
I then get this error message( see below) after waiting about 20 mins
for the excel file to be generated.
There is a lot of data in this report . There is 1240 pages and there
is about 50 rows for each page. That makes it 62000 rows in the
report.
I'm using excel 2007 on my pc. I've updated sql server 2005 to sp2.
Is there some limitation to the amount of data you can export to
excel.'
thanks in advance
Aidan Geraghty
Server Error in '/Reports' Application.
________________________________________
For more information about this error navigate to the report server on
the local server machine, or enable remote errors
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Exception: For more information about this
error navigate to the report server on the local server machine, or
enable remote errors
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace
below.
Stack Trace:
[Exception: For more information about this error navigate to the
report server on the local server machine, or enable remote errors]
[Exception: An error occurred during rendering of the report.]
[Exception: The operation has timed out. The report server has
canceled the operation.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean
isAbortable, String url, Stream outputStream, String& mimeType,
String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean
isAbortable, String format, String deviceInfo, NameValueCollection
urlAccessParameters, Stream reportStream, String& mimeType, String&
fileNameExtension) +958
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
format, String deviceInfo, NameValueCollection additionalParams,
String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection
urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +64
________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.210
When I look at the log files on the server I find this error message:
2007-03-30 14:12:44 65.53.207.181 50411 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:17:43 65.53.206.137 21323 10.251.129.151 80 HTTP/1.1
GET /Reports/Reserved.ReportViewerWebControl.axd?
ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=RebateCoopFundsUploadSixMonthsReport&ContentDisposition=OnlyHtmlInline&Format=EXCEL
- 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:19:13 65.53.206.137 21501 10.251.129.151 80 HTTP/1.1
GET /reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:23:42 65.53.207.181 50419 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:24:54 10.253.212.85 3385 10.253.212.85 80 HTTP/1.1 GET /
ReportServer?%2fReports
%2fRebateCoopFundsUploadSixMonthsReport&rs:SessionID=refs1e553cirzg45tdvejtuw&rs:command=Render&rs:Format=EXCEL&rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
- 478678319 Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 65.53.206.137 21338 10.251.129.151 80 HTTP/1.1
GET /Reports/Reserved.ReportViewerWebControl.axd?
ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Fri
%20Mar%2030%2014%3A49%3A52%202007 - 478678319
Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 10.251.129.151 3419 10.251.129.151 80 HTTP/1.1
POST /reports/ReportService2005.asmx - 478678319
Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 65.53.207.181 50461 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Abandoned_By_AppPool
DefaultAppPool
2007-03-30 14:27:09 65.53.206.137 21553 10.251.129.151 80 - - - - -
Timer_ConnectionIdle -
2007-03-30 15:07:17 10.253.212.85 3710 10.253.212.85 80 - - - - -
Timer_ConnectionIdle -
2007-03-30 15:07:17 10.253.212.85 3714 10.253.212.85 80 - - - - -
Timer_ConnectionIdle -On Apr 2, 9:04 am, aidan.gerag...@.gmail.com wrote:
> Hi I generate a report successfully in SQL reporting services . Once
> the report is generated I then select the export option to export it
> to excel
> I then get this error message( see below) after waiting about 20 mins
> for the excel file to be generated.
> There is a lot of data in this report . There is 1240 pages and there
> is about 50 rows for each page. That makes it 62000 rows in the
> report.
> I'm using excel 2007 on my pc. I've updated sql server 2005 to sp2.
> Is there some limitation to the amount of data you can export to
> excel.'
> thanks in advance
> Aidan Geraghty
> Server Error in '/Reports' Application.
> ________________________________________
> For more information about this error navigate to the report server on
> the local server machine, or enable remote errors
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Exception: For more information about this
> error navigate to the report server on the local server machine, or
> enable remote errors
> Source Error:
> An unhandled exception was generated during the execution of the
> current web request. Information regarding the origin and location of
> the exception can be identified using the exception stack trace
> below.
> Stack Trace:
> [Exception: For more information about this error navigate to the
> report server on the local server machine, or enable remote errors]
> [Exception: An error occurred during rendering of the report.]
> [Exception: The operation has timed out. The report server has
> canceled the operation.]
> Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean
> isAbortable, String url, Stream outputStream, String& mimeType,
> String& fileNameExtension) +489
> Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean
> isAbortable, String format, String deviceInfo, NameValueCollection
> urlAccessParameters, Stream reportStream, String& mimeType, String&
> fileNameExtension) +958
> Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
> format, String deviceInfo, NameValueCollection additionalParams,
> String& mimeType, String& fileExtension) +84
> Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection
> urlQuery, HttpResponse response) +143
> Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
> context) +75
> System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
> +154
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
> Boolean& completedSynchronously) +64
> ________________________________________
> Version Information: Microsoft .NET Framework Version:2.0.50727.42;
> ASP.NET Version:2.0.50727.210
> When I look at the log files on the server I find this error message:
> 2007-03-30 14:12:44 65.53.207.181 50411 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:17:43 65.53.206.137 21323 10.251.129.151 80 HTTP/1.1
> GET /Reports/Reserved.ReportViewerWebControl.axd?
> ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
> df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=RebateCoopFundsUploadSixMonthsReport&ContentDisposition=OnlyHtmlInline&Format=EXCEL
> - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:19:13 65.53.206.137 21501 10.251.129.151 80 HTTP/1.1
> GET /reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:23:42 65.53.207.181 50419 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:24:54 10.253.212.85 3385 10.253.212.85 80 HTTP/1.1 GET /
> ReportServer?%2fReports
> %2fRebateCoopFundsUploadSixMonthsReport&rs:SessionID=refs1e553cirzg45tdvejtuw&rs:command=Render&rs:Format=EXCEL&rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
> - 478678319 Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 65.53.206.137 21338 10.251.129.151 80 HTTP/1.1
> GET /Reports/Reserved.ReportViewerWebControl.axd?
> ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
> df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Fri
> %20Mar%2030%2014%3A49%3A52%202007 - 478678319
> Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 10.251.129.151 3419 10.251.129.151 80 HTTP/1.1
> POST /reports/ReportService2005.asmx - 478678319
> Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 65.53.207.181 50461 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Abandoned_By_AppPool
> DefaultAppPool
> 2007-03-30 14:27:09 65.53.206.137 21553 10.251.129.151 80 - - - - -
> Timer_ConnectionIdle -
> 2007-03-30 15:07:17 10.253.212.85 3710 10.253.212.85 80 - - - - -
> Timer_ConnectionIdle -
> 2007-03-30 15:07:17 10.253.212.85 3714 10.253.212.85 80 - - - - -
> Timer_ConnectionIdle -
I'm not sure about a limitation in MS Excel 2007; but, in MS Excel
2003, the limit was somewhere around 65000 rows. In my opinion, it
sounds like it might be too much data to stream into Excel. Sorry I
could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

get @@identity from several records

Hi,

I was wondering if you can help.

I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.

What I need to do is after I have created these new records update a field in each of them.

Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?

Ideally I would like to use @.@.identity and datareaders with vb.net.

Cheers

Mark :)

You need to put all the TSQL into a stored procedure. After each insert use SCOPE_IDENTITY() to get the id of the record just inserted.

|||Decorate your table with a new column, let's call it "batchid". It could be a uniqueidentifier.

DECLARE @.batchid uniqueidentifier

SELECT @.batchid=newid()

--The insert

INSERT INTO table (column1,column2 ..... , batchid)
SELECT column1,column2......, @.batchid FROM table WHERE...

--Retrieve the new id:s

SELECT id FROM table WHERE batchid=@.batchid

But it's quite likely that you can insert the correct data directly, instead of having to update it afterwards.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...|||

Although I like the solution of gunteman, I'm just wondering why you can't update the field in the INSERT statement?

|||

Hi,

Thanks for your help. But i would really like to update the field in the insert statement.

Any suggestions?

Cheers

Mark :)

|||

I described it above. If you want to set the new value directly, just include it in your insert statement.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...

If you show us your INSERT and UPDATE statement, we could help you combine them.

generating XML from SQL

Hi all,

Can anybody advice me how can i go about creating an XML from SQL. The need arised when our jobs which create Xmls from the select statement using vbscript hang in the executing state because the file gets a sharing voilation so we have to change the generated filename on the vbscript code frquently so i thought about can i generate it directly from SQL......

Any ideas ?

this solution would be very helpful for me

Thanks in ADVANCE

Jacx

This may help you

XML support in SQL Server 2005 (just in case you're not familiar with what is available):
http://msdn2.microsoft.com/en-us/library/ms345117.aspx#sql2k5xml_topic3

Constructing XML using SQL:
http://msdn2.microsoft.com/en-us/ms178107.aspx

Generating Script

What does N indicate in the following script

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].xxxx' )

Plz can anyone explain

EXEC dbo.sp_executesql @.statement = N'Create View xxxxx'

The 'N' in the likes of N'[dbo].xxxx' and N'Create View xxxxx' indicates an NVARCHAR datatype. This is the datatype that is used for representing UNICODE strings. The NVARCHAR datatype uses two bytes to represent each character whereas the VARCHAR datatype uses one byte to represent each character.

Also, if you are asking for an explanation of the statement:

Code Snippet

EXEC dbo.sp_executesql @.statement = N'Create View xxxxx'

The "sp_executesql" stored procedure is a stored procedure that is used for executing dynamically created SQL statements. In this case the dynamic SQL statement is the "CREATE VIEW ... " piece. And in this case this statement is used to create a view.

|||Thank you...i have also seen from the MSDN help that N denotes unicode strings which uses 2 bytes to represent each character|||That is correct. Nvarchar/Nchar will store UNICODE values.

generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The script will contain a simple select, which depending on which database it is run against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when the script is executed from the command line it will produce a flat file with a list of the users on the database.
http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.
|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The scrip
t will contain a simple select, which depending on which database it is run
against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when
the script is executed from the command line it will produce a flat file wit
h a list of the users on the database.http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generate SQL Script using a DTS

Hi
Each month I back up the structure of our databases by right-clicking
on each database and select Generate SQL Script...

>From the General tab I select script all objects and from the Options
tab I select script database, along with script indexes and script
primary keys.
As we have several databases this can take some time. Is there away to
automate this using a DTS?
Thanks
MattI don't know if DTS is the way to go but you can certainly use DMO to do
this. In fact, the sample code is included with SQL. All you need to do is
modify it to run automatically and then invoke from s DTS package.
Alternatively, you could use the SP_OA family of procedures to do it. At my
last job, we scripted out some objects just this way.
"matt.cottam@.googlemail.com" wrote:

> Hi
> Each month I back up the structure of our databases by right-clicking
> on each database and select Generate SQL Script...
>
> tab I select script database, along with script indexes and script
> primary keys.
> As we have several databases this can take some time. Is there away to
> automate this using a DTS?
>
> Thanks
> Matt
>|||Yes, have a look at SQL DMO library, you could use it within an ActiveX
script in DTS.
"matt.cottam@.googlemail.com" wrote:

> Hi
> Each month I back up the structure of our databases by right-clicking
> on each database and select Generate SQL Script...
>
> tab I select script database, along with script indexes and script
> primary keys.
> As we have several databases this can take some time. Is there away to
> automate this using a DTS?
>
> Thanks
> Matt
>