i'm using visual basic 6, and crystal reports 10
i made some application that prints some reports..
when i made the distribution package... everithing was ok
the problem came when i tried to install the software
i got following errors
registrywrapper.dll unable to register
or registrywrapper.dll file not found (i've added it to installation)
and the same problem with
cxlibw-1-6.dll
localcon.cll
commonobjectmodel.dll
objectfactory.dll
and crqe.dll
all from crystal reports...Try to register those dlls
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Problems with text field
my visual c++ application must update an field text.
my problems is that i can't update this field with more than 128 caracters.
i obtain this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\ fonttbl{\\f0\\fswiss\\fprq2\\fcharset0 System;}}\\viewkind4\\uc1\\pard\\b\\f0' is too long. Maximum length is 128.
can somebody help me?
Message posted via http://www.sqlmonster.com
You will need to supply more detail about how you are implementing this
update. Are you using a stored procedure? What kind of data objects are you
using in your application? Etc...
Jim
"Simirad Iulian via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fd773766a99741339d34d68ad1aa712c@.SQLMonster.c om...
> my visual c++ application must update an field text.
> my problems is that i can't update this field with more than 128
> caracters.
> i obtain this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts
> with
> '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\ fonttbl{\\f0\\fswiss\\fprq2\\fcharset0
> System;}}\\viewkind4\\uc1\\pard\\b\\f0' is too long. Maximum length is
> 128.
> can somebody help me?
> --
> Message posted via http://www.sqlmonster.com
my problems is that i can't update this field with more than 128 caracters.
i obtain this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\ fonttbl{\\f0\\fswiss\\fprq2\\fcharset0 System;}}\\viewkind4\\uc1\\pard\\b\\f0' is too long. Maximum length is 128.
can somebody help me?
Message posted via http://www.sqlmonster.com
You will need to supply more detail about how you are implementing this
update. Are you using a stored procedure? What kind of data objects are you
using in your application? Etc...
Jim
"Simirad Iulian via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fd773766a99741339d34d68ad1aa712c@.SQLMonster.c om...
> my visual c++ application must update an field text.
> my problems is that i can't update this field with more than 128
> caracters.
> i obtain this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts
> with
> '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\ fonttbl{\\f0\\fswiss\\fprq2\\fcharset0
> System;}}\\viewkind4\\uc1\\pard\\b\\f0' is too long. Maximum length is
> 128.
> can somebody help me?
> --
> Message posted via http://www.sqlmonster.com
Wednesday, March 28, 2012
Problems with string value "one space"
Hello,
I've been discovering weird problems in my application, and after thorough
research, going to lower and lower levels, until I've found the following:
The setup:
MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
collation). The problems are when there is an entry with one character, code
0x20 (ASCII 32; space).
The first surprise hit me when I discovered that LEN function gives the
length of the value, NOT INCLUDING the trailing spaces. Since there is no
function that takes into account the trailing spaces, the only way to get
the length of the field seems to be something like Len(myField+'X')-1.
Awkward, if you ask me. Is there a more preferred way?
The other, which actually caused the problems in my system, was that
querying for zero-length value, it returned the record with the single
space!
SELECT * FROM MyTable WHERE myField=''
So, to avoid this, I can use the following query:
SELECT * FROM MyTable WHERE myField+'X'='X'
I assume that this behaviour might be connected with collation, but I'd hate
to select binary sort for this language-related value. What is happening
here, and how to fix it?
Thanks,
Pavils"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23SFUuWKXGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I've been discovering weird problems in my application, and after thorough
> research, going to lower and lower levels, until I've found the following:
> The setup:
> MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
> collation). The problems are when there is an entry with one character,
> code 0x20 (ASCII 32; space).
> The first surprise hit me when I discovered that LEN function gives the
> length of the value, NOT INCLUDING the trailing spaces. Since there is no
> function that takes into account the trailing spaces, the only way to get
> the length of the field seems to be something like Len(myField+'X')-1.
> Awkward, if you ask me. Is there a more preferred way?
>
datalength
> The other, which actually caused the problems in my system, was that
> querying for zero-length value, it returned the record with the single
> space!
> SELECT * FROM MyTable WHERE myField=''
> So, to avoid this, I can use the following query:
> SELECT * FROM MyTable WHERE myField+'X'='X'
> I assume that this behaviour might be connected with collation, but I'd
> hate to select binary sort for this language-related value. What is
> happening here, and how to fix it?
This is unrelated to collation. Trailing spaces are ignored in string
comparisons in SQL Server. To compare strings without ignoring trailing
spaces use LIKE.
EG:
select datalength('a ')
select 1 where '' = ' '
select 1 where '' like ' '
David
I've been discovering weird problems in my application, and after thorough
research, going to lower and lower levels, until I've found the following:
The setup:
MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
collation). The problems are when there is an entry with one character, code
0x20 (ASCII 32; space).
The first surprise hit me when I discovered that LEN function gives the
length of the value, NOT INCLUDING the trailing spaces. Since there is no
function that takes into account the trailing spaces, the only way to get
the length of the field seems to be something like Len(myField+'X')-1.
Awkward, if you ask me. Is there a more preferred way?
The other, which actually caused the problems in my system, was that
querying for zero-length value, it returned the record with the single
space!
SELECT * FROM MyTable WHERE myField=''
So, to avoid this, I can use the following query:
SELECT * FROM MyTable WHERE myField+'X'='X'
I assume that this behaviour might be connected with collation, but I'd hate
to select binary sort for this language-related value. What is happening
here, and how to fix it?
Thanks,
Pavils"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23SFUuWKXGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I've been discovering weird problems in my application, and after thorough
> research, going to lower and lower levels, until I've found the following:
> The setup:
> MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
> collation). The problems are when there is an entry with one character,
> code 0x20 (ASCII 32; space).
> The first surprise hit me when I discovered that LEN function gives the
> length of the value, NOT INCLUDING the trailing spaces. Since there is no
> function that takes into account the trailing spaces, the only way to get
> the length of the field seems to be something like Len(myField+'X')-1.
> Awkward, if you ask me. Is there a more preferred way?
>
datalength
> The other, which actually caused the problems in my system, was that
> querying for zero-length value, it returned the record with the single
> space!
> SELECT * FROM MyTable WHERE myField=''
> So, to avoid this, I can use the following query:
> SELECT * FROM MyTable WHERE myField+'X'='X'
> I assume that this behaviour might be connected with collation, but I'd
> hate to select binary sort for this language-related value. What is
> happening here, and how to fix it?
This is unrelated to collation. Trailing spaces are ignored in string
comparisons in SQL Server. To compare strings without ignoring trailing
spaces use LIKE.
EG:
select datalength('a ')
select 1 where '' = ' '
select 1 where '' like ' '
David
Problems with Stored Procedure Call
I have developed an application that interfaces with SQL 2000 via stored procedure calls. The execution time of a particular stored procedure takes approximately 2 seconds on average, but we see spikes every so often up to 150 ~ 200 seconds. The timing of the spikes are erratic and do not correlate with any blocking or maintenance job runs. We have reviewed the stored procedure and optimized it to the nth degree and still no improvement. Any suggestions on how I can track down what is causing the spikes?
index fragmentation
database statictics
disk i/o
hope this helps,
Derek
sqlproblems with sqlreader
Hi everyone,
I am writing a simple login application in asp 2.0. I have spent quite a little bit of time of this error so any help would be greatly appreciated.
Here is the code i am using.
Dim cnAs SqlConnectionDim cmdAs SqlCommand
Dim sqlAsString ="select password from attendant where " & _
" ((username = @.username) and (password = @.password)"
cn =
New SqlConnection("Data Source=z-davis\sqlexpress;Initial Catalog=Trainingdb;Integrated Security=True")' cn = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("TrainingdbConnectionString").ToString)'I have tried both connection strings and get the same error.
cmd =New SqlCommand(sql, cn)
cmd.Parameters.Add(
"@.username", SqlDbType.VarChar, 50)cmd.Parameters(
"@.username").Value = txtuser.Textcmd.Parameters.Add(
"@.password", SqlDbType.VarChar, 50)cmd.Parameters(
"@.password").Value = txtpass.Textcn.Open()
Dim myreaderAs SqlDataReadermyreader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
'The line above is where i get the error.
If myreader.Read()ThenFormsAuthentication.RedirectFromLoginPage(txtuser.Text,
False)ElseResponse.Write(
"Try again")After stepping through the code the error stops on this line:
myreader = cmd.executereader(commandbehavior.closeconnection)
The error that is generated is: Incorrect syntax near ')'
Any help would be greatly appreciated or any other code snippets
Thanks,
notrosh
In this line you have 3 open parentheses and only 2 close parentheses.Dim sqlAsString ="select password from attendant where " & _
" ((username = @.username) and (password = @.password)"
Remove the first open parenthesis and you will have better luck.
Thanks,
Sometimes you need an outside eye to see an error that simple.
zach davis
|||Or you need to have made that same sort of error a few dozen times![Wink [;)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_u_Dzllaqr7m47C-BRDp6AwsdcH0qdhvqXrcrabXxk_Fpn3QHs2IyaX2-ZrTou3yHdI6uMI1dKb94ALEL1PdF7PBMBb0gMXTjCSPjmtVNC9sdCKmgpRUA=s0-d)
I've learrned to count my parentheses using my fingers. Working left to right in my SQL statement, I put up a finger each time I open one, and put down a finger each time I close one. When I get to the last character, if I have any fingers sticking up, I know I have a problem. Pretty silly approach, but it works for me.
problems with SQLDMO.dll
i have a vb.net application that uses SQL-DMO. the development machine is
WinXP
when i build a setup for my application and then install this on a clean
WinXP test machine it all runs fine
but when i install it on a Win2000 test machine i get the following error -
the sqldmo.dll registers successfully - but just gives this error message.
[SQL-DMO]Invalid Paramter Type; must be string or ordinal......
i have followed all the instructions thru here:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;248241
but still cannot get it to run. any ideas as to what may be causing this? is
there different versions of sqldmo.dll that i need to run for win2k?
Cheers,
Craighi Craig,
"Craig G" <craig.gamble@.yarrasoftware.com> ha scritto nel messaggio
news:%23lsbQskmEHA.3340@.TK2MSFTNGP14.phx.gbl...
> i have a vb.net application that uses SQL-DMO. the development machine is
> WinXP
> when i build a setup for my application and then install this on a clean
> WinXP test machine it all runs fine
> but when i install it on a Win2000 test machine i get the following
error -
> the sqldmo.dll registers successfully - but just gives this error message.
> [SQL-DMO]Invalid Paramter Type; must be string or ordinal......
> i have followed all the instructions thru here:-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;248241
> but still cannot get it to run. any ideas as to what may be causing this?
is
> there different versions of sqldmo.dll that i need to run for win2k?
actually not that I'm aware of...
personally I've built my (InnoSetup) SQL-DMO install package on a WinXP sp1
box, and deployed with success on Win98, Win2k, XP and Win2003..
the list of files I ditribute is like following:
; not licensed by redist.txt but available after installation of MDAC2.6
..\WINDOWS\SYSTEM\odbcbcp.dll; DestDir: WinSys ; sharedfile
; not licensed by redist.txt but available after installation of MDAC2.6
..\WINDOWS\SYSTEM\sqlwoa.dll ; DestDir: WinSys
; not licensed by redist.txt but available after installation of MDAC2.6
..\WINDOWS\SYSTEM\sqlwid.dll ; DestDir: WinSys
..\Programmi\Microsoft SQL Server\80\Tools\Binn\w95scm.dll; DestDir:
DestinationFolder\Binn
..\WINDOWS\SYSTEM\sqlunirl.dll ; DestDir: WinSys
..\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlresld.dll; DestDir:
DestinationFolder\Binn
..\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll; DestDir:
DestinationFolder\Binn
; not licensed by redist.txt but available after installation of MDAC2.6
..\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqlsvc.RLL;
DestDir: DestinationFolder\Binn\Resources\1033
; not licensed by redist.txt but available after installation of MDAC2.6
..\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\Sqldmo.rll;
DestDir: DestinationFolder\Binn\Resources\1033
..\Programmi\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll; DestDir:
DestinationFolder\Binn ; file to be registered via regserver
DestinationFolder can either be the installation directory of one instance
of Microsoft SqlServer 2000, like ..\Program Files\Microsoft SQL
Server\80\Tools, even if no istance of SQL Server has been installed, or the
installation directory of your application, but the first is preferred.
Please do respect the hierarchy \Binn\Resources\1033 (where 1033 specifies
the language), where needed, in order to grant correct functionality of
Ole-Automation objects.
In order to install SQL-DMO components for MSDE 2000, Microsoft Internet
Explorer 5.5 or higher is required.
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
WinXP
when i build a setup for my application and then install this on a clean
WinXP test machine it all runs fine
but when i install it on a Win2000 test machine i get the following error -
the sqldmo.dll registers successfully - but just gives this error message.
[SQL-DMO]Invalid Paramter Type; must be string or ordinal......
i have followed all the instructions thru here:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;248241
but still cannot get it to run. any ideas as to what may be causing this? is
there different versions of sqldmo.dll that i need to run for win2k?
Cheers,
Craighi Craig,
"Craig G" <craig.gamble@.yarrasoftware.com> ha scritto nel messaggio
news:%23lsbQskmEHA.3340@.TK2MSFTNGP14.phx.gbl...
> i have a vb.net application that uses SQL-DMO. the development machine is
> WinXP
> when i build a setup for my application and then install this on a clean
> WinXP test machine it all runs fine
> but when i install it on a Win2000 test machine i get the following
error -
> the sqldmo.dll registers successfully - but just gives this error message.
> [SQL-DMO]Invalid Paramter Type; must be string or ordinal......
> i have followed all the instructions thru here:-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;248241
> but still cannot get it to run. any ideas as to what may be causing this?
is
> there different versions of sqldmo.dll that i need to run for win2k?
actually not that I'm aware of...
personally I've built my (InnoSetup) SQL-DMO install package on a WinXP sp1
box, and deployed with success on Win98, Win2k, XP and Win2003..
the list of files I ditribute is like following:
; not licensed by redist.txt but available after installation of MDAC2.6
..\WINDOWS\SYSTEM\odbcbcp.dll; DestDir: WinSys ; sharedfile
; not licensed by redist.txt but available after installation of MDAC2.6
..\WINDOWS\SYSTEM\sqlwoa.dll ; DestDir: WinSys
; not licensed by redist.txt but available after installation of MDAC2.6
..\WINDOWS\SYSTEM\sqlwid.dll ; DestDir: WinSys
..\Programmi\Microsoft SQL Server\80\Tools\Binn\w95scm.dll; DestDir:
DestinationFolder\Binn
..\WINDOWS\SYSTEM\sqlunirl.dll ; DestDir: WinSys
..\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlresld.dll; DestDir:
DestinationFolder\Binn
..\Programmi\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll; DestDir:
DestinationFolder\Binn
; not licensed by redist.txt but available after installation of MDAC2.6
..\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\sqlsvc.RLL;
DestDir: DestinationFolder\Binn\Resources\1033
; not licensed by redist.txt but available after installation of MDAC2.6
..\Programmi\Microsoft SQL Server\80\Tools\Binn\Resources\1033\Sqldmo.rll;
DestDir: DestinationFolder\Binn\Resources\1033
..\Programmi\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll; DestDir:
DestinationFolder\Binn ; file to be registered via regserver
DestinationFolder can either be the installation directory of one instance
of Microsoft SqlServer 2000, like ..\Program Files\Microsoft SQL
Server\80\Tools, even if no istance of SQL Server has been installed, or the
installation directory of your application, but the first is preferred.
Please do respect the hierarchy \Binn\Resources\1033 (where 1033 specifies
the language), where needed, in order to grant correct functionality of
Ole-Automation objects.
In order to install SQL-DMO components for MSDE 2000, Microsoft Internet
Explorer 5.5 or higher is required.
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
problems with SQLDMO.dll
i have a vb.net application that uses SQL-DMO. the development machine is
WinXP
when i build a setup for my application and then install this on a clean
WinXP test machine it all runs fine
but when i install it on a Win2000 test machine i get the following error -
the sqldmo.dll registers successfully - but just gives this error message.
[SQL-DMO]Invalid Paramter Type; must be string or ordinal......
i have followed all the instructions thru here:-
http://support.microsoft.com/default...b;en-us;248241
but still cannot get it to run. any ideas as to what may be causing this? is
there different versions of sqldmo.dll that i need to run for win2k?
Cheers,
Craig
Craig,
You may to make sure the your Windows 2000 machine is on the same version of
..NET as was your XP machine.
Regards,
Cory
"Craig G" wrote:
> i have a vb.net application that uses SQL-DMO. the development machine is
> WinXP
> when i build a setup for my application and then install this on a clean
> WinXP test machine it all runs fine
> but when i install it on a Win2000 test machine i get the following error -
> the sqldmo.dll registers successfully - but just gives this error message.
> [SQL-DMO]Invalid Paramter Type; must be string or ordinal......
> i have followed all the instructions thru here:-
> http://support.microsoft.com/default...b;en-us;248241
> but still cannot get it to run. any ideas as to what may be causing this? is
> there different versions of sqldmo.dll that i need to run for win2k?
> Cheers,
> Craig
>
>
WinXP
when i build a setup for my application and then install this on a clean
WinXP test machine it all runs fine
but when i install it on a Win2000 test machine i get the following error -
the sqldmo.dll registers successfully - but just gives this error message.
[SQL-DMO]Invalid Paramter Type; must be string or ordinal......
i have followed all the instructions thru here:-
http://support.microsoft.com/default...b;en-us;248241
but still cannot get it to run. any ideas as to what may be causing this? is
there different versions of sqldmo.dll that i need to run for win2k?
Cheers,
Craig
Craig,
You may to make sure the your Windows 2000 machine is on the same version of
..NET as was your XP machine.
Regards,
Cory
"Craig G" wrote:
> i have a vb.net application that uses SQL-DMO. the development machine is
> WinXP
> when i build a setup for my application and then install this on a clean
> WinXP test machine it all runs fine
> but when i install it on a Win2000 test machine i get the following error -
> the sqldmo.dll registers successfully - but just gives this error message.
> [SQL-DMO]Invalid Paramter Type; must be string or ordinal......
> i have followed all the instructions thru here:-
> http://support.microsoft.com/default...b;en-us;248241
> but still cannot get it to run. any ideas as to what may be causing this? is
> there different versions of sqldmo.dll that i need to run for win2k?
> Cheers,
> Craig
>
>
problems with SQL Server cache invalidation
Hi I am using SQL Server cache invalidation with caching in my application.
I have a master page and several other pages that are referring the master page.
I have specified :
<%@.OutputCacheDuration="60"VaryByParam="*"SqlDependency="CommandNotification" %> on one of my content page.
and I have included theSystem.Data.SqlClient.SqlDependency.Start(connectionstring) in my Application_Start.
My web.config contains this section as well -<caching>
<sqlCacheDependencyenabled="true"><databases>
<addname="BizPartnerV4"connectionStringName="BizPartnerConnectionString"/><addname="DirectBuyBeaverton"connectionStringName="DirectBuyBeavertonConnectionString"/>
</databases></sqlCacheDependency>
I</caching>
have also run the aspnet_regsql utility to enable sqlcache dependency for the database.But my page is not taking the values from cache when the page is refreshed.
Please help.
Hi,
you have two options to work with SQLServer cache:
1.push from the SQL Server
2.polling from Web server
Check your configuration to see if it si configured the right way.
|||What are you using for the database? SQL Server 2005, SQL Express, or SQL Server 2000?
|||I am using SQL Server 2005.
I guess it is using the push model
|||Change VaryByParam from * to None, and see if it is now caching.
|||no it still doesn't cache
|||If you use implement cache with SQLServer 2005 , try the following steps:
1) Enable notifications for the database using the aspnet_regsql.exe tool. >aspnet_regsql.exe -S ".\SQLExpress" -E -d "pubs" -ed
This only needs to be done once for each database.
2) Enable notifications for the table(s) you want to have dependencies on using the aspnet_regsql.exe tool. >aspnet_regsql.exe -S ".\SQLExpress" -E -d "pubs" -et -t "authors"
3) Register the notification in the configuration for the application. <system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="1000" >
<databases>
<add name="PubsDB" connectionStringName="Pubs" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>The poll time specifies how often the application checks to see whether the data has changed.
4) A SQL dependency can then be used on the OutputCache directive: <%@. OutputCache Duration="999999" SqlDependency="Pubs:Authors" VaryByParam="none" %>Or it can be specified directly on a datasource control: <asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite" SqlCacheDependency="PubsDB:Authors" ... />
Hope this helps.
Thanks.
Friday, March 23, 2012
Problems with reporting
Hi,
I am testing MS reporting on VS 2005 Beta2 with the report file (.rdlc) embedded in a Windows application (no server involved).
Previewing the report works fine, but if I want to print it, I apparently must first
click on the print preview icon in the viewer's toolbar and then click on the print icon.
I believe that one should be able to print a report without showing the form containing the viewer; but is there at least a way of forcing the viewer into the print preview state by means of code?
Will something change in this respect in the delivery version?
I am testing MS reporting on VS 2005 Beta2 with the report file (.rdlc) embedded in a Windows application (no server involved).
Previewing the report works fine, but if I want to print it, I apparently must first
click on the print preview icon in the viewer's toolbar and then click on the print icon.
I believe that one should be able to print a report without showing the form containing the viewer; but is there at least a way of forcing the viewer into the print preview state by means of code?
Will something change in this respect in the delivery version?
One more question: is the version of Crystal Reports bundled in VS capable of building a report based on business objects? I could not find any way of performing this task.
Thanks
ReportViewer has SetDisplayMode () method that can be used to switch between Normal and PrintPreview modes.
|||Hi Lev,
the SetDisplayMode method does not exist in the version of VS2005 I am using (Beta2.050215.4400).
Do you have a newer version?
You can download the Release Candidate of Visual Studio 2005 via MSDN Subscriber Downloads.
Problems with remote connections to Express
Hi,
I'm having problems getting a remote connection to sql server express.
The application connects fine on the local PC (using SQL server authentication), however a networked laptop cannot connect to sql.
The PC/Laptop have been networked using the network wizard, and files on the PC are accesible from the laptop.
tcp/ip is enabled on express and the browser service is running. All firewalls have been switched off.
Any ideas?
pse post back the error no and description you gets. Also post back the connection string
Madhu
|||Hi
Have a look at this post, it may be useful.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1576999&SiteID=1
Tailor
|||Thanks both, the problem I have is that it's one of my users having the problem & I'm having trouble replicating it here. The port stuff sounds interesting & I'll have a play with that & report back.Problems with remote connections to Express
Hi,
I'm having problems getting a remote connection to sql server express.
The application connects fine on the local PC (using SQL server authentication), however a networked laptop cannot connect to sql.
The PC/Laptop have been networked using the network wizard, and files on the PC are accesible from the laptop.
tcp/ip is enabled on express and the browser service is running. All firewalls have been switched off.
Any ideas?
pse post back the error no and description you gets. Also post back the connection string
Madhu
|||Hi
Have a look at this post, it may be useful.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1576999&SiteID=1
Tailor
|||Thanks both, the problem I have is that it's one of my users having the problem & I'm having trouble replicating it here. The port stuff sounds interesting & I'll have a play with that & report back.Wednesday, March 21, 2012
Problems with performance when programmatically printing large amounts of pages
We are using VB.NET 2005 to develop a WinForms application that uses SQL Server 2005 Reporting Services to produce reports.
Originally we tried using code found on Bryan's WebLog, http://blogs.msdn.com/bryanke/articles/71491.aspx, but found that for reports of 300 pages it was taking about 2 minutes to render each page.
When using RenderStream to render idividual pages, reports of about 20 pages printed without a problem, but for reports of 300 pages we recieved the follwoing error:
"The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database."
Is there some cache or something that we should be using? Any ideas appreciated.
-- Leah
The rendering code:
Public Function RenderReport(ByVal reportPath As String) As Byte()()
Dim deviceInfo As String = Nothing
Dim format As String = "IMAGE"
Dim firstPage As Byte() = Nothing
Dim encoding As String = ""
Dim mimeType As String = ""
Dim warnings As Warning() = Nothing
Dim reportHistoryParameters As ParameterValue() = Nothing
Dim streamIDs As String() = Nothing
Dim pages As Byte()() = Nothing
deviceInfo = String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat>{1}</DeviceInfo>", "emf", m_strDeviceInfo)
Try
firstPage = m_reportingService.Render(reportPath, format, Nothing, deviceInfo, m_parameterValues, Nothing, Nothing, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
m_numberOfPages = streamIDs.Length + 1
ReDim pages(m_numberOfPages - 1)
Dim iIndex As Integer = 0
pages(0) = firstPage
Dim pageIndex As Integer = 1
For pageIndex = 1 To m_numberOfPages - 1
' original page rendering from Bryan's blog
'deviceInfo = String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat><StartPage>{1}</StartPage{2}</DeviceInfo>", "emf", pageIndex + 1, m_strDeviceInfo)
'pages(pageIndex) = m_reportingService.Render(reportPath, format, Nothing, deviceInfo, m_parameterValues, Nothing, Nothing, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
' Attempt using streams, indexing in to pull back pages in order
iIndex = Convert.ToInt32(streamIDs(pageIndex - 1).Substring(streamIDs(pageIndex - 1).LastIndexOf("_"c) + 1))
pages(iIndex - 1) = m_reportingService.RenderStream(reportPath, format, streamIDs(pageIndex - 1), Nothing, deviceInfo, m_parameterValues, encoding, mimeType)
Next pageIndex
Catch ex As SoapException
m_strErrorMessage = ex.Message
Catch ex As Exception
m_strErrorMessage = ex.Message
End Try
Return pages
End Function
The best way to get streams for printing is to use URL access to render the report. Generate the url and for your first request pass in the first and last page page number (or nothing if you want all pages), as well as rs:PersistStreams=True. Then continue calling render with the same url only instead of passing rs:PersistStreams=True, pass in rs:GetNextStream=True. Do this until you get back an empty response.
Your first url should look like this:
http://<machinename>/ReportServer?<path to report>&<report params in format name=value>&rs:Command=Render&rs:format=IMAGE&rc:OutputFormat=emf&rc:StartPage=1&rc:EndPage=300&rc:PageWidth=11.0in&rc:PageHeight=8.500in&rc:MarginTop=6.350mm&rc:MarginBottom=6.350mm&rc:MarginLeft=6.350mm&rc:MarginRight=6.350mm&rs:PersistStream=True
This will cause Report Server to Render the entire report on the first request and then server back each page on subsequent request untill all pages have been exhausted. Your current code is attempting to render the entire report on each request which will take forever on large reports.
I hope that helps.
|||Thanks for the reply.
This approach is very different to what we are doing (which as you say, was taking forever).
Could you give more information on how to use the url? Is it part of the web service?
|||If you browse to http://<machine>/ReportServer, you are doing what is call Url access. That is viewing report items via a url (as opposed to using a SOAP call). To do this in you app, create a web request object and set the url to the one mentioned above. You can then send the request and read the response stream to get the data.
Look at the following msdn articles for creating and using web requests:
http://msdn2.microsoft.com/en-us/library/bw00b1dc.aspx
http://msdn2.microsoft.com/en-us/library/system.net.httpwebrequest.aspx
The code will be very similiar to your current code, since the SOAP proxy just wraps a WebRequest object. You need to set the url and the Credentials property to make sure you are properly authenticated.
I hope that helps.
|||So there was no way to get the RenderStream code I posted above to work? :)
It is just that since it was tested as working with good performance for reports up to 100 pages. I thought there was some persistance / cache thing I might have been missing to make it work for large reports.
I will look through the links you provided. Will post back.
|||Unable to get this to work (see below). My preference would be to be able to use the webservice.
In the meantime I have found some code on CodeProject that print a generated PDF. I will look at using this to get users going.
Thanks,
Leah
--
Details of problem with URL access
Code adapted from the links and related help topics.
Tested the code generated URL (strCreateUrl ) by pasting it into a browser and found we had a one page emf.
The ReceiveStream generated could not be indexed. To print using WinForms model we need a stream that we can index for page by page printing.
Code:
Dim strCreateUrl As String = "http://myServer/ReportServer?/myReportPath"
Dim myEnumerator As IEnumerator = m_parameterValues.GetEnumerator
Dim aParameterValue As ParameterValue
While myEnumerator.MoveNext()
aParameterValue = CType(myEnumerator.Current, ParameterValue)
strCreateUrl += "&" + aParameterValue.Name + "=" + aParameterValue.Value
End While
strCreateUrl += "&rs:Command=Render&rs:format=IMAGE&rc:OutputFormat=emf&rc:StartPage=1&rc:PageWidth=11.0in&rc:PageHeight=8.500in&rc:MarginTop=6.350mm&rc:MarginBottom=6.350mm&rc:MarginLeft=6.350mm&rc:MarginRight=6.350mm&rs:PersistStream=True"
Dim myRequest As System.Net.WebRequest = System.Net.WebRequest.Create(strCreateUrl)
myRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
' Return the response.
Dim myResponse As System.Net.HttpWebResponse = CType(myRequest.GetResponse(), System.Net.HttpWebResponse)
Dim ReceiveStream As Stream = myResponse.GetResponseStream()
Your code looks right, the problem you state is because you only call it once. Each call will only get you one page. Your next call should replace PersistStream=True with GetNextStream=True. You continue calling with GetNextStream=True until the response stream is empty. You can store each response as you would like (in the print control we actually store the response in temp files in case there are many pages).
I hope this helps.
|||Do you have any sample code for this?
I have tried to quickly write code that will read through the next streams but I do not know how to detect the last stream.
Thanks,
Leah
__
Code attemp:
Dim strCreateUrl As String = http://myServer/ReportServer?/myReport
Dim myEnumerator As IEnumerator = m_parameterValues.GetEnumerator
Dim aParameterValue As ParameterValue
While myEnumerator.MoveNext()
aParameterValue = CType(myEnumerator.Current, ParameterValue)
strCreateUrl += "&" + aParameterValue.Name + "=" + aParameterValue.Value
End While
strCreateUrl += "&rs:Command=Render&rs:format=IMAGE&rc:OutputFormat=emf&rc:StartPage=1&rc:PageWidth=11.0in&rc:PageHeight=8.500in&rc:MarginTop=6.350mm&rc:MarginBottom=6.350mm&rc:MarginLeft=6.350mm&rc:MarginRight=6.350mm"
' call first page with persist as true
Dim myRequest As System.Net.WebRequest = System.Net.WebRequest.Create(strCreateUrl + "&rs:PersistStream=True")
myRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
' Return the response.
Dim myResponse As System.Net.HttpWebResponse = CType(myRequest.GetResponse(), System.Net.HttpWebResponse)
Dim ReceiveStream As Stream = myResponse.GetResponseStream()
Dim iPageCounter As Integer = 0
While True
Try
myRequest = System.Net.WebRequest.Create(strCreateUrl + "&rs:GetNextStream=True")
ReceiveStream = myResponse.GetResponseStream()
If ReceiveStream Is Nothing Then
Exit While
End If
iPageCounter += 1
Catch ex As Exception
Exit While
End Try
End While
' Close the response to free resources.
myResponse.Close()
Thanks for your answer.
Using ReceiveStream.Length == 0 caused an exception.
In the autos window the RecieveStream length property is shown as:
- Length {"This stream does not support seek operations."} Long
RecieveStream is actually of type System.Net.ConnectStream.
If you think Length property should work... do you think I should I cast the RecieveStream?
|||Ahh, trying to do this from memory is not working. :) Ok, one more try. If you read the stream, it should not have any length. I will try and dig up some code tomorrow.|||Update on status.
Still have not gotten the URL Access technique (as suggested by Daniel) to work.
Further testing of the RenderStream approach shows that it can work on reports up to 240 pages, reports larger then this fail with follwoing SOAP Exception: "The stream cannot be found. The stream identifier that is provided to an operation cannot be located in the report server database"
Another team here has decided to use the RenderStream solution in their applicaiton because it appears to work for reports below 100 pages (100 pages is their largest report). My concern with this approach is that the reports do not appear to be consistantly persisting.
As a temporary fix for the applicaiton I am working on I have implemented a print solution that will render a PDF to file and create a process that uses Adobe to Print to the default printer.
Just writing this update I have had come up with some more ideas to try. I will let you know how I go. In the meantime if you can come up with more details on how you got the URL Access printing working it would be much appreciated. :)
_
Render Stream implementation (extract of code psoted in first post on this thread)
For pageIndex = 1 To m_numberOfPages - 1
' Attempt using streams, indexing in to pull back pages in order
iIndex = Convert.ToInt32(streamIDs(pageIndex - 1).Substring(streamIDs(pageIndex - 1).LastIndexOf("_"c) + 1))
pages(iIndex - 1) = m_reportingService.RenderStream(reportPath, format, streamIDs(pageIndex - 1), Nothing, deviceInfo, m_parameterValues, encoding, mimeType)
Next pageIndex
|||Here is code I use to get the response:
responseLength = (int) response.ContentLength; // -1 means content length not sent so we must get the size.if (responseLength == -1)
{
responseLength = 0;
using (Stream stream = response.GetResponseStream())
{
const int readSize = 64 * 1024;
int amountRead = 0;
byte[] input = new byte[readSize];
do
{
amountRead = stream.Read(input, 0, readSize);
responseLength += amountRead;
} while (amountRead > 0);
}
}
|||Thanks for posting this code.
I am trying to work out where this fits in. I am assuming that your code is used to work out the amount of bytes in the stream so you can read the bytes in the stream into a page array of byte arrays. So your code would be used inside a loop which will GetNextStream while there are still pages.
Could you let me know if I am on the right track?
Thanks,
Leah
Code first draft (not working)
' call first page with persist as true
Dim myRequest As System.Net.WebRequest = System.Net.WebRequest.Create(strCreateUrl + "&rs:PersistStream=True")
myRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
' Return the response.
Dim myResponse As System.Net.HttpWebResponse = CType(myRequest.GetResponse(), System.Net.HttpWebResponse)
While True
Try
Dim responseLength As Integer = CInt(myResponse.ContentLength) ' -1 means content length not sent so we must get the size.
If responseLength = -1 Then
responseLength = 0
' Using
Dim stream As Stream = myResponse.GetResponseStream
Try
Const readSize As Integer = 64 * 1024
Dim amountRead As Integer = 0
Dim input(readSize) As Byte
Do
amountRead = stream.Read(input, 0, readSize)
responseLength += amountRead
Loop While amountRead > 0
Finally
CType(stream, IDisposable).Dispose()
End Try
End If
' TO DO: add code here to read the amountRead of bytes into my page array of byte arrays
myRequest = System.Net.WebRequest.Create(strCreateUrl + "&rs:GetNextStream=True")
'myResponse = CType(myRequest.GetResponse(), System.Net.HttpWebResponse)
Catch ex As Exception
Exit While
End Try
End While
myResponse.Close()
|||You don't need the TODO. You are already reading in the data. I should have explained that the sample I gave, did not care about the content (it was from a test) it only cared about the length. The code is throwing away the data currently.
So if the ContentLength is set, just create a byte array of the correct size, then do a single read to get the data. If not, in the loop you are reading the data, just store it where you want.
You exit the loop when responseLength is zero.
I hope that helps.
Monday, March 12, 2012
Problems with Index Tuning
Hi all,
I'm trying to use SQL Profile and Index tuning to tune performance of my
database.
My Web application use only Stored Procedures.
During the "SQL Profile session" I traced "Stored Procedure RPC:Completed"
as event. Follow and example of trace:
exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8 ', N'@.P1 int ,@.P2 tinyint ,@.P3 tinyint ,@.P4 int ,@.P5 tinyint
,@.P6 tinyint ,@.P7 int ,@.P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
exec sp_executesql N'EXEC SP_CARICAQUADRO_A @.P1, @.P2 ', N'@.P1 int ,@.P2
tinyint ', 59774, 3
...
The trace contains several thousands of above commands. The various Stored
Procedure add, modify, delete records on tables that have not indexes.
The second step is to use the registered trace as workload in "Index tuning
wizard".
At the end of the wizard the responce is:
"No index racciomandation for the workload and choosen parameters."
Unfortunately this is false because the database is not indexed and the
Stored Procedure contained in the workload need of indexes.
Anyone can Help ME ?
Best Regards
Alessandro Zucchi (AlessandroZucchi@.discussions.microsoft.com) writes:
> I'm trying to use SQL Profile and Index tuning to tune performance of my
> database.
> My Web application use only Stored Procedures. During the "SQL Profile
> session" I traced "Stored Procedure RPC:Completed" as event. Follow and
> example of trace:
> exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @.P1, @.P2, @.P3, @.P4, @.P5,
> @.P6, @.P7, @.P8 ', N'@.P1 int ,@.P2 tinyint ,@.P3 tinyint ,@.P4 int ,@.P5 tinyint
> ,@.P6 tinyint ,@.P7 int ,@.P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
> exec sp_executesql N'EXEC SP_CARICAQUADRO_A @.P1, @.P2 ', N'@.P1 int ,@.P2
> tinyint ', 59774, 3
> ...
> The trace contains several thousands of above commands. The various Stored
> Procedure add, modify, delete records on tables that have not indexes.
> The second step is to use the registered trace as workload in "Index
> tuning wizard".
> At the end of the wizard the responce is:
> "No index racciomandation for the workload and choosen parameters."
> Unfortunately this is false because the database is not indexed and the
> Stored Procedure contained in the workload need of indexes.
I have never used ITW, but obviously the event RPC:Completed is not
enough to trace. I would expect SP:StmtCompleted to be required, as well
as some of the performance events, and possibly some of the Object:Scan
events. I suggest that you study the documenation for the Index Tuning
Wizard.
By the way, the sp_ prefix is reserved for system stored procedures, and
you should not use it for your own objects, as SQL Server first looks
for these in master.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Hi Alessandro
Unfortunately, the Index Tuning Wizard is over-simplistic in its
capabilities. You'll probably have to do this work manually by identifying
which stored procedure (or few worst stored procedures) is actually the
worst performing procedure & then tuning that stored procedure/s.
This, however is far easier to say than to do, as aggregating the
information you collect from your profiler traces is no trivial task.
I wrote an application SQLBenchmarkPro, which has this capability & it's
available for free to test at the moment from www.gajsoftware.com . It's
really designed for streamlining on-going SQL Server benchmark work, but it
also has an analysis feature whiich will capture profiler events, aggregate
them & tell you which are the worst performing stored procs to help you
focus your efforts. This is the easiest way I know of to perform this work
when the Index Tuning Wizard isn't up to the job.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Alessandro Zucchi" <AlessandroZucchi@.discussions.microsoft.com> wrote in
message news:0D740F5D-78B3-4417-A53C-3311E01C7F76@.microsoft.com...
> Hi all,
> I'm trying to use SQL Profile and Index tuning to tune performance of my
> database.
> My Web application use only Stored Procedures.
> During the "SQL Profile session" I traced "Stored Procedure
> RPC:Completed"
> as event. Follow and example of trace:
> exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @.P1, @.P2, @.P3, @.P4, @.P5,
> @.P6, @.P7, @.P8 ', N'@.P1 int ,@.P2 tinyint ,@.P3 tinyint ,@.P4 int ,@.P5 tinyint
> ,@.P6 tinyint ,@.P7 int ,@.P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
> exec sp_executesql N'EXEC SP_CARICAQUADRO_A @.P1, @.P2 ', N'@.P1 int ,@.P2
> tinyint ', 59774, 3
> ...
> The trace contains several thousands of above commands. The various Stored
> Procedure add, modify, delete records on tables that have not indexes.
> The second step is to use the registered trace as workload in "Index
> tuning
> wizard".
> At the end of the wizard the responce is:
> "No index racciomandation for the workload and choosen parameters."
> Unfortunately this is false because the database is not indexed and the
> Stored Procedure contained in the workload need of indexes.
> Anyone can Help ME ?
> Best Regards
I'm trying to use SQL Profile and Index tuning to tune performance of my
database.
My Web application use only Stored Procedures.
During the "SQL Profile session" I traced "Stored Procedure RPC:Completed"
as event. Follow and example of trace:
exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @.P1, @.P2, @.P3, @.P4, @.P5,
@.P6, @.P7, @.P8 ', N'@.P1 int ,@.P2 tinyint ,@.P3 tinyint ,@.P4 int ,@.P5 tinyint
,@.P6 tinyint ,@.P7 int ,@.P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
exec sp_executesql N'EXEC SP_CARICAQUADRO_A @.P1, @.P2 ', N'@.P1 int ,@.P2
tinyint ', 59774, 3
...
The trace contains several thousands of above commands. The various Stored
Procedure add, modify, delete records on tables that have not indexes.
The second step is to use the registered trace as workload in "Index tuning
wizard".
At the end of the wizard the responce is:
"No index racciomandation for the workload and choosen parameters."
Unfortunately this is false because the database is not indexed and the
Stored Procedure contained in the workload need of indexes.
Anyone can Help ME ?
Best Regards
Alessandro Zucchi (AlessandroZucchi@.discussions.microsoft.com) writes:
> I'm trying to use SQL Profile and Index tuning to tune performance of my
> database.
> My Web application use only Stored Procedures. During the "SQL Profile
> session" I traced "Stored Procedure RPC:Completed" as event. Follow and
> example of trace:
> exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @.P1, @.P2, @.P3, @.P4, @.P5,
> @.P6, @.P7, @.P8 ', N'@.P1 int ,@.P2 tinyint ,@.P3 tinyint ,@.P4 int ,@.P5 tinyint
> ,@.P6 tinyint ,@.P7 int ,@.P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
> exec sp_executesql N'EXEC SP_CARICAQUADRO_A @.P1, @.P2 ', N'@.P1 int ,@.P2
> tinyint ', 59774, 3
> ...
> The trace contains several thousands of above commands. The various Stored
> Procedure add, modify, delete records on tables that have not indexes.
> The second step is to use the registered trace as workload in "Index
> tuning wizard".
> At the end of the wizard the responce is:
> "No index racciomandation for the workload and choosen parameters."
> Unfortunately this is false because the database is not indexed and the
> Stored Procedure contained in the workload need of indexes.
I have never used ITW, but obviously the event RPC:Completed is not
enough to trace. I would expect SP:StmtCompleted to be required, as well
as some of the performance events, and possibly some of the Object:Scan
events. I suggest that you study the documenation for the Index Tuning
Wizard.
By the way, the sp_ prefix is reserved for system stored procedures, and
you should not use it for your own objects, as SQL Server first looks
for these in master.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Hi Alessandro
Unfortunately, the Index Tuning Wizard is over-simplistic in its
capabilities. You'll probably have to do this work manually by identifying
which stored procedure (or few worst stored procedures) is actually the
worst performing procedure & then tuning that stored procedure/s.
This, however is far easier to say than to do, as aggregating the
information you collect from your profiler traces is no trivial task.
I wrote an application SQLBenchmarkPro, which has this capability & it's
available for free to test at the moment from www.gajsoftware.com . It's
really designed for streamlining on-going SQL Server benchmark work, but it
also has an analysis feature whiich will capture profiler events, aggregate
them & tell you which are the worst performing stored procs to help you
focus your efforts. This is the easiest way I know of to perform this work
when the Index Tuning Wizard isn't up to the job.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Alessandro Zucchi" <AlessandroZucchi@.discussions.microsoft.com> wrote in
message news:0D740F5D-78B3-4417-A53C-3311E01C7F76@.microsoft.com...
> Hi all,
> I'm trying to use SQL Profile and Index tuning to tune performance of my
> database.
> My Web application use only Stored Procedures.
> During the "SQL Profile session" I traced "Stored Procedure
> RPC:Completed"
> as event. Follow and example of trace:
> exec sp_executesql N'EXEC SP_SALVAQUADRO_EONERI @.P1, @.P2, @.P3, @.P4, @.P5,
> @.P6, @.P7, @.P8 ', N'@.P1 int ,@.P2 tinyint ,@.P3 tinyint ,@.P4 int ,@.P5 tinyint
> ,@.P6 tinyint ,@.P7 int ,@.P8 int ', 59773, 3, 33, 11, 12, 0, 1239, 1239
> exec sp_executesql N'EXEC SP_CARICAQUADRO_A @.P1, @.P2 ', N'@.P1 int ,@.P2
> tinyint ', 59774, 3
> ...
> The trace contains several thousands of above commands. The various Stored
> Procedure add, modify, delete records on tables that have not indexes.
> The second step is to use the registered trace as workload in "Index
> tuning
> wizard".
> At the end of the wizard the responce is:
> "No index racciomandation for the workload and choosen parameters."
> Unfortunately this is false because the database is not indexed and the
> Stored Procedure contained in the workload need of indexes.
> Anyone can Help ME ?
> Best Regards
Problems with Identity Specification - deleting rows
First of all, I'm new to the forums, and I'm still getting started with VBEE and SQL.
I'm building a simple Windows Application that has a SQL Database with three tables, related to each other. In all of them I need to set one column as a simple index for the rows (1, 2, 3... etc), to work as a PK and relate to the other tables' FKs.
I've set the property Identity Specification of the column to Yes, and added some rows of data for testing. If you just keep adding rows, the PK columns work fine, but if you start deleting rows, the numbering gets fragmented, and the relations between the tables lose integrity.
I've tried the MSDN Online Help topics, but they just tell you this:
If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If you want to avoid such gaps, do not use the identity property.
So, my questions are: is there another way around this problem? How to number rows automatically and re-number them when a row is deleted, and keep the table relations working? It has anything to do with constraints?
Sorry if my question was already answered, I really couldn't find anything like my problem searching the forums. Thanks in advance.
(and also, sorry for the bad english... 
Hi there and welcome.
It has to do with foreign key constraints, indeed. You either have to buil your own logic to update / delete the related data in the other tables OR you use the cascading option with the Foreign key constraint which deletes the rows associated through the FK to the row which is deleted. The gaps are by design, you can reseed them, but that is not a thing which should always be done after deleting a single row. So you either use the identity property or implement a logic on your own to get a new PK value for the table.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the tips, Jens. I'll try that and let you know if it worked.problems with GETDATE()
Hello
I have an application with visualBasic that conects to SQL server 2000 through ODBC.
In sql server 2000 I have a trigger that inserts into an historic database de transaction made in the active database, like this:
CREATE TRIGGER [TRG_UPD_GTECON] ON [dbo].[GTECON]
FOR UPDATE
AS
INSERT INTO HISTORICO.dbo.HISTO_GTECON (GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES ,GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF,ACCION, FECHAHIST)
SELECT GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES, GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF , 'M',GETDATE() FROM INSERTED
FECMODIF field is a datetime field and it gives the problems.
I make two updates from my application: one to change one field and another one to change another one. It is made "one after the other", I mean: there is no user time between both but there are two different updates that should have different datetime at FECMODIF field as I use GETDATE() in both UPDATEs. the update id like this:
UPDATE GTECON
SET GTETIPNOD = 'H',
CODUSUA = 'coco',
FECMODIF = GETDATE()
WHERE GTECONCOD = 'A01'
AND GTECONTIP= 'H'
My problem is that when I see the historic database there are two registers of modification ('M') but BOTH HAVE THE SAME FECMODIF DATE!!.
It looks that GETDATE() is not indeterminist. If I debug the program, as there are user time between both updates, there is a difference between dates but when it works quickly It looks that theres no difference for getdate(). My historic is like this:
A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M
A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M
Please, some help or Ideas. It looks that nobody have this problem all over Internet.
Note1: I tried to put at the second update something like this FECMODIF = dateadd(ss,3,getdate()) in order to force the date to be different, but It doesn't work. It gives me the SAME DATETIME.
Note2: Everything is under the same transaction (maybe it helps)
RaulI don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran
select * from abc
select * from trigabc
My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773
(2 row(s) affected)
id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773
Does this simulate your transaction process?|||Thank you very much
Your idea is good but I couldn't place it into the trigguers. Instead of this I have place it in program code and it looks to work.
Thank you very much
Raul
Originally posted by achorozy
I don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran
select * from abc
select * from trigabc
My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773
(2 row(s) affected)
id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773
Does this simulate your transaction process?
I have an application with visualBasic that conects to SQL server 2000 through ODBC.
In sql server 2000 I have a trigger that inserts into an historic database de transaction made in the active database, like this:
CREATE TRIGGER [TRG_UPD_GTECON] ON [dbo].[GTECON]
FOR UPDATE
AS
INSERT INTO HISTORICO.dbo.HISTO_GTECON (GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES ,GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF,ACCION, FECHAHIST)
SELECT GTECONCOD,GTECONNIV,GTECONORD,GTECONPAD,GTECONDES, GTECONTIP,GTETIPNOD,CODUSUA,FECMODIF , 'M',GETDATE() FROM INSERTED
FECMODIF field is a datetime field and it gives the problems.
I make two updates from my application: one to change one field and another one to change another one. It is made "one after the other", I mean: there is no user time between both but there are two different updates that should have different datetime at FECMODIF field as I use GETDATE() in both UPDATEs. the update id like this:
UPDATE GTECON
SET GTETIPNOD = 'H',
CODUSUA = 'coco',
FECMODIF = GETDATE()
WHERE GTECONCOD = 'A01'
AND GTECONTIP= 'H'
My problem is that when I see the historic database there are two registers of modification ('M') but BOTH HAVE THE SAME FECMODIF DATE!!.
It looks that GETDATE() is not indeterminist. If I debug the program, as there are user time between both updates, there is a difference between dates but when it works quickly It looks that theres no difference for getdate(). My historic is like this:
A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M
A01 ESTOMATOLOGICOS 2002-12-05 10:46:58.843 M
Please, some help or Ideas. It looks that nobody have this problem all over Internet.
Note1: I tried to put at the second update something like this FECMODIF = dateadd(ss,3,getdate()) in order to force the date to be different, but It doesn't work. It gives me the SAME DATETIME.
Note2: Everything is under the same transaction (maybe it helps)
RaulI don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran
select * from abc
select * from trigabc
My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773
(2 row(s) affected)
id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773
Does this simulate your transaction process?|||Thank you very much
Your idea is good but I couldn't place it into the trigguers. Instead of this I have place it in program code and it looks to work.
Thank you very much
Raul
Originally posted by achorozy
I don't know if you have solved your problem, however I ran the following test on SQL 2000 and 7.0
--create table abc (id int identity(1,1) not null, msg varchar(20), txnTime datetime)
--create table trigabc (id int not null, txnTime datetime)
/*
CREATE TRIGGER trig_test
ON abc
FOR UPDATE
AS
BEGIN
insert trigabc (id,txnTime) select id, getdate() from inserted
END
*/
truncate table abc
truncate table trigabc
go
insert abc (msg,txnTime) select 'First', getdate()
insert abc (msg,txnTime) select 'Second', getdate()
go
begin tran
update abc set msg='First Update', txnTime=getdate() where id = 1
waitfor delay '000:00:03'
update abc set msg='Second Update', txnTime=getdate() where id = 2
commit tran
select * from abc
select * from trigabc
My output on both systems showed a 3 second delay:
id msg txnTime
---- ------- ----------------
1 First Update 2002-12-06 09:55:15.720
2 Second Update 2002-12-06 09:55:18.773
(2 row(s) affected)
id txnTime
---- ----------------
1 2002-12-06 09:55:15.770
2 2002-12-06 09:55:18.773
Does this simulate your transaction process?
Friday, March 9, 2012
Problems with excel 2003 web pivot tables reports
We have an application that gets data from ms-sql databases and displays the
m
on the web using pivot tables. This application is coded for the web using
vbscript. For our users that have office 2002 - excell 2002 the tables
display with no problem. However, for users that have the 2003 version the
tables do not load. Any ideas what might be causing this issue?
--
MikeHaven't used it myself. But have you checked permission? Also, was there any
errors?
-oj
"Mike Zens" <MikeZens@.discussions.microsoft.com> wrote in message
news:179945B1-6441-4999-9384-6F003831174A@.microsoft.com...
> We have an application that gets data from ms-sql databases and displays
> them
> on the web using pivot tables. This application is coded for the web using
> vbscript. For our users that have office 2002 - excell 2002 the tables
> display with no problem. However, for users that have the 2003 version the
> tables do not load. Any ideas what might be causing this issue?
> --
> Mike|||Users are able to get data using version 2002 but when the same users migrat
e
to 2003 version all they receive is the little box in the upper left hand
corner of the screen with a red X in it.
"oj" wrote:
> Haven't used it myself. But have you checked permission? Also, was there a
ny
> errors?
> --
> -oj
>
> "Mike Zens" <MikeZens@.discussions.microsoft.com> wrote in message
> news:179945B1-6441-4999-9384-6F003831174A@.microsoft.com...
>
>
m
on the web using pivot tables. This application is coded for the web using
vbscript. For our users that have office 2002 - excell 2002 the tables
display with no problem. However, for users that have the 2003 version the
tables do not load. Any ideas what might be causing this issue?
--
MikeHaven't used it myself. But have you checked permission? Also, was there any
errors?
-oj
"Mike Zens" <MikeZens@.discussions.microsoft.com> wrote in message
news:179945B1-6441-4999-9384-6F003831174A@.microsoft.com...
> We have an application that gets data from ms-sql databases and displays
> them
> on the web using pivot tables. This application is coded for the web using
> vbscript. For our users that have office 2002 - excell 2002 the tables
> display with no problem. However, for users that have the 2003 version the
> tables do not load. Any ideas what might be causing this issue?
> --
> Mike|||Users are able to get data using version 2002 but when the same users migrat
e
to 2003 version all they receive is the little box in the upper left hand
corner of the screen with a red X in it.
"oj" wrote:
> Haven't used it myself. But have you checked permission? Also, was there a
ny
> errors?
> --
> -oj
>
> "Mike Zens" <MikeZens@.discussions.microsoft.com> wrote in message
> news:179945B1-6441-4999-9384-6F003831174A@.microsoft.com...
>
>
Problems with error logging
I've added a new application to an existing instance of NS allready running another application.
The new application has a single event in it, but when I added the application yesterday it didn't submit anything to the EventBatches table. The event query interval was set to 30 seconds. There was no error logs written at all.
This morning, there was still nothing in the EventBatches table, but there was 6 simoultaneous errors thrown with a time difference of 1-4 hours between them.
(The error message was insufficent rights on the queried table in the event query - no problem, I fixed the rights)
But still, theres nothing in the EventBatches table. Now how long do I have to wait before NS throws an error log I can use for debugging? I would expect NS to throw errors everytime the events query fails - that would be every 30 seconds. But insted it seems to throw error messages rarely at random times.
Now I'm quite sure the problem isn't my interval settings, as I use the exact same settings as the existing application. And this application throws errors every 30 seconds if problems with the event query.
Can anyone figure out why the error logging behaves this way?
/Henrik
The new application has a single event in it, but when I added the application yesterday it didn't submit anything to the EventBatches table. The event query interval was set to 30 seconds. There was no error logs written at all.
This morning, there was still nothing in the EventBatches table, but there was 6 simoultaneous errors thrown with a time difference of 1-4 hours between them.
(The error message was insufficent rights on the queried table in the event query - no problem, I fixed the rights)
But still, theres nothing in the EventBatches table. Now how long do I have to wait before NS throws an error log I can use for debugging? I would expect NS to throw errors everytime the events query fails - that would be every 30 seconds. But insted it seems to throw error messages rarely at random times.
Now I'm quite sure the problem isn't my interval settings, as I use the exact same settings as the existing application. And this application throws errors every 30 seconds if problems with the event query.
Can anyone figure out why the error logging behaves this way?
/Henrik
Hello,
It isn't clear from your post, but I assume you're using the built-in SQLProvider event provider. Is that correct?
Can you post the event class definition and the event provider configuration elements from your ADF?
Thanks
-shyam|||Yes it's a built-in provider.
I'm not able to post the sections from my definition file right now, as I'm not connected to the Internet in my development environment and more important - strict company rules forbids my to do so.
So I understand helping me out gets a bit difficult... :-(
/Henrik
|||Hmm... clearing all the application logs in the logviewer, fixed the problem.
Now error logs are thrown as I would expect them to be, and I can bedug from there.
I'm embarressed I didn't think of this before - sorry for waisting your time.
/Henrik
Problems with disconnected recordsets i Yukon
We have a VB6 application using
We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on
Error: -2147217887 Multiple-step operation generated errors. Check each status value.
Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.
Is this the way
One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.
I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.
I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.
|||Unfortunately I never found any better solution than the one I described.Problems with disconnected recordsets i Yukon
We have a VB6 application using
We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on
Error: -2147217887 Multiple-step operation generated errors. Check each status value.
Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.
Is this the way
One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.
I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.
I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.
|||Unfortunately I never found any better solution than the one I described.Problems with disconnected recordsets i Yukon
We have a VB6 application using
We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on
Error: -2147217887 Multiple-step operation generated errors. Check each status value.
Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.
Is this the way
One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.
I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.
I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.
|||Unfortunately I never found any better solution than the one I described.
Subscribe to:
Posts (Atom)