Problem with SELECT and INSERT T-sql statement

hello everybody

I want to ask for your help in an issue i am having with SQL Server 2005 Developer Edition . here is the issue:

We have 2 servers called: c10 and cweb. In both, we manually installed SQL server 2005 Dev Edition with no problems.

I created a linked server on c10 to access data on cweb. That is working fine with no problem when executing Select or Insert T-SQL statments like these ones from c10:

select * from cweb.DBNAME.dbo.TableNAME

Or

insert into cweb.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c10.DBNAME.dbo.TableNAME

All works fine up to here. But then there is a new server we setup called c7. This time we created an image of c10 and restore that image on this new server c7. That way, we didnt need to install all software needed in this new server. All software seemed to work ok..but then SQL server 2005 on that new server started failing when doing SELECT t-sql statements.

So Now if i am on c7 and i try to execute this: SELECT * from C7.DNAME.dbo.TableName, it fails

C7 in this case is the local server and it should work. however the error it gives me is that :"linked server not recognize"...it shouldnt need a linked server since it is trying to access the local server. Even with that, i tried to create a linked server to the own local server and now that Select t-sql isntruction worked with no problem..But now here is the othe issue i am having: INSERT t-sql statements are not working. When doing this:

insert into c7.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c7.DBNAME.dbo.TableNAME2

It fails with the following 2 error messages:

"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done

The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."

I checked that the SELECT part of the INSERT T-sql statement is not retrieving any invalid data for column intID.

I tried restoring the BD on c10 server and tried the same INSERT statement and it worked ok..which mean the data to be inserted is valid.

So i think it is related to some mis-configuration on the linked server or something in SQL server got broken when restoring c10 server image into the new c7 server

So in summary the problem is this:

1. i can not make SELECT T-sql statements using fully qualified names on the local sql server without having a linked server to the local server (which is strange)

2. I can not make INSERT T-sql statements in the local server. This errors happens when doing it

"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done

The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."

I have been searching thru google and forums but havent found any solutions yet.

Hope you can help me with this..i guess my only option right now is just uninstall and re-install sql server..but maybe there is any other solution to this_?

thanks a lot

Helkyn

This is a Transact-SQL question, not an SSIS question. Moving there...

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

Problem with rollback trigger

Hi, Everybody,

I'm a Hungarian SQL user and I need a little help for SQL Server 7 !

I protect my table against bad data with a trigger. I use ROLLBACK and
RAISERROR statement in this trigger. Users can get my error message
after manual input, but the stored procedure always cancel because of
ROLLBACK. So the input program dont't have chance to analyze the error
message. I could work without ROLLBACK, but it wouldn't be the best
solution.

What's the correct solution with ROLLBACK statement?Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93D032354DC9Yazorman@.127.0.0.1>...
> level8 (level8@.freemail.hu) writes:
> > I protect my table against bad data with a trigger. I use ROLLBACK and
> > RAISERROR statement in this trigger. Users can get my error message
> > after manual input, but the stored procedure always cancel because of
> > ROLLBACK. So the input program dont't have chance to analyze the error
> > message. I could work without ROLLBACK, but it wouldn't be the best
> > solution.
> > What's the correct solution with ROLLBACK statement?
> I don't really understand what you mean with "the input program don't
> have a change to analyse the error message". If you with the input
> program mean the stored procedure, yes, this is correct. Once the
> trigger exits with @.@.trancount = 0, the batch is bye-bye. On the
> other hand the procedure cannot get the message anyway, because only
> the client gets the text of the message.
> And if the input program is the client program, then the rollback
> does not prevent the client from getting the message.
> Note that the ROLLBACK itself does not terminate the batch. Execution
> continues to the end of the trigger. It is the condition @.@.trancount = 0
> on trigger exit that cancels the batch.

Thanks, Erland!

My name is Lszl - from Hungary. Yes, input program is a stored
procedure. I write the trigger and another person writes the input sp.
Yes, trigger continues to the end and it can create an error message
in a log table, but a I would like to use ROLLBACK statement. Can I
set @.@.trancount? Or is it wrong idea?

Lszl|||level8 (level8@.freemail.hu) writes:
> My name is Lszl - from Hungary. Yes, input program is a stored
> procedure. I write the trigger and another person writes the input sp.
> Yes, trigger continues to the end and it can create an error message
> in a log table, but a I would like to use ROLLBACK statement. Can I
> set @.@.trancount? Or is it wrong idea?

Well, you could do:

IF <test for bad data>
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Found bad data', 16, 1)
INSERT errorlog (...)
BEGIN TRANSACTION
END

That is, you can start a new transaction at the end of the trigger. I would
strongly discourage you from doing this, because the consequences are
unclear, least to say. The caller will be left with an open transaction
that he needs to take care of.

So, leave it to the client to handle the error.

I have two articles on by web site of error handling in SQL Server, which
will not really help to solve this problem, but gives some information
about what you can and cannot do. You may know some of the stuff already.
The link to the first article is
http://www.algonet.se/~sommar/error-handling-I.html.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93D05E7181F35Yazorman@.127.0.0.1>...
> level8 (level8@.freemail.hu) writes:
> > My name is Lszl - from Hungary. Yes, input program is a stored
> > procedure. I write the trigger and another person writes the input sp.
> > Yes, trigger continues to the end and it can create an error message
> > in a log table, but a I would like to use ROLLBACK statement. Can I
> > set @.@.trancount? Or is it wrong idea?
> Well, you could do:
> IF <test for bad data>
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Found bad data', 16, 1)
> INSERT errorlog (...)
> BEGIN TRANSACTION
> END
> That is, you can start a new transaction at the end of the trigger. I would
> strongly discourage you from doing this, because the consequences are
> unclear, least to say. The caller will be left with an open transaction
> that he needs to take care of.
> So, leave it to the client to handle the error.
> I have two articles on by web site of error handling in SQL Server, which
> will not really help to solve this problem, but gives some information
> about what you can and cannot do. You may know some of the stuff already.
> The link to the first article is
> http://www.algonet.se/~sommar/error-handling-I.html.

Thanx !

problem with Roles (Read Definition)

Hi everybody,

I have a problem:

- if I assign to a role the general "Read Definition" rights (on the database), I am not able to reduce the visibility on the dimensions, since they are all with "Read Definition" rights and at least "Read" access.

- on the contrary, if I dont'assign to a role the general "Read Definition" rights, and then set "Read Definition" rights on some dimensions (and the "Read" access, too), the users of this role are not allowed to connect to the OLAP database!

This way, it's not possible to assign selective visibility to the dimensions, and it seems you are forced to always set the general "Read Definition" rights in order to access a database (so this flag would be quite unuseful). Can someone suggest me what I am doing not correct?

Thank you very much,

ReadDefinition permission allows a user bit more than access to dimensions , It allows user to retreive metadata for dimension.

What you probably looking for is to grant specific role Read access to one of your cubes. For instance grant Read access to your Sales cube.

If you would like to deny users seeing data in one of dimensions, you can click on the Dimension Data node in the Edit Role dialog.
In this page you should be able to deny acess to individual attributes in your dimension by selecting "Deselect All members" option. Go through all attributes in your dimension and Role users will not be able to see dimension data.

You can test security the Role you create by going into the Cube browser and clicking on the "Change User" (first icon on the upper left corner of the browser).

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you very much for your kind answer.

From what you say, I understand there is no way to limit access to OLAP database metadata to users in roles with general Read Definition rights, correct? Therefore, all the users are allowed to see the definition and properties of all the dimensions, cubes, etc and the roles themselves and we can limit only the "data" (members, cells and so on). Is it?

Another question, in the BIDS environment I couldn't find the "ApplyDenied" property for the Dimension Data, can it be used only programmatically or maybe I didn't search in the right place?

Thank you very much!

|||

To clarify my post above;

To grant Read access to your cube and dimension you don't need to grant your users ReadDefinition rights to your database. It is just enough to grant them Read rights to your cubes and configure access to dimensions.

ReadDefinition is "stronger" compared to the Read permission. You would grant ReadDefinition right to allow your users to peroform advanced tasks: like creating local cubes...

As for the properties of the objects avaliable to you through AMO. Try and browse your database using AmoBrowser sample application. If you installed samples, you should find it under %Drive%:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO. Using AmoBrowser you should be able to see all properties exposed for any AS object.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

problem with role distribution

hello to everybody.

well i have a problem with role distribution too users in a local network. well i crated the roles (browsers) and when they acess report manager they can see only the reports. but when they try to run the report they get a message that either thei don't have access to the analysis services database or the database does not exist. when i ppublish a report isn't there a datasource view with it? then why they cannot run the reports?

thnxs

Hi,

do you defined a datasource in the report properties?

|||do u mean in the report manager? yes when i enter as an administrator and and i look in the properties of a report in the report manager i see the data source of the report. i have chosen integrated security of windows.|||So i am not able to help you.

Problem with printing bank bills

Hi everybody,

I have done a Crystal Report document with the heigth of a bank bill, with the option of printing in a continuous paper in a matrix printer.

Then I create a PDF document, but when I print them, at the end of each page of the PDF document, i have a blank. And this blank is what moves the next bills to another position and here it is the problem. I don't know why is this blank.

I have search in internet but i don't know what to do. I have done all in c# language.

Is there anyone who has printed bank bills with c#? Any ideas?

Thanks.Try Suppressing footers if data is not there to display.

Problem with printing

Hello everybody,

i have used report viewer control in my win app, (c#, .net 2.0) and from msql i get data, fill necessary tableadapters and print report. Everything works fine on my computer, i generate report from template, push Print button in report viewer, Print dialog appears and i print the report by clicking OK.

The problem occurs in another computer, where i launch my app, i generate report, push Print button, and when appears print dialog happen strange things:

1. In print dialog i push "Print" button once, and nothing happens, Print dialog is still on top, then i push second time Print and then i can see that to pages is sended to printer (but my report is only one page). Printer does nothing, in printer schedulles there is no jobs.

2. So i press Print button in report viewer second time, Print dialog appears again, i click Print button nothing happens, then i push second time Print button i can see that 1 page is sended to printer, Print dialog disapear and now printer print my report correctly.

and this mesh is everytime i generate report and want to print it. Printer is installed correctly, i tried newest drivers for it. For example with ms office word everything works fine.

Could it be report viewer bug? what are the ways to find out what is wrong?

I have had the same problems in reporting services. Sometimes it doesn't print, sometimes it does. Sometimes it prints just the one page. Sometimes it prints multiple. I have tried just messing with the margins in the Report Properties menu button. I have found that SSRS doesn't print the report evenly on the page, too, so having compensated by setting the left margin .5 inches less then the right margin it now doesn't print more then one page as often. I don't know if that exact tweek will work for you, but maybe that will give you an idea what to look for. I would assume that most of these things are bugs, but they are not too hard to work around. Good luck!|||

What version of SSRS are you running? Go to http://<server>/ReportServer and note the number in the bottom right. 9.00.1399.00 is RTM (release to manufacturer) and it has known issues printing. There was a hotfix released for what might be your problem, but you should get the latest service pack (SP2) as it rolls up all of the fixes and has been through more testing.

Good luck,

Larry

|||I have version 9.00.1406.00 and it still has some problems with printing. Is this the latest version?|||

Here is a list of build numbers for the various versions of SQL Server 2005 and it indicates that you have a single patch installed (KB 932557) but that is all: 932557) but that is all: http://www.sqlservercentral.com/columnists/sjones/2960.asp

You may well find that applying SP2 will fix your issue.

Larry

problem with orphaned "dbo" user of an attached database

Hi everybody,
I'm in a situation where I allow my users of SQL Server the creation of her
own databases. This is been done in order to allow the "creator" (the owner)
full acces to the created DB. Everything works fine, as long as the DB is
created from scratch, like:
CREATE DATABASE universe
But additionally, my users are also exchanging databases, so they will also
use
CREATE DATABASE universe ... FOR ATTACH
When attaching a database, the owner of the so created database is the login
that
performed the operation, which is fine. But, the "dbo" user in the attached
database is still associated with the original login (before detaching the
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know that
this can be fixed with
ALTER AUTHORIZATION ON DATABASE::universe to [login]
So, after attaching a database, the user can simply all ALTER AUTHORIZATION.
My problem is that ALTER AUTORIZATION requires "CONTROL SERVER" permission,
which is simply a synonym for "sysadmin" role membership and therefore not
what I want. All I want my users grant is "CREATE ANY DATABASE" permission.
Does anybody know a solution besides doing the CREATE DATABASE ... FOR
ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
regarding signature?
Thanks,
HolgerHolger (Holger@.discussions.microsoft.com) writes:
> I know that this can be fixed with ALTER AUTHORIZATION ON
> DATABASE::universe to [login] So, after attaching a database, the user
> can simply all ALTER > AUTHORIZATION. My problem is that ALTER
> AUTORIZATION requires "CONTROL SERVER" permission, which is simply a
> synonym for "sysadmin" role membership and therefore not what I want.
> All I want my users grant is "CREATE ANY DATABASE" permission. Does
> anybody know a solution besides doing the CREATE DATABASE ... FOR ATTACH
> with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
> regarding signature?
You could put the ALTER AUTHORSIZATION statement in a stored procedure
that you sign with a certificate, and then you grant a login associated
with that cert CONTOL SERVER. Note that the login is not a real login,
that is, it cannot connect.
For a lot more detail on this, see this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Holger
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
What is authentication are you using?
See if this helps
http://dimantdatabasesolutions.blog...on.
html
"Holger" <Holger@.discussions.microsoft.com> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
> Hi everybody,
> I'm in a situation where I allow my users of SQL Server the creation of
> her
> own databases. This is been done in order to allow the "creator" (the
> owner)
> full acces to the created DB. Everything works fine, as long as the DB is
> created from scratch, like:
> CREATE DATABASE universe
> But additionally, my users are also exchanging databases, so they will
> also
> use
> CREATE DATABASE universe ... FOR ATTACH
> When attaching a database, the owner of the so created database is the
> login
> that
> performed the operation, which is fine. But, the "dbo" user in the
> attached
> database is still associated with the original login (before detaching the
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
> this can be fixed with
> ALTER AUTHORIZATION ON DATABASE::universe to [login]
> So, after attaching a database, the user can simply all ALTER
> AUTHORIZATION.
> My problem is that ALTER AUTORIZATION requires "CONTROL SERVER"
> permission,
> which is simply a synonym for "sysadmin" role membership and therefore not
> what I want. All I want my users grant is "CREATE ANY DATABASE"
> permission.
> Does anybody know a solution besides doing the CREATE DATABASE ... FOR
> ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with
> a
> regarding signature?
> Thanks,
> Holger|||Hi Erland,
thanks for your answer. I know that this is a solution but the problem is
that, for ALTER AUTHORIZATION I need a login and a certificate inside the
master database and also a master key.
I'd like to fairly avoid storing information of any kind inside master -
regardless of what information this is. That's the reason why I was asking
for a solution without a signed stored procdure.
"Erland Sommarskog" wrote:

> Holger (Holger@.discussions.microsoft.com) writes:
> You could put the ALTER AUTHORSIZATION statement in a stored procedure
> that you sign with a certificate, and then you grant a login associated
> with that cert CONTOL SERVER. Note that the login is not a real login,
> that is, it cannot connect.
> For a lot more detail on this, see this article on my web site:
> http://www.sommarskog.se/grantperm.html
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Uri,
thanks for your answer. As for our SQL Server - we use Windows
authentication. But I do know nothing about the source Server,so it should
work with all authentication modes. Also I guess, in order work with Windows
authentication, the prerequisite will certainly be that all servers reside i
n
one domain, which is not the case. I get databases from unkown sources from
all over the world and simply have to use those.
regards,
Holger
"Uri Dimant" wrote:

> Holger
> What is authentication are you using?
> See if this helps
> http://dimantdatabasesolutions.blog...o
n.html
>
>
> "Holger" <Holger@.discussions.microsoft.com> wrote in message
> news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
>
>|||Holger (Holger@.discussions.microsoft.com) writes:
> thanks for your answer. I know that this is a solution but the problem is
> that, for ALTER AUTHORIZATION I need a login and a certificate inside the
> master database and also a master key.
> I'd like to fairly avoid storing information of any kind inside master -
> regardless of what information this is. That's the reason why I was asking
> for a solution without a signed stored procdure.
If you prefer you can use impersonation in the procedure, that is EXECUTE
AS.
I'm not sure that I understand your reluctance against storing information
in master. After all, it is here you have information about logins,
databases, server-level permissions, the service master key in master.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx