|
thanks for help but I decided to take all the data to a DataTable and then sort it in the C# part of the project, the hard way =)
thanks again
|
|
|
|
|
Simple question I know but I just cannot find a anything that works.
INSERT INTO [Location] ([id],[TZOffset],[Address],[Tel],[Fax],[Contact1],[Contact2],[Contact3],[Company],[Deleted],[Disabled]) VALUES (8,null,N'line1
line2
',N'',N'',1,2,3,null,null,null);
GO
I have tried everything I can find but there is no ESCAPE or CHAR function in SQLCE that I can find. You cannot use Stored Procedures, being CE so it has to be done in a command space.
If I store the CRLF in the raw data and push it using MFC then it works fine.
Any ideas?
Great isn't it. I can use SIN, COT, SQRT but I cannot put CRLF into a string!!?!??!?!
Alan
|
|
|
|
|
Me thinks your data structure is screwed, whenever I seelsomething like contact1, contact2, contact3 I know you are in deep poop. What if there is a fourth contact, woops lets add another column - FAIL
Create another table callemd contacts and a foreign key to this company table.
What has CRLF got to do with your insert statement?
There seems to be a number od problems with your insert statement. There is no comma between line1 and line2. Do comapny, deleted and disabled accept null as valid data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well you would be right if that is the way the fields were being used but they are not. There will only be three contacts as these fields hold pointers to entries in a Contact table.
I want to use a single ADDRESS field to store a multiline address rather than have 5 separate fields (one for each line).
There is no comma between line1 & line2 becuase they are stored as "line1\r\nline2 " in the application internals.
Now I can do that everywhere else (CString, Registry etc...) so why is it so unreasonable to want the same thing in SQL CE?
Alan
|
|
|
|
|
Not being a user of CE, and unwilling to install it in case it destabilises something, I can't test his. Have you tried forcing a linefeed manually by inserting chr(10). Then try chr(13) + chr(10), forcing a carriage return and line feed. Seems like your transport layer may be messing with the \r
Thanks for the explanation (one should stick to the Lounge when one returns home pissed) but I still think field1-3 is an incorrect design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok things are getting even wierder than I thought they were to start with.
I have done some testing with my own code and I have observed that INSERT preserves the 0x000D,0x000A when written to the DB but then if I use UPDATE on the same field with the same data I get '?' stored in the DB?!?!?!?
Now this smells to me of a bug in the OLEDB... Anway it looks like I am going to have to restructure my code and DB to provide separate fields for each address line. I then have to glue them together when I retrieve them and split them apart when writing.
You are still getting hung up on the Contact1/2/3. This is simple the variable names I use within the code so I can easily find all the code dealing with the contacts. In presentation they are Primary, Secondary and Reserve. Simples.
Alan
|
|
|
|
|
Instead of multiple fields for the address you might look into storing it as XML data or varchar using XML to structure the address.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear experts,
Which version ODP.net is exactly required for oracle 11.2 and .net 2.0?
|
|
|
|
|
|
Hi,
Let me start off by giving some background on the events that have lead up to this issue that I'm having. I started with VS2008 and SQLEXPRESS. A few months later, I installed VS2010 and converted my project. A week or so later, I decided that I wanted to install SQL Server 2008 R2, so I installed that as well, not uninstalling SQLEXPRESS. My application has been running great and the reports were working fine for the past several months. I then rebuilt my entire application. Now I can not get my application to compile and I've been spinning my wheels in the mud for a week.
What I am seeing is the message in VS2010 that states: The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded.
Not being a SQL Expert from an Admin side I have read a lot of forums which basically said that I needed to get the Report Server running and grant the roles accordingly. The Report Server now works and I can create reports using Report Server R3 no problem, however I'm still getting the error when trying to compile.
In the ReportViewer Tasks in VS2010, where you can select the report, I'm using a local report, not a server report. IE: ApplicationName.Reportname.rdlc
I am guessing the issue is the NameSpace is for a Server report and not a local report, and I should have some type of ConnectString? I really don't know what the problem is.
Any help is greatly appreciated.
*** UPDATE *** - I still have the issue with a brand new application built with VS2010 using the same table stored in SQL Server 2008 R2. Am I restricted to using Server Based reports after installing SQL Server 2008 R2?
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
<DataSources>
<DataSource Name="KTReunionDataSet">
<ConnectionProperties>
<DataProvider>System.Data.DataSet</DataProvider>
<ConnectString>/* Local Connection */</ConnectString>
</ConnectionProperties>
<rd:DataSourceID>0a8b6f23-4772-446f-9b27-5f180b760bc4</rd:DataSourceID>
</DataSource>
</DataSources>
Glenn
modified on Thursday, September 16, 2010 11:13 PM
|
|
|
|
|
hello,
i am usin sql server 2008.I have database which has been hosted on server i want to know whether they are given full access permission or not. bcoz when i work with local database it works fine. when i connect my server it will not works. like when i right click >> show top 1000 rows menu item will be displayed.it will not in when i connect to server. even i can't view tables listed in database.
|
|
|
|
|
You need to contact the hosting provider to ascertain whether or not you can have full access - although this sounds like the server instance is not properly set up to accept remote connections to the SQL instance.
|
|
|
|
|
Hello
Kindly tell me is it necessary to use
SET XACT_ABORT ON
in sql transaction or not?
Regards
|
|
|
|
|
try doing some reading [^] before jumping in. The second one seems to be interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Basically, XACT_ABORT is a hint to SQL Server about the behaviour of a transaction if an error occurs. If XACT_ABORT is set to ON then the transaction will be rolled back. Now, why did I say it's a hint? The answer is because if XACT_ABORT is set to OFF, there's no guarantee that the transaction will roll back - it may or may not (depending on circumstances) choose to just roll back the statement that caused the error, allowing other parts to complete - or the entire transaction may be rolled back.
|
|
|
|
|
|
Here is code
<br />
SqlConnection connection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename="D:\Working Directory\Win Application\Nakoda\Development\Bin\Database\cERP.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True");<br />
<br />
ServerConnection srvConn = new ServerConnection(connection);<br />
srvSql = new Server(srvConn);<br />
<br />
if (openBackupDialog.ShowDialog() == DialogResult.OK)<br />
{<br />
BackupDeviceItem bdi = default(BackupDeviceItem);<br />
bdi = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);<br />
<br />
BackupDeviceItem bdid = default(BackupDeviceItem);<br />
bdid = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);<br />
<br />
string db = cmbDatabase.SelectedItem.ToString();<br />
<br />
Restore rs = default(Restore);<br />
rs = new Restore();<br />
rs.NoRecovery = true;<br />
rs.Devices.Add(bdi);<br />
rs.Database = db;<br />
rs.SqlRestore(srvSql);<br />
<br />
<br />
I Got Exception
Microsoft.SqlServer.Management.Smo.FailedOperationException occurred
HelpLink=http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
Message=Restore failed for Server '\\.\pipe\1FF7F0B1-18D8-42\tsql\query'.
Source=Microsoft.SqlServer.SmoExtended
Operation=Restore
StackTrace:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at DatabaseBackup.SQL_Backup_Restore.btnRestore_Click(Object sender, EventArgs e) in D:\Working Directory\Win Application\Nakoda\Development\Source\DatabaseBackup\MSSQLBackupRestore.cs:line 186
InnerException: Microsoft.SqlServer.Management.Common.ExecutionFailureException
Message=An exception occurred while executing a Transact-SQL statement or batch.
Source=Microsoft.SqlServer.ConnectionInfo
StackTrace:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
InnerException: System.Data.SqlClient.SqlException
Message=RESTORE cannot process database 'D:\WORKING DIRECTORY\WIN APPLICATION\NAKODA\DEVELOPMENT\BIN\DATABASE\CERP.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation.
RESTORE DATABASE is terminating abnormally.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=3102
Procedure=""
Server=\\.\pipe\1FF7F0B1-18D8-42\tsql\query
State=1
StackTrace:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
InnerException:
|
|
|
|
|
kirankkk2009 wrote: Message=RESTORE cannot process database 'D:\WORKING DIRECTORY\WIN APPLICATION\NAKODA\DEVELOPMENT\BIN\DATABASE\CERP.MDF' because it is in use by this session. It is recommended that the master database be used when performing this operation.
So you are connected to the database you want to restore - fail.
Do what the exception advises you to, connect to the master database and restore your database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
After 1000 this will reset counter for specific term to 1 again. Will this stored procedure be 100% reliable to give unique id every time. There are multiple users accessing the web application that may call this stored procedure.
ALTER PROCEDURE [dbo].[get_next_id](@Term int,@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator] where Term = @Term;
IF @Current<1000
BEGIN
update IdGenerator SET @ID = ID, ID = ID + 1 where Term=@Term
END
ELSE
BEGIN
update IdGenerator SET @ID =1,ID = 2 where Term=@Term
END
One option is to use identity column but that is out of question as to generate unique Id I have to add certain text to each number as that is the part of business requirement. Like add 1001 or 1002 or 1003 etc. in front of numbers. This stored procedure will give me the unique number.
Criticize it / Suggest any alternative .
|
|
|
|
|
Criticise - not a problem this is a dumb idea.
Record identifiers are just that they identify the record, the business has no input into how they are created, maintained of contain.
On the other hand a code used by the users to identify a record is completely up to the business and they can ask for any rules they like
Why are you resetting after 1000 and how can that be unique?
Suggest - also not a problem
Have 2 fields, recordID which is an identity field and record code which store the business defined identifier.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft has given you some good advice.
In terms of your original question, yes this will be reliable if you serialize the transactions.
Incidentally, this will reset before 1000, not after. I assume that is what you want, so you are generating your business numbers in the range 1001... to 1999...
|
|
|
|
|
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code.
For example the original table would look something like this :-
OrderId
ProductCode
Quantity
with data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200
Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
----------------------------
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0
So far so good.
Now, the problem is that I need to attach this to the result of a view which contains other related details.
I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an Id column of type BigInt (which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of type Decimal .
Any pointers, gratefully received.
Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.
|
|
|
|
|
Hi,
You could try inserting the stored Proc's results into a temp table and joining your view from there.
|
|
|
|
|
Q1: Is the "other related data" related to the Order ID or to the Products ?
Q2: I know I could get alot of grief for this but, What about a Temp table ?
Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data.
I could probably offer a better answer if Q1 was answered.
Regards,
David
|
|
|
|
|
David Mujica wrote: Is the "other related data" related to the Order ID or to the Products
The OrderId is the common key.
David Mujica wrote: Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data
Can I define a temp table without knowing the column names or the number of columns?
My other thought was to create a .NET Extension and have the produce the data resultset required and return a datatable. (never done it before, but I am will to try.)
Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.
|
|
|
|
|