SOLUTION! - VB.NET 2005 Express And SQL Server 2005 - NOT Saving Updates To DB - SOLUTION!
Nov 30, 2006
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.
The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks,
Barry G. Sumpter
Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express
Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.
Hi,i want to create a solution which shall consist of a sql server 2005 -data base (express edition if the following features are available byexpress edition) and a mobile windows application (running on asmartphone under windows mobile 5.0).The sql server shall be installed on a ordinary laptop using windowsxp (not professionall edition).Which would be the best way to exchange the data between mobile deviceand pc? I learned that there is a SQL Server Compact Edition for themobile device and that rda would be a way to exchange data - but theni would need internet information system and this does not run on xphome edition.Which alternatives would you suggest me?Thanx in advanceWolfgang
I use Widows Vista Business, have uninstalled all previous versions of SQL before installing SQL 2005 Express.
At first, I faced "Receiving WMI configuration error. SQL Server System Configuration cannot be executed due to to WMI configuration on this computer Error:2147749896 (0x80041008)."
Then, I tried this cmd someone posted as:
FIXWMI.CMD ------------------------
@echo on cd /d c: emp if not exist %windir%system32wbem goto TryInstall cd /d %windir%system32wbem net stop winmgmt winmgmt /kill if exist Rep_bak rd Rep_bak /s /q rename Repository Rep_bak for %%i in (*.dll) do RegSvr32 -s %%i for %%i in (*.exe) do call :FixSrv %%i for %%i in (*.mof,*.mfl) do Mofcomp %%i net start winmgmt goto End
:FixSrv if /I (%1) == (wbemcntl.exe) goto SkipSrv if /I (%1) == (wbemtest.exe) goto SkipSrv if /I (%1) == (mofcomp.exe) goto SkipSrv %1 /RegServer
kipSrv goto End
:TryInstall if not exist wmicore.exe goto End wmicore /s net start winmgmt :End
I tried to install SQL2005 Express again, but then it gives:
Error 1603 installing Microsoft SQL Native Client.
I upgraded my SQL 2005 Workgroup 2005 to SP2 and subsequently could not get the SQL Server service running. Turns out this was a problem because I had created separate folders for my data and log files. The distmdl.mdf and mssqlsystemresource.mdf files were in my data folder and distmdl.ldf and mssqlsystemresource.ldf were in my log folder. During SP2 upgrade a new distmdl.ldf and mssqlsystemresource.ldf were created in my data folder. I deleted the distmdl.ldf and mssqlsystemresource.ldf files in my log folder and then moved the newly created ones in my data folder into the log folder and all is well now.
We have a huge terabyte database that we are planning to upgrade to 2005. We are working on realistic backup solutions in case we have to rollback the upgrade. We have over 400 tables out of which about 100 are kind of static. The other 300 tables have very high transaction rate - thousands of batch transactions per sec. Earlier, prior to 2005 SP2 release we tried setting up replication to a 2000 box and replication agents failed to catch up with the transactions. Not sure of SP2 has any improvements. We are thinking of setting up replication for only the 300 or so tables and we will take a regular nightly back up and can use a day's old data for the 100 small tables.
What are your experiences/suggestions on a proper back up solution to have a 2000 server in almost stand by mode that we can switch back to in case of any issues?
Is it possible to run solution i have created by MS Visual Studio (filling table from file). I was thinking about schedueling job and launching the solution periodically from stored procedure. Anyone has idea how these things work?
I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.
example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345
the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000
In SQL Management Studio Express, is there any wany to save the "solution" consisting of the set of queries that I have open at a point in time?
I generally have 10 views I switch back and forth between to monitor a bunch of data load processes, and it would be helpful to be able to return to the same setup next time I login.
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @@version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
2- Create/access "local"/"Single-User" files for processing purposes. (try to minimize network trafic...)
So far, I've read a lot about SQL Server products (SQL Server, Sql Server Express, SQL Server CE), at least all articles I could find, and would like to have your opinion on what would be the best approach?
1- What SQL version to use for "multi-user" databases?
2- What SQL version to use for "single-user" databases/files?
3- Can SQL Express allow me to achieve both "multi-user" and "single-user" approaches within same app? If not, what would you rather suggest?
I am a Visual Studio .net developer and understand Solutions and Projects in VS.net. Nevertheless, it appears that the paradigm of using Solutions Explorer in SQL Server is not exactly the same. That is, in Visual Studio you can't get anything done without creating a solution first. In SQL Server, it is entirely possible to get a lot done without a solution and the ability to create and make use of a solution seems almost an after thought or an add-on. I am particularly interested in using a solution in SQL Server to allow me to make use of SourceSafe. In the past, we jumped through all kinds of hoops to keep a running trail of changes we had made to (for example) stored procedures. It appears now that this can be managed more efficiently with the use of a Solution and SourceSafe and saving scripts or .sql files. I guess it is not possible to save a pristine copy of a stored procedure directly in SourceSafe, but you can save the code that makes up the stored procedure as a .SQL file which I suppose is about as good. Anyway, what I would like to know is whether there is a good white paper (or chapter in a book) on the philosophy behind Solutions and projects in SQL Server and how MS intends us to use them; perhaps something other than the cold facts from the documentation. I would appreciate something more that gives me the big picture on just how MS sees them being used in SQL Server so I can get the most out of them. I have the feeling that if I "get the vision", they are meant to be an integral part of SQL Server now and in the future. Woody
I understand that to implement SQL Server clustering solution, it isnecessary to have Win 2K Advanced server. We are looking at 2Nodefailover solution on a 2CPU Pentium box. Is there a site where wecan find out the cost of Win2K and SQL server for this configuration.Thanks.Ravi
I made B server which get logs from primary server A as a secondary server in the log shipping solution. it always shows RESTORING in B server, it seems not to accessible.
my question is <if A failed down , how to revoke the B server as the primary one>
help with solution for use order BY inside function and VIEW on the sql server i explain the problem i am a web developer in asp page i BUY an DATA grid object for my web site the problem in this object i can use only ONE field to use ORDER BY can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ? so whan i do SELECT * FROM dbo.tb_pivot_big
the sql server ORDER for ME the result and run this (inside the sql server )
Code Snippet SELECT * FROM dbo.tb_pivot_big ORDER BY new_unit, Fname ASC, val_orginal desc
SQLSourceSafe 2.0 is the right version control solution for SQL Serverdatabase development projects. SQLSourceSafe 2.0 Integrates with bothSQL Server and Visual SourceSafe, providing an effective andeffortless version control management system for individuals andproject teams to manage database objects.By using SQLSourceSafe 2.0, you can manage SQL Server database objectssuch as tables, stored procedures, user defined functions, views,triggers, indexes/keys and constraints as you manage regular files andprojects in SourceSafe. SQLSourceSafe 2.0 enables your to archive andlabel database object changes in SourceSafe, so that you can comparedifferent versions, recover from an undesired write-over of objects,or roll back to a previous release. Advanced users can also useSQLSourceSafe 2.0 to find out the differences between SQL Serverdatabases or deploy the latest or any labeled version SQL Serverdatabase archived in SourceSafe to different computers.For more information, please visit: http://www.bestsofttool.com.
I'm trying to learn some VB programming with the VB 2005 Express Absolute Beginner Series video tutorials (which I think is great) and have come across a problem that I can't solve.
When I follow the instructions in Lesson 9 (Databinding Data to User Interface Controls) my application will display the data from the database correctly and I can edit it (and as long as the debugger is running the data remains changed). However, the changes won't propagate back to the database. I don't get any error messages but after I edit the data, save (with the save button on the BindingNavigator toolbar), and end debugging the data in my database remains unchanged. When I use a MessageBox to show how many rows where edited/updated in the
I get the correct number back. I'm sure the problem is not due to coding errors since I've also tried running the accompanying Lesson 9 project file that can be downloaded from MSDN and the problem persists.
I'm using Windows XP SP2, SQL Server 2005 Express Edition and VB 2005 Express Edition. I've tried installing SQL Server 2005 Express with a number of different settings, including default settings, but it doesn't make any difference.
Would greatly appreciate any feedback on this as I'm keen to resolve this problem so I can get on with the next tutorial lesson.
HiIm trying to connect Visual Basic Express 2005 to a remote SQL Server Express 2005. I cant find how i can do that in VB.net Express.In Web developer there are no problem to connect to a remote SQL server but i cant find it in VB.net Express. The XP with the SQL server that i want to connect to is on the local network. Greatful for help!
Hi Guys, I created a Product database table using Visual Basic 2005 Express and SQL Server 2005 Express. I have just added a new column [Picture] to the database table, which of course, should store an image or picture of a product. I am writing to kindly ask you guys for help .
i) How do I include image files into this column [Picture]? ii) How do I get this image to display on Visual Basic 2005 Express form, so that when a product is selected the product image is displayed accordingly?
If i have been using sql server 2005 express for developing my application and i decide to upgrade to sql server 2005 express with advanced services while still working on the same application, what will happen to my application's database. Can i be able to continue with my work with out any major regrets.
How can I save UTF-8 strings in SQL Server 2005? I don't want to use nvarchar as it would take a lot of space and most of my text will be in latin characters (and it doesn't fully support all unicode code points anyway as it uses UCS2, I'm talking about the supplementary plane here which I know is very rarely used and most unicode fonts don't support it but it's still possible to be used at least in the future). I read in this KB article (http://support.microsoft.com/kb/232580) that my only option is to use varbinary, I'm worried though whether this could affect performance as I never used varbinary before .. I read in the same article that I cannot use varchar to store UTF-8 which I can't really understand, doesn't varchar use the whole 256 values to save character data according to the code page used? what makes UTF-8 characters not valid for being stored as varchar strings? (what I mean here is that I can do the conversion manually from varchar to UTF-8 but I only want to save the data in the varchar columns) Any suggestions are highly appreciated..
This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.
I am using SQL 2005, when I run
select * into #import1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webjeff2.xls', 'select * from [jeff2$]')
I get
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
when I try to compile a SP with that statement in it, I get the same error, like
create stored procedure test
as begin
select * into #import1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webjeff2.xls', 'select * from [jeff2$]')
end
so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?
On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.
I have the same problem at two places, both use SQL 2005.
So far there are three questions
1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.
2, why it gives error in compile stage?
3, why two dbs in different Enviroment has the same problem
The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.
I'm using MS SQLEXPRESS 2005. Everyting worked fine: I could connect to it from Access and VS 2005. After updates (SQLEXPRESS SP2) I can't connect to it. Error:
Connection failed:
SQLState: 01000
SQL Server error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect))
Connection failed:
SQLState: `08001
SQL Server error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exists or access denied
When one process has one record locked in Update-Mode then an other process can't update any other records on that table with some queries, other queries that access the same records (but with a different WHERE statement) will execute. This problem occurs with SQL Server 2005, but it didn't with SQL Server 2000 (or any other database).
The problem: -Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it. (Using OLEDB Pessimistic Cursor-locking) -Process 2 wants to update an other record, buts gets a "Lock timeout" when using one query but not with another.
For example this query will work : UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;
But this qeury will give me a "Lock timeout" : UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';
But it is the same record!! The record with name = 'REC_2' has the row_id = 110, both values are unique in the table.
The data: The table [gwseqnumber] has the following CREATE statement: CREATE TABLE GWSEQNUMBER ( NEXTSEQNR INTEGER, NAME CHAR (20), ADMINISTRATIONCODE INTEGER, FINDHIGHESTNUMBER CHAR (1), CLOSEDYN CHAR (1), ROW_ID INT IDENTITY(1,1) NOT NULL );
CREATE INDEX KEY_1 ON GWSEQNUMBER (NEXTSEQNR); CREATE UNIQUE KEY_2 ON GWSEQNUMBER (ADMINISTRATIONCODE, NAME, IDENTIFIER); CREATE UNIQUE INDEX KEY_3 ON GWSEQNUMBER (ROW_ID);
Both KEY_2 and KEY_3 are unique, KEY_1 is not.
If I remove the index on the NEXTSEQNR column (the index named KEY_1) then both these queries will work, so it is obviously related to the index. Altough the index is obsolete and can be removed from this table, it should not result in bogus locking errors.
Even when i removed the index not all the queries will work. (With work i mean not run into a locking error, while the record is not even locked.)
Solutions tried: - Set the compaitiblity level of the databsae back to 80 (for SQL Server 2000 compatibility). - I have already tried to disable Page-Locking on all the indexes of this table.
So why does the index lock my updates in SQL Server 2005? And how do i fix it so my database does not run into these locks?
Hi, I want to make a component library in Visual Basic.NET and connect to a remote Sql Server. When I create a new DataSet with the wizard, I can only connect to a .mdf file, but not to a Sql Server. With Visual Web Developer I can connect to a Sql server. What is the difference between these enviroments ? How can I do the same with Visual Basic.NET ? Thanks in advance.
Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.
Hi folks, guidance required! Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved. Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.
Hello All, I have a transaction table that holds nearly a million records and my master with 60000 records,I want to fetch details based on two dates.
I use Union operator to query the info from a view,is this right or fetch the result into a temporary table and fetch the result from temporary table using the same Union operator.
Which is the better way to fetch details faster.
Nirene
My SP is pasted below just advice me to execute this more efficiently,cos this SP is called thru a Web application.
CREATE PROCEDURE GL @SDt Varchar(10),@EDt Varchar(10),@Loccode Char(5),@OP Char(4) AS
IF EXISTS (Select 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Gltmp') Begin Drop Table #Gltmp End
Select @Cocode=Cocode from Location Where Loccode=@Loccode Select @CashGL=Glcode,@Gldesc=Gldesc from Glmast Where Cocode=@Cocode and Subgroupcode='CASH'
Set @OpBalQry='Select A.Glcode,'''' as Trtype,''' + @SDt + ''' as Refdt,'' Opening Balance'' as Refno, Max(B.Gldesc) as Narration,Case When (Sum(A.Deb)-Sum(A.Cre))<0 Then (-1*(Sum(A.Deb)-Sum(A.Cre))) Else 0 End as Dr, Case When (Sum(A.Deb)-Sum(A.Cre))>=0 Then (Sum(A.Deb)-Sum(A.Cre)) Else 0 End as Cr from ( Select Glcode, (Case When Drcrflag=''D'' Then Opbal Else 0 End) as Deb, (Case When Drcrflag=''C'' Then Opbal Else 0 End) as Cre from Obdata Union Select Glcode, (Case When Drcrflag=''D'' Then Tramt Else 0 End) as Deb, (Case When Drcrflag=''C'' Then Tramt Else 0 End) as Cre from Trans_Journal Where Refdt <Convert(Datetime,''' + @SDt + ''',103) ) A,Glmast B Where B.Cocode=''' + @Cocode + ''' and A.Glcode=B.Glcode Group By A.Glcode Union ' Set @TrnQry='SELECT T.Glcode,T.Trtype,Convert(Varchar,Max(T.Refdt),103) as Refdt , '''' AS Refno, (Rtrim(Ltrim(Max(G.Gldesc))) + '' -- From Sub-Ledger'') AS Narration, SUM(CASE WHEN T.Drcrflag = ''D'' THEN T.tramt ELSE 0 END) AS Dr, SUM(CASE WHEN T.Drcrflag = ''C'' THEN T.tramt ELSE 0 END) AS Cr FROM Trans_Journal T,Glmast G WHERE T.Glcode=G.Glcode and G.Cocode=''' + @Cocode + ''' and (T.Slcode IS NOT NULL AND Len(Rtrim(Ltrim(T.Slcode)))>0) and (T.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and T.Refdt <=Convert(Datetime,''' + @EDt + ''',103) and T.Loccode= ''' + @Loccode + ''' and (G.Subgroupcode<>''BANK'' or G.Subgroupcode<>''CASH'')) GROUP BY T.Glcode,T.Trtype UNION SELECT A.Glcode,'''' as Trtype,Convert(Varchar,A.Refdt,103) as Refdt, A.Refno, B.Gldesc AS Narration, CASE WHEN A.Drcrflag = ''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag = ''C'' THEN A.Tramt ELSE 0 END AS Cr FROM Trans_Journal, Glmast B WHERE (A.Glcode = B.Glcode and B.Cocode=''' + @Cocode + ''') and (A.Slcode IS NULL OR Len(Rtrim(Ltrim(A.Slcode)))=0) and (A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''',103)) and A.Loccode=''' + @Loccode + ''' and (B.Subgroupcode<>''BANK'' or B.Subgroupcode<>''CASH'') Union Select Glcode,Trtype,Convert(Varchar,Max(Refdt),103) as Refdt,'' '' as Refno,Max(Narration) as Narration,Sum(Dr) as Dr,Sum(Cr) as Cr from (Select A.Glcode,A.Trtype,A.Refdt,B.Gldesc as Narration, CASE WHEN A.Drcrflag =''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag =''C'' THEN A.Tramt ELSE 0 END AS Cr from Trans_Journal,Glmast B WHERE (A.Glcode=B.Glcode and B.Cocode=''' + @Cocode + ''' and A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''' ,103) and A.Loccode=''' + @Loccode + ''') and (B.Subgroupcode=''BANK'' or B.Subgroupcode=''CASH'') Union Select ''' + @CashGL + ''' as Glcode,Trtype,Refdt,''' + @Gldesc + ''' as Narration, CASE WHEN Drcrflag =''C'' THEN Tramt ELSE 0 END AS Dr, CASE WHEN Drcrflag =''D'' THEN Tramt ELSE 0 END AS Cr from Trans_Transnarr WHERE (Trtype=''CPM'' or Trtype=''CRT'') and Loccode=''' + @Loccode + ''' and Refdt>=Convert(Datetime,''' + @SDt + ''' ,103) and Refdt<=Convert(Datetime,''' + @EDt + ''',103)) TT Group By Glcode,Trtype) X'
Set @MainSQry='Select Glcode,Trtype,Refdt,Refno,Narration,Dr,Cr Into #Gltmp from ('
Declare @Fullqry NVarchar(4000)
If @OP='WOB' Begin Set @TrnQry = @OpBalQry+@TrnQry End
Set @FullQry = @MainSQry+@TrnQry
Exec sp_executesql @FullQry
Select Glcode,Trtype,Refdt,Refno,Narration,Ltrim(Rtrim(Str(Dr,14,2))) as Dr,Ltrim(Rtrim(Str(Cr,14,2))) as Cr from #Gltmp SQL2XML Where Dr+Cr<>0 Order By Glcode,Trtype,Refdt,Refno for XML AUTO
i want to get following output: id_order | type | number ------------------------- 1234 | A | 1 1235 |A | 0 1235 |B | 0 1236 |B | 1 1237 |C | 0 1237 |D | 0
create table tbl_order ( id_order int ,type nvarchar(40) )
insert into tbl_order (id_order, type) values (1234, 'A' ) insert into tbl_order (id_order, type) values (1235, 'A' ) insert into tbl_order (id_order, type) values (1235, 'B' ) insert into tbl_order (id_order, type) values (1236, 'B' ) insert into tbl_order (id_order, type) values (1237, 'C' ) insert into tbl_order (id_order, type) values (1237, 'D' ) insert into tbl_order (id_order, type) values (1238, 'A' ) insert into tbl_order (id_order, type) values (1239, 'D' ) insert into tbl_order (id_order, type) values (1239, 'B' ) insert into tbl_order (id_order, type) values (1239, 'A' )
select id_order ,type --,isnull(orderX,'') as number ,case when orderX > 1 then 1 else 0 end as number2 from tbl_order left join (select t2.id_order as orderX from tbl_order as t2 where (select count(t1.id_order) from tbl_order as t1 where t1.id_order = t2.id_order) = 1 )as x on tbl_order.id_order = x.orderX
Is there any better/faster select sentance to do this? i'm using sql2000.
Hi, i am not sure if i can solve the problem with SSIS. I wanted do do it hardcoded with C# or so.
So, for my project I need to download zip-files on a daily-base. In these zip-files are xml-Files. And in these files is information stored for inserting or updating tables on a MS-SQL-Server.
So, my question, is it possible to solve that with SSIS? And if, is it easy to understand and to learn within a few days?
I haven't found good sources for information on how to get the data out of XML-Files.
I've got a .dts which load data to several Sql2k servers by Copy SQL Server Objects Task. I'd like to add another task for a Sql25k but I can't because of it's requesting SMO.
It seems that such task need DMO. I know that there are other ways for do such stuff in that DTS but I wonder if there is any trick or shortcut for to avoid this barrier.
