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

Why does the android studios layout preview look different

When i first create an android studio project, there should be a pre made layout preview in the activity_main.xml, but i only see a grey screen with the words 'Hello World'.

Here is what it looks like...

but it should look like this...

How do i fix this? I tried restarting my computer and pressing reload in android studios, but nothing is working. Does anyone have any ideas?

Extract Root Elements Name attribute from XSD using XSL

I need to extract root element name attribute my input is as below

<?xml version = '1.0' encoding = 'UTF-8'?>
    <xsd:schema xmlns:xsd="http://ift.tt/tphNwY" elementFormDefault="qualified" xmlns:nxsd="http://ift.tt/L8WCuC"    nxsd:encoding="US-ASCII">
        <xsd:element name="car">
            <xsd:complexType>
                <xsd:sequence>
                    <xsd:element name="wheel" type="xsd:string" />
                </xsd:sequence>
            </xsd:complexType>
        </xsd:element>
        <xsd:annotation>
            <xsd:appinfo>NXSDSAMPLE=</xsd:appinfo>
            <xsd:appinfo>USEHEADER=false</xsd:appinfo>
        </xsd:annotation>
    </xsd:schema>

The XSL looks like below

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://ift.tt/tCZ8VR" version="2.0">
    <xsl:template match="/*/*[local-name()='element']">
        <xsl:value-of select="@name"></xsl:value-of>
    </xsl:template>
</xsl:stylesheet>

The required output is only "car" but the transformation is generating

      NXSDSAMPLE=
      USEHEADER=false
car

My program looks like below

 Document document;
  DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
  StringWriter sw = new StringWriter();
  File rootNameStyleSheet = new File("RootElementNameExtractor.xsl");
  File datafile = new File("cf1.xsd");
  try {
      DocumentBuilder builder = factory.newDocumentBuilder();
      document = builder.parse(datafile);
      TransformerFactory tFactory = TransformerFactory.newInstance();
      StreamSource stylesource = new StreamSource(rootNameStyleSheet);
      Transformer transformer = tFactory.newTransformer(stylesource);
      transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
      transformer.setOutputProperty(OutputKeys.INDENT, indent);
      DOMSource source = new DOMSource(document);
      StreamResult result = new StreamResult(sw);
      transformer.transform(source, result);
}

Any inputs on what is wrong with XSL would be helpful

how to handle errors while reading xml files R

I have a list of multiple xml files which have the same structure. Some of them have structural errors in them so they can't be read, i'm not capable of controlling them manually because there are too many files. I know that i need to imply the try or trycatch functions, i tried to understand them but i'm not understanding how to use them proberly on my case. To make the example easy i just want to transform them all into a csv.

library(XML)
k <- 1
Initial.files<- list.files("/My/Initial/Folder")
for(i in initial.files){
data<-dataTable(xmlToDataFrame(xmlParse(i)))
write.csv(data, file = paste("data",(k)".csv"))
k <- k+1
}

The error i get usually looks like:

Start tag expected, '<' not found
Error in xmlToDataFrame(xmlParse(i)) :
error in evaluation the argument 'doc' in selecting a method for function 'xmlToDataFrame': Error 1: Start tag expected, '<' not found

To handle my problem i have to rewrite my 5th line of code(i know that it is wrong):

data<- if(try(dataTable(xmlToDataFrame(xmlParse(i)))!= "try-error")
else{ haven't looked close to this because i didn't got that far...}...

I would like it to read the files and give me a list of the files path which didn't work to be read.

XML Parsing in OS X Terminal for MobileConfig file

I am working on generating (actually editing) a mobileconfig file (aka iOS profile, XML) via bash script.

The script fetch data from a MS Database and has now to inject/replace this data in my mobileconfig file (XML).

The XML file has the following structure:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://ift.tt/vvUEPL">
<plist version="1.0">
<dict>
    <key>PayloadContent</key>
    <array>
        <dict>
            <key>Host</key>
            <string>outlook.office365.com</string>
            <key>MailNumberOfPastDaysToSync</key>
            <integer>7</integer>
            <key>Password</key>
            <string>ActiveSyncPassword</string>
            <key>PayloadDescription</key>
            <string>Configures an Exchange account</string>
            <key>PayloadDisplayName</key>
            <string>Exchange ActiveSync</string>
            <key>PayloadVersion</key>
            <integer>1</integer>
            <key>SSL</key>
            <true/>
            <key>UserName</key>
            <string>xxxxxxx@xxx.com</string>
            <key>disableMailRecentsSyncing</key>
            <false/>
        </dict>
        <dict>
            <key>AutoJoin</key>
            <true/>
            <key>EncryptionType</key>
            <string>WPA</string>
            <key>HIDDEN_NETWORK</key>
            <true/>
            <key>IsHotspot</key>
            <false/>
            <key>Password</key>
            <string>WEPWPAWPSPEAPTLS</string>
            <key>PayloadType</key>
            <string>com.apple.wifi.managed</string>
            <key>PayloadVersion</key>
            <real>1</real>
            <key>ProxyType</key>
            <string>None</string>
            <key>SSID_STR</key>
            <string>SSID</string>
        </dict>
        <dict>

I would like to replace the WiFi Password but also ActiveSync "Password" fields between the < string> < /string> using any native (xmllint, sed) or non-native tool.

Can anyone please help ?

How to handle xml missing ns in Java SOAP WS

I'm writing a Java SOAP web service that works as follows: I'm receiving messages from 2 clients. The difference between them two is in the body of a specific field where there is a ns tag. I have implemented the web service using wsimport from a WSDL.

Client 1:

<soapenv:Envelope...>
...
    <ns9:StackOverFlow xmlns:ns9="...example...">
        ...
    </ns9:StackOverFlow>
... 
</soapenv:Envelope>

Client 2:

<soapenv:Envelope...>
...
    <StackOverFlow xmlns="...example...">
        ...
    </StackOverFlow>
... 
</soapenv:Envelope>

client1 works fine, but in client2 there is a field, StackOverFlow for example, that always converts to null in my Java web service. After debugging and looking into it, I have figured out it is because the missing ns.

Is there a way to support those 2 clients without asking them to change the xml they are sending me?

How to recognize 0 and null value in nillable element

I have xsd:

<xs:complexType name="RobZmenObyvateleDataType">
    <xs:annotation>
        <xs:documentation xml:lang="cs">Oprava referenčních údajů fyzické osoby v ROB.
        </xs:documentation>
    </xs:annotation>
    <xs:sequence>
        <xs:element name="AdresaPobytu" type="rob:AdresaPobytuStavType" minOccurs="0" nillable="true" />
    </xs:sequence>
</xs:complexType>

<xs:complexType name="AdresaPobytuStavType">
    <xs:annotation>
        <xs:documentation xml:lang="cs">Adresa místa pobytu v ČR včetně stavu a času poslední změny.
        </xs:documentation>
    </xs:annotation>
    <xs:simpleContent>
        <xs:extension base="AdresaPobytuType">
            <xs:attribute name="stav" type="xs:string" />
        </xs:extension>
    </xs:simpleContent>
</xs:complexType>

<xs:simpleType name="KodAdresniMistoType">
    <xs:annotation>
        <xs:documentation xml:lang="cs">Identifikátor adresního místa v RUAIN.</xs:documentation>
    </xs:annotation>
    <xs:restriction base="xs:int">
        <xs:minExclusive value="-10"/>
        <xs:maxExclusive value="999999999"/>
    </xs:restriction>
</xs:simpleType>

this generate class:

public class AdresaPobytuStavType
    implements Serializable
{

    @XmlValue
    protected int value;
    @XmlAttribute(name = "stav")
    protected String stav;

}

You can see value is int and not Integer. So I add binding to cast it to Integer because I need to recognize null value:

<jaxb:bindings schemaLocation="../MyXsd.xsd">
    <jaxb:bindings node="//xs:simpleType[@name='KodAdresniMistoType']">
        <jaxb:javaType name="java.lang.Integer" />
    </jaxb:bindings>
</jaxb:bindings>

so now It look good:

@XmlValue
@XmlJavaTypeAdapter(Adapter2 .class)
protected Integer value;

problem is that is not working.

when I send xml:

<urn2:RobZmenObyvateleData>
<urn3:AdresaPobytu xmlns:xsi="http://ift.tt/ra1lAU" xsi:nil="true" stav="spravny"/>
</urn2:RobZmenObyvateleData>

it return 0 instead of null.

<urn2:RobZmenObyvateleData>
<urn3:AdresaPobytu stav="spravny">0</urn3:AdresaPobytu stav="spravny">
</urn2:RobZmenObyvateleData>

and this also return 0

So now how should I recognize 0 and null value ?

Run two templates for same node (XSLT)

I have some xml files like the following. They contain all different tree structures and some elements do have attributes.

<root>
  <element n="A">
    <element n="B">
      <attribute a="1"/>
      <attribute a="2"/>
    </element>
    <element n="C">
      <element n="D">
        <attribute a="3"/>
      </element>
    </element>
  </element>
</root>

I want to transform these files using XSLT to get the following output. I have to keep the tree structure and also create a list of all elements with their attributes:

<root>
  <structure>
    <newElement n="A">
      <newElement n="B">
        <newAttribute a="1"/>
        <newAttribute a="2"/>
      </newElement>
      <newElement n="C">
        <newElement n="D">
          <newAttribute a="3"/>
        </newElement>
      </newElement>
    </newElement>
  </structure>
  <list>
    <listElement n="A"/>
    <listElement n="B">
      <listAttribute a="1"/>
      <listAttribute a="2"/>
    </listElement>
    <listElement n="C"/>
    <listElement n="D">
      <listAttribute a="3"/>
    </listElement>
  </list>
</root>

I try to run two different templates "e1" and "e2" for one node "element" but it doesn't work. It seems that the first template is ignored. So what do I have to change?

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://ift.tt/tCZ8VR">

<xsl:template match="/">
  <root>
    <structure>
      <xsl:apply-templates name="e1"/>
    </structure>
    <list>
      <xsl:apply-templates name="e2"/>
    </list>
  </root>
</xsl:template>

<xsl:template match="element" name="e1">
  <newElement>
    <xsl:attribute name="n">
      <xsl:value-of select="@n"/>
    </xsl:attribute>
    <xsl:apply-templates name="a1"/>
    <xsl:apply-templates name="e1"/>
  </newElement>
</xsl:template>

<xsl:template match="attribute" name="a1">
  <newAttribute>
    <xsl:attribute name="a">
      <xsl:value-of select="@a"/>
    </xsl:attribute>
  </newAttribute>
</xsl:template>

<xsl:template match="element" name="e2">
  <listElement>
    <xsl:attribute name="n">
      <xsl:value-of select="@n"/>
    </xsl:attribute>
    <xsl:apply-templates name="a2"/>
  </listElement>
  <xsl:apply-templates select="element"/>
</xsl:template>

<xsl:template match="attribute" name="a2">
  <listAttribute>
    <xsl:attribute name="a">
      <xsl:value-of select="@a"/>
    </xsl:attribute>
  </listAttribute>
</xsl:template>

</xsl:stylesheet>

dimens file in values-sw360dp clashes with nexus4 & nexus5 UI

Creating an application in portrait mode where I have to align Button on image based on top margin. I'm using dimens file in values-sw360dp which is looking proper in nexus 5 but the same values is not aligning the Buttons in nexus 4 as both of the devices using values-sw360dp folder for dimens file.

Can you please suggest the solution for this. Also can any one provide list of all possible values folder that should be integrated to support multiple screens

Following is the code:

<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="@color/windowBackground">


    <ImageView
        android:id="@+id/bc_logo"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:scaleType="fitStart"
        android:src="@drawable/bc_imgbc_logo" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/bc_logo"
        android:orientation="horizontal"
        android:weightSum="1"
        android:layout_alignParentTop="true"
        android:layout_marginTop="@dimen/bc_img_margin_top">

        <Button
            android:id="@+id/login_btn"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_weight="0.5"
            android:background="@drawable/signing_tab_btn"
            android:text="SIGN IN"
            android:textColor="@color/colorAccent" />

        <Button

            android:id="@+id/registration_btn"
            style="?android:attr/buttonBarButtonStyle"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_weight="0.5"
            android:text="REGISTER"
            android:textColor="#FFFFFF" />


    </LinearLayout>
</RelativeLayout

In above code I need to align the LinearLayout on the ImageView so using android:layout_marginTop="@dimen/bc_img_margin_top"for setting margin.

error Error parsing XML mismatched tag in string.xml

Log Type : Recompiling

Log Recorded At : Mon 29-06-2015 16:13:44.53

Log Recorded By : Advanced ApkTool v4.1.0 By BDFreak

---

max res 19, skipping drawable-v21 max res 19, skipping layout-v21 max res 19, skipping values-v21 F:\Downloads\Compressed\apkmanager\3-Out\Suspended_menu_3.3.3.apk\res\values\strings.xml:40: error: Error parsing XML: mismatched tag

error: Error parsing XML: mismatched tag

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="abc_action_bar_home_description">Navigate home</string>
    <string name="abc_action_bar_up_description">Navigate up</string>
    <string name="abc_action_menu_overflow_description">More options</string>
    <string name="abc_action_mode_done">Done</string>
    <string name="abc_activity_chooser_view_see_all">See all</string>
    <string name="abc_activitychooserview_choose_application">Choose an app</string>
    <string name="abc_searchview_description_clear">Clear query</string>
    <string name="abc_searchview_description_query">Search query</string>
    <string name="abc_searchview_description_search">Search</string>
    <string name="abc_searchview_description_submit">Submit query</string>
    <string name="abc_searchview_description_voice">Voice search</string>
    <string name="abc_shareactionprovider_share_with">Share with</string>
    <string name="abc_shareactionprovider_share_with_application">Share with %s</string>
    <string name="UMAppUpdate">App updating</string>
    <string name="UMBreak_Network">Please make sure you are connected to internet,update failed</string>
    <string name="UMDialog_InstallAPK">The lastest version has been downloaded, install now ?</string>
    <string name="UMGprsCondition">(Warning: Not WIFI Condition)</string>
    <string name="UMIgnore">Ingore this</string>
    <string name="UMNewVersion">Latest version:</string>
    <string name="UMNotNow">Not now</string>
    <string name="UMTargetSize">Target size:</string>
    <string name="UMToast_IsUpdating">Updating....</string>
    <string name="UMUpdateCheck">2.4.2.20140520</string>
    <string name="UMUpdateContent">Update Content</string>
    <string name="UMUpdateNow">Update now</string>
    <string name="UMUpdateSize">Update size:</string>
    <string name="UMUpdateTitle">New version found</string>
    <string name="abc_action_bar_home_description_format">%1$s, %2$s</string>
    <string name="abc_action_bar_home_subtitle_description_format">%1$s, %2$s, %3$s</string>
    <string name="abc_search_hint">Search…</string>
    <string name="abc_toolbar_collapse_description">Collapse</string>
    <string name="about_title">About</string>
    <string name="accesibility_service">""Analog return key", "news tips", need to set up - auxiliary functions, the list of services to open the menu service suspension. (At the top or bottom)

If the service is "unexpectedly shut down", please check whether the background management software to clean up, and then the software whitelisted。"</string>
    <String name ="accesibility_service_close">service unexpectedly turned off, check whether the background management software cleanup. If yes, please whitelist software</String>
    <string name="add">add value</string>
    <String name ="any_click_read_msg_introduction">When turned on click any gesture column can read the message</string>
    <String name ="any_click_read_msg_summary"> after opening, when there is a message, click the icon or click the sidebar suspension can quickly read the message to ignore the current click Action Settings </string>
    <String name ="any_click_read_msg_title"> Click to read the message </string>
    <String name ="app_name"> suspension Menu </string>
    <String name ="auto_hind_app_list"> Auto-hide the list of program </string>
    <String name ="auto_hind_app_list_introduction"> if the current application is an application, the suspension will be automatically hide menu "list" within </string>
    <String name ="auto_start_title"> boot </string>
    <String name ="backup"> Backup </string>
    <String name ="backup_autp_info"> There are currently no backup files, automatic backup is restored? </String>
    <String name ="backup_exist_info"> Backup file already exists, whether or not covered? </String>
    <String name ="backup_fail"> backup failed </string>
    <String name ="backup_info"> "backup file will be saved in:
Document / FloatMenu / back "</string>
    <String name ="backup_setting_title"> Backup Restore </string>
    <String name ="backup_suc"> backup success </string>
    <String name ="cancel"> Cancel </string>
    <String name ="cata_base_setting_title"> Basic Settings </string>
    <String name ="cata_experimental_features"> experimental features </string>
    <String name ="cata_float_view_feature_setting"> Appearance Settings </string>
    <String name ="cata_gesture_setting"> Gesture Settings </string>
    <String name ="cata_other_title"> Other settings </string>
    <String name ="cata_right_top_gesture_setting"> "top right corner" Gesture Settings </string>
    <String name ="cata_top_left_gesture_setting"> "upper left corner" Gesture Settings </string>
    <String name ="cata_view_feature_setting_title"> Appearance Settings </string>
    <String name ="caution"> Note: </string>
    <String name ="choose_feedback_way"> select the feedback mode </string>
    <String name ="clear_function"> Clear All </string>
    <String name ="click_for_restore_float_view"> Click the icon to restore suspended </string>
    <String name ="click_for_restore_light"> Click to close the screen long light </string>
    <String name ="click_for_restore_side"> Click to restore the sidebar </string>
    <String name ="click_to_del"> Tip: Click to delete the contents of </string>
    <String name ="combine_list_introduction"> When performing "key combination" function, the function "List" will be performed once within </string>
    <String name ="combine_list_summary"> function key combination will be performed once within </string>
    <String name ="combine_list_title"> key combination </string>
    <String name ="confirm"> OK </string>
    <String name ="countdown_close_flash"> To protect the flash automatically turns off two minutes after </string>
    <String name ="d_defalut_menu"> [{name: Set, type: 0, packageName: com.android.settings, className: com.android.settings.Settings, menuId: 0}, {name: return key, type: 1 , order: back_key_no_root, menuId: 0}, {name: Home key, type: 1, order: home_key, menuId: 0}, {name: Mute, type: 1, order: mute_toggle, menuId: 0}, {name: lock screen, type: 1, order: lock_screen, menuId: 0}, {name: display the notification bar, type: 1, order: toggle_notifaction, menuId: 0}, {name: Hide Icons, type: 1, order: switch_floatview, menuId: 0}] </string>
    <String name ="d_float_click"> [{name: default menu, type: 2, menuId: 2}] </string>
    <String name ="d_float_down"> [{name: Home key, type: 1, order: home_key, menuId: 0}] </string>
    <String name ="d_float_left"> [{name: return key, type: 1, order: back_key_no_root, menuId: 0}] </string>
    <String name ="d_float_right"> [{name: Task button, type: 1, order: recent_key, menuId: 0}] </string>
    <String name ="d_float_up"> [{name: show | hide suspension icon, type: 1, order: switch_floatview, menuId: 0}] </string>
    <String name ="d_no_root_content"> Current features require Root permission, if it is determined the phone has Root, Root can install the management software to manage Root privileges, whether to download and install? </String>
    <String name ="d_no_root_down"> Download </string>
    <String name ="d_no_root_title"> Unable to get Root privileges </string>
    <String name ="d_side_left_down_click"> [{name: Home key, type: 1, order: home_key, menuId: 0}] </string>
    <String name ="d_side_left_down_down"> [{name: display the notification bar, type: 1, order: toggle_notifaction, menuId: 0}] </string>
    <String name ="d_side_left_down_right"> [{name: return to the previous program, type: 1, order: switch_app, menuId: 0}] </string>
    <String name ="d_side_left_up_click"> [{name: Home key, type: 1, order: home_key, menuId: 0}] </string>
    <String name ="d_side_left_up_right"> [{name: return key, type: 1, order: back_key_no_root, menuId: 0}] </string>
    <String name ="d_top_right_click"> [{name: running the program, type: 2, menuId: 1}] </string>
    <String name ="defaultMenuName"> default menu </string>
    <String name ="define_FloatingActionButton" />
    <String name ="del"> Delete </string>
    <String name ="del_confirm"> Delete OK? </String>
    <String name ="del_suc"> deleted successfully </string>
    <String name ="double_home_title"> Double-click the Home button to lock the screen </string>
    <String name ="drawer_close"> draw close </string>
    <String name ="drawer_open"> draw open </string>
    <String name ="dynamic_position"> dynamic menu position </string>
    After <string name = "dynamic_position_introduction"> Open, pop-up menus dynamically adjusted based on the location of the sidebar position </string>
    <String name = "dynamic_position_summary"> After opening, the position of the pop-up menu will be dynamically adjusted based on the position of the gesture bar </string>
    <String name = "erro_menu_name_duplicate"> same menu name already exists </string>
    <String name = "erro_menu_name_is_null"> Enter the correct menu name </string>
    <String name = "erro_menu_name_null"> menu name can not be empty </string>
    <String name = "error_menu_has_been_del"> menu has been deleted </string>
    <String name = "error_net"> failed to load, please check your network </string>
    <String name = "error_no_root_access"> without root privileges </string>
    <String name = "evaluate_setting_title"> evaluation </string>
    <String name = "exclude_apps_summary"> When you run these programs, hide icons, and other gestures suspension bar </string>
    <String name = "exit"> Exit </string>
    <String name = "expired"> software expires, go to the store to download the latest version. </String>
    <String name = "extra_setting"> More Settings </string>
    <String name = "feedback_setting_title"> Feedback recommendation </string>
    <String name = "feedback_title"> suggestion feedback </string>
    <String name = "float_extra_setting_summary"> position fix, move freely, automatically and transparently, edge absorption </string>
    <String name = "float_tu"> "" Click ": the pop-up menu
"Press": Move Icon "</string>
    <String name = "float_view_auto_side_model"> edge absorption </string>
    <String name = "float_view_auto_trans_model"> Auto transparent </string>
    <String name = "float_view_freedom_move_model"> freedom of movement </string>
    <String name = "float_view_lock_model"> position lock </string>
    <String name = "float_view_touch_feedback"> Touch Animation </string>
    <String name = "function_click"> click </string>
    <String name = "function_double_click"> double click </string>
    <String name = "function_down"> fell </string>
    <String name = "function_left"> Left slide </string>
    <String name = "function_long_pressed"> long press </string>
    <String name = "function_right"> right slide </string>
    <String name = "function_up"> slide </string> on
    <String name = "gesture_setting_title"> Gesture Settings </string>
    <String name = "hind_into_statebar"> to hide the status bar </string>
    <String name = "home_show_float_title"> Home key to display the suspended icon </string>
    <String name = "ignore"> Ignore </string>
    <String name = "info_long_pressed_pop"> [press] icon menu inside, you can "Select menu style" and "Edit Menu" </string>
    <String name = "input_menu_name"> Enter the menu name </string>
    <String name = "input_new_menu_name"> Please enter a new menu name </string>
    <String name = "internal_theme"> Built </string>
    <String name = "introduction_native_theme"> "No custom skin found
The Picture folder on the SD card / FloatMenu / custom / file
Skin formats: PNG, size not larger than 200x200 "</string>
    <String name = "is_out_up_dismiss_summary"> After opening, if the slide gesture triggers the menu, lift your finger outside the menu, the menu disappears </string>
    <String name = "is_out_up_dismiss_title">, raising his hand to close the menu </string>
    <String name = "is_undo"> Are Undelete? </String>
    <String name = "item_menu_help"> Help </string>
    <String name = "item_menu_setting"> Other settings </string>
    <String name = "keep_light_close"> screen long bright close </string>
    <String name = "keep_light_open"> screen long bright open </string>
    <String name = "keyboard_cover_summary"> Special Note: After opening SmartBar cause some applications display is not normal, such as SMS interface unrealistic send button, please use discretion. </String>
    <String name = "keyboard_cover_title"> input method covering </string>
    <String name = "landscape_auto_hind_title"> horizontal screen Auto-hide </string>
    <String name = "left_and_right_to_del"> sliding around deleted </string>
    <String name = "library_FloatingActionButton_author"> Oleksandr Melnykov </string>
    <String name = "library_FloatingActionButton_authorWebsite"> http://ift.tt/1tAerbF </string>
    <String name = "library_FloatingActionButton_isOpenSource"> true </string>
    <String name = "library_FloatingActionButton_libraryDescription"> Android Google+ like floating action button which reacts on the list view scrolling events. Becomes visible when the list view is scrolled up and invisible when scrolled down. </String>
    <String name = "library_FloatingActionButton_libraryName"> FloatingActionButton </string>
    <String name = "library_FloatingActionButton_libraryVersion"> 1.0.0 </string>
    <String name = "library_FloatingActionButton_libraryWebsite"> http://ift.tt/1tAerbF </string>
    <String name = "library_FloatingActionButton_licenseId"> mit </string>
    <String name = "library_FloatingActionButton_repositoryLink"> http://ift.tt/1tAerbF </string>
    <String name = "like_this_one"> Like </string>
    After <string name = "lock_model_summary"> Open, you can set a long press gesture </string>
    <String name = "lock_notification_title"> lock screen message prompts </string>
    <String name = "lock_screen"> a key lock screen </string>
    <String name = "long_pressed_disable_summary"> mobile suspension icon </string>
    <String name = "long_pressed_to_edit"> prompt: "Press" menu list can be edited menu </string>
    <String name = "menu_edit"> Edit Menu </string>
    <String name = "menu_transparency"> Transparency </string>
    <String name = "native_theme"> local </string>
    <String name = "need_high_api"> currently feature only supports Android4.1 and above </string>
    <String name = "need_position_lock_switch_on"> The suspension icon positions locked or free to move the switch is turned to set </string>
    <String name = "need_service_on"> In order to ensure the normal operation of the software, you need to open the menu service suspension. </String>
    <String name = "no_backup"> no backup file </string>
    <String name = "no_email_app"> Email is not installed </string>
    <String name = "no_flash_permission"> no camera (pictures / videos) permission, please give appropriate permissions system settings </string>
    <String name = "no_notification"> There is currently no message notification </string>
    <String name = "no_running_apps"> is not currently running program </string>
    <String name = "no_sdcard"> can not be loaded SD card, make sure the available </string>
    <String name = "no_warm"> has opened </string>
    <String name = "not_support_this_function"> The current system does not support this feature </string>
    <String name = "notification_all_switches_closed"> all current switch is turned off </string>
    <String name = "notification_bluetooth_close"> Bluetooth turned off </string>
    <String name = "notification_bluetooth_open"> Bluetooth Open </string>
    <String name = "notification_can_not_launch_short_cut"> Unable to start fast program </string>
    <String name = "notification_dismiss_title"> 10S ignore the message after the prompt </string>
    <String name = "notification_force_show_float"> mandatory message prompts </string>
    After <string name = "notification_force_show_float_introduction"> Open, regardless suspension icon switch is turned on, will be forced to display a message prompts </string>
    <String name = "notification_gprs_close"> Data flow shutdown </string>
    <String name = "notification_gprs_open"> Data traffic open </string>
    After <string name = "notification_introduction"> Open, notification bar when there is news, suspension icon will prompt No message read </string>
    <String name = "notification_mute_close"> Mute Off </string>
    <String name = "notification_mute_open"> Mute On </string>
    <String name = "notification_not_install_mms"> you have not yet installed pop information, please download and install </string>
    <String name = "notification_not_install_profile"> You have not installed the Magic with the change, please download and install </string>
    <String name = "notification_not_install_query_charges"> you have not yet installed a key inquiry calls, please download and install </string>
    <String name = "notification_not_install_quick_clean"> You have not installed or cheetah cleanup LBE Masters, please download and install </string>
    <String name = "notification_rotation_close"> screen rotation Close </string>
    <String name = "notification_rotation_open"> screen rotation Open </string>
    <String name = "notification_summary"> icon suspension prompt QQ, micro letters and other news, click Read </string>
    <String name = "notification_title"> message prompts </string>
    <String name = "notification_vibreate_close"> Vibrate Off </string>
    <String name = "notification_vibreate_open"> vibration open </string>
    <String name = "notification_white_list_introduction"> only when the application "white list" have a notification message within, will have a message prompts </string>
    <String name = "notification_white_list_title"> message whitelist </string>
    <String name = "notification_wifi_close"> Wi-Fi turned off </string>
    <String name = "notification_wifi_open"> Wi-Fi to open </string>
    <String name = "nvg_float"> suspension icon </string>
    <String name = "nvg_menu"> Menu Settings </string>
    <String name = "nvg_notification"> message prompts </string>
    <String name = "nvg_other"> Other settings </string>
    <String name = "nvg_side"> Gestures side bar </string>
    <String name = "nvg_top"> gesture top bar </string>
    <String name = "official_version"> official version </string>
    <String name = "online_theme"> online </string>
    <String name = "open"> to open </string>
    <String name = "open_accessibility_service_title"> ○ Settings -> Accessibility -> "suspension Menu" under the list of services </string>
    <String name = "open_notification_service_title"> ○ Settings -> Security -> Notification Read permission -> "suspension Menu" </string>
    <String name = "open_service_title"> need to open the following services to run properly </string>
    <String name = "open_tip"> Tip: If the service is frequently shut down, make sure the software is added to the white list system or other back office management software </string>.
    <String name = "opened"> turned </string>
    <String name = "over_write"> cover </string>
    <String name = "p_add_menu_title"> "Add Menu" </string>
    <String name = "p_notification_title"> "prompt message" </string>
    <String name = "p_online_theme_title"> "Live Skin" </string>
    <String name = "p_point_cost_content"> Current Total% 1 $ d points, unlock% 2 $ s% 3 $ d consumed points left after unlocking% 4 $ d integration, whether unlock? </String>
    <String name = "p_point_get_content"> Current Total% 1 $ d points, unlock% 2 $ s% 3 $ d consumed points, just under% 4 $ d integral unlock function, whether free access points? </String>
    <String name = "p_point_item_get_points"> Get Points </string>
    <String name = "p_point_item_unlock"> unlocked </string>
    <String name = "p_point_loading_content"> Points get in ... </string>
    <String name = "p_title"> Tip </string>
    <String name = "p_unlock_failed"> Unlock failed </string>
    <String name = "p_unlock_suc"> unlock success </string>
    <String name = "pop_auto_kill_tip"> If you can not "show suspension icon," go to System Settings Open "floating window Permissions" suspension of the menu. </String>
    <String name = "pop_auto_kill_tip_for_meizu"> "1. If the software" automatic shutdown ", set the software to join the white list cleaning software.

2. If the "unable to boot", make sure the software has permission to boot. "</String>
    <String name = "pop_auto_kill_tip_for_xiaomi"> "detected in your mobile phone MIUI system, you need the following settings to normal use:

MIUI V5 System:
Settings - Applications - suspension Menu
Open the "Display floating window" rights.

MIUI V6 system:
Security Center - Application Management - suspension Menu
Open the "Display floating window" rights. "</String>
    <String name = "pref_float_view_size_title"> size </string>
    <String name = "pref_float_view_theme"> Skin </string>
    <String name = "pref_float_view_trans_title"> Transparency </string>
    <String name = "purchase_official"> buy the official version </string>
    <String name = "questions"> Frequently Asked Questions list </string>
    <String name = "rename"> Rename </string>
    <String name = "restore"> Recovery </string>
    <String name = "restore_suc"> successful recovery </string>
    <String name = "running_app"> Running Programs </string>
    <String name = "running_switch_app_black_list_summary"> list within the program will not be displayed or switched </string>
    <String name = "running_switch_app_black_list_title"> Running Programs / handover procedure blacklist </string>
    <String name = "save_comfirm_title"> Enter to save? </String>
    <String name = "scr_menu_style_title"> menu style </string>
    <String name = "scr_other_setting_title"> Other settings </string>
    <String name = "screen_shot_auto_hind"> Screenshot hide </string>
    <String name = "screen_shot_auto_hind_summary"> via software screenshots, icons and sidebar hidden suspension </string>
    <String name = "screen_shot_show_summary"> only for the screenshot feature within the program </string>
    <String name = "screen_shot_show_title"> Screenshot Hide suspension icons </string>
    <String name = "screen_shot_tip"> "will have vibration feedback after successful shots, pictures will be saved to:
SDCard / Pictures / ScreenShots / "</string>
    <String name = "service_describe"> case of non-ROOT "Analog return key", "the message tips", need to open this service </string>
    <String name = "service_destroy"> suspension menu service shut down unexpectedly, check that background management software has been added to the whitelist </string>
    <String name = "show_notification_msg_summary"> After opening, if [the message] whitelist program has a message notification, suspension icon automatically becoming an icon for the program to prompt a new message arrives, click to read the message </string>
    <String name = "show_notification_msg_switch_title"> message prompts </string>
    After <string name = "show_side_introduction"> turned back to the desktop will continue to display the sidebar </string>
    After <string name = "show_side_summary"> open, exit the program continues to display the location of the next portion of the sidebar </string>
    <String name = "show_side_title"> Sidebar </string>
    <String name = "side_distinguish_up_down_title"> distinguish between upper and lower portions </string>
    <String name = "side_gesutre_setting_title"> sidebar Gesture Settings </string>
    <String name = "side_left_down_setting"> "at the left part of" gesture </string>
    <String name = "side_left_up_setting"> "on the left part of the" gesture </string>
    <String name = "side_left_view_switch_title"> left side of the gesture column switch </string>
    <String name = "side_left_width"> left side width </string>
    <String name = "side_length"> length </string>
    <String name = "side_position"> Location </string>
    <String name = "side_right_down_setting"> "under the right part of the" gesture </string>
    <String name = "side_right_up_setting"> "on the right-hand part of the" gesture </string>
    <String name = "side_right_view_switch_title"> on the right gesture column switch </string>
    <String name = "side_right_width"> on the right width </string>
    <String name = "slide_del_running_app"> slide Remove the running program </string>
    <String name = "slide_del_running_app_attention"> "Note:
After opening, the surface may appear not click on the pop-up menu on some phones, please caution. "</String>
    After <string name = "slide_del_running_app_introduction"> Open, when the pop-up menu to "running program - a vertical", you can slide around the end of the program, you need Root privileges. </String>
    <String name = "start"> began to experience </string>
    <String name = "style"> style </string>
    <String name = "switch_anim"> start / switch procedural animation </string>
    Applications within the <string name = "switch_black_list_introduction"> "list" does not appear in the running list and will not be switched </string>
    <String name = "switch_float_view_title"> suspension icon switch </string>
    <String name = "switch_off_freedom_model_first"> Turn off the free movement of switch </string>
    <String name = "switch_off_lock_model_first"> Turn off location lock switch </string>
    <String name = "switch_on_service"> Open </string>
    <String name = "t_notification_float_msg"> When the notification bar when the news: \ u0009 </string>
    <String name = "t_notification_float_normal"> suspension icon looks like a normal time: \ u0009 </string>
    <String name = "t_start_float_like_this"> long suspension icon like this: \ u0009 </string>
    <String name = "test"> <font color = "red"> update settings </font> </string>
    <String name = "test_modle"> beta </string>
    <String name = "test_version"> Trial </string>
    <String name = "toggle_screen_func_introduction"> "1. Trigger pull-down screen automatically restored after hovering five seconds.


 2. fix some functions require permission prompt unprivileged Bug.

 3. Repair other known Bug.

 4. Input coverage options. "</String>
    <String name = "update_title"> Update Logs </string>
    <String name = "vibrate_time_title"> vibration intensity </string>
</resources>