Dropdown list from different table CakePHP

I am working on a form that will allow the user to edit a certain order. I would like to be able to make a drop down list of Sales Agents that are already stored in a different table.

The foreign key is UserID (Alias for User table is SalesAgent).

This is in my view for the edit_sales_agent action: In my controller I have this:

echo $this->Form->create("Order");
echo $this->Form->input("UserID");

I thought that a drop down list should automatically be created when I do this, but a text area is created instead.

Does anyone know what to do?

Thanks!

via Chebli Mohamed

SQL query to get the total machine running hours between two dates but split into 3 time zones: standard time, peak time and off-peak time

SQL query to get the total machine running hours between two dates but split into 3 time zones: standard time, peak time and off-peak time.

Context
Programming environment: Wonderware ArchestrA
Programming language: ArchestrA Quick Script .Net
Database: Historian - SQL Server (InSQL)
External: A couple of pumps in the mining industry, need to know the pump usage during 3 different power tarif times (peak, standard, off-peak times).
Standard Time: 10:00 to 17:00 AND 20:00 to 22:00
Peak Time: 6:00 to 10:00 AND 17:00 to 20:00
Off-Peak Time: 22:00 to 06:00

I need
Between two dates:

• Total Hours pump ran during Peak Time
• Total Hours pump ran during Off-Peak Time Total Hours pump ran during Standard Time

What I've tried, it works sometimes.

-- this script only gets the total peak time hours
SET NOCOUNT ON  

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  
DECLARE @var1 REAL  
DECLARE @Date24 DateTime  

SET @StartDate = '2015/07/01 05:00:00 AM'  
SET @EndDate = GetDate()  
SET NOCOUNT OFF  

-- Get the Date and time where the running hours is running hours minus 24
SET @Date24 = (SELECT TOP 1 [DateTime] 
               FROM  
                   (SELECT 
                        History.TagName, DateTime, Value, StartDateTime  
                    FROM 
                        History  
                    WHERE 
                        History.TagName IN ('me.a0_MainPump.RunningHours.Tagname.FA_PV')  
                        AND Value IS NOT NULL  
                        AND Value = " + Text(me.a0_MainPump.RunningHours.FA_PV - 24, "#") 
                        AND wwVersion = 'Latest'  
                        AND DateTime >= @StartDate) temp)  

-- Get the first portion of the peak time hours  
SET @var1 =  
(  
SELECT  'Count' = Count(DiscreteHistory.Value)/60.0  
FROM DiscreteHistory  
WHERE DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
AND DiscreteHistory.Value = 1  
AND wwRetrievalMode = 'Cyclic'  
AND wwResolution = 60000  
AND DateTime >= @Date24  
AND (CAST(DateTime as time) >= '06:00:00' AND CAST(DateTime as time) < '10:00:00')  
GROUP BY DiscreteHistory.TagName  
)  -- Get the second portion of the peak time hours
SELECT 'Count' = (Count(DiscreteHistory.Value)/60.0 )+@var1  
FROM DiscreteHistory  
WHERE DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
AND DiscreteHistory.Value = 1  
AND wwRetrievalMode = 'Cyclic'  
AND wwResolution = 60000  
AND DateTime >= @Date24  
AND (CAST(DateTime as time) >= '17:00:00' AND CAST(DateTime as time) <= '20:00:00')  

Thank you.

Sample Data

I log the following information into the database:

A unique Tag Name for Run Feedback: KDCE_S04_22PMP01_Machine.FA_RF This is a run feedback which is a "1" or "0" or "null"

A unique Tag Name for Machine running hours: me.a0_MainPump.RunningHours.FA_PV which is an integer value of the amount of pump running hours.

Both tag names gets logged with TagName, Value, DateTime, quality, etc.

I have a table that include the following columns:

| DateTime | TagName | Value | QualityDetail |   

This works:

SET NOCOUNT ON  

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  

SET @StartDate = '20150701 05:00:00.000'  
SET @EndDate = '20150731 05:00:00.000'  
SET NOCOUNT OFF  

SELECT 
    DateTime, TagName, Value, Quality  
FROM 
    DiscreteHistory  
WHERE 
    DiscreteHistory.TagName IN ('KDCE_S04_22PMP01_Machine.FA_RF')  
    AND DateTime >= @StartDate AND DateTime <= @EndDate  

It returns this output if I export to csv:

DateTime,TagName,Value,Quality
2015/07/01 05:00:00 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,133
2015/07/01 05:09:46 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 05:09:53 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 06:44:20 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 06:45:54 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 07:36:22 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 07:36:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 01:53:44 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 01:53:44 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:04:52 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 02:05:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:07:25 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/01 02:09:13 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/01 02:14:54 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 12:10:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 05:24:06 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 05:24:16 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 05:50:52 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 05:50:59 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/02 06:00:15 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 06:55:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 06:55:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 09:46:58 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 09:46:58 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 01:30:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/02 01:30:27 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 05:38:03 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/02 07:01:56 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 03:41:09 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/03 09:05:18 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 10:42:00 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/03 10:57:31 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/03 04:53:36 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/04 10:08:17 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/05 06:43:50 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/05 09:43:08 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/05 01:04:03 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/06 09:37:53 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/06 11:07:15 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/06 11:29:48 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/06 05:02:38 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 06:15:33 AM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/07 06:32:24 AM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 09:05:20 AM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 01:10:09 PM,KDCE_S04_22PMP01_Machine.FA_RF,(null),1
2015/07/07 01:10:16 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 04:45:12 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0
2015/07/07 08:19:40 PM,KDCE_S04_22PMP01_Machine.FA_RF,1,0
2015/07/07 09:01:35 PM,KDCE_S04_22PMP01_Machine.FA_RF,0,0

via Chebli Mohamed

Sql Order by Student Grade No

I want to order my data,i have following data.

Below is my Data

• 1
• 1+
• 1-
• 2
• 2+
• 2-
• 3
• 3+
• 3-
• 4
• 4+
• 4-

I want to order Like this

• 1+
• 1
• 1-
• 2+
• 2
• 2-
• 3+
• 3
• 3-
• 4+
• 4
• 4-

I tried this

select * from RatingGrade where ScoreCardID=1   order by GradeNo

via Chebli Mohamed

How to GroupBy a part of DateTime using System.Dynamic.LINQ library

I have tables that contain a lot of rows, each record is excellent by whom and on what date was added. I want to group all records, based on AddedBy and AddedOn fields.

The problem is that the dates are in full including the miliseconds and I only want to group by day, month or year.

I hereby noted that at Compilation time I do not know which table it is and Therefore I use with System.Dynamic.LINQ library.

Below I demonstrate how did a grouping by datetime using System.Dynamic.LINQ library:

Dim groupColl= myColl.GroupBy("new(AddedName, AddedOn), it").Select("new(Key.AddedName, Key.AddedOn, Count() AS Count)")

But the problem is that I need to grouping by day, month or year.

In sql server I found how to do it, by day or by month or by year. In Lambda Expression also found the way how to do it, but only by day.

But through System.Dynamic.LINQ not find any way how to do it.

Below I demonstrate how I'm doing this in sql server and Lambda Expression:

Using SQL SERVER:

SELECT AddedBy, DATEADD(DAY, DATEDIFF(DAY, 0, AddedOn), 0) as AddedOn, count(*)
FROM myTable
GROUP BY AddedBy, DATEADD(DAY, DATEDIFF(DAY, 0, AddedOn), 0)

Using Lambda Expression vb.net code (Imports)

Dim groupColl = myCollection.GroupBy(Function(x) New With {Key x.AddedBy, Key .AddedeOn_Day = DbFunctions.TruncateTime(x.AddedOn)}).Select(Function(y) New With {y.Key.AddedBy, y.Key.AddedeOn_Day, y.Count})

I would be grateful to anyone who would help me how to do it using System.Dynamic.LINQ Library

Thanks

via Chebli Mohamed

SQL amount of pages bigger than it should be

I have a DB in SQL Express 2012 and there is one particular table which I am struggling to understand. Said table is has a CI and the following properties:

CREATE TABLE [dbo].[Hourly_Flows](
    [S_ID] [smallint] NOT NULL,
    [FromID] [tinyint] NOT NULL,
    [ToID] [tinyint] NOT NULL,
    [Hour8760] [smallint] NOT NULL,
    [FromTo] [smallint] NULL,
    [ToFrom] [smallint] NULL,
 CONSTRAINT [PK__oHF_Hour__60BEC9D565570293] PRIMARY KEY CLUSTERED 
(
    [S_ID] ASC,
    [FromID] ASC,
    [ToID] ASC,
    [Hour8760] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I created a new empty DB, with the exact same table and inserted all the records into it, the size of the new and old table is below:

+--------------+--------------+--------------+
|    Table     |     Old      |     New      |
+--------------+--------------+--------------+
| RowCounts    | 109,911,720  | 109,911,720  |
| TotalPages   | 436,323      | 259,137      |
| UsedPages    | 436,270      | 259,098      |
| DataPages    | 434,434      | 258,009      |
| TotalSpaceMB | 3,408        | 2,024        |
| UsedSpaceMB  | 3,408        | 2,024        |
| DataSpaceMB  | 3,394        | 2,015        |
+--------------+--------------+--------------+

As you can see the amount of pages and size is much smaller in the new table. Before inserting the data in the original table I tried truncated the table, dropped and recreated, cleantable and always get the same results.

Not sure what I am missing here, I have the same results if I re-insert the data into the original table. The only other issue could be that the DB is growing over the 10GB size limit of SQL Express DBs, but not sure why this could make a difference.

The table used to have more columns which were deleted (hence we truncated it), we do this often but this is the first time it does not reduce its size.

via Chebli Mohamed

Trying to complete simple SQL query involving a case and a table join

I have an SQL Select query that I am using to return a column of Ids that matched from one table to another and a column of Ids that didn't match. There are a couple other columns that are in the query as well, but they don't matter as much here. There is another column (not currently in the query) in table2 called StatusId. I would like the Ids to only show up in the matched column if the StatusId = 3. Otherwise, I would like them to show up in the Unmatched Column. Sounds super simple, but I can't figure out how to work the WHERE statement in.

Query:

Select  t2.Id as MatchedId,
case when t2.Id is null
then t1.Id end as UnmatchedId
, t2.Number,t1.Date
from Table1 t1 
left join Table2 t2 
on t2.Id = t1.Id;

via Chebli Mohamed

First record in SQL Cursor missing a variable

I am sending a newsletter in SQL server using a cursor. All is working fine except the first email has no html.

Here is the stored procedure:.....

DECLARE
  @html varchar(max)
  SET @html = (SELECT html from NewsLetter where nLID=@nLID)
    DECLARE crsEmailList CURSOR FOR
    SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
  OPEN crsEmailList
  FETCH NEXT FROM crsEmailList INTO @email, @ListID
    while @@FETCH_STATUS = 0 
    BEGIN
    DECLARE @UniqueKey varchar(20),
    @UnSubscribeURL varchar(200),
    @ClickURL varchar(200)
    SET @UnSubscribeURL='<a href=''http://.../userfiles/OHP/UnSubscribe.aspx?listID=' + convert(varchar, @ListID) + '''>Unsubscribe</a>'
    SET @ClickURL='<a href=''http://.../userfiles/OHP/clicked.aspx?Key=' + convert(varchar, @UniqueKey ) + '&URL='
    EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
    SET @html = (SELECT html from NewsLetter where nLID=@nLID)
    SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
    SET @html = Replace(@html,'<a href=\''',@ClickURL)
    SET @html = Replace(@html,'[UnSubscribe]', @UnSubscribeURL )
    SET @html = Replace(@html,'[date]', DATENAME(month, getdate()) )
          EXEC msdb.dbo.sp_send_dbmail 
       @profile_Name ='Local Server',
       @recipients= @email ,
       @subject = @Subject,
       @body = @html,
       @body_format='HTML'
    FETCH NEXT FROM crsEmailList INTO @email, @ListID
    END

I have tried moving the line SET @html = (SELECT html from NewsLetter where nLID=@nLID) to different locations but no positive results.

via Chebli Mohamed

How to pass tSQL CONCAT string result to GridView BoundField Column?

I'm trying to get a SQL string result from CONCAT to a GridView Column. Is there a way to grab fullName for a column DataField?

Every answer I can find on the internet says to use a template field in asp.net. What if I want to use a stored procedure?

Perhaps I don't know correct terminology, but there is no answer to this on stackoverflow.

SELECT 
    CASE
       WHEN IsNull(Table.firstName, '') = ''
         THEN CONCAT(Table.lastName, ', ', Table.firstName) 
         ELSE Table.lastName
    END as **fullName**, 
    Table.website

via Chebli Mohamed

Column "columnName" does not belong to table "tableName"

I have a .NET console application deployed to an Azure VM. The utility is pretty simple, it just looks at a table and sends emails. However, we randomly get errors on this line:

foreach (DataRow bcemail in BCEmails.Tables[0].Rows)
{
email.Subject = bcemail["subject"].ToString(); 
//other stuff
}

The error we received says: column 'subject' does not belong to table Table. I have checked that the stored proc always returns only 1 table and always has "subject" as a column. Keep in mind it works more than half the times, giving error only randomly. It works totally fine in my local environment. The Azure VM also has several other apps. Researching this problem, I found this link: http://ift.tt/1K2hgIF Which talks about corrupted database connection pool and recommends that you make sure the connection is closed properly. I have done this throughout the application. The link also mentions that we use 'iisreset' every time we stop the IIS server. So I tried doing an IISReset and running the app again and it works fine. If I do an IISReset every time before it's scheduled to run, it works fine.

My questions are this: 1. This is a console app, not a web app, in fact there are no web sites configured on IIS on this VM. So why does IISReset work? 2. Can anyone recommend any other approach to solve this problem other than to do IISReset every time?

via Chebli Mohamed

Sqlalchemy connection creates two entries in the failed login information log for SQL Server

I'm trying to establish a SQL Server connection to a SQL server database using SQLAlchemy, but when I enter bogus credentials, the SQL Server log (which I view using xp_readerrorlog) say there are two failed login attempts within a few milliseconds. Why are there two entries and how do I make each connection attempt log only one entry?

Thanks!

Python code:

import sys
from sqlalchemy import create_engine

username = "test3"
password = "12345"
db = "@servername/dbname?driver=SQL+Server+Native+Client+11.0"
connection_string = "mssql://domain\\" + username + \
        ":" + password + db
#
test_engine = create_engine(connection_string)
test_connection = test_engine.connect()

SQL Server log:

LogDate ProcessInfo Text
2015-08-04 00:00:18.670 spid26s This instance of SQL Server has been using a process ID of 4800 since 8/3/2015 4:00:36 AM (local) 8/3/2015 9:00:36 AM (UTC). This is an informational message only; no user action is required.
2015-08-04 12:26:12.590 Logon   Error: 18456, Severity: 14, State: 5.
2015-08-04 12:26:12.590 Logon   Login failed for user 'ENT\test3'. Reason: Could not find a login matching the name provided. [CLIENT: 10.122.162.41]
2015-08-04 12:26:13.000 Logon   Error: 18456, Severity: 14, State: 5.
2015-08-04 12:26:13.000 Logon   Login failed for user 'ENT\test3'. Reason: Could not find a login matching the name provided. [CLIENT: 10.122.162.41]

via Chebli Mohamed

Connect to SQL database when the application and database are on the same Azure VM

I have an ASP.NET MVC 5 application published on a Virtual Machine on Azure. I also have a database on the same Virtual Machine.

Application works fine, however it cannot connect to the database.

I have these information from Azure:

• Domain Name = "something.cloudapp.net"
• Host Name = "sampleName"
• Virtual IP Address = "137.135.123.149"
• Private IP Address = "10.0.0.4"

This is my connection string:

<add name="RepositoryDbConnectionString"
 connectionString="Data Source=137.135.123.149,1433; Database=Codex-Repo-Cloud; User Id=myUser; Password=mypass; MultipleActiveResultSets=True;"
 providerName="System.Data.SqlClient" />

I also have tried these connection strings, none worked so far:

connectionString="Data Source=something.cloudapp.net; Database=Codex-Repo-Cloud; User Id=myUser; Password=mypass; MultipleActiveResultSets=True;"

connectionString="Data Source=10.0.0.4; Database=Codex-Repo-Cloud; User Id=myUser; Password=mypass; MultipleActiveResultSets=True;"

NOTE With the first connection string, I can remotely connect to the database in Visual Studio Server Explorer and I can actually modify the data.

Question When the SQL Database and the Web application are both on the same Azure Virtual Machine, how can I connect to the database within my application. Is there any settings, or is it just my connection string?

via Chebli Mohamed

Insert into multiple tables

A brief explanation on the relevant domain part:

A Category is composed of four data:

1. Gender (Male/Female)
2. Age Division (Mighty Mite to Master)
3. Belt Color (White to Black)
4. Weight Division (Rooster to Heavy)

So, Male Adult Black Rooster forms one category. Some combinations may not exist, such as mighty mite black belt.

An Athlete fights Athletes of the same Category, and if he classifies, he fights Athletes of different Weight Divisions (but of the same Gender, Age and Belt).

To the modeling. I have a Category table, already populated with all combinations that exists in the domain.

CREATE TABLE Category (
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [AgeDivision_Id] [int] NULL,
  [Gender] [int] NULL,
  [BeltColor] [int] NULL,
  [WeightDivision] [int] NULL
)

A CategorySet and a CategorySet_Category, which forms a many to many relationship with Category.

CREATE TABLE CategorySet (
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Championship_Id] [int] NOT NULL,
)

CREATE TABLE CategorySet_Category (
  [CategorySet_Id] [int] NOT NULL,
  [Category_Id] [int] NOT NULL
)

Given the following result set:

| Options_Id | Championship_Id | AgeDivision_Id | BeltColor | Gender | WeightDivision |
|------------|-----------------|----------------|-----------|--------|----------------|
| 2963       | 422             | 15             | 7         | 0      | 0              |
| 2963       | 422             | 15             | 7         | 0      | 1              |
| 2963       | 422             | 15             | 7         | 0      | 2              |
| 2963       | 422             | 15             | 7         | 0      | 3              |
| 2964       | 422             | 15             | 8         | 0      | 0              |
| 2964       | 422             | 15             | 8         | 0      | 1              |
| 2964       | 422             | 15             | 8         | 0      | 2              |
| 2964       | 422             | 15             | 8         | 0      | 3              |

There are 8 rows in this result set.

I need CategorySet and CategorySet_Category to be populated in two different ways (it can be two queries).

One Category_Set for each row, with one CategorySet_Category pointing to the corresponding Category.

One Category_Set that groups all WeightDivisions in one CategorySet in the same AgeDivision_Id, BeltColor, Gender. In this example, only BeltColor varies.

So the final result would have a total of 10 CategorySet rows:

| Id | Championship_Id | 
|----|-----------------|
| 1  | 422             |
| 2  | 422             | 
| 3  | 422             |
| 4  | 422             | 
| 5  | 422             | 
| 6  | 422             |
| 7  | 422             |
| 8  | 422             |
| 9  | 422             |  /* groups different Weight Division for BeltColor 7 */
| 10 | 422             |  /* groups different Weight Division for BeltColor 8 */

And CategorySet_Category would have 16 rows:

| CategorySet_Id | Category_Id |
|----------------|-------------|
| 1              | 1           |
| 2              | 2           |
| 3              | 3           |
| 4              | 4           |
| 5              | 5           |
| 6              | 6           |
| 7              | 7           |
| 8              | 8           |
| 9              | 1           | /* groups different Weight Division for BeltColor 7 */
| 9              | 2           | /* groups different Weight Division for BeltColor 7 */
| 9              | 3           | /* groups different Weight Division for BeltColor 7 */
| 9              | 4           | /* groups different Weight Division for BeltColor 7 */
| 10             | 5           | /* groups different Weight Division for BeltColor 8 */
| 10             | 6           | /* groups different Weight Division for BeltColor 8 */
| 10             | 7           | /* groups different Weight Division for BeltColor 8 */
| 10             | 8           | /* groups different Weight Division for BeltColor 8 */

I have no idea how to insert into CategorySet, grab it's generated Id, then use it to insert into CategorySet_Category

I hope I've made my intentions clear.

I've also created a SQLFiddle.

Edit 1: I commented in Jacek's answer that this would run only once, but this is false. It will run a couple of times a week. I have the option to run as SQL Command from C# or a stored procedure. Performance is not crucial.

Edit 2: Jacek suggested using SCOPE_IDENTITY to return the Id. Problem is, SCOPE_IDENTITY returns only the last inserted Id, and I insert more than one row in CategorySet.

Edit 3: Answer to @FutbolFan who asked how the FakeResultSet is retrieved.

It is a table CategoriesOption (Id, Price_Id, MaxAthletesByTeam)

And tables CategoriesOptionBeltColor, CategoriesOptionAgeDivision, CategoriesOptionWeightDivison, CategoriesOptionGender. Those four tables are basically the same (CategoriesOption_Id, FooBar_Id).

The query look like this:

SELECT * FROM CategoriesOption co
LEFT JOIN CategoriesOptionAgeDivision ON 
    CategoriesOptionAgeDivision.CategoriesOption_Id = co.Id
LEFT JOIN CategoriesOptionBeltColor ON 
    CategoriesOptionBeltColor.CategoriesOption_Id = co.Id
LEFT JOIN CategoriesOptionGender ON 
    CategoriesOptionGender.CategoriesOption_Id = co.Id
LEFT JOIN CategoriesOptionWeightDivision ON 
    CategoriesOptionWeightDivision.CategoriesOption_Id = co.Id

via Chebli Mohamed

Creating a table with a list of ids or objects

This is another basic question, I've been trying to find the answer to for days. I only come here with these types of questions, when I'm researching through the same resources over and over and cannot find a solution.

If I want for instance a cart, so it has multiple items or item ids (ints) within the one instance.

Do I just keep adding item after item using the same id?

I am confused and just can't get my head around it. I am sure there's information out there, but I have failed to dig it up.

Is there a simple way to explain the concept of how this is done?

I happen to be creating a project in VS using Sql Server, but really the principles remain the same, I'm guessing.

Any help is appreciated.

via Chebli Mohamed

MSSQL "String or binary data would be truncated." Error on Update

I have a MSSQL table users:

CREATE TABLE users (
  ID int IDENTITY(1,1) NOT NULL,
  firstname nvarchar(20) NOT NULL,
  lastname nvarchar(20) NOT NULL,
  dir bit NOT NULL,
  cc nvarchar(15),
  readyacc bit NOT NULL,
  region nvarchar(50),
  org nvarchar(50),
  suborg nvarchar(50),
  section nvarchar(50),
  title nvarchar(50),
  floor tinyint,
  wkstn nvarchar(50),
  fc nvarchar(15)
);

And I'm trying to update an existing entry with the prepared query:

UPDATE users SET ? = ? WHERE ID=?;

With my parameters as:

Array ( [0] => title [1] => Teleco [2] => 1 )

But it seems as though if the string length is greater than 5 it gives me the error "String or binary data would be truncated.". Eg, Telec works but Teleco does not. When I try the same query in the SQL Management Studio it gives me no errors.

Am I just missing something obvious? Please help

via Chebli Mohamed

sql select min or max based on condition

Hi all I'm trying to find a way to select min or max from a range of data based on conditions. If setuptime and processtime cols are all O select MIN(oprNum) (operation hasn't started yet so get first oprnum) if setuptime and process time are not 0, get max oprnum (active operation). based on either of these I want ONE row... please see attached example of data. Thanks! This is part of a much larger query so i need 1 output row per prodid...

+------------+--------+---------+--------------------+--------------------+
| ProdId     | OprNum | Company | SetupTime          | ProcessTime        |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 10     | 12      | 1.3400000000000000 | 1.6100000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 10     | 12      | 0.0000000000000000 | 0.0000000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 15     | 12      | 1.0000000000000000 | 0.0000000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 50     | 12      | 0.0000000000000000 | 0.0000000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 60     | 12      | 0.0000000000000000 | 0.0000000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 60     | 12      | 0.0000000000000000 | 0.0000000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 70     | 12      | 0.0700000000000000 | 0.0400000000000000 |
+------------+--------+---------+--------------------+--------------------+
| 12M0003381 | 70     | 12      | 0.0000000000000000 | 0.0000000000000000 |
+------------+--------+---------+--------------------+--------------------+

via Chebli Mohamed

Java GSS/JDBC Issue

I have a bit of an unusual issue I'm trying to solve. I develop on a Mac. I'm writing some code that connects to a database using jdbc. I don't have direct access to the db server - to get to it, I have to set up port forwarding on ssh, which goes to a proxy server in the network where the db server resides. The server I proxy into is a Linux server, and the db is MS Sql Server on Windows. Once I've setup the portforwarding, I can connect to the database using a DB browsing tool like SquirrelSQL. When I try to connect to the database using the jdbc code I've written, using the exact same JDBC driver, I get this:

Exception in thread "main" java.sql.SQLException: I/O Error: GSS Failed: Invalid name provided (Mechanism level: Cannot locate default realm)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:654)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at sql.generator.SQLGenerator.main(SQLGenerator.java:80)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Caused by: java.io.IOException: GSS Failed: Invalid name provided (Mechanism level: Cannot locate default realm)
    at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1976)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:617)
    ... 10 more

Research on Google indicates that it may be a Kerberos issue, which I haven't had to work with before, but I'm not sure that's right. What is the db browser doing that I need to duplicate in the application?

TIA, Alex

via Chebli Mohamed

Select sub query in function

I have to create an inline function (stored procedure not allowed, as it cannot be accessed by end users, multistatement function not allowed because of performance issues). I am struck up in a piece of code.

The code goes like this.

ALTER FUNCTION [CDC].[FN_CBR_COPERNICUS_DBO_AGMASTER_ANY] (@START_LSN BINARY(10), @END_LSN BINARY(10),@APPLNID SMALLINT)
RETURNS TABLE
AS
RETURN(
SELECT
  (
  SELECT  
       STUFF((SELECT ', ' + CBR.CBRCA_COLUMNNANME
         FROM CBRCOLAPPLN CBR 
       WHERE CBR.CBRCA_APPLICATIONID = 1 AND CBR.CBRCA_TABLENAME = 'COPERNICUS_CORE_PARAMETER'
         FOR XML PATH(''), TYPE)
        .VALUE('.','NVARCHAR(MAX)'),1,2,' ') CHANGED_COLUMNS) ,__$OPERATION AS CBR_OPERATION  
              FROM CBR.FN_CBR_GET_NET_CHANGES_ES_CMS_DBO_COPERNICUS_CORE_PARAMETER(0X0009631C000016F90029, 0X0009631D000008720001, 'ALL WITH MASK') AS CBRUPDATEDCOLUMNS)

The subquery select will return values , if I execute the subquery alone, it returns the below

CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN

When I place the values of subquery inside the function and place the code as

Select  CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN,__$operation as CBR_OPERATION  
              FROM CBR.FN_CBR_GET_NET_CHANGES_ES_CMS_DBO_COPERNICUS_CORE_PARAMETER (0x0009631c000016f90029, 0x0009631d000008720001, 'all with mask') AS CBRUPDATEDCOLUMNS

Upon executing it , I get the desired results,from the function CBR.FN_CBR_GET_NET_CHANGES_ES_CMS_DBO_COPERNICUS_CORE_PARAMETER

Whereas if I execute the first set of code (the one written in function), placed in the begining of the question, it just results me the value of the subquery * number of rows in the function . It just gives me the result as below, along with the some ,__$OPERATION value

CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN
CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN
CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN
CMS_ID, CMS_PARA_NAME, CMS_PARA_TYPE, CMS_PARA_MODULE, CMS_PARA_VALUE, CMS_COMMENTS, CMS_ACTIVE_YN

Could you please help me where am I going wrong?

Regards.

via Chebli Mohamed

String Comparison in LINQ to SQL result null

I have two string array and want to see if there are any intersection or not. I am using linq to sql in c# and fetch the database field and compare it with the user input as below :

string[] Materials = material.Split('-');
                    AllItems = (from item in AllItems
                                where item.Material.Split(',').Intersect(Materials).Count() != 0
                                select item).ToList();

Materials are user input string which has - delimiter and in database I have string with , delimiter.

I wonder why the result is always null !!! I mean the query result says there is no intersection but I check and there is !!!

another alternative query that I used and take the same result was this :

string[] Materials = material.Split('-');
                    HashSet<string> stringSet = new HashSet<string>(Materials);
                    AllItems = (from item in AllItems
                                where item.Color.Split(',').Where(c => stringSet.Contains(c)).Count() != 0
                                select item).ToList();

I am so confused whats the problem with these quries !!! I should mention that the strings are UTF8 one and contains 2 bytes character in persian language, I guess maybe It's the problem but I don't know how to solve it.

Is there any solutions? Thanks

via Chebli Mohamed

A better way of concatenating thousands of rows than using case when [duplicate]

This question already has an answer here:

• How to make a query with group_concat in sql server 4 answers

I have a table with thousands of rows (unique at ID-code level) that is constructed like:

ID           Code            Description
1             123               A
1             789               B
2             789               B
2             123               A
2             a1b2              C
3             101               D
4             102               F

I need it to look like:

ID    Description
1        A,B
2        B,A,C
3        D
4        F

Currently I am using mulitple case when conditions that look like:

max(Case when Code='123' then (description)+',' else '')+
max(Case when Code='789' then (description)+',' else '')+

etc.

Group by ID

Is there a faster way of doing this? I have been advised that using a stuff function may be viable, but honestly I can't see how a stuff function would save me any lines of code

via Chebli Mohamed

Incorrect syntax near 'Name'

I getting errors:

Incorrect syntax near 'nvarchar'.
Incorrect syntax near 'Name'.

Please help to get from this.

I also added scalar to the names (@) but I am not getting anything.

public partial class Form1 : Form
{
    SqlCommand cmd;
    SqlConnection con;

    private void button1_Click(object sender, EventArgs e)
    {
        con = new SqlConnection(@"Data Source=DELL_LAPTOP\sqlexpress;Integrated Security=True");
        con.Open();

        cmd = new SqlCommand("Insert Into newproj (Name,Designation,Gender,Age,Address,Date,Staff Name,Shift,ST,ET,Hours) Values (@Name,@Designation,@Gender,@Age,@Address,@Date,@Staff Name,@Shift,@ST,@ET,@Hours)", con);
        cmd.Parameters.Add("@Name", textBox4.Text);
        cmd.Parameters.Add("@Designation", textBox2.Text);
        cmd.Parameters.Add("@Gender", comboBox1.SelectedItem.ToString ());
        cmd.Parameters.Add("@Age", textBox3.Text);
        cmd.Parameters.Add("@Address", textBox5.Text);
        cmd.Parameters.Add("@Date", dateTimePicker1.Text);
        cmd.Parameters.Add ("@Staff Name", textBox1.Text);
        cmd.Parameters.Add ("@Shift", comboBox2.SelectedItem.ToString());
        cmd.Parameters.Add("@ST", textBox7.Text);
        cmd.Parameters.Add("@ET", textBox8.Text);
        cmd.Parameters.Add("@Hours", textBox6.Text);

        cmd.ExecuteNonQuery();       
    }
}

via Chebli Mohamed

Sum result of SELECT...WHERE in SQL Server

Can someone tell me what I'm doing wrong, and if I can get the expect result... (Keep in mind this is a VIEW)

SELECT
      [Id]
    , [Nome]
    , [Estado]
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc = 'A1.' OR EstProc = 'A2.') AND LI.LogImpFiles_Id = LIF.Id) AS ItemsProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE EstProc = 'A3.' AND LI.LogImpFiles_Id = LIF.Id) AS ItemsErroProcessamento
    , (SELECT COUNT(EstProc) FROM LoginsImp AS LI WHERE (EstProc= 'A4' OR EstProc= 'A5') AND LI.LogImpFiles_Id= LIF.Id) AS ItemSucessoProcessamento
    , SUM(ItemsErroProcessamento + ItemSucessoProcessamento) AS [ItemsProcessados]
    , [CreatedOn]
    , [CreatedBy]
FROM
    [dbo].[LogImpFiles] AS LIF
GROUP BY 
    [Id], Nome, Estado, CreatedOn, CreatedBy

The result is this:

1   TesteImport1        6   2   3   0   2015-08-04 15:41:41.5130000 110032797

I was expecting something like this:

1   TesteImport1        6   2   3   **5**   2015-08-04 15:41:41.5130000 110032797

via Chebli Mohamed

Stored Procedures and asp.net programmability; variable or SQL

Trying to display a users Lastname, Firstname --- Website And I need to insert a comma and space after Lastname to a GridView. I am trying to add a CASE statement in SQL and having trouble figuring it out.

Perhaps I need to use @parameter (scalar variable?) to abstract the memory read from CASE statement; or my syntax is wrong and I just don't understand.

SELECT 
CASE
          WHEN IsNull(people_Table.firstName, '') = ''
          THEN CONCAT(people_Table.lastName, ', ',
          people_Table.firstName) 
          ELSE people_Table.lastName
          END as fullName, people_Table.website
FROM people_Table INNER JOIN membership_Table on people_Table.ID =
membership_Table.personID
WHERE rectype = 'Master'
AND membershipType = 'Business'
AND expirationDate > GetDate()
ORDER BY people_Table.lastName

Edit: Getting SqlServer error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'people_Table'.

Otherwise I suppose I should use an asp databoundevent in the template. What is better for performance and security?

via Chebli Mohamed

Passing One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Procedure 1:

EXEC Parse
@Part = '0123,4567'
@Qty = '1,1';

returns the following:

Part        Qty
0123         1
4567         1

This procedure simply takes a part and quantity input and parses the strings at each instance of ",".

Procedure 2:

EXEC PA
@Part = '0123'
@Qty = '1';

returns the following:

Top-Level Assembly     TotalQty      MaterialPart     Qty
      0123                1             12A            2
      0123                1             13A           21
      0123                1             14A            5

My overall goal is to have a user enter an assembly part or list of assembly parts (delimited by a comma) and their appropriate quantities. The first procedure creates a result list of all the assembly parts. The second procedure should run off of the result set from the first procedure to get all of the pieces that make up the assembly part.

How can I run my second procedure based off of the result of the first procedure? Any help is greatly appreciated!!

via Chebli Mohamed

How to show all database objects that operate on a given object

Is there a command to show all the jobs and functions and stored procedures etc. in a database where operations involving a specific table are executed? For example, all the jobs that INSERT into Tablex, all the jobs that CREATE Tablex, and any other operations on that table.

Thank you!

via Chebli Mohamed

Stored Procedure in cursor firing only once

I am getting an output from a stored procedure but in my cursor it only returns the first value.

SP 1

ALTER PROCEDURE [dbo].[register_system_email_audits]
    @UserId int,
    @EmailFor varchar(500),
    @DateSent datetime,
    @UniqueKey varchar(20) output
AS
BEGIN
    INSERT INTO [SystemEmailsAudit]
           ([UserId]
           ,[EmailFor]
           ,[DateSent]
           ,[UniqueKey]
           )
     VALUES
           (@UserId 
           ,@EmailFor
           ,@DateSent
           ,(SELECT CAST( CAST(RAND() * 100000000 AS int) as varchar(20)))
           );
     SELECT @UniqueKey=s.UniqueKey FROM [SystemEmailsAudit] s 
        WHERE s.RecordId=SCOPE_IDENTITY();

END

SP2

ALTER PROCEDURE [SendNewsletterMails]
(
    @nLID int,
    @Category VARCHAR(50)
)
as
DECLARE
  @html varchar(max),
  @Description VARCHAR(100),
  @Subject varchar(50),
  @Email varchar(100),
  @listID   int,
  @DLC smalldatetime,
  @Date DATETIME = NULL
    set @html = (SELECT html from NewsLetter where nLID=@nLID)
    DECLARE crsEmailList CURSOR FOR
    SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
  OPEN crsEmailList
  FETCH NEXT FROM crsEmailList INTO @email, @ListID
    while @@FETCH_STATUS = 0 BEGIN
    --Add Beacon
    DECLARE @UniqueKey varchar(20)
    EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
    SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
    EXEC msdb.dbo.sp_send_dbmail 
      @profile_Name ='Local Server',
       @recipients= @email ,
       @subject = @Subject,
       @body = @html,
       @body_format='HTML'
    FETCH NEXT FROM crsEmailList INTO @email, @ListID
    END
  CLOSE crsEmailList
  DEALLOCATE crsEmailList
GO

The stored procedure returns the proper @UniqueKey but only for the first record in the cursor. I have been contemplating a while loop or a temp table but settled on the cursor route for now.

via Chebli Mohamed

Which SQL Query is the site running?

The websites (intranet sites or extranet sites - sometimes web portals) at my company return certain results (which is obtained via SQL queries/commands in the back-end systems). I"m trying to find out which queries are being run in the background and how I could track back the query results onto the tables where they come from. How can I achieve that? I tried looking at the "source" but found no queries there. Back-end uses SQL Server if that matters.

via Chebli Mohamed

SQL Server Query Aid

I have a query in SQL Server to return a list of Reports, it has to return either a string representing a location, or a string representing the store it's referencing.

The issue is my query is only returning reports that references a store id, instead of returning all reports and the relevant location information. I'm convinced its a stupid syntax issue, but I haven't done database work for a while, and can't seem to pick it out. I've tried several different ways to get this to work, but it simply refuses.

SELECT rep.rep_id AS "RepId", ISNULL(rep.rep_status, 'C') AS "RepStatus", ISNULL((loc.location_street + ' ' + loc.location_city), store.Description) AS "Location", rep.date_reported AS "DateReported", rep.reported_by AS "ReportedBy"
FROM Report rep JOIN Report_Location reploc ON reploc.rep_id = rep.rep_id
JOIN Location loc ON loc.location_id = reploc.location_id
LEFT JOIN Store store ON store.StoreID = loc.store_id;

I've tried removing the left join and just adding a where loc.store_id = store.StoreID or loc.store_id IS NULL. Neither worked. Thanks in advance for your help.

via Chebli Mohamed

delete millions records using partition tables?

We write daily about 1 million records into a sql server table. Records has a insertdate and status fields, among others of course. I need to delete records from time to time to free space on the volume but leaving the last 4 days records there. The problem is the deletion takes hours and lots of resources.

I have think about partition tables setting the partition field on the insertdate, but I never used that kind of tables.

How can I archieve the goal using the less cpu/disk resources and having the solution the less drawbacks possible? (I assume any solution has its own drawbacks, but please explain them if you know).

Thank you in advance

via Chebli Mohamed

SQL Lookup table & Entity Framework 6

I have 2 tables in a SQL database 'tbl_Job' and 'tbl_JobType', I added a FK relationship to tbl_Job pointing to tbl_JobType but when I reverse engineer using Entoty Framework 6 code first it reads as

 this.HasRequired(t => t.JobType)
     .WithMany(t => t.Jobs)
     .HasForeignKey(d => d.JobTypeId);

And it has completely through me, I appreciate SQL doesn't understand 1 to 1 relationships and neither does EF6 but i wasn't expecting the foreign key relationship to create a navigation property pointing in the wrong direction.

Does anyone have a suggestion on what the best way to use a lookup table in SQL with a relationship or constraint with an example?

Thanks in advance.

via Chebli Mohamed

Using TRY / CATCH to perform INSERT / UPDATE

I have this pattern in a number of stored procedures

-- Table1
[id] [int] IDENTITY(1,1) NOT NULL
[data] [varchar](512) NULL
[count] INT NULL

-- 'data' is unique, with a unique index on 'data' in 'Table1'
BEGIN TRY 
    INSERT INTO Table1 (data, count) SELECT @data,1;
END TRY
BEGIN CATCH
    UPDATE Table1 SET count = count + 1 WHERE data = @data;
END CATCH

I've been slammed before for using this pattern

You should never have exception "catching" in your normal logic flow. (Thus why it is called an "exception"..it should be exceptional (rare). Put a exists check around your INSERT. "if not exists (select null from Data where data = @data) begin /* insert here */ END

However, I can't see a way around it in this instance. Consider the following alternative approaches.

INSERT INTO Table1 (data,count) 
SELECT @data,1 WHERE NOT EXISTS 
    (SELECT 1 FROM Table1 WHERE data = @data)

If I do this, it means every insert is unique, but I can't 'catch' an update condition.

DECLARE @id INT;  
SET @id = (SELECT id FROM Table1 WHERE data = @data)

IF(@id IS NULL)
    INSERT INTO Table1 (data, count) SELECT @data,1;
ELSE 
    UPDATE Table1 SET count = count + 1 WHERE data = @data;

If I do this, I have a race condition between the check and the insert, so I could have duplicates inserted.

BEGIN TRANSACTION
   DECLARE @id INT;  
   SET @id = (SELECT id FROM Table1 WHERE data = @data)

   IF(@id IS NULL)
       INSERT INTO Table1 (data, count) SELECT @data,1;
   ELSE 
       UPDATE Table1 SET count = count + 1 WHERE data = @data;
END TRANSACTION

If I wrap this in a TRANSACTION it adds more overhead. I know TRY/CATCH also brings overhead but I think TRANSACTION adds more - anyone know?.

People keep telling me that using TRY/CATCH in normal app logic is BAD, but won't tell me why

Note: I'm running SQL Server 2005 on at least one box, so I can't use MERGE

via Chebli Mohamed

Create a function for generating random number in SQL Server trigger

I have to create a function in a SQL Server trigger for generating random numbers after insert. I want to update the column with that generated random number please help what I have missed in my code.

If you know other ways please suggest a way to complete my task.

This my SQL Server trigger:

ALTER TRIGGER [dbo].[trgEnquiryMaster]
ON [dbo].[enquiry_master]
AFTER INSERT 
AS 
    declare @EnquiryId int;
    declare @ReferenceNo varchar(50);
    declare @GenReferenceNo NVARCHAR(MAX);

    select @EnquiryId = i.enquiry_id from inserted i;
    select @ReferenceNo = i.reference_no from inserted i;
BEGIN
     SET @GenReferenceNo = 'CREATE FUNCTION functionRandom (@Reference VARCHAR(MAX) )
        RETURNS VARCHAR(MAX)
        As
        Begin
        DECLARE @r varchar(8);
        SELECT @r = coalesce(@r, '') + n
        FROM (SELECT top 8 
        CHAR(number) n FROM
        master..spt_values
        WHERE type = P AND 
        (number between ascii(0) and ascii(9)
        or number between ascii(A) and ascii(Z)
        or number between ascii(a) and ascii(z))
        ORDER BY newid()) a

        RETURNS @r
        END
        '

        EXEC(@GenReferenceNo)

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- update statements for trigger here
    UPDATE enquiry_master 
    SET reference_no ='updated' 
    WHERE enquiry_id = @EnquiryId
END   

via Chebli Mohamed

SQL to find rows with a similar numeric value

I have a table in a database which lists the similarity of an item to another, where each row(s) is essentially a search result, where similarity is a numeric value.

A row is either a parent (no similarity level) which may have "children" results

Or a child, where a numeric similarity percentage is given of its parent

What I need to do is identify all the items which are similar. This can be done as if two items have a near identical similarity score to a parent, then those two items can be said to be similar.

However; I'm having trouble accomplishing this with SQL. I'm using Access, and can split the table into parents and children if need be, but can't do much more

An example of my table is below:

id, parent, score
aaa,,
aab,,
cas,aab,97
cad,aab,96
agd,aab,70
aac,,
aad,aac,100

In the above example, I'd like to pick out items "cas" and "cad" as the results.

Conversely, I can pick out all the results which are similar to a parent (such as aab and aac) via a simple SELECT query.

Thanks for the help.

via Chebli Mohamed

ASP.net get max value from Profile field

I have a classic ASP website containing a users table with a ID_USER field (int, primary key, auto increment). The ID_USER value is used to track user's activity and is saved in other tables as part of the "Saved by","Saved date" logic.
Now, the website was updated and moved to ASP.NET, with the authentication rewritten using the ASP.NET membership and Profile providers. The old user's table was imported in the new structure. The ID_USER field became a Profile value in the new Membership/Profile logic.
All the other tables remained the same becouse of the compatibility between the two websites.

Question:
When creating new users I need to set the value for the ID_USER field too.
How can I do this? Can I somehow get the max value of the ID_USER profile field?
Thanx

via Chebli Mohamed

How to run single select statement across all the databases in the same schema

I need to run a simple select statement across all the databases in the schema(SQL Server). I have around 30-40 databases. This table has same structure in all the databases.

select * from table1 where condition

Can you please let me know how to get the records from all databases??

via Chebli Mohamed

Can't use addslashes on PHP SQL Server ODBC

EDIT I'm using MS SQL, I query to MySQL then Insert it to MS SQL

I wrote a script that will query a data from a databse and upload it to another database. So the script is like this

    $id                       = $row["id"];
    $title                    = $row["title"];
    $firstname                = safe($row["firstname"]);
    $surname                  = safe($row["surname"]);
    $dob                      = $row["dob"];
    $phone                    = $row["phone"];
    $addr1                    = safe($row["addr1"]);
    $addr2                    = $row["addr2"];
    $towncity                 = $row["towncity"];
    $postcode                 = $row["postcode"];
    $user_platform            = $row["user_platform"];
    $user_browser             = $row["user_browser"];
    $user_browser_ver         = $row["user_browser_ver"];
    $user_ip                  = $row["user_ip"];
    $terms                    = $row["terms"];
    $privacy_policy           = $row["privacy_policy"];
    $column_header            = $row["column_header"];
    $column_header_response   = safe($row["column_header_response"]);
    $column_header_response_2 = safe($row["column_header_response_2"]);
    $column_header_response_3 = safe($row["column_header_response_3"]);
    $column_header_response_4 = safe($row["column_header_response_4"]);
    $column_header_response_5 = safe($row["column_header_response_5"]);
    $filename                 = $row["filename"];
    $cpl                      = $row["cpl"];
    $rejected                 = $row["rejected"];
    $reject_reason            = $row["reject_reason"];
    $email                    = $row["email"];
    $created_at               = $row["created_at"];
    $updated_at               = $row["updated_at"];

$values = "
            $id,
            '$title',
            '$firstname',
            '$surname',
            '$dob',
            '$phone',
            '$addr1',
            '$addr2',
            '$towncity',
            '$postcode',
             $age,
            '$user_platform',
            '$user_browser',
            '$user_browser_ver',
            '$user_ip',
            '$terms',
            '$privacy_policy',
            '$column_header',
            '$column_header_response',
            '$column_header_response_2',
            '$column_header_response_3',
            '$column_header_response_4',
            '$column_header_response_5',
            '$filename',
            '$cpl',
            '$rejected',
            '$reject_reason',
            '$email',
            '$created_at',
            '$updated_at'
            ";

 function safe($value){ 
   return addslashes($value); 
} 

Then this

$query = "INSERT INTO forms VALUES(".$values.");";

Then I have some error

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Neill'., SQL state 37000 in SQLExecDirect 

which is likely to be the unescapped string but I already have it. When I tried to echo the query:

INSERT INTO forms VALUES( 122, 'Miss', 'John', 'O\'Neill', '1973-08-16', '+447939161234', '31w Red Square', '', 'Johnstone', 'PA5 8AD', 44, '', '', '', '', '', '', 'washing_machine', '5yrs and above', 'zanussi', '', '', '', '', '0.17', '', '', 'john.oneill@gmail.net', '2015-08-04', '2015-08-04' );

Looks like the O'Nielll is not escapped or there's a problem with my quotes? How to fix this? thanks

via Chebli Mohamed

cakePHP find("list") returns empty array

I am trying to make a drop down list of users by using the foreign key [UserID]. In the controller, I have find("list"). When I debug $this->Order->SalesAgent in the controller, it prints the User Object. However, in the view page, when I debug the result of $this->Order->SalesAgent->find("list"), shows and empty array.

Heres the Controller:

    public function edit_sales_agent ($id=null) {
        debug($this->Order->SalesAgent);
        $this->set("users",$this->Order->SalesAgent->find("list"));
        debug($this->users);
    }

and heres the View:

debug($users);
echo $this->Form->create("Order");
    echo $this->Form->input("UserID");

$users is the result of find("list")

Could anyone help me out? Thanks!

Association:

class Order extends AppModel{
    public $useTable = 'CustomerOrder';
    public $primaryKey = 'OrderID';
    **public $belongsTo = array(
        "SalesAgent"=>array(
            "className"=>"User",
            "foreignKey"=>"UserID"**
        ),

Sales Agent Model:

<?php
class User extends AppModel{
    public $useTable = 'UserAccount';
    public $primaryKey = 'UserID';
    public $order = array(
        "User.LastName"=>"asc",
        "User.FirstName"=>"asc"
    );
    public function __construct($id = false, $table = null, $ds = null) {
        parent::__construct($id, $table, $ds);
        $this->virtualFields['full_name'] = sprintf("(%s.FirstName+' '+%s.LastName)", $this->alias, $this->alias);
    }
    public function login($data){
        return $this->find("first",array("conditions"=>$data['User']));
    }
}

UPDATE:

Alright, so I figured out what the problem is but I dont know how to fix it. When I type find(list), this is the query it runs:

SELECT [SalesAgent].[UserID] AS [SalesAgent__0], [SalesAgent].[UserID] AS [SalesAgent__1] FROM [UserAccount] AS [SalesAgent] WHERE 1 = 1 ORDER BY [User].[LastName] asc, [User].[FirstName] asc

THis is the error it proposes:

SQL Error: The column prefix 'User' does not match with a table name or alias name used in the query. [APP/Model/Datasource/Mssql.php, line 749]

The SalesAgent uses class User, which uses table UserAccount

via Chebli Mohamed

SQL Server snapshot replication: error on table creation

I receive the following error (taken from replication monitor):

The option 'FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME' is only valid when used on a FileTable. Remove the option from the statement. (Source: MSSQLServer, Error number: 33411)

The command attempted is:

CREATE TABLE [dbo].[WP_CashCenter_StreamLocationLink]( [id] [bigint] NOT NULL, [Stream_id] [int] NOT NULL, [Location_id] [numeric](15, 0) NOT NULL, [UID] [uniqueidentifier] NOT NULL ) WITH ( FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=[UC_StreamLocation] )

Now, for me there's two things unclear here.

1. Table already existed on subscriber, and I've set @pre_creation_cmd = N'delete' for the article. So I don't expect the table to be dropped and re-created. In fact, table still exists on subscriber side, although create table command failed to complete. What am I missing? Where does this create table command come from and why?

2. I don't understand why does this FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME option appear in creation script. I tried generating create table script from table in SSMS and indeed, it's there. But what's weird, I can't drop and re-create the table this way - I get the very same error message.

via Chebli Mohamed

Difficulty printing one particular query in MSSQL

I'm trying to construct a small query which will pull data from individual fields in a DB and print them in a human readable list format (it's what the operators are used to seeing). The code I have here is far from complete but It seems to me that it should work.

DECLARE @PSUCARD VARCHAR(20)
DECLARE @EQUIPMENT VARCHAR(50)
DECLARE @T1 VARCHAR
SET @PSUCARD = 'PSU-888'
SET @EQUIPMENT = '123_POUCH'

PRINT @PSUCARD + ':'
PRINT @EQUIPMENT
PRINT ''

IF (SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND     PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)IS NOT NULL  BEGIN
    SET @T1 = (SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)
    PRINT 'Temperature: ' + @T1
    --(SELECT TEMPERATURE_MAIN FROM PSU WHERE PSU.PART_ID = @PSUCARD AND PSU.OPERATION_RESOURCE_ID = @EQUIPMENT)
END

If I execute the code as is, @T1 returns a * rather than a value. If I remove comments from the line below I am reassured that there is indeed a value there. I have other code very similar to this which works fine. Any ideas?

Also, I don't know if this helps in diagnosing the problem, but despite the temperature field in the DB being an INT, I get a conversion message if I try to treat @T1 an an INT.

via Chebli Mohamed

Updating table by using self joining the same table

I have a @table like below. I need to calculate the values for the row "Apple left in warehouse after".

For this I use this query. But I am not getting the final counts correctly for TA.Left_Counts. Could you please correct the query if I am wrong.

UPDATE TA
SET TA.Left_Counts = TA1.Left_Counts + TA2.Left_Counts +  
                     TA3.Left_Counts - TA4.Left_Count
FROM @TableA TA
INNER JOIN @TableA1 TA1 ON (TA.offsetNumber-1) = TA1.offsetNumber
                        AND TA1.DataField = 'Apple left in Warehouse After' --> To get the Apple's left in warehouse from previous month.
INNER JOIN @TableA TA2 ON TA.DateField = TA2.DateField
                       AND TA2.DataField = 'Apple in Stock' 
INNER JOIN @TableA TA3 ON TA.DateField = TA3.DateField
                       AND TA3.DataFields = 'Apple in Production'
INNER JOIN @TableA TA4 ON TA.DateField = TA4.DateField
                       AND TA4.DataFields = 'Apples Sold'
WHERE 
    TA.DataFields = 'Apple left in Warehouse After' 
    AND TA.offsetNumber <> 0

via Chebli Mohamed

SQL server, data integrity - table referenced by many tables

I have a table which has some generic data, that must be referenced by a multiple number of other tables. The referenced table can't be simplified to fit columns of the referencing tables. How do I enforce data integrity and relationships in such a scenario?

via Chebli Mohamed

Update or delete splitted data

In customers table I have Email column which could contain multiple emails separated by (;).
I used split function to separate emails for each customer:

Cust1 --->email1
cust1 --->email2
cust1 ---> emailN

And I could add more emails to the same customer.
I want to be able to update or delete the splitted emails, in other words if email2= abc@company.com I want to change it to xyz@company.com or delete it.
Is it possible to do using split function? or any other way?

Here is my split function

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Calling the function to split emails:

select tb1.custId, split.splitdata from customers tb1
outer apply [dbo].[fnSplitString] (tb1.email,';') split
where tb1.Email like '%;%'

To add new email to the same customer:

UPDATE Customers set Email=Email+';new Email' Where CustId='customerId'

for updating or deleting existing emails, any suggestions?

Thanks in advance

via Chebli Mohamed

How to connect to (localdb) database on server for Asp.Net membership?

I'm trying to output a list of users membership.GetAllUsers(), but I get an exception:

When establishing a connection to SQL Server error has occurred that is associated with the network or with a specific instance. Server not found or unavailable. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error upon detection of the specified server or instance)

My connection string :

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-SDC-20150804171038;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-SDC-20150804171038.mdf" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

via Chebli Mohamed

Create new dimension using values from another dimension in SQL?

I currently have a SQL table that looks something like this:

   RuleName      | RuleGroup
---------------------------
Backdated task   | DRFHA  
Incorrect Num    | FRCLSR
Incomplete close | CFPBDO
Appeal close     | CFPBDO
Needs letter     | CFPBCRE
Plan ND          | DO
B7IND            | CORE

I am currently writing SSMS procedure that pulls these dimensions from the existing table. However, I also want the procedure to create a new dimension that will create a "SuperGroup" dimension for each rule based on the text in it's RuleGroup (and an other column for the rest). For example:

   RuleName      | RuleGroup | SuperGroup
--------------------------------------------
Backdated task   | DRFHA     | Other
Incorrect Num    | FRCLSR    | Fore
Incomplete close | CFPBDO    | DefaultOp
Appeal close     | CFPBDO    | DefaultOp
Needs letter     | CFPBCRE   | Core
Plan ND          | DO        | DefaultOp
B7IND            | CORE      | Core

I have currently tried used the "GROUP BY" function, as well as using SELECT with several "LIKE" statements. However, the issue is that this needs to be scaleable - although I only have 21 groups right now, I want to automatically sort if new groups are added.

Here is the SSMS procedure as well:

CREATE PROCEDURE [Rules].[PullRulesSpecifics]
AS
BEGIN
SELECT
    ru.RuleName
    ru.RuleGroup
FROM RuleData.groupings ru
WHERE 1=1
AND   ru.ActiveRule = 1
AND ru.RuleOpen >= '2015-01-01'

via Chebli Mohamed

Server managment studio Export/Import Tables: The Ordering of Execution in SSIS Package

im making some SSIS package to import data from production servers to my own local databases.

The question is, is there a way to order the execution of the tables?

Problem: Table1: PK Table2Key FK Table1Key Table2: PK Table2Key

It seams the ordering gets chosen based on the names of the tables. So here it will try Table1 first, this will fail couse there are forign key constraints to Table2

I want Table2 to get fetched before Table1. I tried editing the order of the components in the ssis package, this did not work :(

Kind regards Tor Einar

via Chebli Mohamed

Why am I getting a converting data type error when selecting a decimal column?

I am trying to SELECT a column from a view as shown.

The datatype of the column is stored as a decimal(18, 7), null.

SELECT DecimalPercentageColumn 
FROM dbo.DetailsView

However when I try to do this an error message is returned:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

via Chebli Mohamed

SQL query to list of all lg and lt based upon rectangular polygon points

I have two tables one having longitude and latitude points (2 columns) and other having gps coordinates which define a rectangular polygon (8 columns , 4 GPS co-ordinate , 2 point each)

Here is TABLE A

zip   state city                 lat    lng
35004   AL  Acmar             33.584132 -86.515570
35005   AL  Adamsville        33.588437 -86.959727
35006   AL  Adger             33.434277 -87.167455
35007   AL  Keystone          33.236868 -86.812861
35010   AL  New Site          32.941445 -85.951086
35014   AL  Alpine            33.331165 -86.208934
35016   AL  Arab              34.328339 -86.489638
35019   AL  Baileyton         34.268298 -86.621299
35020   AL  Bessemer          33.409002 -86.947547
35023   AL  Hueytown          33.414625 -86.999607

and TableB is as follows

locationID  lt_Upleft   lg_UpLeft   lt_UpRight  lg_UpRight  lt_DownLeft lg_DownLeft lt_DownRight    lg_DownRight
        1   41.7127     -75.0059     41.7127    -73.0059    39.7127        -75.0059  39.7127         -75.0059

Now I want to get result form TableA Which falls inside 8 points of rectangular polygon. And Same way I want to get result of TableB which falls under longitude and latitude points.

I am new to google maps things. Not sure how I will get results. I have gone through some sample eg :

DECLARE @POLIGON geography; 
SET @POLIGON = geography::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 4326);
DECLARE @LLTABLE TABLE
(
    POINT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
    ,LAT FLOAT NULL
    ,LON FLOAT NULL
)


;WITH NN1(N) AS (SELECT NM.N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS NM(N))
,NUMBERS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM NN1 N1,NN1 N2, NN1 N3
ORDER BY 1 OFFSET 0 ROWS FETCH FIRST (20) ROWS ONLY)

INSERT INTO @LLTABLE (LAT,LON)
SELECT
   LATN.N  AS LATITUDE
   ,LONN.N  AS LONGITUDE
FROM NUMBERS LATN, NUMBERS LONN;

SELECT 
     LT.POINT_ID
    ,LT.LAT
    ,LT.LON
FROM @LLTABLE LT
WHERE geography::STPointFromText(CONCAT
        (
            'POINT('
           ,CAST(LT.LON AS VARCHAR(12))
           ,CHAR(32)
           ,CAST(LT.LAT AS VARCHAR(12))
           ,')'
        ), 4326).STWithin(@POLIGON)  = 1

But don't how to use it.

via Chebli Mohamed

I need help SELECT querying all ISNULL or empty values in SQL [on hold]

I need help SELECT querying all ISNULL or empty values in all tables of a SQL ArcSDE database. I need this functionality in SQL management studio if possible. The SQL database is 2008R2, the SQL server is SQLEXPRESS locally.I have access to arcCatalog also if it is easier (10.3) and ArcServer 10.3.1.

So far I can highlight to see what I need (null verses not null in image below) and I can see each NULL column highlighted.

SELECT *
FROM [database].[user].[table]

quick query showing all fields in a single table:

SELECT TOP 1000 [OBJECTID]
      ,[EventID]
      ,[GlobalID]
      ,[TagID]
      ,[AlternateAssetID]
      ,[ISATID]
      ,[OriginEventID]
      ,[CreatedBy]
      ,[CreatedDate]
      ,[EffectiveFromDate]
      ,[EffectiveToDate]
      ,[LastModified]
      ,[ModifiedBy]
      ,[HistoricalState]
      ,[ProcessFlag]
      ,[SourceCL]
      ,[Remarks]
      ,[Description]
      ,[InServiceDate]
      ,[OperationalStatus]
      ,[SiteEventID]
      ,[Measure]
      ,[SeriesEventID]
      ,[Station]
      ,[SymbolRotation]
      ,[POINT_X]
      ,[POINT_Y]
      ,[POINT_Z]
      ,[RouteEventID]
      ,[VisualOffset]
      ,[TypeCL]
      ,[ManufacturerCL]
      ,[MaterialCL]
      ,[DiameterInletCL]
      ,[DiameterOutletCL]
      ,[WallThicknessInCL]
      ,[WallThicknessOutCL]
      ,[SpecificationCL]
      ,[PressureRating]
      ,[DateManufactured]
  FROM [test].[test].[REDUCER]

via Chebli Mohamed

SQLCMD results to Excel

I am using SQLCMD command in powershell to run SQL scripts. When i pipe the output to TXT ,the formatting is all messed up. i want to export the results to excel file. how can i do that.

My command Sqlcmd -Q 'select top 10 * from people' -S serverName -d testDB -o c:\output.txt | format-table

Thanks in advance.

via Chebli Mohamed

Starting problems with OTL 4.0 MS SQL, ODBC and Code:Blocks

I have to insert some values into a MS SQL-Database on a Windows Machine, but also have to insert some values in a MySQL-Database on Linux soon. So I looked around and found SOCI and OTL 4.0.

I decided to use OTL 4.0 because it looked more easy for one, who haven't worked with C++ and databases before.

I tried to compile a modification of an example ( made it easier) and stumbled serveral times.

My Code so far:

    #include "db.h"
#include <iostream>
#include <fstream>
#include <stdio.h>

#include "plc.h"

#include <sqlncli.h>
#include <oledbdm.h>

using namespace std;


#define OTL_ODBC_MSSQL_2008 // Compile OTL 4/ODBC, MS SQL 2008
//#define OTL_ODBC // Compile OTL 4/ODBC. Uncomment this when used with MS SQL 7.0/ 2000
#include "otlv4.h" // include the OTL 4.0 header file

otl_connect db; // connect object

void insert(TIMESTAMP_STRUCT tm, string table)
{

 string sql_statement;
 //sql_statement = "insert into " << table << " values (:"
 otl_stream o(50, // buffer size
              "insert into test_tab2 values(:f1<float>,:f2<timestamp>)",
                 // SQL statement
              db // connect object
             );

 for(int i=1;i<=10;++i){
  tm.year=1998;
  tm.month=10;
  tm.day=19;
  tm.hour=23;
  tm.minute=12;
  tm.second=12;
  tm.fraction=0;
  o<<(float)i<<tm;
 }
}



void getDataIntoDB(plcdata &data)
{
    otl_connect::otl_initialize(); // initialize ODBC environment
    try{

        db.rlogon("UID=scott;PWD=tiger;DSN=mssql2008"); // connect to ODBC

        }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.sqlstate<<endl; // print out SQLSTATE message
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from Oracle


}

Build log:

-------------- Build: Debug in TimeLogger (compiler: GNU GCC Compiler)---------------

mingw32-g++.exe -Wall -fexceptions -g -DOTL_ODBC -IC:\Users\bauermar\GTK -IC:\Users\bauermar\Documents\Sources\EN3 -I"C:\Program Files\Microsoft SQL Server\90\SDK\Include" -c C:\Users\bauermar\Documents\Sources\TimeLogger\db.cpp -o obj\Debug\db.o In file included from C:\Users\bauermar\Documents\Sources\TimeLogger\db.cpp:8:0: C:\Program Files\Microsoft SQL Server\90\SDK\Include/sqlncli.h:17:0: warning: ignoring #pragma warning [-Wunknown-pragmas] In file included from C:\Users\bauermar\Documents\Sources\TimeLogger\db.cpp:8:0: C:\Program Files\Microsoft SQL Server\90\SDK\Include/sqlncli.h:133:19: fatal error: oledb.h: No such file or directory compilation terminated. Process terminated with status 1 (0 minute(s), 0 second(s)) 1 error(s), 1 warning(s) (0 minute(s), 0 second(s))

I installed the Microsoft SQL Native Client with SDK, I included all libs and headers in the IDE of Code::Blocks I also included the sqlncli.h like it is said here

But I have no idea how to continue

I'm using Windows 7, Code::Blocks and MinGW32

Does anybody knows a good tutorial for working on DBs with C++ on several OS? Had someone the same problem? Is there a easier way to handle that with C++?

Thanks in advice!

via Chebli Mohamed

SQL Server: How to get the column name which causes the SQLException?

Q: How can i get the column name which causes the SQLException?

C: I am working with SQL Server. My data which i get from my database is visualized with C#/WPF datagrid/DataTable.

If i want to insert an new datarecord to the database where i for example forgot to put some data in a cell which can not be NULL, i will get an SQLException with the message:

Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

'%.*ls' is replaced automatically by the columnname which causes the SqlException.

Is there a way to get this column name mentioned in the error message. Or does i have to extract it from the message string myself?

via Chebli Mohamed