Hi
What is SQL Server 2005 default behavior with statement like this:
SELECT * FROM dbo.Products
Will it take any shared locks on Products table? Is it different than:
BEGIN TRAN
SELECT * FROM dbo.Products WITH NOLOCK
COMMIT
TIA
When i execute the following in Sql query anlyzerDeclare @dbname varchar(30),@str varchar(500),@emailID varchar(50)set @EmailID='santosh@yahoo.com'set @dbname='DB_kms_prv'set @str='SELECT empid, NTName, officialEmail, PreferredName FROM ' +@dbname + '.dbo.tblEmployee where officialEmail=' + @emailIDexec (@str)I get error messageThe column prefix 'santosh@yahoo' does not match with a table name oralias name used in the query.How to get rid of it..?
View 1 Replies View RelatedHi,
I try to run sql statement by placing them in a variable as given below but receive the following error message
set @sql_string= 'BACKUP DATABASE '+ @temp_db+' TO '+ @bk_device+' WITH FORMAT,INIT, NAME = '''+ @bk_name+''''
EXECUTE IMMEDIATE @sql_string
Error Message
Server: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'IMMEDIATE'.
Server: Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'BACKUP DATABASE DTSTEST TO bk_dev1 WITH FORMAT,INIT, NAME = 'Full backup''.
Thanks
John Jayaseelan
Hello,
I am trying to import a very large amount of data into an SQL database. The data is in a format of SQL statements already - it is all in one large text file consisting of a number of CREATE TABLE X and INSERT INTO X VALUES().
The problem is that the amount of values being inserted into some of the tables is so large, that I am unable to open the .SQL file using query analyzer to run it, because the line-size limit for query analyzer is 64kb, whereas actual line-size in the file is in some cases in excess of 15MB.
I would appreciate any advice on how to get all this data into a managable format. I keep thinking that there simply has to be a way to execute these over-size SQL statements.
Thank you in advance!
-Sergey
I have a stored procedure named usp_CreateSomeAdminObjects that resides in say a database named AdminStuff (ok, fictional but it gets the point across :) ). This proc generates a series of dynamic SQL statements (DROP TRIGGER, CREATE TRIGGER and EXECUTE sp_settriggerorder) against tables that reside in different databases. However I am unable to change the context of the database from AdminStuff to the database I am cycling. Any suggestions on how I can have execute this dynamic SQL in the context of a specific database (other than the one the proc resides in)?
I can not put the USE statement in a stored procedure. Attempts to use sp_executesql, EXECUTE and even run the script through sqlcmd using either (a) the -d option or (b) a USE <dbname>; statement as part of the -q parameter have all been unsuccessful.
Heck, even if I execute this code through SSMS it does not work as I need it to:
USE OtherDatabaseName;
EXEC DBA.dbo.usp_CreateSomeAdminObjects
I confirmed this by adding these statements to the proc:
/* Gets list of tables from the current database */
SELECT name, create_date FROM sys.objects WHERE type = 'U' ORDER BY name
SELECT DB_NAME();
These statements return a list of user tables that reside in AdminStuff and not OtherDatabaseName (the same goes for the DB_NAME() statement.
Any suggestions on how I can accomplish what I'm looking to do - have this proc execute dynamically generated DDL scripts for tables that reside in a different database. I'd rather not name this procedure sp_CreateSomeAdminObjects and compile it in the master database. If that is my only option I will but figured I'd check out other options first.
Hi,
We have unreliable circuits connecting our office to remote sql servers. At our office I need to delete/insert a bunch of records from those remote locations. Since the connection is unreliable what is the best way to check if a server is available before executing delete/insert statements. A few points to consider:
1. I want this to be done in SQL
2. I have a cursor that cycles through a tabe that contains the name of the linked servers. The cursure is used to delete/insert a bunch of records from each remote server.
3. If one server is unavailable the stored proc should continue to the next sql server (next record in the cursor.)
Thanks,
Bradley
Hello all,
I am trying to build a package running a container that includes several tasks: 2 Execute SQL tasks and a data flow task. I would like to execute this container in a single transaction, which means that if any of the tasks fail, no changes are been made by the container execution.
To achieve this, I have followed the instructions of how to configure the DTC on the server and locally, and set the container's TransactionOption Property to "Required".
The problem I encounters is that when using a dataflow task in the container, the running process runs OK until it reaches the dataflow task, then it just gets stuck (marking the task in yellow). When removing the dataflow task, left with only Execute SQL tasks everything works fine.
Any idea?
Thanks,
Liran
Hi,
I have a stored proc and using transactions as foolows
(not coplete Sp)
Begin Transaction TransName
Select @vsSql = Create a temp table (dynamically)
Exec( @vsSql )
Select @vsSql = dynamic insert statement
Exec( @vsSql )
and executing couple of dynamic statements using Exec
And @ the end of SP
if @@error <>0
rollback transaction TransName
else
commit transaction TransName
and when i execute the stored proc i am getting the following error
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
And also my sql server management studio hogs up
Can any one please help me on this
~Mohan
If I send multiple sql's with ado.net in one statement (one executeSql separated by semilcolons), and the second one fails, will the first one be rolled back? or do I need to put it all in a transaction?
View 1 Replies View RelatedI am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
View 2 Replies View RelatedI'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.
Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)
[Code] ...
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedHi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View Relatedis anybody using this? it looks like a lot of trouble to return a formatted string. I am considering it for something. Any opinions?
http://technet.microsoft.com/en-us/library/aa226532(SQL.80).aspx
caution: you have to X out of the Do You Want to Download Silverlight popup.
i have a situation, where i need to group my information by a certainid, but that information which should already be grouped, i need toorder based on a number in one of the columnsexample:<ex id=2 listorder=1><description>desc 2</description></ex><ex id=1 listorder=2><description>desc 1</description></ex>in my order clause i first put order by the id, and it will group theinformation accordingly, but will put the lowest number id first, notcaring about the listorderbut if i order by the listorder column first, the information isn'tgroupd properlyi don't know if i made myself clear enough?hope someboyd can help methnx in advance
View 1 Replies View RelatedHi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
Hi ,
I've tried to switch MS-Sql/Server 6.5 on explicit_transactions
without success.
(set implicit_transaction on/off)
What is the exact syntax for doing that ?
Herve
(PS: Thanks Gregory for your quick answer )
Hi All,
I don't know MS-SQLserver internal system at all. I 've just used Oracle
a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo)
you can switch off the implicit transaction by using
the option AUTOCOMMIT ON/OFF.
How can switch off the implicit transaction system on MS-SQLServer ?
In advance thanks,
Herve
I created a SELECT statement that uses the FOR XML EXPLICIT clause so that SQL Server data can be exported to another system. Format thios system accepts is XML. What should I define within a DTS package so that this Select statement is executed and an XML file is created?
View 12 Replies View RelatedI have an cursor that loops 720 times.
Each FETCH does 6 INSERTS.
Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @@TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @@TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @@TRANCOUNT to commit at in this case?
Thanks crew,
Carl
Hello,
In the SP below, I get the following error when I run it:
Explicit value must be specified for identity column in table 'Tests' when IDENTITY_INSERT is set to ON.
From what I've read of the INSERT method, it says you can add a variable value. So I don;t understand why it won't take mine.
CREATE PROC qaspAddTestRecord AS
DECLARE @aKey bigint
DECLARE @aTestSuiteId bigint
exec qaspGetNewID 'Tests', @aKey OUT
SELECT @aTestSuiteId= NewID FROM ID WHERE TableName='TestSuite'
SET IDENTITY_INSERT Tests ON
INSERT INTO Tests (TestSuiteId, TestIDInternal) VALUES (@aTestSuiteId, @aKey)
SET IDENTITY_INSERT Tests OFF
GO
--PhB
Hello all,I'm trying to generate some XML directly from MS SQL with thefollowing codeSELECT1 AS tag, NULL AS parent, NULL AS [GoogleCustomizations!1], NULL AS [Annotations!2], NULL AS [Annotation!3], NULL AS [Annotation!3!about], NULL AS [Annotation!3!score], NULL AS [Label!4], NULL AS [Label!4!name]UNIONSELECT2 AS tag, 1 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, NULLUNIONSELECTTOP 503 AS tag, 2 AS parent, NULL, NULL, NULL, 'www.' + domainName, 1 -- score, NULL, NULLFROM tbl_auDomainNameUNIONSELECT4 AS tag, 3 AS parent, NULL, NULL, NULL, NULL, NULL, NULL, '_cse_ad-o6lgdody'FOR XML EXPLICITThe XML it needs to create is as following<GoogleCustomizations><Annotations><Annotation about="www.clickfind.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.lookle.com/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation><Annotation about="www.sensis.com.au/*" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>It is currently creating<GoogleCustomizations><Annotations><Annotation about="www.10000steps.org.au" score="1" /><Annotation about="www.101fm.asn.au" score="1" /><Annotation about="www.aao.gov.au" score="1"><Label name="_cse_ad-o6lgdody" /></Annotation></Annotations></GoogleCustomizations>I cannot get my head around how I can get the label <Labelname="_cse_ad-o6lgdody" />in each element. Does anyone know?Thanks in advance.
View 2 Replies View RelatedI am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. 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.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag,
NULL as Parent,
Ex_Id as [Exam!1!Ex_Id],
NULL as [Ex_Title!2!!cdata],
NULL as [Ex_Situation!3!!cdata]
FROM Exams
WHERE ex_State = 'P'
UNION ALL
SELECT 2 as Tag,
1 as Parent,
Ex_Id,
ex_Title,
ex_Situation
FROM Exams
WHERE ex_State = 'P'
order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata]
FOR XML EXPLICIT, ROOT('Exams')
The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
Hi:
How can I force MsSQL to a explicit DateFormat.
Like: 'DD/YY'
performance wise what is the difference between following queries
Select * from A, B Where A.ID = B.ID
Select * from A INNER JOIN B ON A.ID = B.ID
(ID is the primary key)
I am developing an integration process between two databases. One ofthem is a SQL Server 2000 and the other is using MSDE 2000. Theintegration process is done in C# (VS2003).The main database is the SQL Server, the MSDE will contain a reallysmall subset of the data found on the main. To help diminish the amountof time taken to develop an integration process between thosedatabases, the same structure are found on both side. The onlydifference, when I insert data in the MSDE from the SQL Server, I setthe IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.I can insert one set of data without problem, but from there, if I tryagain, I will always receive the "Cannot insert explicit value foridentity column in table ... when IDENTITY_INSERT is set to OFF." Isaw on Microsoft website the article ID 878501; I noticed I was usingMSDE sp3, I upgraded to SP4... and I still have the problem.I know, when I call the update function on the sqldataadapter, theadapters contain the IDENTITY_INSERT ON and it's set to OFF after theinsert. The "Cannot insert..." error is the only one I received.Can anyone help me on that issue? Take note that this approach wasused because of customer requirements; the size of the database alsocauses some problem (over 200 tables) and we decided to use the samestructure on both side to minimize the support time.
View 3 Replies View Related
Hi all,
I'm using SQL Server Express 2005, with mixed authentication mode.
I would like to connect, manipulate data and administrate db's on my server without explicit permissions on those db's.
I've created a new Login with SQL Server authentication and assigned it the public & sysadmin server roles BUT I can't even connect to db's where I don't have an explicit db role (db_owner, ddl_admin...)!
What am I missing?
How can I administrate a db without explicit permissions (db role) on that db?
Thanks,
Assaf
Hello!
View 1 Replies View Related