I have a problem: CREATE NONCLUSTERED INDEX [IDX_table_column] ON [dbo].[table1] ( [column1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO When I perform this script on SQL Server 10.50.2500, there is no problem but […]

Bulk Insert using format file for varying number of columns between datafile and actual database table

This is the Schema for a table Create table dbo.Project ( ProjectID (int,not null) ManagerID (int,not null) CompanyID(int, not null) Title (nvarchar(50),not null) StartDate(datetime,not null) EndDate(datetime,null) ProjDescription(nvarchar(max)) ) I created a datafile called bob.dat from this table which has around 15 rows with the following bcp command bcp “Select ProjectID,ManagerID,CompanyID,Title,StartDate from CATS.dbo.Project” queryout “C:\Documents\bob.dat” -Sbob-pc […]

I'm using SUBSTRING to isolate the text “AUG” from a flat file record. How can I convert that to “08”?

I am using SSIS to create a data flow that imports a flat file into a database table. One of the transformations that needs to be done is to convert the date in the flat file which is formatted like “30AUG2013” to a datetime datatype format that can be written into the table. I believe […]

Read from files and parse SQL table

All the following must be done in C#. Parsing the SQL table (SQL Server) will be done using methods in System.Data.Odbc. Let’s assume I have two .csv files, fi1 and fi2. The first csv file has two columns id and val1, and the second csv has two columns as well, id and val2. I would […]

Error when trying to establish a MVC connection to SQL Server 2012 (localdb) running on my PC

I have set up my web.config like this: <add name=”DefaultConnection” connectionString=”Data Source=’C:\Users\Marilou\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0\authentication.mdf’;Initial Catalog=javacert;Persist Security Info=True;User ID=sa;Password=yyy” providerName=”System.Data.SqlClient” /> But I am getting the message: Message=The provider did not return a ProviderManifestToken string. Source=EntityFramework StackTrace: at System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) at System.Data.Entity.Utilities.DbProviderServicesExtensions.GetProviderManifestTokenChecked(DbProviderServices providerServices, DbConnection connection) InnerException: System.Data.SqlClient.SqlException HResult=-2146232060 Message=A network-related or instance-specific error occurred […]

Parsing non-standard XML in SQL Server with XTbl

I have a serialised piece of data in a column that I want to retrieve a single value from, in the following form: <FirstNode>Something</><SecondNode>Something Else</> I want to retrieve, for example, ‘Something’ from this in SQL Server. I’ve tried the following: declare @data xml; set @data = cast([my data source] as xml); select @data.value(‘(/UserName)[1]’,’varchar(50)’) I’m […]

Code inside SQL Cursor will only execute first FETCH

I have a SQL Cursor which I’m having issues with. When I remove the IF @debug = 1 statement from inside the cursor, only the first record from the FETCH will get updated but if I leave the IF @debug = 1 all the required records are updated. Any idea as to why this is […]

Update a table column with Value based on the same table fied

I have the below table Structure. Order_Table Ord_ID Ord_Date Del_Date Del_Qty Del_Time Hits Miss 10001 9/13/2013 12:30 9/13/2013 13:10 2 40 2 0 10002 9/13/2013 13:09 9/13/2013 15:10 1 119 0 1 10003 9/13/2013 16:20 9/13/2013 16:50 1 30 1 0 I want to update the table Del_Time based on (Ord_Date – Del_Date), Hits should […]

Migrate SQL Server DB to MongoDB

I want to Migrate all the features and functions of SQL Server DB to Mongo DB, But I am stuck with Foreign Key Concept, I know there is no Concept ForeignKey in NoSQL Dbs, But I want to connect and Store the Tables as Shown Below, Please share your thoughts and Ideas how to achieve […]

Adding aliasing to field names in Pivot SQL query

I have a query below, and need to have field [Cmp-Goal-RF-148] (which is pivoted to be a column) – I need the column title to be something besides [Cmp-Goal-RF-148], so I suppose I need to alias it. Doing this throws up an error: ([Cmp-Goal-RF-148] AS ‘Ghost’). What am I missing? select * from ( select […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.