db datatypes: 2012

Friday, March 30, 2012

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
Stew
Create a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
StewCreate a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.sql

Problem with Maintenance Plan to delete files older than 2 days

Hi,
Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.
If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.
If I look at the script produced by clicking the Script button I get:
EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'
Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.
I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.
Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.
Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?
StewCreate a batch file that deletes everything in a directory (or a vbs
script or whatever)
Use xp_cmdshell to execute the batch file like so
declare @.retcode int
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @.retcode retcode
Your batch can take arguments or anything too, whatever works w/ your
installation
execute @.retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)
Just remember the execute is relative to the server so permissions all
that need to be set right.
Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

Problem with mailing labels and line feed

I am trying to print mailing labels and suppress optional address lines to eliminate white space while maintaining label alignment. This is what I am trying but it does not work.

I create a function that determines the length of a field (i.e. street) and increments a counter if the length is zero. It also takes a second parameter that detremines whether to reset the counter. I then use the function in the visibility each row of the label table. Example iif(Code.LineLen(Fields!Street.Value)=0,True,False). I place this on the visibility of each row except the last as that is City,State,Zip and is required. On this last row the expession I use is

=Fields!city.Value & ", " & Fields!state.Value & " " & Fields!zip.Value & iif(Code.LineCount<5,StrDup(5-Code.LineCount,vbCRLF),""). What this is intended to do is print a carraige return and line feed for every row that did not print. Instead no line feeds occur. I have verified that the Code.LineCount is indeed calculating correctly (I printed the value). I've removed the conditional to make sure it has no issues but again nothing. For clarification the code block I am using is this.

Public LineCount as Integer=0

Function LineLen(byval LineIn as string,byval IsFirst as boolean) as Integer
if IsFirst=True then

LineCount=1
end if
If len(LineIn)>0 then
LineCount=LineCount+1
end if
LineLen=Len(LineIn)
end Function

So if anyone either knows why the line feeds do not occur or a better way to handle this please let me know. Thanks.

Well I partially figured out this issue. Apparently the StrDup function in .Net does not like carraige returns and line feeds. I wrote my own string dup function and now I get the line feeds.

I would still be interesting in a better way of doing this if anyone knows.

Problem with machine that has only reporting services2005 installe

Hello all,
I am new to reporting services.
We have one installation of reporting services with SQL server 2005 and
Analysis services 2005 on one machine, and we have another server with only
Reporting services 2005 installed (a proxy server). I dont have a problem
with the first machine, but the second machine keeps showing this error when
i try to enter the default 'Report service' page:
http://localhost/reports
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and
then click the Refresh button, or try again later.
----
A name was started with an invalid character. Error processing resource
'http://ar2krepprox/Reports/'. Line 1, Position 2
<%@. Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false"
Inherits="Microsoft.ReportingServices.UI.HomePag...
I am able to deploy reports, but when run them i get this error:
' Your browser does not support scripts or has been configured not to allow
scripts. Click here to view this report without scripts. '
How do i get rid of these errors?
Is there any way to create a data driven subscription through the SQL Server
management studio?
I encountered no installation errors!
Thanks in advance
IshanI found the solution!!
a shout note by luna.dave@.gmail.com was v helpful
"Ishan Bhalla" wrote:
> Hello all,
> I am new to reporting services.
> We have one installation of reporting services with SQL server 2005 and
> Analysis services 2005 on one machine, and we have another server with only
> Reporting services 2005 installed (a proxy server). I dont have a problem
> with the first machine, but the second machine keeps showing this error when
> i try to enter the default 'Report service' page:
> http://localhost/reports
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error and
> then click the Refresh button, or try again later.
> ----
> A name was started with an invalid character. Error processing resource
> 'http://ar2krepprox/Reports/'. Line 1, Position 2
> <%@. Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.HomePag...
> I am able to deploy reports, but when run them i get this error:
> ' Your browser does not support scripts or has been configured not to allow
> scripts. Click here to view this report without scripts. '
> How do i get rid of these errors?
> Is there any way to create a data driven subscription through the SQL Server
> management studio?
> I encountered no installation errors!
> Thanks in advance
> Ishan
>|||Within IIS Manager on your RS server check you have Scripts Enabled on the
"Home Directory" tab.
Paul Walsh
"Ishan Bhalla" wrote:
> Hello all,
> I am new to reporting services.
> We have one installation of reporting services with SQL server 2005 and
> Analysis services 2005 on one machine, and we have another server with only
> Reporting services 2005 installed (a proxy server). I dont have a problem
> with the first machine, but the second machine keeps showing this error when
> i try to enter the default 'Report service' page:
> http://localhost/reports
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error and
> then click the Refresh button, or try again later.
> ----
> A name was started with an invalid character. Error processing resource
> 'http://ar2krepprox/Reports/'. Line 1, Position 2
> <%@. Page language="c#" Codebehind="Home.aspx.cs" AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.HomePag...
> I am able to deploy reports, but when run them i get this error:
> ' Your browser does not support scripts or has been configured not to allow
> scripts. Click here to view this report without scripts. '
> How do i get rid of these errors?
> Is there any way to create a data driven subscription through the SQL Server
> management studio?
> I encountered no installation errors!
> Thanks in advance
> Ishan
>

problem with lookup ,is this a bug?

All,

I’m having problem with lookup transformation using Advanced TAB, it seems TOP 1 in a query doesn’t work and lookup cache some values no matter what. Below are the details if someone likes to try it out.

-This is the source table

CREATE TABLE [dbo].[DEPT_temp2](

[DEPTNO] [int] NOT NULL,

[NUM_Id] [int] NULL,

[date_id] [int] NULL

) ON [PRIMARY]

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,111,11111);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,222,22222);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,333,33333);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,444,44444);

INSERT INTO DEPT_temp2 ([DEPTNO],[NUM_Id],[date_id]) VALUES (1,555,55555);

--This is the destination table

CREATE TABLE [dbo].[DEPT_temp2_end](

[DEPTNO] [int] NOT NULL,

[NUM_Id] [int] NULL,

[NEW] [int] NULL,

[date_id] [int] NULL

) ON [PRIMARY]

in the LOOKUP transformation

OLE DB source-àSQL command:

select * from DEPT_temp2

In LOOKUP transformation à Reference Table tab à Use a result ofa sql query

select deptno, num_id as new

from DEPT_temp2

à Columns TAB

Linked the deptno, num_id from the input to lookup column deptno, New

--à Advanced TAB --à Checked Enable memory restriction -à Modify the SQL statement

selecttop 1 *from

(select deptno, num_id as new

from DEPT_temp2) as refTable

where [refTable].[deptno] = ? and [refTable].[new] > ?

In the property of the lookup, cache type is none( I tried partial also) and enable cache is unchecked. I got the same result

Here is the result from the lookup tran

Deptnonum_id newdate_id

111122211111

122222222222

133344433333

144444444444

1555NULL55555

But expected result should be

Deptnonum_id newdate_id

111122211111

122233322222

133344433333

144455544444

1555NULL55555

What I’m missing here? Is this a bug?

Hi,

I know that when you are using memory restrictions some lookups still have caching enabled in the properties tab. Apart from the SQL used I cannot see anything wrong.

Normally I do not use a select top 1 because as far as I know, the first result found in the lookup is used.

select deptno, num_id as new from DEPT_temp2 where [deptno] = ? and [num_id] > ? order by num_id

Naturally the hash table created by the lookup when using partial or full caching disabled the possibility of using greater or smaller than. I have once solved an issue like this one using a view that handled the complex greater than statement. I simply could use the id as lookup to the view and full caching.

Problem With LookUp

Iam trying to make a look up of 'ColumnA' with 'columnB' of another table .But 'columnB' has some duplicate vlaues..so lookup transmition is giving an error.

Is there any solution for this

Thanks
Niru
Findout out the solution...using Sqlquery as OLEDB Source instead of Table.This solved the thing.

Thanks
Niru
sql

Problem with Look up

Hi all;

I have created a SSIS package to import data from flat file to database table, which consists of
Flat file Source -> Look up -> OLE DB Destination.
I'm using Look Up in order to avoid duplicate records.
My database table contain around 35,00000 s of rows.
So when I'm running the package the above amount of data goes to Lookup cache, which require a large storage memory.
So I'm not able to execute my package.
I need to run the package in order to update my database.
So is there any solution, in which I can check the duplicate records to get into the database and time consumption to run the package will be less.

Thanks in advance...

Be sure that you are using a SQL statement to only select distinct keys from the table:

select primary_key from table

Then you just simply join the key fields.|||

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

|||

Andy Leonard wrote:
Hi Sanidha,
You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.
Hope this helps,
Andy

Why would this matter? Selecting the primary key of the full table (should be indexed!) should be less work than staging the keys first, and then selecting from there. That sounds like an extra, unnecessary step to me and will, in turn, cost more than my approach.

Am I missing something? Unless of course, you're talking about performing a SQL join against that staging table so as to avoid caching the keys in memory.|||

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

|||

Andy Leonard wrote:
My apologies Sanidha,
I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.
Andy

Well, yeah, that's the one thing we don't know. I want to be sure the user is selecting *just* the key, and not the whole table.

Problem with long running report timing out

I have a 'YTD' report that is timing out after 30 minutes, where do I go to
change the default timeout period for reports?
TIA,
Bill Youngman
AnexinetIn SSRS2000, you open the Execution property for the report and select
the "Limit report execution to the following number of seconds:" option
under Report Execution Timeout. Then just set the number of SECONDS to
whatever amount will cover the proc run time.
I don't know if this is the same method used in SSRS2005 but I think
there'd be something similar. Hope this helps.
Bill Youngman wrote:
> I have a 'YTD' report that is timing out after 30 minutes, where do I go to
> change the default timeout period for reports?
> TIA,
> Bill Youngman
> Anexinet

Problem with logon SQL Server (MSDE)

Hi
I am working on a replication database system, I will use SQL Server Enterprise at the main database and I'm planning to have remote databases with MSDE, I installed MSDE in a remote computer using a valid logon account for the domain in the domain controller (I had it connected for the set up). If I disconnect the computer from the network an restart the machine the Sql Server won't start with the cache information.

How can I solve this problem?MSDE Doesn't support a login from the network; only from the local computer.

Am I right?

problem with loging in

I installed a default instance of Enterprise edittion and

didn't face any error during the instalation process.

In Management Studio in the Server Name section for loging in I wrote the name of the local system.

but I couldn't log in.

Control Panel \ administrative tools\services

there is no SQL Server service at all.

should I install any thing extra just like Express Edition which I used to install SQL Express in Visual Studio 2005 pakage or like MSDE in SQL Server 2000?

please tell me what to do

thanks.

pooyan

Hello,

It would seem that the core dbms has not been installed correctly - did you perhaps only select client tools during the install?

Reinstall the server components (make sure you run setup from the server, not tools folder).

Cheers,

Rob

Problem With Login Null

Hi,
I have a problem. My web server is a Windows 2k Server and my database
server a Windows 2003. I use windows authentication to login at SQL Server,
but when I access from a client machine that uses Win 2k Professional I
always receive this message: "[Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.".
When I access from a client machine that uses Windows XP everything its
ok.
This problem only occurs at the web site. When I try to access the Query
Analyzer or the Enterprise Manager I still have the connection.
Can anyone help me?!
Thanks,
Riane Santos
riane@.cpunet.com.br
I've seen this error when a local security policy was in place preventing
access from the network. But, I don't see how that could be your problem
since it works from some machines.
"Riane de Oliveira Torres Santos" <riane@.cpunet.com.br> wrote in message
news:eI4Ehr1jEHA.2788@.tk2msftngp13.phx.gbl...
> Hi,
> I have a problem. My web server is a Windows 2k Server and my database
> server a Windows 2003. I use windows authentication to login at SQL
Server,
> but when I access from a client machine that uses Win 2k Professional I
> always receive this message: "[Microsoft][ODBC SQL Server Driver][SQL
> Server]Login failed for user '(null)'. Reason: Not associated with a
trusted
> SQL Server connection.".
> When I access from a client machine that uses Windows XP everything its
> ok.
> This problem only occurs at the web site. When I try to access the Query
> Analyzer or the Enterprise Manager I still have the connection.
> Can anyone help me?!
> Thanks,
> Riane Santos
> riane@.cpunet.com.br
>
sql

Problem With Login null

problem with login list

Hi,

I'm using the 64-bit version of SQL Server 2005, SP2, on Windows
Server 2003 R2 X64 Enterprise Edition. I've got a bunch of users out
there who are the db_owner, db_accessadmin and db_securityadmin of
their different respective databases. I would expect that they would
be able to add users to their databases, given that a login exists on
the server. However, when they go to browse logins to add a user in
Management Studio, they are only shown a very short list (like,
themselves and sa, and that's it). We have hundreds of logins on the
server, and they should be able to add any one of them to their
databases if they wish. And if they try to type in the login name
directly, they get a permission denied error.

I am the system administrator, so thankfully I've not experienced this
problem, and I can add users for them. But I'd rather they be able to
do it themselves as they see fit. I have experimented by creating a
test SQL-authenticated login, and making it db_owner of a test
database. When I login with that test login and try to add a user, I
see the exact same behavior. The only logins viewable are my test
login and sa. The only other thing I can add is it's not just
occurring with the GUI interface; the same thing happens when I do a
direct query on the master.sys.syslogins view: I only see the same two
logins. So it appears it's happening at that level and the result
appears up in the GUI.

It appears this is a security/permissions thing. Anyone know if
there's a configuration setting or something that might be preventing
non-privileged users from being able to view all the server logins
when attempting to add users to their databases, in which they are
assigned the db_owner role?

Thanks
GringoSkyGringo (doug@.bu.edu) writes:

Quote:

Originally Posted by

>
I'm using the 64-bit version of SQL Server 2005, SP2, on Windows
Server 2003 R2 X64 Enterprise Edition. I've got a bunch of users out
there who are the db_owner, db_accessadmin and db_securityadmin of
their different respective databases. I would expect that they would
be able to add users to their databases, given that a login exists on
the server. However, when they go to browse logins to add a user in
Management Studio, they are only shown a very short list (like,
themselves and sa, and that's it). We have hundreds of logins on the
server, and they should be able to add any one of them to their
databases if they wish. And if they try to type in the login name
directly, they get a permission denied error.

They need to have VIEW DEFINITION on the logins they need to add. There
is no permission VIEW ANY LOGIN, but there is a server-level VIEW ANY
DEFINITION you can grant to them, but I would think twice before you
did.

Quote:

Originally Posted by

The only other thing I can add is it's not just occurring with the GUI
interface; the same thing happens when I do a direct query on the
master.sys.syslogins view: I only see the same two logins. So it
appears it's happening at that level and the result appears up in the
GUI.

It appears that you are of the old SQL 2000 school. :-)

Microsoft did a lot around security in SQL 2005, and one thing is
that objects are no longer visible to everyone. Essentially, you
can only see an object, if you have permission to it.

And the place to look for logins these days, is sys.server_principals.
While the old system tables are around for compatibility, they may not
show aspects that are new to SQL 2005.

--
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

problem with login

I have a problem with login on slq server 2005 on the other machine. The story is:

I have been trying create classes with help of SqlMetal (Linq tool) and all the time i have a message

Unhandled Exception: System.Data.SqlClient.SqlException: Login failed for user 'me'

So, I tried connect by sqlcmd.

sqlcmd -S OURSERVER -d database -U me -P password

And the error message is:

Msg 18456, Level 14, State 1, Server OURSERVER, Line 1
Login failed for user 'me'.

and...

I CAN connect to this database with help of "SQL Server Management Studio Express"

with the same username and password.

thanks for help

bakuuu

ps. sorry for my english

From your description, I don’t have enough information to determine if my following assumption is correct or not, but it may be worth a try.

I am guessing that the scenarios that fail (1) & (2) are from a remote machine, but the one that succeeds (3) is on the same machine as SQL Server Express. If this is true, it may be possible that TCP communications are disabled and/or the windows firewall is not allowing to connect. Please use SQL Server Configuration Manager to enable TCP (and restart the server) and make sure the firewall allows the connection.

Another possibility is that the DB “database” specified with the parameter –d is not available or you don’t have permission to connect. Try using the default DB (remove the –d database parameter) or master (-d master).

If my assumptions are not correct, we will appreciate further information in order to help.

Thanks a lot.

-Raul Garcia

SDE/T

SQL Server Engine

Problem with logging/System::ErrorDescription variable

Hi,

I've come up against a problem in my error handling.

I have a package-level OnError handler which is a SQL Execute task. The SQL execute task constructs a SqlStatementSource expression to insert the error details into a table. This works fine and I've tested it by introducing various errors in the package.

I have another SQL Execute task, which executes a SQL script from a file connection. This is a very long script (100s of lines) and works fine. I introduced a deliberate error into it and got the following errors:

Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters.
Error: Reading the variable "System::ErrorDescription" failed with error code 0xC0047100.
Error: The expression <expression> on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.

The problem is obviously the length of the script, since SSIS attempts to copy all of it into the ErrorDescription, but this seems to occur before I can do anything about it. I've tried putting a SUBSTRING function around the ErrorDescription variable without success. If I introduce an error into a shorter script the error handling works fine.

Is this a bug in SSIS? Is there any sort of workround that anybody knows of?

thanks
- Jerzy

It sounds like it could be a bug. It needs validating by someone from the SSIS dev team but unfortunately they don't seem to appear on here as much as they used to.

Try logging it at the feedback center with repro steps: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||Thanks. I've done that. Bug ID FDBK45894

- Jerzy
|||Sounds a bit funny, but there is a known limit of 4000 characters on expressions, so for long errors you may hit this. The other issue you can hit is that the message may contain single or double quotes. Best case, it fails, worst case you have a SQL injection attack. For this reason alone I'd say just use a stored procedure, and the built in parameter support. The ErrorDescription variable should map to a procedure parameter, which should solve both the size limit (if that is your issue), and prevent injection.

problem with Log

Hello,

Could anybody let me know why is this happening while executing a stored procedure through asp.net

Log Entry string is too long. A string written to the event log cannot exceed 32766 characters.

general

The message sounds like you tried to write a very large string to you system event log.
Possibly as a result of throwing an exception when trying to call the stored proc.

Do you have an Exception logging mechanism in place that is configured to put exception messages into your system Event Log?

Can you step into the code to see any possible exception when you make the stored proc call, or turn off exception handling/logging for this call and let the exception go to the page.

sql

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, Mefisto
Can you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, MefistoCan you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locks - Help!

Good morning to everyone, I am writing to ask an advice: I have been
having lots of problems in the last time because of some locks that (I
suppose) a software create in SqlServer. The problem details is:
apparently randomly, when we try to save a record with that software
everything freeze. If I watch at the locks in Enterprise Manager I can
see some locks. If I terminate the locks the software unfreeze and the
record will be saved correctly. If I watch the detail of the processes
locked, I can see that instruction: "sp_unprepared;1". I spoke many
times with the company which produce the software and they say that I am
the only customer with this problem and that surely the problem is on my
sqlserver settings. I am not so sure, for me it is a problem of the
software. Does anybody can help me to find out what is going on? I would
really appreciate any kind of suggestion (except to change the software
;-) ). I am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server
sp.4. Sorry for my English. Thank you very much for your help, have a
nice day, MefistoCan you run SQL Server Profiler while you try to save a row and see what is
going on.?
"Mefisto" <fastyno@.NOSPAM.freemail.it> wrote in message
news:dnjdau$hg6$1@.newsread.albacom.net...
> Good morning to everyone, I am writing to ask an advice: I have been
> having lots of problems in the last time because of some locks that (I
> suppose) a software create in SqlServer. The problem details is:
> apparently randomly, when we try to save a record with that software
> everything freeze. If I watch at the locks in Enterprise Manager I can see
> some locks. If I terminate the locks the software unfreeze and the record
> will be saved correctly. If I watch the detail of the processes locked, I
> can see that instruction: "sp_unprepared;1". I spoke many times with the
> company which produce the software and they say that I am the only
> customer with this problem and that surely the problem is on my sqlserver
> settings. I am not so sure, for me it is a problem of the software. Does
> anybody can help me to find out what is going on? I would really
> appreciate any kind of suggestion (except to change the software ;-) ). I
> am using Microsoft SqlServer 2000 sp.4 in a Windows 2000 server sp.4.
> Sorry for my English. Thank you very much for your help, have a nice day,
> Mefisto

Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

Regards

Bjrn(bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.
>
The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.
>
I have also checked if there are any locks holding the other SPs back
- there isn't any.
>
So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.

problem with local connection

I've just installed VS .NET 2005 and also installed sql Express that come along with it
when I tried to run a simple web page wih gridview that connect to the sql express with the table that I create and test it
status is "Tested connection suceeded" or test query is also successful but when I run I got the following message please help

-----------------------

Cannot open database "customers" requested by the login. The login failed.
Login failed for user 'myServer\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open database "customers" requested by the login. The login failed.
Login failed for user 'USFER54-8B481CE\ASPNET'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot open database "customers" requested by the login. The login failed.Login failed for user 'USFER54-8B481CE\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

Thanks in advance for your help

ASPNet ('USFER54-8B481CE\ASPNET') user is not having access to the database. You can add ASPNet user to this database using management studio and then try accessing the site.

You can also try accessing the site using SQL Server User instead of Windows User.

sql

Problem with LoadReport web method

Hi,

I'm working on a reporting client for Reporting Services 2005 using SOAP. The problem I'm having is that for reports with multiple pages or a document map, the LoadReport web method, always says they have 0 pages or no document map.

To simplify the problem, i created a simple console app that just calls LoadReport on the /AdventureWorks Sample Reports/Product Catalog report to see if HasDocumentMap is true (it should be but isn't).

Any suggestions?

Kwan

I figured it out and it makes sense. The information is available only after you call Render.

So call, Render, then GetExecutionInfo to get the correct values for HasDocumentMap and NumPages.

Problem with ListAvailableSQLServers in vb 6

Hello,
I have got a very strange problem...
In VB6, in the beginning of my application, I make a
ListAvailableSQLServers to see what MSDE servers are on the network for
my application.
The problem is that if a computer is disconnected from the network, but
has MSDE working (a laptop) then the ListAvailableSQLServers will return
no server, even if there is a local server.
Is there something to do to have it?
PS : In VB6, I use
dim ListSvr as collection
Set ListSvr = SQLDMO.ListAvailableSQLServers
and the count is 0
I have also tried with
Dim oServer As New SQLDMO.SQLServer2
Dim oNameList As SQLDMO.NameList
Set oNameList = oServer.Application.ListAvailableSQLServers '
and oNameList.Count is still 0
Can you please help me?
Thank you
Marc Allard
Allcomp
hi Marc,
"Allcomp" <marc@.nospam.allcomp.be> ha scritto nel messaggio
news:41d95753$0$329$ba620e4c@.news.skynet.be
> Hello,
> I have got a very strange problem...
> In VB6, in the beginning of my application, I make a
> ListAvailableSQLServers to see what MSDE servers are on the network
> for my application.
> The problem is that if a computer is disconnected from the network,
> but has MSDE working (a laptop) then the ListAvailableSQLServers will
> return no server, even if there is a local server.
> Is there something to do to have it?
> PS : In VB6, I use
> dim ListSvr as collection
> Set ListSvr = SQLDMO.ListAvailableSQLServers
> and the count is 0
> I have also tried with
> Dim oServer As New SQLDMO.SQLServer2
> Dim oNameList As SQLDMO.NameList
> Set oNameList = oServer.Application.ListAvailableSQLServers '
> and oNameList.Count is still 0
>
can you please verify your local MSDE instance has network protocols enabled
via Server Network Utility (svrnetcn.exe)?
if network protocols are disabled (default for MSDE installation), the
relative instance will not be enlisted in the broadcast call for server
enumeration..
as regard ListAvailableServer method provided by SQL-DMO object model, I've
found during my (little) experience, this sort of rules:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
The latest problem has been added by Windows XP service pack 2, which
implements a strong protection of the local computer closing all ports for
incoming and outgoing connections, requiring to manually open the desired IP
port in order to allow external remote connections
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello,
My enabled networks protocols are Named pipes and Tcp/IP
With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
have no server). I have exactly the same error with Osql -L
I use WinXP SP2 without firewall for my tests.
With SQL Server 2000, a Win98 computer is in the list
I have also seen something strange...
I make the ListAvailableSQLServers
If I have a return count of 0, then I use
the object
SQLDMO.SQLServer2
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
will return the local computer when no network is enabled.
Thank you
Marc Allard
Allcomp
Andrea Montanari wrote:
> hi Marc,
> "Allcomp" <marc@.nospam.allcomp.be> ha scritto nel messaggio
> news:41d95753$0$329$ba620e4c@.news.skynet.be
>
> can you please verify your local MSDE instance has network protocols enabled
> via Server Network Utility (svrnetcn.exe)?
> if network protocols are disabled (default for MSDE installation), the
> relative instance will not be enlisted in the broadcast call for server
> enumeration..
> as regard ListAvailableServer method provided by SQL-DMO object model, I've
> found during my (little) experience, this sort of rules:
> ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
> libraries installed by Mdac;
> this is a mechanism working in broadcast calls, which result never are
> conclusive and consistent, becouse results are influenced of various
> servers's answer states, answer time, etc.
> Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
> broadcast, on which SQL Servers respond (Default protocol for SQL Server
> 7.0), while in SQL Server 2000 the rules changed, because the default client
> protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
> broadcast, listening on port 1434:
> which is using a UDP broadcast on port 1434, if instance do not listen or
> not respond on time they will not be part of the enumeration.
> Some basic rules for 7.0 are:
> - SQL Servers have to be running on Windows NT or Windows 2000 and have to
> listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
> show up, because they do not listen on Named Pipes.
> - The SQL Server has to be running in order to respond on the broadcast.
> There is a gray window of 15 minutes after shutdown, where a browse master
> in the domain may respond on the broadcast and answer.
> - If you have routers in your network, that do not pass on NetBIOS
> broadcasts, this might limit your scope of the broadcast.
> - Only servers within the same NT domain (or trust) will get enumerated.
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
> Courtesy of Mr. Gert E.R. Drapers
> further Information at
> http://sqldev.net/misc.htm
> The Service Pack 3a introduced some new amenity in order to prevent MSDE
> 2000 to be hit by Internet worms like Slammer and Saphire virus and to
> increase security, so that Microsoft decided to default for disabling
> SuperSockets Network Protocols on new MSDE 2000 installation.
> Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
> UDP port 1434 when they are configured to not listen on any network
> protocols. This will stop enlisting these servers.
> The latest problem has been added by Windows XP service pack 2, which
> implements a strong protection of the local computer closing all ports for
> incoming and outgoing connections, requiring to manually open the desired IP
> port in order to allow external remote connections
|||If you have no network this is expected, since the enumeration is based on a
UDP (TCP) broadcast. Since this is a broadcast it normally only sees
computers in the same subnet, since most routers are configured not to pass
on UDP broadcast requests.
Besides that there is a response time issue. After the request is issued the
client only waits for a certain period of time on UDP replies from the
servers that received the UDP broadcast request.
On the local machine the API does a Registry scan, which is why local
services should show up when no network is enabled. MSDE has different
Registry entries that is why these do not show.
If you want to use the same API as SQL-DMO you need to use the
ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
the OLE-DB based API, which theoretically should provide the same results,
but you never know.
On Win98 I think they only enumerate local instances as far as I know.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Allcomp" <marc@.nospam.allcomp.be> wrote in message
news:41da4402$0$316$ba620e4c@.news.skynet.be...[vbcol=seagreen]
> Hello,
> My enabled networks protocols are Named pipes and Tcp/IP
> With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
> have no server). I have exactly the same error with Osql -L
> I use WinXP SP2 without firewall for my tests.
> With SQL Server 2000, a Win98 computer is in the list
> I have also seen something strange...
> I make the ListAvailableSQLServers
> If I have a return count of 0, then I use
> the object
> SQLDMO.SQLServer2
> Set oServer = New SQLDMO.SQLServer2
> Set ListSvr = oServer.ListInstalledInstances
> will return the local computer when no network is enabled.
>
> Thank you
> Marc Allard
> Allcomp
>
>
>
>
> Andrea Montanari wrote:
|||Hello,
In fact, I was thinking that when I remove the computer from the
network, it is able to see itself (by UDP) you can answer to yourself
(but it seem that it is not possible)?
So I think I will have to list all the local instances if I can see no
network.
PS : Win 98 can see any server (local or networked).
Thank you
Marc Allard
Allcomp
Gert E.R. Drapers wrote:
> If you have no network this is expected, since the enumeration is based on a
> UDP (TCP) broadcast. Since this is a broadcast it normally only sees
> computers in the same subnet, since most routers are configured not to pass
> on UDP broadcast requests.
> Besides that there is a response time issue. After the request is issued the
> client only waits for a certain period of time on UDP replies from the
> servers that received the UDP broadcast request.
> On the local machine the API does a Registry scan, which is why local
> services should show up when no network is enabled. MSDE has different
> Registry entries that is why these do not show.
> If you want to use the same API as SQL-DMO you need to use the
> ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
> the OLE-DB based API, which theoretically should provide the same results,
> but you never know.
> On Win98 I think they only enumerate local instances as far as I know.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Allcomp" <marc@.nospam.allcomp.be> wrote in message
> news:41da4402$0$316$ba620e4c@.news.skynet.be...
>
>
|||If you use SQLBrowseConnect directly (not SQL-DMO) you can point it at a
single machine, which would give you this.
If you try the ListSQLSvr.exe tool with -S <your machine name> -X it will
give you a listing of your local servers only.
If you install a loopback adapter the UDP broadcast will work locally.
Good to know that Win98 does local and remote, I never touch that OS, since
NT was introduced I left DOS behind me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Allcomp" <marc@.nospam.allcomp.be> wrote in message
news:41da6e27$0$338$ba620e4c@.news.skynet.be...[vbcol=seagreen]
> Hello,
> In fact, I was thinking that when I remove the computer from the network,
> it is able to see itself (by UDP) you can answer to yourself (but it seem
> that it is not possible)?
> So I think I will have to list all the local instances if I can see no
> network.
> PS : Win 98 can see any server (local or networked).
> Thank you
> Marc Allard
> Allcomp
> Gert E.R. Drapers wrote:
|||Hello,
Thank you for your help
In fact, I must use Win98 because a lot of my custommers use Win98 and
don't want to change.
Marc Allard
Allcomp
Gert E.R. Drapers wrote:
> If you use SQLBrowseConnect directly (not SQL-DMO) you can point it at a
> single machine, which would give you this.
> If you try the ListSQLSvr.exe tool with -S <your machine name> -X it will
> give you a listing of your local servers only.
> If you install a loopback adapter the UDP broadcast will work locally.
> Good to know that Win98 does local and remote, I never touch that OS, since
> NT was introduced I left DOS behind me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Allcomp" <marc@.nospam.allcomp.be> wrote in message
> news:41da6e27$0$338$ba620e4c@.news.skynet.be...
>

Problem with linking tables

I have an Access database that contains local files and files linked on a SQL server through a DNS connection. I have been using this for years and during that time I have dropped links, added links, changed the DNS server information, etc.

I changed the DNS server information this weekend, deleted all the links, and was ready to start linking the tables with the new DNS information. I click on File >> Get External Data >> Link Tables and am given the traditional file selection dialogue. I go to the bottom (Files of Type) and scroll to the last selection... ODBC Databases().

In the past, after I made that selection, I would then be taken to the ODBC dialogue from my computer to select the connection type and then the actual connection.

Now however, the dialogue immediately stops. It doesn't lock, it just stops and returns me to the Database window from my database as though I wasn't doing anything.

I have reinstalled Office and redone all the updates. Does anyone have any suggestions?

Thanks,

Gary

I just had the same problem today, but after installing new version of Norton Antivirus.

I found the solution on forum from jan 2006 (I'm not that smart)...

Turning off the Office plug-in in Norton Antivirus did indeed work. For those who need specific directions:

1. In Norton Antivirus, select Options from the toolbar, choose Norton Antivirus.
2. On the left select the Miscellaneous option
3. Uncheck the box for the Office Plug-in (scanning MS Office documents)
4. If you had MS Access open during this process, shut it down and restart it.

Hope this helps!

Emilija

|||

Thanks so much, Emilija. You hit it right on the head. I wouldn't have thought of that in 1,000,000 years!

Now all we have to do is make it esaier to find and change things in Norton! Every new edition hides things even more.

Thanks again,

Gary

Problem with linking servers

Hi.

I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM', N'SQL
Server' to link one to the other and also vise versa.

Each server has two users permissioned.

My problem is when ever I try to do something that does a remote write I get
the follow error message
Microsoft OLE DB Provider for SQL Server error '80040e14'

[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Also

when I try and manually run a stored procedure, I get:

Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.

(1 row(s) affected)

(50 row(s) affected)

Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
65
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]

Can anyone suggest how I resolve this.

FYI

Database Creation Script: (both database are the same scripts but db names
are changed)

CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
(NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' , SIZE
= 2000, FILEGROWTH = 10%)

COLLATE Latin1_General_CI_AS

exec sp_dboption N'msmprim', N'autoclose', N'false'

exec sp_dboption N'msmprim', N'bulkcopy', N'false'

exec sp_dboption N'msmprim', N'trunc. log', N'false'

exec sp_dboption N'msmprim', N'torn page detection', N'true'

exec sp_dboption N'msmprim', N'read only', N'false'

exec sp_dboption N'msmprim', N'dbo use', N'false'

exec sp_dboption N'msmprim', N'single', N'false'

exec sp_dboption N'msmprim', N'autoshrink', N'false'

exec sp_dboption N'msmprim', N'ANSI null default', N'false'

exec sp_dboption N'msmprim', N'recursive triggers', N'false'

exec sp_dboption N'msmprim', N'ANSI nulls', N'false'

exec sp_dboption N'msmprim', N'concat null yields null', N'false'

exec sp_dboption N'msmprim', N'cursor close on commit', N'false'

exec sp_dboption N'msmprim', N'default to local cursor', N'false'

exec sp_dboption N'msmprim', N'quoted identifier', N'false'

exec sp_dboption N'msmprim', N'ANSI warnings', N'false'

exec sp_dboption N'msmprim', N'auto create statistics', N'true'

exec sp_dboption N'msmprim', N'auto update statistics', N'true'

User Creation Script:

/****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/

use [msmprim]

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

use [msmprim]

declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'

declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'

declare @.logindb nvarchar(132) select @.logindb = N'msmprim'

declare @.loginlang nvarchar(132) select @.loginlang = N'British English'

if not exists (select * from master.dbo.syslogins where loginname =
@.sqlLoginName)

BEGIN

if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)

select @.logindb = N'master'

if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'British English')

select @.loginlang = @.@.language

exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang

END

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

if not exists (select * from dbo.sysusers where name = @.sqlLoginName and uid
< 16382)

EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datareader', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_datawriter', @.sqlLoginName

/****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/

exec sp_addrolemember N'db_owner', @.sqlLoginName

GOI have got a little further, by adding 'SET XACT_ABORT ON' I have mangaged
to get the remote write to work.

Can someone tell me what the implication of setting 'SET XACT_ABORT ON' is
and what effect will it have on my SPs. Will they still roll back as
before?

Sample SP

procedure [msm].ams_Insert_Audit
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strSCRIPT varchar(128),
@.strLOGINID varchar(32),
@.strVFEID varchar(16),
@.strBILLORGID varchar(16),
@.strRETAILERID varchar(16),
@.strLOCATIONID varchar(16),
@.strPOSTID varchar(16),
@.strXACTIONID varchar(16),
@.strCODE varchar(64),
@.strDATA varchar(1024)
as
declare @.strStep varchar(32)
declare @.trancount int

set @.trancount = @.@.trancount
set @.strStep = 'Start of Stored Proc'

if (@.trancount = 0)
begin tran ams_Insert_Audit
else
save tran ams_Insert_Audit

SET XACT_ABORT ON

set @.strStep = 'Writing MSM Audit'

insert into
HAN.msmprim.msm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

set @.strStep = 'Writing PSM Audit'

insert into
ACSPSM.psmprim.psm.AMSAUDIT (TIMESTAMP, SCRIPT, LOGINID, VFEID, BILLORGID,
RETAILERID, LOCATIONID, POSTID, XACTIONID, CODE, DATA)
values
(GetDate(), @.strSCRIPT, @.strLOGINID, @.strVFEID, @.strBILLORGID,
@.strRETAILERID, @.strLOCATIONID, @.strPOSTID, @.strXACTIONID, @.strCODE,
@.strDATA)

if (@.@.error <> 0)
begin
rollback tran ams_Insert_Audit
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : Error Occured'
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end
-- commit tran if we started it

if (@.trancount = 0)
commit tran ams_Insert_Audit

return 0

Regards

Steve

"Steve Thorpe" <stephenthorpe@.nospam.hotmail.com> wrote in message
news:bkeqcl$h14$1@.titan.btinternet.com...
> Hi.
> I have two sql servers and have ran exec sp_addlinkedserver 'ACSPSM',
N'SQL
> Server' to link one to the other and also vise versa.
> Each server has two users permissioned.
> My problem is when ever I try to do something that does a remote write I
get
> the follow error message
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
> Also
> when I try and manually run a stored procedure, I get:
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (50 row(s) affected)
> Server: Msg 7395, Level 16, State 2, Procedure ams_Move_Stock_To_PSM, Line
> 65
> Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
> nested transaction was required because the XACT_ABORT option was set to
> OFF.
> [OLE/DB provider returned message: Cannot start more transactions on this
> session.]
>
> Can anyone suggest how I resolve this.
>
>
> FYI
> Database Creation Script: (both database are the same scripts but db names
> are changed)
> CREATE DATABASE [msmprim] ON (NAME = N'msmprim_Data', FILENAME =
> N'D:\ACS_DB\data\msmprim_Data.MDF' , SIZE = 2000, FILEGROWTH = 10%) LOG ON
> (NAME = N'msmprim_Log', FILENAME = N'D:\ACS_DB\logs\msmprim_Log.LDF' ,
SIZE
> = 2000, FILEGROWTH = 10%)
> COLLATE Latin1_General_CI_AS
> GO
> exec sp_dboption N'msmprim', N'autoclose', N'false'
> GO
> exec sp_dboption N'msmprim', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'msmprim', N'trunc. log', N'false'
> GO
> exec sp_dboption N'msmprim', N'torn page detection', N'true'
> GO
> exec sp_dboption N'msmprim', N'read only', N'false'
> GO
> exec sp_dboption N'msmprim', N'dbo use', N'false'
> GO
> exec sp_dboption N'msmprim', N'single', N'false'
> GO
> exec sp_dboption N'msmprim', N'autoshrink', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'msmprim', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'msmprim', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'msmprim', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'msmprim', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'msmprim', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'msmprim', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'msmprim', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'msmprim', N'auto update statistics', N'true'
> GO
>
>
> User Creation Script:
> /****** Object: Login MSM Script Date: 31/10/2002 10:41:26 ******/
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'msm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO
> use [msmprim]
> GO
> declare @.sqlLoginName nvarchar(32) select @.sqlLoginName = N'psm'
> declare @.UserPassword nvarchar(32) select @.UserPassword = N'wibble'
> declare @.logindb nvarchar(132) select @.logindb = N'msmprim'
> declare @.loginlang nvarchar(132) select @.loginlang = N'British English'
>
> if not exists (select * from master.dbo.syslogins where loginname =
> @.sqlLoginName)
> BEGIN
>
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'British English')
> select @.loginlang = @.@.language
> exec sp_addlogin @.sqlLoginName, @.UserPassword, @.logindb, @.loginlang
> END
>
>
>
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> if not exists (select * from dbo.sysusers where name = @.sqlLoginName and
uid
> < 16382)
> EXEC sp_grantdbaccess @.sqlLoginName, @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datareader', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_datawriter', @.sqlLoginName
> /****** Object: User sm_web Script Date: 31/10/2002 10:41:26 ******/
> exec sp_addrolemember N'db_owner', @.sqlLoginName
> GO

problem with linked servers and INSERT

Hello

I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.

Here's the code:

-------

create table #b (type int,label varchar(100),x int,y int,so int)

declare @.arg nvarchar(100)
set @.arg = 'BLANKET.MaalGrupper.ID38'

declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( '' + @.arg + '' )'' )'
insert #b exec (@.tsql)

drop table #b

--------

I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@.arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.

Can anybody help with this very irritating problem?

Thanks
MNJdoesn't anybody have an idea of might be wrong?|||Below is working for me (SQL2000)

-- on linked server
CREATE FUNCTION getit (@.id int=null)
RETURNS TABLE
AS
RETURN (SELECT *
FROM sysobjects WHERE id = coalesce(@.id,id))
go
select * from getit(null)
-------------
create table #b (label varchar(100))
insert #b
select * from openquery(linked,'select name from testDB.dbo.getit(null)')

declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery(linked,''select name from testDB.dbo.getit(null)'')'
insert #b exec (@.tsql)|||Do you have Distributed Transaction Coordinator running on both machines?|||Hm, the first select works for me, the second doesn't. And I need to be able to specify a parameter.

DTC is running on both machines.

MNJ|||Have you set any special environment variables or anything like that?

MNJ|||Have you set any special environment variables or anything like that?

MNJ
Nothing special...sql

Problem with Linked Servers

I am trying to link a sql 2000 server to a sql 2005 Server. I setup the link to the sql 2000 box from the sql 2005 (x64, Enteprrise) box using SSMS. Both boxes have the same Windows domain account that I'm using for auithentication. (I also selected the Impersonate option in SSMS) When I run a simple query on the sql 2005 box referencing the sql 2000 box ( select * from sql2000Box.DBName.dbo.tblName) I get the error message:

OLE DB provider "SQLNCLI" for linked server "sql2000Box" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "sql2000Box" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "sql2000Box".

Help!.

TIA,

Barkingdog

I called Microsoft on this one. They recommended that I enable RPC OUT on the server trying to link to the linked server. When I did I got another error message that appears explicilty in this article:

"You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server"

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I will see if this solution works.

Barkingdog

Problem with linked servers

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561632
I have two servers that are linked together ServerA and ServerB. I can do
selects from remote tables on both servers with no problems. The problem
that i am running into is when I am doing an insert into a local table via a
join with a local and remote table.
Both Win2k3 server with SQL 2000 SP3
ie (I know this code isnt correct, just an example)
insert into ServerA.DB.dbo.table
select *
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
I can run the selects just fine, but when adding the inserts, it craps out.
----
---
this is the error that i am getting
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].try this, it might help
select *
into #tmpData
from
ServerA.DB.dbo.table
inner join
ServerB.DB.dbo.table
insert into ServerA.DB.dbo.table
select *
from
#tmpData
remember that 'select *' shouldn't be used in production code.
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> I have two servers that are linked together ServerA and ServerB. I can do
> selects from remote tables on both servers with no problems. The problem
> that i am running into is when I am doing an insert into a local table via
a
> join with a local and remote table.
> Both Win2k3 server with SQL 2000 SP3
> ie (I know this code isnt correct, just an example)
> insert into ServerA.DB.dbo.table
> select *
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> I can run the selects just fine, but when adding the inserts, it craps
out.
> ----
---
> this is the error that i am getting
> The operation could not be performed because the OLE DB provider
'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561776
I had given thought to the temp table, but i would like to know if there is
configuration problem with my server keeping me from doing this.
TIA
Kevin E.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4360865d$0$141$7b0f0fd3@.mistral.news.newnet.co.uk...
> try this, it might help
> select *
> into #tmpData
> from
> ServerA.DB.dbo.table
> inner join
> ServerB.DB.dbo.table
> insert into ServerA.DB.dbo.table
> select *
> from
> #tmpData
> remember that 'select *' shouldn't be used in production code.
>
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:OLydnQns-ctvdMLeRVn-vg@.centurytel.net...
> a
> out.
> ---
> 'SQLOLEDB'
>

Problem with linked servers

Hi,
we are trying to get some data from a linked server via an ODBC connection.
This server has 3 intances of the same software, we load 2 tables from each
installation. From one instance we get both tables, from the two others we
can only acces one of the two.
I explain that only because I want to make clear the the ODBC driver
"normally" should be able to be used with Linked servers.
When we try to load the two other tables, we get an error 7317:
OLE/DB provider returned an invalid schema definition
The ODBC driver is based on the SIMBA ODBC framework.
Any idea what I can do? What's about the "schema definition"?
Thanks,
Thomas1. Can you do simple select like "SELECT * from
servername.dbname.owner_name.table_name"
2. Does the account that has been used in defining the linked server has
proper security on the linked server tables.
"Thomas Pagel" <tpagel@.software4you.com> wrote in message
news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> Hi,
> we are trying to get some data from a linked server via an ODBC
connection.
> This server has 3 intances of the same software, we load 2 tables from
each
> installation. From one instance we get both tables, from the two others we
> can only acces one of the two.
> I explain that only because I want to make clear the the ODBC driver
> "normally" should be able to be used with Linked servers.
> When we try to load the two other tables, we get an error 7317:
> OLE/DB provider returned an invalid schema definition
> The ODBC driver is based on the SIMBA ODBC framework.
> Any idea what I can do? What's about the "schema definition"?
>
> Thanks,
>
> Thomas
>|||Ammar,
1.) This works with some of the tables, others don't
2.) The queries work well if I direclty use the ODBC driver i.e. with DTS
with the same user and it has all rights, too. So this couldn't be a
security issue...
Thanks,
Thomas
"Ammar" <ammar.ansari@.anthem.com> schrieb im Newsbeitrag
news:%23CeduY4VDHA.2544@.tk2msftngp13.phx.gbl...
> 1. Can you do simple select like "SELECT * from
> servername.dbname.owner_name.table_name"
> 2. Does the account that has been used in defining the linked server has
> proper security on the linked server tables.
>
> "Thomas Pagel" <tpagel@.software4you.com> wrote in message
> news:uiSX6u3VDHA.1180@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > we are trying to get some data from a linked server via an ODBC
> connection.
> > This server has 3 intances of the same software, we load 2 tables from
> each
> > installation. From one instance we get both tables, from the two others
we
> > can only acces one of the two.
> >
> > I explain that only because I want to make clear the the ODBC driver
> > "normally" should be able to be used with Linked servers.
> >
> > When we try to load the two other tables, we get an error 7317:
> >
> > OLE/DB provider returned an invalid schema definition
> >
> > The ODBC driver is based on the SIMBA ODBC framework.
> >
> > Any idea what I can do? What's about the "schema definition"?
> >
> >
> > Thanks,
> >
> >
> > Thomas
> >
> >
>

Friday, March 30, 2012

Problem with Maintenance Plan to delete files older than 2 days

Problem with Maintenance Plan to delete files older than 2 days

Problem with Maintenance Plan to delete files older than 2 days

Problem with mailing labels and line feed

Problem with machine that has only reporting services2005 installe

problem with lookup ,is this a bug?

Problem With LookUp

Problem with Look up

Problem with long running report timing out

Problem with logon SQL Server (MSDE)

problem with loging in

Problem With Login Null

Problem With Login null

problem with login list

problem with login

Problem with logging/System::ErrorDescription variable

problem with Log

Problem with locks - Help!

Problem with locks - Help!

Problem with locks - Help!

Problem with locking in Selects

problem with local connection

Cannot open database "customers" requested by the login. The login failed.
Login failed for user 'myServer\ASPNET'.

Problem with LoadReport web method

Problem with ListAvailableSQLServers in vb 6

Problem with linking tables

Problem with linking servers

problem with linked servers and INSERT

Problem with Linked Servers

Problem with linked servers

Problem with linked servers

db datatypes

Blog Archive

About Me

Friday, March 30, 2012

Cannot open database "customers" requested by the login. The login failed.Login failed for user 'myServer\ASPNET'.

Blog Archive

About Me

Cannot open database "customers" requested by the login. The login failed.
Login failed for user 'myServer\ASPNET'.