Sql Update command: created

Showing posts with label created. Show all posts

Friday, March 30, 2012

Problems installing External Stored procedure

Hi There

I'm having problems installing a external stored procedure that I created in Visual C++. I've created the .ddl and put it in the correct directory.

I've run the following commands to install it
sp_addextendedproc xp_regex, "D:\garth\ExternalStoredProcedures\Tecas\xp_regex.dll"

I then exec the procedure and get the following:

execute master..xp_regex

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\tedevsql02\garth\ExternalStoredProcedures\Tecas, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

The .dll is definately in that directory. I'm not sure what I'm doing wrong. Any tips?

Thanks

SynAck
Sorry, that error should read as follows:

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL D:\garth\ExternalStoredProcedures\Tecas\xp_regex.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

|||

Make sure you have also copied to that directory other .dll's that you reference from you program.

|||At the moment, the program is the equivilent of a "hello world" program

Problems inserting records into non dbo schema

I have a basic data flow which tries to insert data from an excel spreadsheet to a loading table (sql server 2005). I have created this table in a non dbo schema. I have used the schema owner as the sql server login for this loading step.

The problem is SSIS seems to throw a strange error when I do this:

OnError,VH0635,VHOLS\blakema,Populate Load Table,{F1C28F63-39D2-4FBB-9803-E24385014E9F},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,VH0635,VHOLS\blakema,RunControllerFares,{0F7B32E6-58D9-4DD4-A0AC-311E2C194028},{514E8012-6998-409C-BED1-E04CE3200295},06/09/2006 11:17:37,06/09/2006 11:17:37,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

When I create this table in the dbo it seems to work ok. I have tried giving the schema owner sa rights on the sql server and it still doesnt work. Im wondering if this is a known bug in ssis.

Does anyone have any ideas?This turned out to be a conflict between using Nvarchar(max) and Nvarchar(255). Even though the data would fit ssis didnt seem to like it.|||

Of course not. they're two different data types!

If I understand correctly varchar(max) is not the same as an infinitely long "normal" varchar. Or it helps to not think about it that way anyway.

I'm sure you'd get a better answer on the T-SQL forum

-Jamie

sql

Wednesday, March 28, 2012

Problems in executing SQL or Stored Procs with ASP

Hi experts,

I m working with MS SQL Server 2000 with ASP for my application. There're some Stored Procedures created for the new functions but seems I can't run these new SPs with my ASP pages. When I load that ASP page, it shows error message that can't find my SP. I've execute the SP alone in SQL Enterprise Manager and it works.

When I work with the SPs, I can connect with the DB with Enterprise Manager only without administrator right. As my SPs I suppose they should work with ASP but I just worry would it be the problem with my right granted in SQL server?

There are some other SPs running fine created by dbo but for my SPs not by dbo. Would there be any differences? Does it mean my SPs need to be granted by dbo instead of my current role? I m sure if I use the same ASP page then running another existing SPs, it works really smooth.

I also tried to make a SQL statement in my ASP page (e.g. an insert statement) but it seems nothing can be inserted. I got really screwed up!!!

Thanks in advance!!
Manfred

Manfred:

First, how are your ASP pages connecting to your database? Do you know they login, etc.? How do you give this login/user permissions to execute your stored procedure? Also, you might have an "owner" problem with the stored procedure object. Try running this query and posting the results:

select type,
uid,
left ([name], 40) as [name]
from sysobjects
where type = 'P'
and name = 'yourProcName'
-- - Sample Query Results: --
-- type uid name
-- - -
-- P 1 myProcName

Dave

|||

Thanks a lot for your advice Dave!

I got a dbo login name/password from my colleague and I've created a new SP under dbo login. It works! I can execute the stuff I want with the SP.

Here 's the query result

SELECT type, uid, LEFT(name, 40) AS name
FROM dbo.sysobjects
WHERE (type = 'P') AND (name = 'PROC_TESTDEPT')

-- type uid name
-- - -
-- P 1 proc_TestDept

Monday, March 26, 2012

Problems getting information out of a SQLDataSource

I'm working in Visual Studio, I've added a SQLDataSource to the webpage which links to a SQL server database via a DSN (created with the wizard) and it brings back data from a stored procedure with no problems.

I want to access the information from this SQLDataSource in my code and can't seem to get anything from it.

help would be most appreciated.

What do you want to do with the data? Put it in a Gridview? Datalist? Here is a article with a GridView accessing a datasource.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/GridViewEx02.asp

|||I actually want to put the information into a text field rather than a datagrid.|||gave up on that and did it the easy way, all code rather than using the wizards.|||

How would you want to access the information from the SqlDataSource? If you just want to access the database retrieved from database by the Select command, you can do something like this:

SqlDataSource sds = new SqlDataSource(conn.ConnectionString, cmd.CommandText);

DataSourceSelectArguments dssa = new DataSourceSelectArguments();

dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);

DataView dv = (DataView)sds.Select(dssa);

Problems executing DML via linked server

Hey folks..
I have a linked server that connects to a DB2 database through the OLE DB
for ODBC Providers driver. I have a DSN created and can, through the linked
server run SELECT statements to my heart's content. When I want to run a
simple DELETE, for example (DELETE HCEDB.APPLQUE2), I get the following
error.
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I have gone to a colleague's machine to use a DB2 Client and authenticated
using the same user specified in both the DSN and Linked Server and was able
to execute the DELETE. Any ideas as to cause and resolution?
Thanks..
Peace,
Gary HampsonIf you are using a DELETE FROM (Table Name), beware.
I was doing the same thing on a DB2 Database with some test data given to me
, and I would get the same error when I tried to delete using the Linked Ser
ver, where if I tried to do it straight in the DB2 Console, I could execute
without any problems.
So I ran an Event Monitor. Happens out, the Microsoft OLE DB Provider for OD
BC Drivers turns it into count(*) many "DELETE FROM TABLE WHERE field = ?".
So if your table has 1000 rows, you'll have 1000 Delete Statements run in se
quence. Very inefficent, but it gets the job done. The reason for my error w
as that there were 3 rows that were identical to each other, and so it had 3
identical "DELETE FROM" statements.
My solution: Deleted the replicated rows. It apparantly was an oversight whe
n creating the test data.
I still have an issue with it producing all those Delete Statements though.
I'm currently looking into other ways of doing it.

Friday, March 23, 2012

Problems Creating TVF function

I had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:

Code Snippet

CREATE FUNCTION f_GetGroupMembership(@.providerKey nvarchar(200), @.connection nvarchar(1000))

RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25))

EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser

This returns an error:

Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1

The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.

Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:

Code Snippet

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.DirectoryServices;

using System.Collections;

public class GroupMembership

{

[SqlFunctionAttribute(FillRowMethodName = "FillGroupRow")]

public static IEnumerable GetGroupsForUser(String ProviderKey, String Connection)

{

return groups.GetGroupsForUser(ProviderKey, Connection);

}

public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type)

{

//cast the enumerator passed in

group grp = (group)obj;

//create output parameters

guid = new SqlGuid(grp.guid);

name = new SqlString(grp.Name);

type = new SqlString(grp.applicationData);

}

}

Can anyone tell me why this is happening? How can I overcome this problem?

Help will be highly appreciated.

Help Please! there is no reference to this error anywhere|||
In your CREATE FUNCTION statement, you are referencing the wrong class, groups.GetGroupsForUser instead of GroupMembership.GetGroupsForUser.|||

Steven,

Thank you I figured out I did not reply here because I did not want to look like the dumba** that I am. Thanks Anyway. I'll try to review my code a third time before I make a fool of myself in public.

Regards,

Problems converting varchar to smallmoney

My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.

The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)

As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.

What do i have to do to convert the value as it is entered by the user?

Thanks in advance

Statement:

INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()

DDL for concerned database:

CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

I am little confused. In your INSERT INTO statement, you are converting {3} and {4} to numeric, while the datatype for these two columns are smallmoney. Why don't you use smallmoney directly in the INSERT statement?

The reason it rounds up for you is that the default scale (max number of decimal digits) of numeric data type is 0. It would work fine if you use numeric(20, 2) for example. See "decimal and numeric (Transact-SQL)" in SQL Server Book Online for details.

|||

thx

seems to work now using smallmoney directly or numeric (20,2)

Tuesday, March 20, 2012

Problems attaching to database from VWD

Hi Mike,

I hope you can help me with my SQL Server Express problem. I have a database that was created in VWD Express 2005, that is currently in C:\web projects\project name\App data...etc. I have been able to connect to and use the database with an application that I am building as a learning exercise, ( I'm a complete newbie to .NET and SQL Server) but now I can't connect to the database from SQL Server manager.

I have tried attaching the file which has worked a few times, but now that won't work either. I keep getting a message that the file can't be found or I do not have sufficient permissions to access it. I can connnect to the file fine from within VWD, but because of the above problem I can't now run the application (it throws an exception).

I suspect that I've got some of the basic configuration settings wrong, but I really don't know where to start looking. Below is an example of one of the messages I've been getting

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
CREATE FILE encountered operating system error 32(error not found) while attempting to open or create the physical file 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf'. (.Net SqlClient Data Provider)

Any help would be very gratefully recieved

regards

jon

Hi Jon,

I split this into a new thread, you added it to the bottom of an unrelated question where it would have gotten lost.

The first problem I see is that you're said your file was located at C:\web projects\project name\App data, but the error says it's looking for the file at 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf', these are two different directories. I'm not sure where the difference comes from.

The real problem is probably related to User Instances. When you create a database in VWD, it's going to set it up to use a User Instance, which is a special process of SQL Server that is started just for the application calling your database. Since a database file can only be attached to a single instance of SQL Server at a time, you can't attach the database to the parent instance, which is what you are looking at in Management Studio, while it is being used by the User Instance that is started by your application.

The second likely issue is that SQL Express is running as Network Service, which is a low privelelge account that does not have permissions to the directory where your database is located. SQL Express can not attach to database files in folders that it doesn't have permissions for. You would need to set the folder permissions to allow tje Network Service account access to the database file.

For the record, the way the VS Express products, such as VWD Express, are designed to work with SQL Express pretty much assumes that you will do all your database manipulation using the VWD interface. Trying to use Managment Studio in combination with VWD Express can cause some odd behavior because of this. You should read the white paper on User Instances to gain a better understanding about what this is as it is what's going to cause you the most confusion if you try to use these two products together.

Mike

|||

Thank you very much for your help with this issue, I will read the White paper that you suggested and hopefully that will clear a few things up. I didn't realise that VWD and VS Express work so independantly of each other, and you are right, the behavior has been odd and inconsistent, which added to my confusion.

Thanks again for your help,

Jon

|||Hello Mike,

> The second likely issue is that SQL Express is running as Network Service, which is a low privelelge
> account that does not have permissions to the directory where your database is located. SQL Express
> can not attach to database files in folders that it doesn't have permissions for. You would need to set
> the folder permissions to allow tje Network Service account access to the database file.

have found this:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx
See headline Detaching and Attaching a Database.

I have written a little visual basic 2005 test program and found the following.

There is a SQL Server login for the standard user which I used mapped to the sysadmin server role.

1.
The .mdf file is in a subdirectory of the all users profile directory.
Network service has no permissions on the .mdf file:

Windows authentication:

Logged on as administrator:
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

Logged on as standard user:
The standard user himself did copy the file in the directory.
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file read only.

Logged on as standard user.
The standard user himseld did copy the file in the directory.
sp_attach_db attaches the .mdf file read only.

2.
The .mdf file is in the Data Directory of SQL Server.
Network service has full permissions on the .mdf file:

Windows authentication:

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
sp_attach_db ends with the message Access denied.

So it seems that a standard user can't run a program which calls an attach.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
There is a login for the user mapped to the sysadmin server role.
sp_attach_db attaches the .mdf file correctly.

Here this worked what did not with Windows Authentication.

|||Supplement:

To Point 1.
After attaching a .mdf file which is in i a subdirectory of the all users profile directory:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

After detaching the Database with the Management Studio i tried again:

Windows authentication:
Logged on as standard user:
Access denied.

SQL Server server authentication:
(SA was the user in the connection string.)
Logged on as standard user.
sp_attach_db attaches the .mdf correctly.

|||

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop (name of database)
EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.

Problems attaching to database from VWD

Hi Mike,

I suspect that I've got some of the basic configuration settings wrong, but I really don't know where to start looking. Below is an example of one of the messages I've been getting

Any help would be very gratefully recieved

regards

jon

Hi Jon,

I split this into a new thread, you added it to the bottom of an unrelated question where it would have gotten lost.

Mike

|||

Thanks again for your help,

Jon

|||Hello Mike,

have found this:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx
See headline Detaching and Attaching a Database.

I have written a little visual basic 2005 test program and found the following.

There is a SQL Server login for the standard user which I used mapped to the sysadmin server role.

1.
The .mdf file is in a subdirectory of the all users profile directory.
Network service has no permissions on the .mdf file:

Windows authentication:

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file read only.

Logged on as standard user.
The standard user himseld did copy the file in the directory.
sp_attach_db attaches the .mdf file read only.

2.
The .mdf file is in the Data Directory of SQL Server.
Network service has full permissions on the .mdf file:

Windows authentication:

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
sp_attach_db ends with the message Access denied.

So it seems that a standard user can't run a program which calls an attach.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
There is a login for the user mapped to the sysadmin server role.
sp_attach_db attaches the .mdf file correctly.

Here this worked what did not with Windows Authentication.

|||Supplement:

SQL Server server authentication:
(SA was the user in the connection string.)
Logged on as standard user.
sp_attach_db attaches the .mdf correctly.

|||

Hi Mike,

1) I close down VWD and open Management Studio,

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop (name of database)
EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

Jon

Problems adding an article to an existing merge replication if owner is not dbo

Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
Just some more info. It seems like the problem occurs when the snapshotagent
generates the bulk copy data for system table
ms_merge_contents_<<newtable>>. It seems like this procedure doesn't use the
right owner to generate the bcp file.
Stefan
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> schreef in bericht
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I created a new table on the publication database the owner of the new
> table is not dbo. We then called sp_addmergearticle to add the article to
> the publication, using the @.source_owner and @.destination_owner parameters
> to specify the owner is different from dbo, and the
> @.force_invalidate_snapshot parameter.
> When we start the snapshot agent, it prepares the newly added table for
> replication, and generates the necessary scripts. At one moment the
> snapshot agent stops with an error stating 'invalid object name' and the
> name of the new table. I think this occurs when the snapshot agent wants
> tot generate the bcp files.
> Inspecting the article's properties dialog, showed 'that source table
> owner' and 'destination table owner' are correct. And except for the
> tablename an article name the properties are no different to the
> previously published articles.
> I retried this for a table that has dbo as owner. This worked without
> problems.
> Does anybody experienced the same problems?
> Best regards,
> Stefan
>
>
|||I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs in merge replication system stored procedures. Try looking for my messages (end of last year) - you should find attached archive with those fixes. if you will not find it, I could re-post it in this thread (I have fixes for SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore

Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message news:%234dbV1GBFHA.3528@.tk2msftngp13.phx.gbl...
Thanks Kestutis,
I looked for your posts but I didn't find them. Could you repost them?
regards,
Stefan
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> schreef in bericht
news:ORM4krGBFHA.1400@.TK2MSFTNGP11.phx.gbl...
I had multiple problems with merge replication of objects not owned by dbo.
And I made multiple posts to this newsgroup with a solution - fixes to bugs
in merge replication system stored procedures. Try looking for my messages
(end of last year) - you should find attached archive with those fixes. if
you will not find it, I could re-post it in this thread (I have fixes for
SQL server versions 8.00.760 (SP3a) and 8.00.818)
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Stefan Gevaert" <stefan.gevaert@.omegasoft.be> wrote in message
news:%23dRgpbFBFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
I created a new table on the publication database the owner of the new table
is not dbo. We then called sp_addmergearticle to add the article to the
publication, using the @.source_owner and @.destination_owner parameters to
specify the owner is different from dbo, and the @.force_invalidate_snapshot
parameter.
When we start the snapshot agent, it prepares the newly added table for
replication, and generates the necessary scripts. At one moment the snapshot
agent stops with an error stating 'invalid object name' and the name of the
new table. I think this occurs when the snapshot agent wants tot generate
the bcp files.
Inspecting the article's properties dialog, showed 'that source table owner'
and 'destination table owner' are correct. And except for the tablename an
article name the properties are no different to the previously published
articles.
I retried this for a table that has dbo as owner. This worked without
problems.
Does anybody experienced the same problems?
Best regards,
Stefan
|||OK - I've put them on the downloads section. I can't give you the precise link, other that request that you browse to www.replicationanswers.com, as this website is currently done on the cheap (soon to change providers so I can have absolute urls).
Rgds,
Paul Ibison (SQL Server MVP)
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message news:%23AaQjgHBFHA.2552@.TK2MSFTNGP09.phx.gbl...
Here they are.
Make sure that you will apply them VERY carefully and step by step.
Also make sure that version of your SQL Server (SELECT @.@.VERSION) and version indicated in my fixes DO MATCH. Othervise you WILL have problems.
Good news is that Paul Ibson kindly offered to put my scripts on www.replicationanswers.com, so in near future all the explanations and fixes regarding merge replication of "non dbo" objects should appear there. I will not need to repost them in this newsgroup anymore

Friday, March 9, 2012

Problem written with few words... come and read

I've created an alias of my computer name
with the Network client utility
Computer name : SCG59730
Instance name : Mercure
Alias : MyProject
When I try to register this new server (MyProject\Mercure)
SQL Server doesn't find the serverI think you should simply register with the alias alone:
MyProject
not MyProject\Mercure
.. although I have not used it very much.|||In fact I wanted to create an Alias
to avoid using the Machine Name in the connection string

but I FINALLY found the of creating my local server in
the Enterprise Manager

the name of the server must be

(LOCAL)\INSTANCENAME

thanks anyway

Saturday, February 25, 2012

Problem with website and db

I am new to .NET and the Visual Web Developer Express and what not...I have created a website on my machine which works wonderfully...however, and I'm sure this is somewhere but I can't find it, I cannot get it to work on my host server...the Default page comes up, but when I try to sign in it says:

"An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\harrison0801\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

The host support tells me to change the db name from aspnetdb to something else...I do this and when I run the site again or try to log in, I get the exact same error message...then they go on to tell me that I have to remove the LocalSqlServer in the web.config file which now looks like this:

</connectionStrings>

Of course this isn't working either...otherwise I would off playing on my server, and not posting here. Also, when I set up all the roles, users, and what not on my machine...does this carry over to the host machine?

Desperately needing massive help here...

Not a DBA and no expert in .NET

Thank you very much in advance,

Darren

Rename the .mdf file from aspnetdb.mdf to harrison0801.mdf and change your connection string to

|||

Okay...renamed the ASPNETDB.MDF and changed the config connection string to the above...hit CTRL F5 to run it...and guess what magically appeared? ASPNETDB without regard to the one I just changed the name on.

Also, when I run the ASP NET Configuration utililty in the VWD...walks me through all the roles, users, etc...it loads all that info into the ASPNETDB...is there something in the default config for the DB creator that can be changed? Perhaps this is something that the ASPNET creators can work out...maybe like allow the developers to name there own DB in the process of creating websites? Would be nice...less of a headache.

Monday, February 20, 2012

Problem with user created

Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and pa
s
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database object
s
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test databas
e
and the few extended sp in the master database.
ThanksAll users can access master, msdb and tempdb via the guest user in those
databases. Access to master and tempdb via the guest user is essential for
the proper functioning of any user login. Msdb access is not strictly
essential, but users by default have permissions to create jobs and DTS
Packages, among others, and jobs and DTS Packages are stored in msdb
Jacco Schalkwijk
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
> Hi,
> I created a user (from EM) and give him only access to a test database and
> 3
> extended sp in the master db, however, when I login with the username and
> pas
> in Query Analyzer I can access app the sp, extended sp, tables in the
> master
> database, tempdb and the msdb. I even see them listed in the database
> objects
> section. When I go to users under tempdb and msdb I don't see the user
> there
> whent going on here. I only want the user to access and see the test
> database
> and the few extended sp in the master database.
> Thanks|||It is probably due to the fact that the guest user is enabled in those
databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and
pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database
objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test
database
and the few extended sp in the master database.
Thanks|||Thanks Guys!
"Chris" wrote:

> Hi,
> I created a user (from EM) and give him only access to a test database and
3
> extended sp in the master db, however, when I login with the username and
pas
> in Query Analyzer I can access app the sp, extended sp, tables in the mast
er
> database, tempdb and the msdb. I even see them listed in the database obje
cts
> section. When I go to users under tempdb and msdb I don't see the user the
re
> whent going on here. I only want the user to access and see the test datab
ase
> and the few extended sp in the master database.
> Thanks

Problem with user created

Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test database
and the few extended sp in the master database.
ThanksAll users can access master, msdb and tempdb via the guest user in those
databases. Access to master and tempdb via the guest user is essential for
the proper functioning of any user login. Msdb access is not strictly
essential, but users by default have permissions to create jobs and DTS
Packages, among others, and jobs and DTS Packages are stored in msdb
--
Jacco Schalkwijk
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
> Hi,
> I created a user (from EM) and give him only access to a test database and
> 3
> extended sp in the master db, however, when I login with the username and
> pas
> in Query Analyzer I can access app the sp, extended sp, tables in the
> master
> database, tempdb and the msdb. I even see them listed in the database
> objects
> section. When I go to users under tempdb and msdb I don't see the user
> there
> whent going on here. I only want the user to access and see the test
> database
> and the few extended sp in the master database.
> Thanks|||It is probably due to the fact that the guest user is enabled in those
databases.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and
pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database
objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test
database
and the few extended sp in the master database.
Thanks|||Thanks Guys!
"Chris" wrote:
> Hi,
> I created a user (from EM) and give him only access to a test database and 3
> extended sp in the master db, however, when I login with the username and pas
> in Query Analyzer I can access app the sp, extended sp, tables in the master
> database, tempdb and the msdb. I even see them listed in the database objects
> section. When I go to users under tempdb and msdb I don't see the user there
> whent going on here. I only want the user to access and see the test database
> and the few extended sp in the master database.
> Thanks

Problem with user created

Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test database
and the few extended sp in the master database.
Thanks
All users can access master, msdb and tempdb via the guest user in those
databases. Access to master and tempdb via the guest user is essential for
the proper functioning of any user login. Msdb access is not strictly
essential, but users by default have permissions to create jobs and DTS
Packages, among others, and jobs and DTS Packages are stored in msdb
Jacco Schalkwijk
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
> Hi,
> I created a user (from EM) and give him only access to a test database and
> 3
> extended sp in the master db, however, when I login with the username and
> pas
> in Query Analyzer I can access app the sp, extended sp, tables in the
> master
> database, tempdb and the msdb. I even see them listed in the database
> objects
> section. When I go to users under tempdb and msdb I don't see the user
> there
> whent going on here. I only want the user to access and see the test
> database
> and the few extended sp in the master database.
> Thanks
|||It is probably due to the fact that the guest user is enabled in those
databases.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4AA98274-740F-4F89-8178-CED99DD1DCF0@.microsoft.com...
Hi,
I created a user (from EM) and give him only access to a test database and 3
extended sp in the master db, however, when I login with the username and
pas
in Query Analyzer I can access app the sp, extended sp, tables in the master
database, tempdb and the msdb. I even see them listed in the database
objects
section. When I go to users under tempdb and msdb I don't see the user there
whent going on here. I only want the user to access and see the test
database
and the few extended sp in the master database.
Thanks
|||Thanks Guys!
"Chris" wrote:

> Hi,
> I created a user (from EM) and give him only access to a test database and 3
> extended sp in the master db, however, when I login with the username and pas
> in Query Analyzer I can access app the sp, extended sp, tables in the master
> database, tempdb and the msdb. I even see them listed in the database objects
> section. When I go to users under tempdb and msdb I don't see the user there
> whent going on here. I only want the user to access and see the test database
> and the few extended sp in the master database.
> Thanks

Problem with update query based on subquery

Hello all!
I have created a update query, which looks like this:
UPDATE NMR.dbo.NMR_wpisy
INNER JOIN
(SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
[Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
= [Confirmed_Production_Labor]
Could you modify these query to be right, because there is the error? I
would be very grateful for it...
Please have a look what I would achieve:
I want to sum double or more values from the table using such query
(let`s call it query1):
SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
Machine]) AS Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
ProductionConfirmation ON (N.workcenter_sortowanie =
NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
= ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
This query works fine. After that I want to update the values of
NMR_wpisy table which are in relations with nr_NMR field from the above
query1 and there (in query1) are the values which I would like to to
update the fields. I don`t know how to do that. I was trying to do it on
MS Access and copy this whole query into the MS SQL but if the subquery
exists I cannot do that.
Please help
I would be very grateful for it
Best regards
Marcin
*** Sent via Developersdex http://www.examnotes.net ***Try,
UPDATE a
SET
a.czas_machine_sortowanie = b.[Confirmed_Production_Machine],
a.czas_labour_sortowanie = b.[Confirmed_Production_Labor]
from
NMR.dbo.NMR_wpisy as a
INNER JOIN
(
SELECT
N.nr_NMR,
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine,
Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
Confirmed_Production_Labor
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
GROUP BY
N.nr_NMR
) as b
ON b.nr_NMR = a.nr_NMR
-- or
UPDATE NMR.dbo.NMR_wpisy
SET
NMR.dbo.NMR_wpisy.czas_machine_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Machine]) AS
Confirmed_Production_Machine
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0),
NMR.dbo.NMR_wpisy.czas_labour_sortowanie = isnull(
(
SELECT
Sum(ProductionConfirmation.[Confirmed Production Labor])
FROM
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
GROUP BY
N.nr_NMR
), 0)
where
exists(
select
*
from
NMR.dbo.NMR_wpisy N
INNER JOIN
ProductionConfirmation
ON (N.workcenter_sortowanie = NMR.dbo.ProductionConfirmation.WorkCenter)
AND (N.nr_zlecenia_sortowanie = ProductionConfirmation.ProductionOrder)
where
N.nr_NMR = NMR.dbo.NMR_wpisy.nr_NMR
)
go
AMB
"Marcin Zmyslowski" wrote:

> Hello all!
> I have created a update query, which looks like this:
> UPDATE NMR.dbo.NMR_wpisy
> INNER JOIN
> (SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR)
> ON NMR.dbo.NMR_wpisy.nr_NMR=N.nr_NMR SET
> NMR.dbo.NMR_wpisy.czas_machine_sortowanie =
> [Confirmed_Production_Machine], NMR.dbo.NMR_wpisy.czas_labour_sortowanie
> = [Confirmed_Production_Labor]
> Could you modify these query to be right, because there is the error? I
> would be very grateful for it...
> Please have a look what I would achieve:
> I want to sum double or more values from the table using such query
> (let`s call it query1):
> SELECT N.nr_NMR, Sum(ProductionConfirmation.[Confirmed Production
> Machine]) AS Confirmed_Production_Machine,
> Sum(ProductionConfirmation.[Confirmed Production Labor]) AS
> Confirmed_Production_Labor FROM NMR.dbo.NMR_wpisy N INNER JOIN
> ProductionConfirmation ON (N.workcenter_sortowanie =
> NMR.dbo.ProductionConfirmation.WorkCenter) AND (N.nr_zlecenia_sortowanie
> = ProductionConfirmation.ProductionOrder) GROUP BY N.nr_NMR
> This query works fine. After that I want to update the values of
> NMR_wpisy table which are in relations with nr_NMR field from the above
> query1 and there (in query1) are the values which I would like to to
> update the fields. I don`t know how to do that. I was trying to do it on
> MS Access and copy this whole query into the MS SQL but if the subquery
> exists I cannot do that.
> Please help
> I would be very grateful for it
> Best regards
> Marcin
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Friday, March 30, 2012

Problems installing External Stored procedure

Problems inserting records into non dbo schema

Wednesday, March 28, 2012

Problems in executing SQL or Stored Procs with ASP

Monday, March 26, 2012

Problems getting information out of a SQLDataSource

Problems executing DML via linked server

Friday, March 23, 2012

Problems Creating TVF function

Problems converting varchar to smallmoney

Tuesday, March 20, 2012

Problems attaching to database from VWD

Problems attaching to database from VWD

Problems adding an article to an existing merge replication if owner is not dbo

Friday, March 9, 2012

Problem written with few words... come and read

Saturday, February 25, 2012

Problem with website and db

Monday, February 20, 2012

Problem with user created

Problem with user created

Problem with user created

Problem with update query based on subquery

Sql Update command

Blog Archive

About Me