New Database Column Isn't Receiving Data
Feb 26, 2008
I created a new column and modified my businesslogic file and the stored procedure. My program uses the SQL helpder class to execute the stored procedure which inserts the article into the database. All of the other previous columns are getting written to, however the new column I created is getting NULL when I open the database to inspect. Why does this happen? I made sure the db column name matches exactly with the params elsewhere.
Did I miss something? It's not throwing an error anymore. it's not taking the data I enter into the form and putting it into the db column.
View 4 Replies
ADVERTISEMENT
Mar 29, 2007
Hello,
I am attempting to backup a database that is receiving log shipments and is presently in standby / readonly mode. What is the best way to do this?
Thanks in advance,
Bob
View 10 Replies
View Related
Apr 7, 2007
I have a table that records sales leads and I need to calculate the total number of leads per user and the total amount owed, then then show only the users who have recieved 3 leads. The UserID is an integer.
Here is what i have:
SELECT * FROM
(
SELECT DISTINCT UserID,
(SELECT COUNT(*)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalLeads,
(SELECT SUM(Price)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalAmount
FROM LeadMatches AS L
)
WHERE TotalLeads = 3
This should work but it produced the following result:
UserID | TotalLeads | TotalAmount
---------------------------------------------------------------
<Unsupported Data Type> | 3 | 75.00
<Unsupported Data Type> | 2 | 50.00
I have no idea why the result produceing <Unsupported Data Type> for the UserID and have been unable to find a way around as of yet. Any help would be appreciated.
View 3 Replies
View Related
Sep 29, 2014
I have setup database mail account with the gmail smtp. But I m getting error (i.e. mail server failure) while sending or receiving database mails.
View 5 Replies
View Related
Sep 26, 2007
I have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows: -------------------------------------------------------------------------------------------------------
sw_calendar = calendarAdapter.GetEventByID(cid)
If sw_calendar.Rows.Count > 0 Then
lblStartDateText.Text = sw_calendar(0).eventStartDate
lblEndDateText.Text = sw_calendar(0).eventEndDate
lblTitleText.Text = sw_calendar(0).title
lblLocationText.Text = sw_calendar(0).location
lblDescriptionText.Text = sw_calendar(0).description
Else
lblStartDateText.Text = "*** Not Found ***"
lblEndDateText.Text = "*** Not Found ***"
lblTitleText.Text = "*** Not Found ***"
lblLocationText.Text = "*** Not Found ***"
lblDescriptionText.Text = "*** Not Found ***"
End If
-------------------------------------------------------------------------------------------------------
If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message:
Unable to cast object of type 'System.DBNull' to type 'System.String'.
I've tried a bunch of different things such as:
Adding ".ToString" to the database field,
Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value...
...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message.
Can anyone help me with the code required to trap the null within the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline )
Thanks,
Brad
View 6 Replies
View Related
May 13, 2008
Using the following code we are having a problem connecting a clients WIndows Mobile device to the SDF under IIS. We have success elsewhere. Does anyone have any clues ?
Cheers Al
Dim repl As New SqlCeReplication()
Try
repl.InternetUrl = InternetUrl
repl.Publisher = Repl_Publisher
repl.PublisherDatabase = Repl_PublisherDatabase
repl.PublisherSecurityMode = SecurityType.DBAuthentication
repl.Publication = Repl_Publication
repl.Subscriber = Repl_Subscriber
repl.SubscriberConnectionString = Repl_SubscriberConnectionString
repl.DistributorLogin = Handhelds_User
repl.DistributorPassword = Handhelds_Password
repl.AddSubscription(AddOption.ExistingDatabase)
repl.Synchronize()
MsgBox("Synched!")
Catch err As SqlCeException
MessageBox.Show(err.ToString, "Error")
Finally
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
End Try
View 3 Replies
View Related
Aug 17, 2007
Any idea of what this means?
My acknowledgement messages are getting stuck in sys.transmission_queue. In profiler, the message generated by the target server (the one sending the ack) is:
This message could not be delivered because it is a duplicate.
The result of the function GET_TRANSMISSION_STATUS is
An error occurred while receiving data: '121(The semaphore timeout period has expired.)'.
Each time I send a new mesage I get one record in sys.conversation_endpoints and two in sys.transmission_queue. The two records in the transmission_queue are the same except one has a NULL for the message body and is_end_of_dialog=1
If I try to clean out the queues they re-populate (I guess becasue the initiator keeps re-trying the message?)
I have double checked my routes back to the initiator and they seem correct (see below). In fact, a second server is using an identical route and it is working OK.
The only thing tricky on this machine is added a second message queue for a differnet kind of message, and it is replying to a different service broker instance. In other words, on server A, database "Production" sends a message to server B, database "Destination" and Server A, database "Schedule" also sends a message to server B, database "Destination".
In the respective return routes I have double checked the guid for the broker_instance.
The broker_instance value in each return route is right out of server A's sys.databses view:
name service_broker_guid
-------------------------------------------------------------------------------------------------
Production 3BE812A8-5212-45D2-8664-521F83689585
Schedule 86113D85-A594-4EAB-8460-3E459EE8F055
The return routes look like this:
CREATE ROUTE [SendReplyProduction] AUTHORIZATION [mssqlssb]
WITH SERVICE_NAME = N'//mycompany.com/SSB/Services/ProductionChangeService' ,
BROKER_INSTANCE = N'3BE812A8-5212-45D2-8664-521F83689585' ,
ADDRESS = N'TCP://Server_A:4022'
The above route should send the reply to the "Production" database on Server A
CREATE ROUTE [SendReplySchedule] AUTHORIZATION [mssqlssb]
WITH SERVICE_NAME = N'//mycompany.com/SSB/Services/ScheduleChangeService' ,
BROKER_INSTANCE = N'86113D85-A594-4EAB-8460-3E459EE8F055' ,
ADDRESS = N'TCP://Server_A:4022'
The above route should send the reply to the "Schedule" database on Server A
Is this the correct way to do it? And can anyone tell me why I get the semaphore error?
For what it is worth, I am using Transport security only with windows authentication (both machines on the same domain and no certificates)
Thanks for any help!
John
View 1 Replies
View Related
Jan 3, 2006
What does this error mean? I get this in the transmission_status column of sys.transmission_queue
An error occurred while receiving data: '10053(An established connection was aborted by the software in your host machine.)'.
Also, does the number in this message correspond to error 10053 in sys.messages?
Thanks
View 3 Replies
View Related
Nov 20, 2007
Hi.
I am stuck with error: An error occurred while receiving data: '64(error not found)'.
My Service Broker configuration:
Server A initiator,
Server B target.
Server A sends message to Server B, Server B sends back reply.
On this stage I receive problem. Server B message does not come to server A.
It stays on sys.transmission_queue on server B.
This problem occurs only during initial setup. During initial setup, on Server B messages, queues, services, contracts, routes, bindings are created. On Server A: routes and bindings. As a last step, a message is sent to Server B.
So, reply on this message never comes. Initial setup is run using procedure with execute as €˜owner€™, owner=€™dbo€™.
But, whenever I send messages after that, everything works fine.
Any suggestions?
Please :-)
View 7 Replies
View Related
Oct 31, 2007
Hi,
I have set up SB between 2 databases, and I keep geting a variety of error messages in the queue on both sides. The first is:
An error occurred while receiving data: '10054(error not found)'.
And on the other side its
Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.
The only difference between this and the tutorials is that the TCP port have been moved to 4321 instead of 4022, and this has been done both sides, because something else was blocking the 4022 port. This one is definately free on both sides. I have recreated the certificates, the users, the end points, the queues and the services multiple times, and checked them all in the sys.routes etc and they all seem fine.
I do also get a message in the queue that I can receive sometimes that tells me I don't have rights to the service on the other machine. I can send a message to itself and it doesn't complain.
Both machines are on the same domain, and I have also tried to grant rights to public to no avail.
Help!
TIA
Ian.
View 2 Replies
View Related
Aug 23, 2007
We are using Service Broker to synchronise two databases using async triggers (using a middle server to preform data mapping processing). We are re-using dialogs as we want to ensure order when sending messages which contain the data to be synced. A dialog is created between each intiator and target service (there are 3 of each) which is kept open indefinately (we are only ending conversation upon receiving an end conversation message or error message).
We seem to get it working for a period then after about 1 hour it seems to stop sending and we see the following error in SQL Profiler:
An error occurred while receiving data: '64(error not found)'
Any ideas what could be causing this? We do not see any errors or end conversations. It seems to happen at irregular points.
View 2 Replies
View Related
Jan 25, 2007
Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.
How could I backup/restore master key, symmetric and asymmetric key created for my database?
Thank you
View 6 Replies
View Related
Aug 20, 2014
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrialBalance](
[Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
View 7 Replies
View Related
Jun 23, 2005
Hi there,
I am writing an SQL stored procedure that returns a dataset as well as
a return value. When I execute the stored proc, all seems to
work, except the return value comes back as null.
Code:
// Create Instance of Connection and Command Object
SqlConnection
connection = new SqlConnection(ApplicationConfig.ConnectionString);
SqlCommand
command = new SqlCommand("SoundLeaf_GetPagedProductsByCategory",
connection);
// Mark the Command as a SPROC
command.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter
parameterID = new SqlParameter("@CategoryID", SqlDbType.Int);
parameterID.Value = categoryID;
command.Parameters.Add(parameterID);
SqlParameter
parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
parameterPageIndex.Value = pageIndex;
command.Parameters.Add(parameterPageIndex);
SqlParameter
parameterRowCount = new SqlParameter("@RowCount", SqlDbType.Int);
parameterRowCount.Value = rowCount;
command.Parameters.Add(parameterRowCount);
SqlParameter
parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int);
parameterPageCount.Direction = ParameterDirection.Output;
command.Parameters.Add(parameterPageCount);
// Open the connection and execute the Command
connection.Open();
SqlDataReader
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
pageCount =
(int)command.Parameters["@PageCount"].Value; // Null Reference Exception
}
SQL:
CREATE PROCEDURE GetPagedProductsByCategory]
(
@CategoryId int = 0,
@PageIndex int = 1,
@RowCount int = 10,
@PageCount int OUTPUT
)
AS
Declare @TotalRows int, @StartPosition int
Declare @PK int
DECLARE @tmpTable TABLE (
PK int NOT NULL PRIMARY KEY
)
SELECT @StartPosition = (((@PageIndex - 1) * @RowCount) + 1)
Set @TotalRows = @StartPosition + @RowCount
Set RowCount @TotalRows
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
select a.ProductId
from
products a,
categoryProducts b
where
b.categoryid = @CategoryID and
b.productid = a.productid
Order By a.productid
Open PagingCursor
Fetch Relative @StartPosition From PagingCursor Into @PK
while (@RowCount <> 0) And (@@Fetch_Status = 0)
begin
Insert Into @tmpTable (PK)
Values (@PK)
Fetch Next From PagingCursor Into @PK
Set @RowCount = @RowCount - 1
end
Close PagingCursor
Deallocate PagingCursor
Select Products.* From Products
Join @tmpTable temp ON Products.ProductID = temp.PK
Order By Products.ProductID
SELECT @PageCount = COUNT(*) / @RowCount FROM Products // value is never returned
Set RowCount 0
GO
Any thoughts?
JR
View 6 Replies
View Related
Aug 3, 2006
I have a scenario whereby I will have 200+ clients putting messages onto a service broker queue. This message will go through a pipes and filters based messaging system, and ultimately the message will pop out the other end.
Here's the question: what is a good way of making sure the same client gets the response to the message he received. Is there anyway I can selectively receive messages from a queue, i.e., pass a correlation id in with the message, and then filter messages based on that id.
Or if someone knows a better way to do it altogether i'd really appreciate it.
Many thanks,
Paul
View 9 Replies
View Related
Jul 12, 2006
Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.
The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.
Here is what I'm trying to do:
;RECEIVE TOP(1) @MsgBody = CAST(message_body as XML)
FROM ProcessingLetters
WHERE conversation_handle = @Conversation_Handle
It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.
I've even tried this:
where cast(Conversation_Handle as varchar(100)) = cast(@Conversation_Handle as varchar(100))
Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.
So is the WHERE conversation_handle = @Conversation_Handle supposed to work?
Thanks.
View 22 Replies
View Related
Oct 31, 2007
I am right now looking at the new functionality in SSIS to cover some of the ways we can streamline proceses in the company. I know that SSIS has SendMail tasks, however I was wondering if there is any functionality for going out and receiving mail and parsing those messages in? Would this have to be done as custom code within SSIS and if so can anyone direct me to what would be needed to do so? Thanks.
View 4 Replies
View Related
Jul 12, 2007
hi friends, i want to store an image in DB. but most of my friends told that, to store an image in web server then store tat location in a DB and access it anywhere. this is for asp.net in C#-code behind. how to do this? i've a table with a text field. then .......? waiting for ur reply............ note: i need coding. not by using controls. pls...
View 1 Replies
View Related
Aug 19, 2014
Is there a way for me to set up CDC so that all the processing (SQL Agent, etc) happens on the machine receiving the data? I'd like to move as much of the processing as possible to the destination.
View 3 Replies
View Related
Feb 20, 2008
I have just had SQL Server 2005 installed on my machine. I did have SQL Server 2000 on my machine previously and can still access parts of 2000.
In 2005 I do not have the option schema option under security for any of the databases; therefore, I tried creating the schema manually as you will see below.
In 2005 I execute the following
CREATE SCHEMA TEST
GO
The message returned is "COMMAND COMPLETED SUCCESSFULLY"
I, then, execute the following
CREATE TABLE TEST.SalesPeople
(
SalesPersonId INT,
SalesPersonName VARCHAR(50)
)
GO
The message returned is
Msg 2760, Level 16, State 1, Line 1
Specified owner name 'TEST' either does not exist or you do not have permission to use it.
Can you give me the correct syntax on how to create the schema manually? Do you know why this is not showing up under security for the databases?
Thanks in advance for your help.
Tara
View 15 Replies
View Related
Jun 28, 2006
If the service is defined with multiple contracts is there a way to receive a message with a specific contract?
View 5 Replies
View Related
May 2, 2006
I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.
I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.
Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.
Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?
Thanks,
Chris
View 5 Replies
View Related
Feb 23, 2005
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.
And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.
Thanks for help on this,
Peter
View 14 Replies
View Related
Nov 17, 2006
Hi All,
I was using VB6 to access a MS SQL Server database. The code worked and works fine. I then decided to migrate the code to C#.Net 2005 using ADO 2.8 (not ADO.Net). Doing that yields with the same exact code the error message, "Current Recordset does not support updating".
I did a whole bunch of Google searches and didn't see anything useful. Mainly the advice from Microsoft and others is to make sure the mode on the connection string is set to "ReadWrite", as the default is "Read Only" and to make sure to set the lock type to either optimistic or pessimistic. Still others said that the code should set the CursorLocation property of the recordset.
I can safely say that I have been setting the mode to "Read/Write" since the start and have played around with the lock type, cursor location, and open method. Nothing works on C#, BUT VB6 is so totally happy with everything.
The provider works fine, as VB6 works fine, and the lock type is also fine, so therefore the built in suggestions do not apply.
My code is:
// Connection string template. Filled in properly in real code.
strConnect = "Server={0};Database={1};"
// Set the connection properties.
this.SQLConnection.ConnectionString = strConnect;
this.SQLConnection.Provider = "SQLOLEDB";
this.SQLConnection.Mode = adModeReadWrite;
// Open the connection.
this.SQLConnection.Open(strConnect, strUserName, strPassword, -1);
=================
// Create the ADO objects needed.
dbRSAdd = new Recordset();
// Open the recordset.
dbRSAdd.Open(strTable, dbCatalog.ActiveConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, (int)CommandTypeEnum.adCmdTable);
// Cycle through each record to add.
dbRS.MoveFirst();
for (lRecord = 0; lRecord < dbRS.RecordCount; lRecord++)
{
// Add a new record.
dbRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
...
}
// NOTE: The code crashes with the call to 'AddNew'.
Any advice?
View 1 Replies
View Related
Jul 3, 2007
I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.
The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.
I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.
Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.
Thanks for any help!
John
View 6 Replies
View Related
Jan 6, 2006
While attempting to configure DTC on a Windows xp Pro, SP 2 machine I receive the error message [DTC was installed by the SQL Server. Please reinstall!] Where do I find this installation file to complete the requested task -- any help would be awesome -- I have tried down loading hotfixes, and security updates that mention fixing problems with DTC -- but alas they did not fix my error ;-)
View 1 Replies
View Related
Nov 2, 2006
I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )
When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.
But when I run the same app against the local machine SQL Server, I receive the message "Options".
Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.
View 7 Replies
View Related
Feb 1, 2012
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
View 5 Replies
View Related
Aug 27, 2015
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl]
@MtlTblMaterialType READONLY
AS
BEGIN
Set NOCOUNT ON;
[code]....
View 9 Replies
View Related
Jul 3, 2007
I keep receiving an update notification for KB 932557 and when I try to apply the fix, it fails. Checking update history tells me error code 0x2c24 is the reason for failure.
I cannot find any help on this error code anywhere.
Any help most appreciated.
View 16 Replies
View Related
Jan 10, 2008
I have the following query for my site which was functioning great using the OLEDB driver. Unfortunately, my client's web host forced us to use ODBC and it is no longer working Any suggestions for what I should do?
Here is the query:
SELECT t.*
FROM (SELECT TOP 10 itemnum, itemnum FROM (
SELECT TOP 100 itemnum, itemnum
FROM(CatView)
WHERE [CATALOG PAGE] LIKE 'SM%'
ORDER BY itemnum ASC) AS foo
ORDER BY itemnum DESC) AS bar INNER JOIN CatView AS t ON bar.itemnum = t.itemnum
ORDER BY bar.itemnum;
Here is the error i'm now receiving:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'itemnum' could refer to more than one table listed in the FROM clause of your SQL statement.
I know it was working great before, so i'm not sure what to do.
Michael
View 3 Replies
View Related
Oct 5, 2007
I've set up a Service Broker and it was working fine.
All of a sudden each time I switch on my computer it's not working anymore.
I have to drop the services, queues, contract and messages and create them again.
Then it works again until I shutdown my computer. When I start it again it's not working.
Any suggestion on how to fix this?
Thank you
View 8 Replies
View Related
May 15, 2006
good morning,
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
Best regards.
View 1 Replies
View Related
