Showing posts with label created. Show all posts
Showing posts with label created. Show all posts

Sunday, March 25, 2012

COunt Function in Report

I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far.

I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial.

So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field

I also want to print the the same fields in the report total area.

Any ideas ?

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")

Guys, I figured this one out.

Here is the solution :
=Sum(IIF Fields!InvStatus.Value = "PAID",1,0)

sql

Thursday, March 22, 2012

Count All Things Happened Today

Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John

check out the year, month and day or datepart functions in t-sql.

You can assemble the date anyway you want.

|||

forgot to add:

SET DATEFORMAT

CONVERT


|||

i found this : http://support.microsoft.com/kb/q186265/

which helps me get stuff like secounds etc :

 Ms for Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second

how do i get it as a short date string, like "11/10/2007" please John

|||

Try this:

WHERE timecreated>=DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 0)AND timecreated<DATEADD(dd,DATEDIFF(dd, 0,GETDATE()), 1)

|||

that worked perfectly, is it "read" by like this :

WHERE TimeCreated is equalto or greater than today + 0 days. AND TimeCreated is LessThan today + 1day.

just wondering becuase i wish to adjust this code to count things happening this week / month etc.


Thanks John

|||

Both DATEADD and DATEDIFF are very useful functions to work with datetime in TSQL. You can search to find more information on these two key words. You can modify the code with the same logic to get what you want, but i have read some good articles with a lot usages for these two functions.

|||

yeh they seem to be invalueable when attempting to work with dates / times! thanks for this! John

couldn't rename or delete maintenance plan in Management Studio

Hi,
I tried to rename and delete one old maintenance plan which is created by
other DBA and before the machine was renamed. But, I kept getting the
following error. I can rename and delete the maintenance plan I created.
An error has occurred while establishing a connection to the server. When
connecting to SQL server 2005, this failure may be cuased by the fact that
under the default settinfs SQL server does not allow remote connections
(provider: Named Pipes providers, error: 40 - could not open a connection to
SQL server) (Microsoft SQL server, Error: 53)
Shared Memory, TCP and Named Piples are all enabled.
Please advise.
Best Regards,
Lynn
I think the problem is that the maintenance plan use the Local Server
Connection which still point to the old machine name. (I couldn't move this
connection or modify the server name for this connection at all). I add
another connection, but I still couldn't delete this maintenance plan.
Please advise.
Best Regards,
Lynn
"Lynn" wrote:

> Hi,
> I tried to rename and delete one old maintenance plan which is created by
> other DBA and before the machine was renamed. But, I kept getting the
> following error. I can rename and delete the maintenance plan I created.
> An error has occurred while establishing a connection to the server. When
> connecting to SQL server 2005, this failure may be cuased by the fact that
> under the default settinfs SQL server does not allow remote connections
> (provider: Named Pipes providers, error: 40 - could not open a connection to
> SQL server) (Microsoft SQL server, Error: 53)
> Shared Memory, TCP and Named Piples are all enabled.
> Please advise.
> --
> Best Regards,
> Lynn
|||I've been pounding my head with a similar problem. A Server was renamed and
now I can't do anything with the plan.
Any more thoughts?
"Lynn" wrote:
[vbcol=seagreen]
> I think the problem is that the maintenance plan use the Local Server
> Connection which still point to the old machine name. (I couldn't move this
> connection or modify the server name for this connection at all). I add
> another connection, but I still couldn't delete this maintenance plan.
> Please advise.
> --
> Best Regards,
> Lynn
>
> "Lynn" wrote:

Monday, March 19, 2012

Could not obtain a required interface error

I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.

But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server

select * from linktompp.testmpp.dbo.tasks

such that linktompp is the linked server name

testmpp is the catalog name

tasks is the table name i need to select data from

it shows an error that says

"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."

"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"

could anyone tell me the reason of this error

thanks

Hi my friend

I try to insert data into MSP

i can select data from MSP and show into datagrid

but now i coulde not insert data

i live in Iran,you can call to me with Email:jazayery@.gmail.com

Best Reagard

Could not obtain a required interface error

I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.

But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server

select * from linktompp.testmpp.dbo.tasks

such that linktompp is the linked server name

testmpp is the catalog name

tasks is the table name i need to select data from

it shows an error that says

"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."

"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"

could anyone tell me the reason of this error

thanks

Hi my friend

I try to insert data into MSP

i can select data from MSP and show into datagrid

but now i coulde not insert data

i live in Iran,you can call to me with Email:jazayery@.gmail.com

Best Reagard

Sunday, March 11, 2012

could not locate entry in sysdatabases

Hey there, could someone please help me!!
I had created a database in sql server but since then installed windows xp pro and now when i go through the microsoft sql server dsn configuration the database is not valid and therefore i cannot change the default database as it says it doesn't exist.
So then when i type in command prompt use 'db' it says it doesn't exist but when i try to create 'db' it says that the database already exists.
I get the error message, could not locate entry in sysdatabases for 'cti' (database name).
When i look in mysql - data - along with master etc there is my cti.mdf and log files??
I'm stuck?
Is it that the files just aren't registering through sql server?
Please help!Have you tried using enterprise manager or query analyzer ? Also, can you be more specific when you say "installed windows xp pro". Which version of sql server are you using ? When you configure your dsn, what are you putting in the "Server" dropdown ? Have any of your security settings changed and which type of security are you using to access sql server ? Have you tried to create a new dsn ?|||Hey there, i use (local) for the server in the dsn configuration.
I have just put on windows xp pro sp1 - I orginally had an earlier windows xp pro.
I'm using sql server 2000 sp3
All security settings have remained the same and I've tried to do new dsn's but they still don't register that the database files aren't in the sysdatabases.

Could not load file or assembly 'System.Data.SqlServerCe'...

I am trying to use SQL Mobile 2005 with Visual Studio 2005. I have a simple sql mobile db i created and am trying to test connectivity to the DB in a simple app. I added the reference to the SqlServerCE and the verison # that is shown in properties is 3.0.3600.0, but when i look at the physical DLL in explorer (found at C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0) the version # is 3.0.5206.0, so when i compile and run the test, I get :
System.IO.FileLoadException: Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) > System.IO.FileLoadException: Could not load file or assembly 'System.Data.SqlServerCe, Version=3.0.3600.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, Retargetable=Yes' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040).

I have uninstalled visual studio and reinstalled thinking I had a driver issue because I had beta products installed, but I get the same result. I am at a loss of what to do about this.
Hi Mike,

Which project you are working on?
PPC2003/PPC2005/SPH2005/WCE42/WCE50/WIN32

Also is it the device or emulator?

Can you try installing VS 2005 on a very clean machine (no uninstalls and installs) and see if it repros. Basically we want to figure out whether it is a setup issue or a functional issue.

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Coporation|||I seemed to have narrowed down the issue. I am using PPC 2005 btw.
The above error occurs if I am using the built in Unit Testing in Team Edition. If i strip out that stuff and just have a simple connection object (outside a test harness) it works fine. Has anyone been able to do TDD using win mobile and sql mobile 2005?|||

UT is not supported for devices, so the answer is no.

|||

Ilya Tumanov wrote:

UT is not supported for devices, so the answer is no.

well that is garbage. anyway, for this example i wasn't testing anything using mobile, just a simple sql mobile connection.|||

Hi,

I have developed a project for Pocket PC 2003 and when i install that to device HP 2200, then its shows error.

Error: File or Assembly : system.data.sqlserverce version 3.0.3600.0 Token No." " or one of its dependencies not found.

Please tell me how can i remove this error. I am using .Net 2.0 pocket pc 2003 for developing the project. Please send me the answer as soon as possible and at my ID thats given below......................................

Thanks

Shalabh Gupta

shalabhgupta16@.hotmail.com

shalabhgupta16@.yahoo.com

Thursday, March 8, 2012

Could not find stored procedure 'sp_MSupd_xxxxxx'

I created a pull subscription to pull information from a production
database. When I started the subscription, the error "Could not find stored
procedure 'sp_MSupd_tablename'" showed.
I deleted the subscription in the subscriber and drop the distribution by
running the "Disable Publishing and Distribution Wizard" in the distributor.
Then, I recreated both the distribution and subscription from the very
beginning once again. However, the same error still showed.
I'd be grateful is anybody can help.
Thanks in advance!
KM
If you have used @.sync_type = None in sp_addsubscription or in the
subscription wizard, 'Initialize subscription' page, you select 'no, the
subscriber already has the schema and data' you may get this error. The
system tables for replication will be created on the subscriber in either
case but you'll need to manually create the stored procedures on the
subscriber if you use this option otherwise you'll have the above error when
you change a row on the publisher. Use sp_scriptpublicationcustomprocs
(assuming you have >= sp1) to create a script which creates the procedures
and run this script on the subscriber.
HTH,
Paul Ibison
"krygim" <krygim@.hotmail.com> wrote in message
news:ewJovUqhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I created a pull subscription to pull information from a production
> database. When I started the subscription, the error "Could not find
stored
> procedure 'sp_MSupd_tablename'" showed.
>
> I deleted the subscription in the subscriber and drop the distribution by
> running the "Disable Publishing and Distribution Wizard" in the
distributor.
> Then, I recreated both the distribution and subscription from the very
> beginning once again. However, the same error still showed.
>
> I'd be grateful is anybody can help.
>
>
> Thanks in advance!
>
> KM
>
|||Hi Paul,
Thanks a lot for your information. However, I was not able to find
sp_scriptpublicationcustomprocs in BOL. Could you show me how to use of the
procedure?
Thanks in advance.
KM
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e4pQoWrhEHA.1184@.TK2MSFTNGP12.phx.gbl...
> If you have used @.sync_type = None in sp_addsubscription or in the
> subscription wizard, 'Initialize subscription' page, you select 'no, the
> subscriber already has the schema and data' you may get this error. The
> system tables for replication will be created on the subscriber in either
> case but you'll need to manually create the stored procedures on the
> subscriber if you use this option otherwise you'll have the above error
when[vbcol=seagreen]
> you change a row on the publisher. Use sp_scriptpublicationcustomprocs
> (assuming you have >= sp1) to create a script which creates the procedures
> and run this script on the subscriber.
> HTH,
> Paul Ibison
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:ewJovUqhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> stored
by
> distributor.
>
|||This scenario may not be exactly yours, but you'll get the
idea:
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;320499

>--Original Message--
>Hi Paul,
>Thanks a lot for your information. However, I was not
able to find
>sp_scriptpublicationcustomprocs in BOL. Could you show me
how to use of the[vbcol=seagreen]
>procedure?
>Thanks in advance.
>KM
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:e4pQoWrhEHA.1184@.TK2MSFTNGP12.phx.gbl...
sp_addsubscription or in the[vbcol=seagreen]
you select 'no, the[vbcol=seagreen]
this error. The[vbcol=seagreen]
subscriber in either[vbcol=seagreen]
procedures on the[vbcol=seagreen]
the above error[vbcol=seagreen]
>when
sp_scriptpublicationcustomprocs[vbcol=seagreen]
creates the procedures[vbcol=seagreen]
from a production[vbcol=seagreen]
error "Could not find[vbcol=seagreen]
the distribution[vbcol=seagreen]
>by
Wizard" in the[vbcol=seagreen]
subscription from the very[vbcol=seagreen]
showed.
>
>.
>
|||Thanks
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:2c3f01c486c2$b82bc470$a301280a@.phx.gbl...[vbcol=seagreen]
> This scenario may not be exactly yours, but you'll get the
> idea:
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;320499
>
> able to find
> how to use of the
> sp_addsubscription or in the
> you select 'no, the
> this error. The
> subscriber in either
> procedures on the
> the above error
> sp_scriptpublicationcustomprocs
> creates the procedures
> from a production
> error "Could not find
> the distribution
> Wizard" in the
> subscription from the very
> showed.
|||Later editions of BOL may have these details, but if not this link has more
details: http://support.microsoft.com/default...b;EN-US;299903
Regards,
Paul Ibison
|||Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uOnbmgthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Later editions of BOL may have these details, but if not this link has
more
> details: http://support.microsoft.com/default...b;EN-US;299903
> Regards,
> Paul Ibison
>
|||Hi
do let me know if you've had any difficulties running
"sp_scriptpublicationcustomprocs".
After creating the replication as described in the KB 320499, if you see
that Inserts get replicated well but "updates" gives
us the Following error;
"37000 Cannot update identity column 'column1'. 8102 "
Do let me know.
SB.

Could not find stored procedure dbo.aspnet_CheckSchemaVersion.

I created new database in sql server 2005 dev. edition and useaspnet_regsql to configure my database to store information for ASP.NETmembership.Than i created simple asp.net login application and thatwork fine on my local machine but when i deploy it on host server thani got this message :

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

please help.

Likely, your server is using a beta version of the asp.net SQL stuff (or your local machine is).

This post:

http://forums.asp.net/926578/ShowPost.aspx

Discusses the problem...

|||

Two ways:

1. Open file Web.config: (if you use SQL Server 2000 Professional)

<connectionStrings>
<add name="SqlClient" providerName="System.Data.SqlClient" connectionString="Server=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; User id=tuanba; Password=tuanba"/>
</connectionStrings>

2. Open file Web.config: (if you use SQL Server 2005)

<connectionStrings>
<remove name="LocalSqlClient"/>
<add name="LocalSqlClient" providerName="System.Data.SqlClient" connectionString="Data Source=ServerName; Initial Catalog=DatabaseName; Integrated Security=SSPI; User id=tuanba; Password=tuanba;"/>
</connectionStrings>

Goodluck!

Could not find stored procedure

I have an Access 2000 database connected to a SQL Server and am trying
to execute my first stored procedure. I created the stored procedure
and verified that it works, but when I try to execute it from Access:

cnn.Execute("sp_IPT")

it says: 'Could not find stored procedure 'sp_IPT'

Any ideas?

Norman B. Scheinin
F-22 Applications Development
M/S 4E-09
(206) 655-7236
norman.b.scheinin@.boeing.comNorman Scheinin (norman.b.scheinin@.boeing.com) writes:
> I have an Access 2000 database connected to a SQL Server and am trying
> to execute my first stored procedure. I created the stored procedure
> and verified that it works, but when I try to execute it from Access:
> cnn.Execute("sp_IPT")
> it says: 'Could not find stored procedure 'sp_IPT'
> Any ideas?

I would guess that you have not specified which database to work in.
You define this with "Initial Catalog" in the connection string. You
can also have a default database set for your SQL login. You can also
specify a three-part name when you invoke the procedure:

cnn.Execute("yourdb..sp_IPT")

I would also like to take the occassion to discourage you from using
sp_ as the first letters in the names of your stored procedures. This
prefix is reserved for system procedures, and SQL Server first looks
in the master database for these. It then looks in your current database,
so the prefix is not the cause for your problem.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

Could not find server

I have linked a server withinh the "SQL Server Management Studio
Express"
I also created a database where I created a view using the openquiery
statement.
The view runs perfectly within the "SQL Server Management Studio
Express"
I created a new project with "Microsoft Visual Studio 2005"
I created a shared data source to the database where I created the
views.
The data source is OK because I can see the cretaed views on the Query
Builder.
Once I created the query on the Query Builder and I try to run it, the
following message appears:
"SQL Execution Error
Error Source: .NetSqlClient Data Provider
Error Message: Could not find server 'xxx' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
Could not use view or function 'xxx' because of binding errors"
I have looked for server 'xxx' in sysservers and it is there, so I do
not understand where the error originates.
Any help would be most appreciated.
Abraham Hirsch
Mexico CityAbraham,
Is the working connection local and the non-working connection remote?
If so, open SQL Server Configuration Manager and adjust connection
protocols for SQL Express.
Andrew Watt MVP
On 9 Jun 2006 11:25:03 -0700, "Eibi" <ahirschp@.gmail.com> wrote:
>I have linked a server withinh the "SQL Server Management Studio
>Express"
>I also created a database where I created a view using the openquiery
>statement.
>The view runs perfectly within the "SQL Server Management Studio
>Express"
>I created a new project with "Microsoft Visual Studio 2005"
>I created a shared data source to the database where I created the
>views.
>The data source is OK because I can see the cretaed views on the Query
>Builder.
>Once I created the query on the Query Builder and I try to run it, the
>following message appears:
>"SQL Execution Error
>Error Source: .NetSqlClient Data Provider
>Error Message: Could not find server 'xxx' in sysservers. Execute
>sp_addlinkedserver to add the server to sysservers.
>Could not use view or function 'xxx' because of binding errors"
>I have looked for server 'xxx' in sysservers and it is there, so I do
>not understand where the error originates.
>Any help would be most appreciated.
>Abraham Hirsch
>Mexico City|||Both connections are local!
Andrew Watt [MVP] wrote:
> Abraham,
> Is the working connection local and the non-working connection remote?
> If so, open SQL Server Configuration Manager and adjust connection
> protocols for SQL Express.
> Andrew Watt MVP
> On 9 Jun 2006 11:25:03 -0700, "Eibi" <ahirschp@.gmail.com> wrote:
> >I have linked a server withinh the "SQL Server Management Studio
> >Express"
> >I also created a database where I created a view using the openquiery
> >statement.
> >
> >The view runs perfectly within the "SQL Server Management Studio
> >Express"
> >
> >I created a new project with "Microsoft Visual Studio 2005"
> >I created a shared data source to the database where I created the
> >views.
> >
> >The data source is OK because I can see the cretaed views on the Query
> >Builder.
> >
> >Once I created the query on the Query Builder and I try to run it, the
> >following message appears:
> >"SQL Execution Error
> >Error Source: .NetSqlClient Data Provider
> >Error Message: Could not find server 'xxx' in sysservers. Execute
> >sp_addlinkedserver to add the server to sysservers.
> >Could not use view or function 'xxx' because of binding errors"
> >
> >I have looked for server 'xxx' in sysservers and it is there, so I do
> >not understand where the error originates.
> >
> >Any help would be most appreciated.
> >
> >Abraham Hirsch
> >Mexico City

could not find row in sysindexes for database

I am trying to attach a database that was created on another server. I believe the database was created using SQL Server Management Studio Express 2005. Now, I have successfully attached the database in my development environment, which is using SQL Server Management Studio Express 2005.

However, when I tried to attach the database onto our production server, which is SQL Server 2000 I received the error:

Error 602:
Could not find row in sysindexes for database ID 18, object ID 1, index ID 1. Run DBCC CheckTable on sysindexes.

In my development environment, I ran DBCC CheckDB on the source database and no errors are returned.

I aslo checked the compatibility level under the database properties and it shows: Sql Server 2000 (80)

So, this should not be a version incompatibility issue. What is causing the attach to fail on SQL Server 2000?

Thanks for any help.

What I know is:

If you have an .mdf file in SQL Server 2000, then you attached it to SQL Server 2005 ... no way you can use it (the .mdf file) again with SQL Server 2000.

What I think about is SQL Serve 2006 .mdf files can not be used with SQL Server 2000!!

Try Backup/Restore option or Copy Database Wizard.

Good luck.

|||

Are you trying to attach a SQL 2005 database to a SQL 2000 server? If so, that's the issue. Obviously you can upgrade (2000 --> 2005), but you can't downgrade.

-Damien

|||

CS4Ever:

What I know is:

If you have an .mdf file in SQL Server 2000, then you attached it to SQL Server 2005 ... no way you can use it (the .mdf file) again with SQL Server 2000.

What I think about is SQL Serve 2006 .mdf files can not be used with SQL Server 2000!!

Try Backup/Restore option or Copy Database Wizard.

Good luck.

Here is a link that support what I wrote and it marked as answered in MSDN forum: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=165696&SiteID=17

This link might be helpful for you as well: http://sqlserver-qa.net/blogs/tools/archive/2007/08/14/error-could-not-find-row-in-sysindexes-for-database-8966-823-and-602.aspx

Good luck.

Saturday, February 25, 2012

Could not Create Publication

Hi,
I created 2 windows user named PublishUser and ReplSnapAgent with less
previllege. Ran SSMS using PublishUser and created snapshot publication.
Used RepSnapAgent user as Snapshot Agent. I got an error saying
SQL Server could not create publication 'Product'. (New Publication Wizard)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Proxy "Computer-Name\ReplSnapAgent" is not a valid Windows user.
Changed database context to 'ReplTesting'. (.Net SqlClient Data Provider)
Is anybody else help me out in solving this issue?
Is ReplSnapAgent a local user account on the computer Computer-Name? Is it
in the dbo_role on the distribution database and the publication?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bharathi" <NoSpam_BB@.hotmail.com> wrote in message
news:Of7JjnPKHHA.2632@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I created 2 windows user named PublishUser and ReplSnapAgent with less
> previllege. Ran SSMS using PublishUser and created snapshot publication.
> Used RepSnapAgent user as Snapshot Agent. I got an error saying
> SQL Server could not create publication 'Product'. (New Publication
> Wizard)
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> Proxy "Computer-Name\ReplSnapAgent" is not a valid Windows user.
> Changed database context to 'ReplTesting'. (.Net SqlClient Data Provider)
> Is anybody else help me out in solving this issue?
>

Friday, February 24, 2012

Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Client in

Hello,

I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.

In Visual Studio 2005 Pro I proceed following steps:

    New application (C#) Add web reference to the project (http://localhost:8084/Produktdaten?wsdl) Add DataGridView to the form1 in the designer window Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.

Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.

Could you please help to get this simple web service client running.

Best regards,
Wolfgang

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace AWProductWebServiceClient

{

publicpartialclassForm1 : Form

{

public Form1()

{

InitializeComponent();

localhost.Produktdaten ws = new localhost.Produktdaten();

dataGridView1.DataSource = ws.GetProduct();

dataGridView1.DataMember = "Product";

}

privatevoid dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

{

}

}

}

Wolfgang,

In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.

If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}

If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];

Jimmy

|||

Jimmy,

sorry for my late response (vacation).

The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.

Thank you very much.

Best regards,
Wolfgang

Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Client in

Hello,

I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.

In Visual Studio 2005 Pro I proceed following steps:

    New application (C#)

    Add web reference to the project (http://localhost:8084/Produktdaten?wsdl)

    Add DataGridView to the form1 in the designer window

    Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.

Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.

Could you please help to get this simple web service client running.

Best regards,
Wolfgang

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace AWProductWebServiceClient

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

localhost.Produktdaten ws = new localhost.Produktdaten();

dataGridView1.DataSource = ws.GetProduct();

dataGridView1.DataMember = "Product";

}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

{

}

}

}

Wolfgang,

In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.

If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}

If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];

Jimmy

|||

Jimmy,

sorry for my late response (vacation).

The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.

Thank you very much.

Best regards,
Wolfgang

Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Clien

Hello,

I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.

In Visual Studio 2005 Pro I proceed following steps:

    New application (C#)

    Add web reference to the project (http://localhost:8084/Produktdaten?wsdl)

    Add DataGridView to the form1 in the designer window

    Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.

Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.

Could you please help to get this simple web service client running.

Best regards,
Wolfgang

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace AWProductWebServiceClient

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

localhost.Produktdaten ws = new localhost.Produktdaten();

dataGridView1.DataSource = ws.GetProduct();

dataGridView1.DataMember = "Product";

}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

{

}

}

}

Wolfgang,

In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.

If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}

If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];

Jimmy

|||

Jimmy,

sorry for my late response (vacation).

The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.

Thank you very much.

Best regards,
Wolfgang

Sunday, February 19, 2012

Could not allocate new page for database TEMPDB?

"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

I get this error when running a query on another database. But why?

Both data and transaction files on TEMPDB are set to "automatically grow file" and "unrestricted file growth" and there is 70GB of free space on the disk drive. Shouldn't the files just grow? Why would this happen?because temporary table store in tempdb|||because temporary table store in tempdb

Thanks for the reply. I don't understand what you are saying but it sounds like you know what the issue is. Could you please elaborate a little bit?|||You say :I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?|||You say :I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?

No, I'm not explicitly doing anything with TEMPDB.

My query is very straight forward:

SELECT TOP 100 SampleDescriptor, COUNT(*) RepCount FROM DataSamples GROUP BY SampleDescriptor ORDER BY RepCount DESC

On my dev system, with 30 test records, this works fine.

On the production server with 188 million records (yes, a LOT of records!), this fails with the TEMPDB error that I mentioned.

I'm trying a simple: SELECT COUNT(*) FROM DataSamples now. It's been running for over 30 minutes and still going. I wonder if it will hit a similar error...|||When you get the error, have you checked the size of tempdb (both data and log) and free space of the hard drive?

I assume that tempdb is located in the hard drive with 70GB free space.|||Yes, of course. The size of both tempdb data and transaction files are 2MB and they are located on the drive with 68GB free. All drives on that system have plenty of space (although for tempdb, D: should be the only one that matters):

C: has 25 GB free
D: has 68 GB free
E: has 124 GB free|||What do sp_spaceused and sp_helpdb return?

BTW, which version and service pack of SQL Server?

Tuesday, February 14, 2012

Corrupted view

Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand

Corrupted view

Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
Ferdinand
Ferdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand
|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand

Corrupted view

Some days ago I created a view on a sqlserver database, that returns the
price and some other infos of invoice items. everything worked fine.
Now, the view didn't return the same as when I created it. The price
column of the returned record set showed the invoice date instead of the
price and some other columns also contained wrong information.
After recreating the view everything was fine again.
How can this happen?
Cheers
FerdinandFerdinand,
Did someone change the underlying table definitions? If so, run
sp_refreshview 'viewname' to get the view resynchronized with the underlying
tables.
RLF
"Ferdinand Zaubzer" <ferdinand.zaubzer@.schendl.at> wrote in message
news:OkORyxYuHHA.4540@.TK2MSFTNGP05.phx.gbl...
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand|||The explanation was given by Russell.
I strongly suggest you remove any * from the selection list in the
view's definition. Apart from the side effects in the view when adding
columns to the specific table, it is considered a bad practice to use
SELECT * in views in production code.
Gert-Jan
Ferdinand Zaubzer wrote:
> Some days ago I created a view on a sqlserver database, that returns the
> price and some other infos of invoice items. everything worked fine.
> Now, the view didn't return the same as when I created it. The price
> column of the returned record set showed the invoice date instead of the
> price and some other columns also contained wrong information.
> After recreating the view everything was fine again.
> How can this happen?
> Cheers
> Ferdinand