I have this code, running parallel in two separate threads. It works fine for a few times, but at some random point it throws InvalidOperationException:
The transaction is either not associated with the current connection or has been completed.
At the point of exception, I am looking inside the transaction with visual studio and verify its connection is set normally. Also command.Transaction._internalTransaction. _transactionState is set to Active and IsZombied property is set to false.
This is a test application and I am using Thread.Sleep for creating longer transactions and causing overlaps.
Why may the exception being thrown and what can I do about it?
IDbCommand command = new SqlCommand("Select * From INFO");
IDbConnection connection = new SqlConnection(connectionString);
command.Connection = connection;
IDbTransaction transaction = null;
try
{
connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.ExecuteNonQuery(); // Sometimes throws exception
Thread.Sleep(forawhile); // For overlapping transactions running in parallel
transaction.Commit();
}
catch (ApplicationException exception)
{
if (transaction != null)
{
transaction.Rollback();
}
}
finally
{
connection.Close();
}
Hi all,
I need to export the results of a query to a csv file in an FTP folder.
Is it possible to achieve this within a stored procedure?
If yes, comes yet another constraint: can I achieve this without sysadmin privileges, aka without using xp_cmdshell + BCP utility?
If no to 2., does the caller have to have sysadmin privileges or would it suffice if the SP owner has sysadmin privileges?
Here are some more details to the problem: The SP must export and transfer the file on the fly and raise error if something went wrong. The caller must get a response immediately, i.e. in case of no error, he can assume that the results are successfully transferred to the folder. Therefore, a DTS/SSIS job that runs every N minutes is not an option. I know the problem smells like I will have to do this at application level, but I would be more than happy if all those stuff could be done from T-SQL.
I am developing a C# VS2008/SQL Server website application. I've never used the Dictionary class before, but I am trying to replace my Hashtable with a Dictionary variable.
Here is a portion of my aspx.cs code:
...
Dictionary<string, string> openWith = new Dictionary<string, string>();
for (int col = 0; col < headers.Length; col++)
{
@temp = (col + 1);
@tempS = @temp.ToString();
@tempT = "@col" + @temp.ToString();
...
openWith.Add(@tempT, headers[col]);
}
...
for (int r = 0; r < myInputFile.Rows.Count; r++)
{ resultLabel.Text = ADONET_methods.AppendDataCT(myInputFile, openWith); }
But this is giving me a compiler error on this last line:
Argument '2': cannot convert from 'System.Collections.Generic.Dictionary' to 'string'
How do I pass the entire openWith variable to AppendDataCT? AppendDataCT is the method that calls my SQL stored proc. I want to pass in the whole row where each row has a unique set of values that I want to add to my database table. For example, if each row requires values for cells A, B, and C, then I want to pass these 3 values to AppendDataCT, where all of these values are strings. How do I do this with Dictionary?
Hi, I have yet another problem with Reporting services. Finally I was able to set the ball rolling, achieving success in configuring reporting services with a domain user account. Now I have a problem accessing the server from another machine (in the same domain) using Integrated Windows Authentication in IIS 6.0.
I have never had problems with this on networks using NT Authentication, but this is the first time our network is set up to use Kerberos. Here are my IIS settings -
Reports (Manager) & ReportServer Virtual directories under Default Website,
Both these are configured to run with Integrated Windows Authentication..Anonymous access turned OFF.
Reporting Services Windows Service runs under Network service account and Web Service under a domain user account.
Note - If I turn on Basic Authentication, it works fine, exceeding all my expectations, but this is not something I want.
Any help is appreciated.
Thanks
Vineet
declare @t1 Table
(
a1 int
)
insert into @t1
select top 10 AnimalID from Animal
--select * from @t1
declare @t2 table
(
dogs int null
)
update @t2
set dogs = (Select COUNT(*) from @t1)
--------- The out put it gives me is just 0
My solution uses a proprietary assembly, which when debugging the solution throws an Exception saying it can't find an assembly that is meant to be one of the projects in my solution.
I cannot add a reference to the proprietary assembly because all I have is the DLL.
When I compile everything into a single application directory and run the app it works fine, but I want to debug.
Where should assemblies be placed if you want a proprietary assembly in the solution to see them?
I assume the issue is that there is no path specified and it is just looking in a default directory of some kind.
Hi ,
I've defined table with this schema :
CREATE TABLE [dbo].[Codings](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[CodeId] [int] NOT NULL,
[Title] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,
CONSTRAINT [PK_Codings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
And fill it up with data like this :
Id ParentId CodeId Title
----------- ----------- ----------- ----------
1 NULL 0 Gender
2 1 1 Male
3 1 2 Female
4 NULL 0 Educational Level
5 4 1 BS
6 4 2 MS
7 4 3 PHD
Now , I'm looking for a solution , in order , When i delete a record that is parent ( like Id= 1 or 4 ), It delete all child automatically ( all records that their ParentId is 1 or 4 ) .
I supposed i can do it via relation between Id and Parent Id ( and set cascade for delete rule ) , But when i do that in MMS , the Delete Rule or Update Rule in Properties is disabled .
My question is , What can i do to accomplish this ?
Thank you
Hello Experts,
I have a flat text file data which I import into MSSQL table.
It creates and table with specified name along with multiple columns as per data file.
now I need a query which will return the data and its count. e.g.
data file :
BREAD,MILK
BREAD,DIAPER,BEER,EGGS
MILK,DIAPER,BEER,COKE
BREAD,MILK,DIAPER,BEER
BREAD,MILK,DIAPER,COKE
BREAD,ICE,MANGO
JUICE,BURGER
Result should be
BREAD | 5
MILK | 4
DIAPER| 4
and so on.
I'd like to use System.Net.IPAddress.TryParse to validate IPv6 addresses because I don't want to write my own reg exp :-)
However, this seems to allow strings such as "(validIPv6)](anythingatallhere)" - for example, "1234::5678:abcd]whargarbl".
Is there a reason for these being valid, or is this a fault?
This is further complicated by the fact that I actually want only strings of the form "[(validIPv6)]:(portnumber)" so I'm going to have to do a bit of validation myself.
Input
@StartDate = '01/25/2010'
@EndDate = '02/06/2010'
I have 2 CTEs in a stored procedure as follows:
with CTE_A as
(
[gives output A..Shown below]
),
with CTE_B as
(
Here,
I want to check if @StartDate is NOT in output A then replace it with the last known date. In this case, since @startdate is less than any date in output A hence @StartDate will become 02/01/2010.
Also to check if @EndDate is NOT in output A then replace it with the last known date. In this case, since @enddate is 02/06/2010 hence it will be replace with 02/05/2010.
// Here there is a query using @startDate and @EndDate.
)
output A
Name Date
A 02/01/2010
B 02/01/2010
C 02/05/2010
D 02/10/2010
declare @mydata nvarchar(4000)
set @mydata = '36|0, 77|5, 132|61'
I have this data that I need to get into a table. So for Row1 columnA would be 36 and columnB would be 0. For Row2 columnA would be 77 and columnB would be 5 etc.
What is the best way to do this?
Thanks
Everywhere I read says that messages handled by the service broker are processed in the order that they arrive, and yet if you create a table, message type, contract, service etc , and on activation have a stored proc that waits for 2 seconds and inserts the msg into a table, set the max queue readers to 5 or 10, and send 20 odd messages I can see in the table that they are inserted out of order even though when I insert them into the queue and look at the contents of the queue I can see that the messages are all in the right order.
Is it due to the delay waitfor waiting for the nearest second and each thread having different subsecond times and then fighting for a lock or something?
The reason i've got a delay in there is to simulate delays with joins etc
Thanks
demo code:
--create the table and service broker
CREATE TABLE test
(
id int identity(1,1),
contents varchar(100)
)
CREATE MESSAGE TYPE test
CREATE CONTRACT mycontract
(
test sent by initiator
)
GO
CREATE PROCEDURE dostuff
AS
BEGIN
DECLARE @msg varchar(100);
RECEIVE TOP (1) @msg = message_body FROM myQueue
IF @msg IS NOT NULL
BEGIN
WAITFOR DELAY '00:00:02'
INSERT INTO test(contents)values(@msg)
END
END
GO
ALTER QUEUE myQueue
WITH STATUS = ON,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dostuff,
MAX_QUEUE_READERS = 10,
EXECUTE AS SELF
)
create service senderService
on queue myQueue
(
mycontract
)
create service receiverService
on queue myQueue
(
mycontract
)
GO
--**********************************************************
--now insert lots of messages to the queue
DECLARE @dialog_handle uniqueidentifier
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>1</test>');
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>2</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>3</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>4</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>5</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>6</test>')
BEGIN DIALOG @dialog_handle
FROM SERVICE senderService
TO SERVICE 'receiverService'
ON CONTRACT mycontract;
SEND
ON CONVERSATION @dialog_handle
MESSAGE TYPE test
('<test>7</test>')
I am looking for something like TRYCAST in TSQL or an equivalent method / hack.
In my case I am extracting some date data from an xml column.
The following query throws "Arithmetic overflow error converting expression to data type datetime." if the piece of data found in the xml cannot be converted to datetime (in this specific case, the date is "0001-01-01" in some cases). Is there a way to detect this exception before it occurs?
select
[CustomerInfo].value('(//*:InceptionDate/text())[1]', 'datetime')
FROM Customers
An example of what I am trying to achieve in pseudocode with an imagined tsql function TRYCAST(expr, totype, defaultvalue):
select
TRYCAST(
[CustomerInfo].value('(//*:InceptionDate/text())[1]', 'nvarchar(100)'),
datetime,
null)
FROM Customers
Hi all,
Is it possible to hide a dynamic query from the result sets provided from a Stored Procedure?
I am using the @@rowcount of the dynamic query to set a variable that is used to determine whether another query runs or not.
The other query is used by code that I cannot change - hence why I am changing the Stored Procedure. The dynamic query returns as the first result set from the Stored Procedure is now the result of the dynamic query which currently is "breaking" the calling code.
Thanks in advance
This app I'm working on needs to store some meta data fields about an entity. The problem is that we can already foresee that these fields are going to change a lot in the future. I'm using Hibernate (in ColdFusion) and each entity's properties are translated to one column in the entity table, but altering table columns later down the raod will be costly and error-prone right?
Should I go for something like this?
MetaDataField
-----
metaDataFieldID (PK), name
FieldValue
----------
EntityID (PK, FK), metaDataFieldID (PK, FK), value [varchar(255)]
Is this common? Anything to watch out for?
p.s. I also thought of using XML on SQL Server 05+. After talking to some ppl, seems like it is not a viable solution 'cause it will be too slow for doing certain query for reporting purposes.
I'm writing a SQL Server stored procedure in which I want to lock a table for update before executing the body of the stored procedure. I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table.
Here is my first attempt:
CREATE PROCEDURE someProcedure
BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMITTED
BEGIN TRANSANCTION
SELECT COUNT(*) FROM TheTable WITH (UPDLOCK, TABLOCK)
-- Pause procedure so that we can view the locks with sp_lock
WAITFOR DELAY '00:15'
-- Do stuff
COMMIT
END
When I execute the stored procedure, and invoke sp_lock, I see that the table is indeed locked. However, it's locked with an Exclusive lock instead of an update lock:
spid | dbid | ObjId | IndId | Type | Resource | Mode | Status
------------------------------------------------------------------
63 | 10 | 233208031 | 0 | TAB | | X | GRANT
How can I get an update (U) lock instead?
I’ve been trying to understand some strange values in the RESOURCE_ASSOCIATED_ENTITY_ID column of SYS.DM_TRAN_LOCKS when RESOURCE_TYPE is “OBJECT”. Although these should be object Ids, I cannot determine what object they actually refer to.
I’ve tried everything I can think of, including querying all system tables with columns of type INT and BIGINT to see if I can find the value. No luck. The funny values actually appear in SYS.DM_TRAN_LOCKS, SYS.SYSLOCKINFO and SP_LOCK.
My job is the maintain one application which heavy use SQL server (MSSQL2005).
Until now middle server stores TSQL codes in XML and send dynamic TSQL queries without using stored procs.
As I am able change those XML queries I want to migrate most of my queries to stored procs.
Question is folowing:
Most of my queries have same Where conditions against one table
Sample:
Select
.....
from ....
where ....
and (a.vrsta_id = @vrsta_id or @vrsta_id = 0)
and (a.podvrsta_id = @podvrsta_id or @podvrsta_id = 0)
and (a.podgrupa_2 = @podgrupa2_id or @podgrupa2_id = 0)
and (
(a.id in (select art_id from osobina_veze where podosobina_id in (select ado from dbo.fn_ado_param_int(@podosobina))
group by art_id
having count(art_id)= @podosobina_count ))
or ('0' = @podosobina)
)
They also have same where conditions on other table.
How I should organize my code ?
What is proper way ?
Should I
make table valued function that I will use in all queries
or use #Temp tables and simple inner join my query to that each time when proc executing?
or use #temp filed by table valued function ?
or leave all queries with this large where clause and hope that index is going to do their jobs.
or use WITH(statement)
I have tried the following code but has no effect:
Imports system.Runtime.InteropServices
<DllImport("UxTheme.DLL", BestFitMapping:=False, CallingConvention:=CallingConvention.Winapi, CharSet:=CharSet.Unicode, EntryPoint:="#65")> _
Shared Function SetSystemVisualStyle(ByVal pszFilename As String, ByVal pszColor As String, ByVal pszSize As String, ByVal dwReserved As Integer) As Integer
End Function
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'This code will set Window Themes
SetSystemVisualStyle("C:\WINDOWS\Resources\Themes\HmmXP_2_0_1\HmmXP\HmmXP.msstyles", "NormalColor", "NormalSize", 0)
End Sub
Can anyone plz help?
I have never used UDF in sql server. Today I got to know that we can have functions which can return a table. So I just wanted to know can I use functions in place of views.
If yes, which one is the better choice and why