I'm trying to use LIKE in report builder and I got this error:
An error occurred while reading data from the query result set.
Conversion failed when converting the varchar value '%' to data type int.
(Microsoft SQL Server, Error: 245)
this is my code:
select count(*) from projects where received LIKE '%'+@received+'%'
and institution# = @institution and program# = @program
how could i solve it?
I have got a table where one of the column has text mixed with HTML data. This was due to a bug (fixed) in a script, but the SQL data needs to be edited to reflect the changes. The column has a type nvarchar(max, null). All I need to do is find tags such as <a img="lkss">,<div attr=val> and replace them with empty string "". I looked into this, but the solution says how to replace the entire contents based on one pattern. My problem is that I need to replace contents partly, but preserve clean text (i.e. not HTML tags/attributes). Any suggestion/help is appreciated.
Test column data:
<div attr=val; color=somecolor>inside text<div some=val><a some=val>Inside anchor
Expected result:
inside textInside anchor
I am creating a table collaborating a lot of mapped data, but the query result would be far better suited to produce one row per unique ID than it currently is.
For example, I currently have:
UNIQUE ID | ID NAME | SourceName | SourceDate | StoreName | StoreOrder
1 | First | Example 1 | 1990 | Barnes | 1
1 | First | Example 1 | 1990 | Noble | 2
1 | First | Example 2 | 1996 | Barnes | 1
1 | First | Example 2 | 1996 | Noble | 2
2 | Second | Example 1 | 1990 | Barnes | 1
.... And so on ...
Source info and Store info are not related, they are only related through UniqueID.
What would be ideal is:
UNIQUE ID | ID NAME | SourceName1 | SourceDate1 | SourceName2 | SourceDate2 | StoreName1 | StoreOrder1| StoreName2 | StoreOrder2
1 | First | Example 1 | 1990 | Example 2 | 1996 | Barnes | 1 | Noble | 2 |
I know this is a bit of a messy solution, but it is required for further analysis. A single table, single row per unique ID would be perfect, no matter the column names etc.
It would also be great to have the column names dynamically allocated. For example if one ID has 4 stores allocated then there would be StoreName1 through to StoreName4 (Empty data entries would be 0 or NULL).
Does anyone have any suggestions on how to perform this?
I'm using EF6 code first with ASP.NET Web API.
Suppose there are two model classes
public class RawMaterial {
public int ID { get; set; }
public string Name { get; set; }
}
public class Furniture {
public int ID { get; set; }
public string Name { get; set; }
public virtual ICollection<RawMaterial> RawMaterials { get; set; }
}
DbContext
public class FurnitureContext : DbContext {
public DbSet<RawMaterial> RawMaterials { get; set; }
public DbSet<Furniture> Furnitures { get; set; }
}
And in the initializer class,
protected override void Seed (FurnitureContext context) {
var glass = new RawMaterial { Name = "glass" };
var wood = new RawMaterial { Name = "wood" };
var paint = new RawMaterial { Name = "paint" };
context.RawMaterials.AddRange(new RawMaterial[] { glass, wood, paint });
var chair = new Furniture {
Name = "chair",
RawMaterials = new RawMaterial[] { wood, paint }
};
var coffeeTable = new Furniture {
Name = "coffee table",
RawMaterials = new RawMaterial[] { wood, glass }
};
context.Furnitures.AddRange(new Furnitures[] { chair, coffeeTable });
context.SaveChanges();
}
I encountered a run time error complaining "an item cannot be removed from fixed size array". So clearly the program's trying to remove wood from chair before adding it to coffeeTable. So I changed the initializations to use Lists, as
var chair = new Furniture {
Name = "chair",
RawMaterials = new List<RawMaterial> { wood, paint }
};
After that, I could clearly see that wood was indeed removed from one of the furnitures' RawMaterials.
I also tried selecting wood from the context by
var chair = new Furniture {
Name = "chair",
RawMaterials = new RawMaterial[] {
context.RawMaterials.Where(r => r.Name == wood.Name).FirstOrDefault()
}
};
The result is still the same.
So my question is: how can I add the test data such that wood is present in both chair and coffeeTable? I am aware that this is not typically how many-to-many relations are defined, since RawMaterial does not know of Furniture. Or should I define the models the other way?
Thank you.
Edit: I check the database tables in SQL Server Object Explorer, and the SQL for RawMaterial is
CREATE TABLE [dbo].[RawMaterials] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,
[Furniture_ID] INT NULL,
CONSTRAINT [PK_dbo.RawMaterials] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_dbo.RawMaterials_dbo.Furnitures_Furniture_ID] FOREIGN KEY ([Furniture_ID]) REFERENCES [dbo].[Furnitures] ([ID])
);
GO
CREATE NONCLUSTERED INDEX [IX_Furniture_ID]
ON [dbo].[RawMaterials]([Furniture_ID] ASC);
And the SQL for Furniture is
CREATE TABLE [dbo].[Furnitures] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_dbo.Furnitures] PRIMARY KEY CLUSTERED ([ID] ASC)
);
So basically entity framework is not creating the database the way I need. That's why I cannot add wook to both chair and coffeeTable. How should I modify the Entity Models?
My data somewhat looks like this:
Product Attribute1 Attribute2 P1 1 -1 P1 1 -1 P1 1 -1 P1 1 -1 P1 1 -1 P2 1 1 P2 1 1 P2 1 1 P2 1 1 P2 1 1 . . .
Now I need to find out the correlation between attribute1 and attribute2 for each product individually. My actual data set contains around 100 products. Hence my output should be somewhat like this" Product Correlation P1 -1 P2 1 . . . How will I go about that in SAS/SQL?
I know this has come up a lot in the past but none of the fixes I've Googled or found on here has worked for me in this instance.
I'm running a fairly standard SQL Server Agent Job as a Transact-SQL script with the follow details: (replaced some stuff as *** for my boss's sanity)
-- Start T-SQL
USE msdb
EXEC sp_send_dbmail
@profile_name = 'MainProfile',
@recipients = 'test@test.co.uk',
@subject = 'T-SQL Query Result',
@execute_query_database = 'test30',
@query = 'SELECT ReferralReceivedDate,testRef,testcoMetadata.testcoRef,testcoMetadata.TimeSpentWithtester
FROM TestCasesTable, TestcoMetadata
WHERE testcasestable.CaseID = TestcoMetadata.CaseID AND AgencyName = [Test Injury] AND TestcoMetadata.TestcoRef IS NOT NULL AND TestcoRef <> ''
order by ReferralReceivedDate desc',
@attach_query_result_as_file=1,
@query_attachment_filename = 'Results.csv',
@query_result_separator = ','
-- End T-SQL --
The query itself runs fine as a normal query with no issues. The owner of this job has been used on other jobs again with no problems. In the step properties the Database selected is the same one as that mentioned in the @execute line.
I have a feeling this is either falling over the way it's trying to create the csv or something to do with permissions with dbmail part. I'm only a part time DBA so this has now lost me and I need help.
I have sql server developer edition in my computer and im doing many databases to same customer, so which edition of sql server should i install in customer server pc to give him th ability to work in all databases at same time frm differnt applecations.
I would like to get list of values from stored procedure. How to do it ?
Example : My stored Procedure
create PROCEDURE Get_ListOf_Holiday
AS
BEGIN
select * from holiday
END
In my Linq :
using (PlanGenEntities3 entity2 = new PlanGenEntities3())
{
var testList = entity2.Get_ListOf_Holiday();
}
But i am always getting values like -1. But in my Sql server I am getting the output like list of holiday details.
How to solve this. Please any one help me to fix ?
I have a XML structure "1234"
I know it can be done with the below query.
DECLARE @AttachVersions XML
SET @AttachVersions = '<AttachVersion><Version>1</Version><Version>2</Version><Version>3</Version><Version>4</Version></AttachVersion>'
SELECT ParamValues.[Version].value('.', 'VARCHAR(10)') AS [Version] FROM @AttachVersions.nodes('/AttachVersion/Version') as ParamValues([Version])
I know there is an alternate way to get the with "OPENXML" if it's an attribute value. Here's the sample code
DECLARE @FileterOptions VARCHAR(MAX)
SET @FileterOptions = '<AttachVersion><Version Value="1" /><Version Value="2" /><Version Value="3" /><Version Value="4" /></AttachVersion>'
DECLARE @AttachVersionHandle INT
CREATE TABLE #tmpAttachVersionList
(
[Value] INT
)
EXEC sp_xml_preparedocument @AttachVersionHandle OUTPUT, @FileterOptions
INSERT #tmpAttachVersionList ( [Value] )
SELECT [Value] FROM OPENXML( @AttachVersionHandle, '//Version' ) WITH #tmpAttachVersionList
SELECT * FROM #tmpAttachVersionList
DROP TABLE #tmpAttachVersionList
Is it possible to get the "Version" values with OPENXML using "XPath"?
i have a website hosted on GoDaddy and I need to create a database for it. So headed to the Databases section and created a MS SQL Database then on my local pc I tried to access the database via SQL Server Management studio, I was able to login to the database but I cannot make any operations. I get it does not exist or you do not have permission. So deciced to go the Security tab, then Login and tried to change my username that I'm using to systemadmin role but I also got Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission. What could be the problem? There are no other accounts on it. The default sa account is disbaled and I can't enable it coz it will prompt no permissions etc.
I don't understand it. Why GoDaddy allows me to create a database but with no permissions or rather I cannot alter it. Anyone facing the same issue? Thanks
while importing any txt file to sql server, i create an empty table first by mentioning data structure (variable names and their data type) and then copying data into this empty table from text file. Following is the query which i use.
CREATE TABLE test ( account_id varchar(20), target integer, ttl_spend_bp_8m numeric(38,4), trns_bp_fuel_8m bigint );
COPY test FROM 's3://acncrmbpmanalytics/PT/sc_feb_jfr_final_population.txt' WITH CREDENTIALS AS 'xxxxxxxxxx' IGNOREHEADER 1 maxerror 1000 delimiter '\t' DATEFORMAT 'YYYY-MM-DD' TIMEFORMAT 'YYYY-MM-DD HH:Mi:SS' COMPUPDATE ON;
But this time i have a txt file having 255 variables and i do not know data types of these variables. How should i import this table to sql server?
Hi right no I have this method to execute the store procedure:
public static DataTable ExecuteProcedureNoParams(string connectionStringName, string procedureName)
{
using (DbCommand sprocCmd = SqlDB(connectionStringName).GetStoredProcCommand(procedureName))
{
DataTable dt = new DataTable();
using (IDataReader sprocReader = SqlDB(connectionStringName).ExecuteReader(sprocCmd))
{
dt.Load(sprocReader);
// DisplayRowValues(sprocReader);
}
return dt;
}
}
private static SqlDatabase sqlDB;
public static SqlDatabase SqlDB(string connectionString)
{
if (sqlDB == null)
{
sqlDB = CreateConnection(connectionString);
}
return sqlDB;
}
private static SqlDatabase CreateConnection(string connectionString)
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
if (string.IsNullOrWhiteSpace(connectionString))
{
// Create the default Database object from the factory.
// The actual concrete type is determined by the configuration settings.
return factory.CreateDefault() as SqlDatabase;
}
else
{
// Create a Database object from the factory using the connection string name.
return factory.Create(connectionString) as SqlDatabase;
}
}
Now, the error that i'm getting is that it cannot find my store procedure. I tested the connection String in the web config and it also working properly.
it fails in the IDataReader call.
could please anyone help me with this issue?
I have a database column:
Model
------
EH2
I want to make it like:
Model
---------
Primo EH2
I have tried:
update Table
set Model = REPLACE(Model,' ','Primo EF2')
but it did not work.
I have created a query which gets results between 2 dates, Its working fine.. Now i have a scenario in which users does not enter any date..In this case I need to fetch all the result from database regardless of date.. The following query fails when user does not enter any date, it returns empty result set.
Kindly guide me how to resolve this problem.
select * from emp_register
where date between '10-10-2015' and '15-10-2015'
or status= 'Y'
i have this query which give me this table:
SELECT
CS.Name,
CS.FirstStep,
--CSS.NextCAPAStepID,
CS.ID
FROM CAPA_STEP_SEQUENCE CSS
LEFT JOIN CAPA_STEP CS ON CS.ID = CSS.CAPAStepID
WHERE CAPAStepID in (100000009,100000010,100000011,100000012,100000013)
GROUP BY CS.Name, CS.ID, CS.FirstStep
ORDER BY CS.FirstStep DESC
I heave to sort this table by this column from another table:
SELECT NextCAPAStepID
FROM CAPA_STEP_SEQUENCE
WHERE CAPAStepID in (100000009,100000010,100000011,100000012,100000013)
When i try to order by first query by CSS.NexstCAPAStepID it makes that in the first order column NextCAPAStepID is sorted ASC and then whole table is sorted with this sequence but in my case i want to sort whole table by sequence from CSS.NextCAPAStepID. Notice also that row with value 1 in column FirstStep should be always in the top.
I want to connect MS Access with MySQL. I installed Xampp for MySQL and now I installed the ODBC driver 5.3. I went to Data Sources and clicked on System-DSN to add the MySQL ODBC 5.3 Driver (like shown in this video https://www.youtube.com/watch?v=F06hvR6ksh4). But there wasn't any MySQL ODBC 5.3 Driver. My OS is Windows 7 Home x64. How can I connect the ODBC Driver to the MySQL Server?
We just migrated our SQL server to a cloud machine.Everything seems working but we are facing some issues with BCP command. a)Whenever we run Job the job Process exit with Failure."The process cannot access the file because it is being used by another process. Process Exit Code 1. The step failed".I find out that the BCP.exe is still runing after doing his task.
b)BCP command import the data in table from file .It works correct but for % column it does not import the data completely it only load the data which is having % equals or greater than 1 . for others its giving
Starting copy... SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Native Client]String data, right truncation.
Any help appreciated
For a SSRS report I need to create a boxplot from a subgroup of my data. In order to do this I'd like to calculate the percentile values from within the report. (1st quartile, median, 3th quartile).
The data has both a series group and category groups. The user shall be able to select a single category group to subdivide the data. For each category, the percentile values should be generated.d
To achieve this I've included custom code to calculate the percentile values. I've added as category group variables of my boxplot the following:
Values: Code.AddValue(IIF(Parameters!Output.Value=0,
CDbl(Fields!Value1.Value),
IIF(Parameters!Output.Value=1,
CDbl(Fields!Value2.Value),
IIF(Parameters!Output.Value=2,
CDbl(Fields!Value3.Value),
Double.NaN
)
)
)
)
Median: Code.Percentile(50)
Q1: Code.Percentile(25)
Q3: Code.Percentile(75)
The values variable is an arraylist which should be populated by all the variables in the subgroup.
My Custom code:
Public Dim values As System.Collections.ArrayList
Public Function AddValue(ByVal newValue As Double)
If (values Is Nothing) Then
values = New System.Collections.ArrayList()
End If
If Not Double.IsNaN(newValue) AndAlso newValue > 0 Then
values.Add(newValue)
End If
End Function
Public Function Percentile(ByVal percentileVal As Double) As Double
Return PercentilePos(values, percentileVal)
End Function
Private Function PercentilePos(values As System.Collections.ArrayList, ByVal percentile As Double) As Double
If IsNothing(values) OrElse values.Count = 0 Then
Return Nothing
End If
If percentile > 1 AndAlso percentile <= 100 Then
percentile = percentile / 100
ElseIf percentile <= 1 AndAlso percentile > 0 Then
percentile = percentile
Else
Throw New ArgumentException("percentile value is invalid")
End If
Dim numberCount As Integer = values.Count
Dim index As Integer = CInt(values.Count * percentile)
Dim sortedNumbers As New System.Collections.Generic.List(Of Double)
For Each val As Double In values
sortedNumbers.Add(CDbl(val))
Next
sortedNumbers.Sort()
If (sortedNumbers.Count - 1) < index Then
index = sortedNumbers.Count - 1
End If
Try
If (numberCount Mod 2 = 0) Then
If index > 0 Then
PercentilePos = (sortedNumbers(index) + sortedNumbers(index - 1)) / 2
Else
PercentilePos = sortedNumbers(index)
End If
Else
PercentilePos = sortedNumbers(index)
End If
Catch err As System.Exception
Throw New ArgumentException(err)
Return Nothing
End Try
End Function
Now I'm having the following problem: The group variable Values only collects the value from the first row of the subgroup and therefore the calculations are off. How do I get the group variable to collect / enumerate over all the rows in the subgroup?
Thanks in advance for your time and effort.
How can I insert a column of table to row of another?
Example :
Value
------
66
249
64
236
Rotate to this :
Digit1 Digit2 Digit3 Digit4
------------------------------
66 249 64 236
How do I change an existing column from bit to a foreign key (int)?
For example, column NEW_B was created like this:
ALTER TABLE [dbo].[APPLICATION]
ADD [NEW_B] [bit] NULL
GO
But now I want the NEW_B to reference column ID (int) of table ATTACHMENT (want to keep the name NEW_B, also allow NULLs).
in my project sql dependency works fine, but when the same project publish and run on iis , doesn't work. actually i run the project as local an run the same project on iis at the same time and click something to fired sql dependency, in local fired but on iis project doesn't fire. the web site name in iis is test and i run this query : GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "IIS APPPOOL\test" but doesn't work again
Select distinct c.classID, co.fCourseName as CourseName, StreetAddress + ', ' + l.City as LocationAddress, s.SessionName, sh.fShift as shift, StartTime, EndTime, c.classname, s.SessionID,
c.StartDate,c.enddate
From dbo.vw_Class c
Inner Join dbo.lk_Session s
On (s.SessionID = c.sessionID)
Inner Join dbo.lk_Course co
On (co.CourseID = c.CourseID )
Inner Join dbo.vw_Location l
On (l.locationid = c.locationid)
Inner Join lk_District d
On (d.districtID = c.districtId)
Inner Join lk_Province p
On (p.provik = d.provik)
Inner Join lk_Shift sh
On (c.shiftid = sh.shiftid)
where
c.DistrictID = case when @Districtid is null then c.DistrictID else @Districtid end
and c.LocationID = case when @locationid is null then c.LocationID else @locationid end
and s.SessionID = case when @sessionid is null then s.SessionID else @sessionid end
and c.CourseID = case when @levelid is null then c.CourseID else @levelid end
and c.ShiftID = case when @shiftid is null then c.ShiftID else @shiftid end
and c.StartDate >= case when @startdate is null then c.StartDate else @startdate end
and c.EndDate <= case when @enddate is null then c.EndDate else @enddate end
and convert(time,c.StartTime) >= case when @starttime is null then convert(time,c.StartTime) else convert(time,@starttime) end
and convert(time,c.endtime) <= case when @endtime is null then convert(time,c.endtime) else convert(time,@endtime) end
and c.Monday = case when @day1 = 'N' then c.monday else @day1 end
and c.Tuesday = case when @day2 = 'N' then c.Tuesday else @day2 end
and c.Wednesday = case when @day3 = 'N' then c.Wednesday else @day3 end
and c.Thursday = case when @day4 = 'N' then c.Thursday else @day4 end
and c.Friday = case when @day5 = 'N' then c.Friday else @day5 end
and c.Saturday = case when @day6 = 'N'then c.Saturday else @day6 end
and c.Sunday = case when @day7 = 'N' then c.Sunday else @day7 end
and c.RowStatus = 'A'
ORDER BY co.fCourseName, s.SessionID ,c.ClassName
In the above code for which columns i need to create the index.(None of tables used in this query don't have either primary key or indexes created)
I am using SQL Server 12/Azure and have 3 tables (T1, T2, T3) where T1 has 1-many with T2 and T3, I want to select from T2 and return the information of T1 records and their associated T3 records. To give a simplified example, T1 is "Customer", T1 is "Orders", T3 is "CustomerAddresses", so a customer can have many orders and multiple addresses. Now I want to query the orders and include the customers information and addresses, to make things a little bit complicated, the query for orders could include matching on the customer addresses, e.g. get the orders for these addresses.
Customer Table
----------------------
Id, Name,...
----------------------
Orders Table
------------------------------
OrderId, CustomerKey, Date,...
------------------------------
CustomerAddresses
-----------------------------------------------
AutoNumber, CustomerKey, Street, ZipCode,...
-----------------------------------------------
I am having trouble writing the best way (optimized) to return all the results in one transaction and dynamically generate the sql statements, this is how I think the results should come back:
Orders (T2) and customer information (T1) are returned in one result-set/table and CustomerAddresses (T2) are returned in another result-set/table. I am using ADO.NET to generate and execute the queries and use System.Data.SqlClient.SqlDataReader to loop on the returned results.
Example of how the results could come back:
Order-Customer Table
-------------------------------
Order.OrderId, Customer.Id, Customer.Name, Order.Date,....
-------------------------------
CustomerAddresses
-------------------------------
AutoNumber, CustomerKey, Street
-------------------------------
This is an example of a query that I currently generate:
SELECT [Order].[OrderId], [Order].[Date], [Customer].[Id], [Customer].[Name]
FROM Order
INNER JOIN [Customer] on [Order].[CustomerKey] = [Customer].[Id]
WHERE ([Order].[Date] > '2015-06-28')
Questions: 1. How do I extend the above query to also allow returning the CustomerAddresses in a separate result-set/table? To enable matching on the CustomerAddresses I should be able to do a join with the Customer table and include whatever columns I need to match in the WHERE statement.
I have a sql database on azure and can load data like articles. If I try to save data in table orders, billingDetails and positions it is running through the saving process without any exceptions but if I let the data of the tables be shown in the visual studio IDE afterwards there are no new entries displayed.
My fluent API of the tables orders, positions and billingDetails looks like this:
//------Relationship Orders <--> Billing Details-------
//Configure the primary key for orders (Primary key BillingDeatailID is foreign key in orders)
modelBuilder.Entity<Orders>()
.HasKey(b => b.BillingDetailID);
//one-to-one relationship
modelBuilder.Entity<BillingDetails>()
.HasRequired(b => b.Order)
.WithRequiredPrincipal(b => b.BillingDetails)
.WillCascadeOnDelete(false);
//------Relationship Products <--> Positions-------
//one-to-many relationship (a position can have one product but a product can have many positions (optional relationship); the table positions contains ProductID as a required foreign key if the relation exists)
modelBuilder.Entity<Positions>()
.HasOptional<Products>(p => p.Product)
.WithMany(p => p.Positions)
.HasForeignKey(p => p.ProductID);
//------Relationship Orders <--> Positions-------
//one-to-many relationship (a position can have one order but an order can have many positions (optional relationship); the table positions contains OrderID as a required foreign key if the relation exists)
modelBuilder.Entity<Positions>()
.HasOptional<Orders>(o => o.Order)
.WithMany(o => o.Positions)
.HasForeignKey(o => o.OrderID);
my Action for saving the data:
public ActionResult CompleteOrder()
{
//save data to database
using (var context = new OnlineShopContext())
{
BillingDetails billDetails = new BillingDetails();
Orders order = new Orders();
try
{
//save billing details
try
{
billDetails.Owner = Session["PaymentOwner"].ToString();
billDetails.CardType = (int)Session["PaymentType"];
if(Session["PaymentType"].ToString() == "0"){
billDetails.Number = Session["PaymentCreditcardNumber"].ToString();
}else{
billDetails.Number = Session["PaymentAccountNumber"].ToString();
}
billDetails.ExpiryMonth = (int)Session["PaymentExpireMonth"];
billDetails.ExpiryYear = (int)Session["PaymentExpireYear"];
billDetails.Swift = Session["PaymentSwift"].ToString();
billDetails.Blz = Session["PaymentBlz"].ToString();
billDetails.IBAN = Session["PaymentIBAN"].ToString();
context.BillingDetails.AddOrUpdate(billDetails);
context.Entry(billDetails).State = EntityState.Added;
if (context.SaveChanges() > 0)
{
//saved
}
else
{
string Msg = "Error while saving!";
return View((object)Msg);
}
}
catch (OptimisticConcurrencyException ocEx)
{
log.Fatal("OptimisticConcurrencyException while saving billing details: " + ocEx.Message);
string Msg = "Error while saving!";
return View((object)Msg);
}
//get the id of added billing details item and complete the order
var billingDetailsId = billDetails.BillingDetailID;
order.BillingDetailID = billingDetailsId;
order.DeliveryName = Session["DeliveryName"].ToString();
order.DeliveryStreet = Session["DeliveryStreet"].ToString();
order.DeliveryCity = Session["DeliveryCity"].ToString();
order.DeliveryZipCode = Session["DeliveryZipCode"].ToString();
order.DeliveryCountry = Session["DeliveryCountry"].ToString();
order.BillName = Session["BillName"].ToString();
order.BillStreet = Session["BillStreet"].ToString();
order.BillCity = Session["BillCity"].ToString();
order.BillZipCode = Session["BillZipCode"].ToString();
order.BillCountry = Session["BillCountry"].ToString();
order.OrderDate = DateTime.Now;
//save the order
try
{
context.Orders.AddOrUpdate(order);
context.Entry(order).State = EntityState.Added;
if(context.SaveChanges() > 0){
//saved
}
else{
string Msg = "Error while saving!";
return View((object)Msg);
}
}
catch (OptimisticConcurrencyException ocEx)
{
log.Fatal("OptimisticConcurrencyException while saving order: " + ocEx.Message);
string Msg = "Error while saving!";
return View((object)Msg);
}
//get id of added order
var orderId = order.OrderID;
//save all positions of this order
foreach (var item in CartItems)
{
Positions position = new Positions();
position.OrderID = orderId;
position.ProductID = item.product.ProductID;
position.Amount = item.amount;
try
{
context.Positions.AddOrUpdate(position);
context.Entry(position).State = EntityState.Added;
if(context.SaveChanges() > 0){
//saved
}
else{
string Msg = "Error while saving!";
return View((object)Msg);
}
}
catch (OptimisticConcurrencyException ocEx)
{
log.Fatal("OptimisticConcurrencyException while saving position: " + ocEx.Message);
string Msg = "Error while saving!";
return View((object)Msg);
}
}
}
catch (Exception ex)
{
log.Fatal("Error while saving order data. Exception: " + ex.Message);
string Msg = "Error while saving!";
return View((object)Msg);
}
//empty the shopping cart
RemoveAllCartItems();
//redirect to the catalog
return RedirectToAction("Index", "Products");
}
}
The IDs are incremented correctly when I check them while debugging (e.g. ID 9) and if I reproduce it one more time the IDs are incremented again (e.g. ID 10).
There are already some dummy data in the database and they do not change so it is not updating them by accident as well.
Why are my new added data not displayed if I try to display them in the IDE?
I need to convert the following Function of SQL Server To MySQL. I am new to MySQL. Help needed. the Function returns a generated Id based on the date : -S1 or S2 ..S5 : the letter S followed by the number of week on month start from monday -0X the month in two digits -15 the year in two digits
Ex :
2015/06/01 to 2015/06/07 is S10615
2015/06/29 to 2015/07/05 is S50615
2015/07/06 to 2015/07/12 is S10715
Function
Create Function GetIdPanier()
Returns Varchar(25)
As
Begin
declare @week varchar(2) = DATEPART(DAY, DATEDIFF(DAY, 0, GETDATE())/7 * 7)/7 + 1
declare @month int = case when DATEPART(DAY,GETDATE()) < 7 and @week > 1
then DATEPART(MONTH,DATEADD(MONTH,-1,GETDATE()))
else DATEPART(MONTH,GETDATE())
end
return 'S' + @week + RIGHT('00' + CAST(@month AS varchar(2)), 2) + RIGHT(CAST(DATEPART(yyyy, GETDATE()) AS varchar(4)), 2)
End
I have a table in SQL Server with 5 fields a-e with a as key field. a-e has rows/values/data. I altered the table by adding two new column f,g, they are added but empty. Now I have an excel spreadsheet containing the rows a,f,g with data in them. Now Can I update my sql server table from the spreadsheet where a(excel sheet) matches a(sqlserver table). Thank You.
My Environment: SSMS 2012. VB.NET 2013 Professional. MS-Excel 2010
have a stored Procedure that do some operation and return 1 or 0 as below.
CREATE PROCEDURE dbo.UserCheckUsername11
(
@Username nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
IF Exists(SELECT UserID FROM User WHERE username =@Username)
return 1
ELSE
return 0
END
Using linq I tried to get return value. But i am getting the output as -1 always.
Below is my Linq code :
using (PlanGenEntities3 entity2 = new PlanGenEntities3())
{
var result = entity2.Entity_test_GetHoliday();
string output = result.ToString();
}
How to solve this ?
Series_name Age value
Per_03 5.00 13.7000 Per_03 5.50 13.6000 Per_03 6.00 13.6000 Per_03 6.50 13.6000 Per_03 7.00 13.6000 Per_03 7.50 13.7000 Per_03 8 13.8000 Per_10 5.00 14.2000 Per_10 5.50 14.1000 Per_10 6.00 14.0000 Per_10 6.50 13.9000 Per_10 7.00 14.0000 Per_10 7.50 14.1000 Per_10 8.00 14.2000 Per_25 5.00 14.5000 Per_25 5.50 14.6000 Per_25 6.00 14.5000 Per_25 6.50 14.6000
I have datatable like this and i Have to make each different series name as datacolunm and their respective value into their age, series name column row-wise
I have a ssis package deployed and created a sql agent job which executes the package.I need to change the SqlStatementSource in one of the sql task in package through job step advanced tab. Can any one help me how to do that? I somewhere read its possible but not able to recall how exactly it can be done?
I need to make a complex SQL query and add row numbers to it. My query has 3 tables joined, added a custom row with union. No matter how I try, I get syntax errors, please help me finding the solution.
Main query:
select null as EAN, null as CustomsCode, ProductId as SupplierItemCode, '![CDATA['+Product.Name+']' as ItemDescription, '![CDATA['+Product.ShortDescription+']' as ItemNote, null as VATType, 'CU' as PackageType, Quantity as OrderQuantity, 'darab' as UnitOfMeasure, UnitPriceExclTax as OrderedUnitNetPrice from [Order] inner join OrderItem on [Order].Id=OrderItem.OrderId Inner join Product on OrderItem.ProductId=Product.Id where OrderId='150960' UNION select null as EAN, null as CustomsCode, '00001' as SupplierItemCode, '![CDATA[Szállítási díj]' as ItemDescription, '![CDATA[A termék postázási költsége]' as ItemNote, null as VATType, 'CU' as PackageType, '1' as OrderQuantity, 'darab' as UnitOfMeasure, OrderShippingExclTax as OrderedUnitNetPrice from [Order] Where [Order].Id='150960'
I need to add rank() to this table without getting same numbers as row number My version was:
select Row_Number() OVER (Order by ProductID) as LineNumber, null as EAN, null as CustomsCode, ProductId as SupplierItemCode, '![CDATA['+Product.Name+']' as ItemDescription, '![CDATA['+Product.ShortDescription+']' as ItemNote, null as VATType, 'CU' as PackageType, Quantity as OrderQuantity, 'darab' as UnitOfMeasure, UnitPriceExclTax as OrderedUnitNetPrice from [Order] inner join OrderItem on [Order].Id=OrderItem.OrderId Inner join Product on OrderItem.ProductId=Product.Id where OrderId='150960' UNION select Row_Number() OVER (Order by Id) as LineNumber, null as EAN, null as CustomsCode, '00001' as SupplierItemCode, '![CDATA[Szállítási díj]' as ItemDescription, '![CDATA[A termék postázási költsége]' as ItemNote, null as VATType, 'CU' as PackageType, '1' as OrderQuantity, 'darab' as UnitOfMeasure, OrderShippingExclTax as OrderedUnitNetPrice from [Order] Where [Order].Id='150960'
Which resulted row numbers: 1,1,2, I got same result with Rank()
Can anyone help?
I have this piece of script:
Create Table AA (ID int identity(1,1), Col1 varchar(10))
Create Table BB (ID int identity(1,1), Col1 varchar(10))
GO
Create proc p6
as
insert into AA
(Col1)
Values('')
GO
Create Trigger [dbo].[TR_AA] on [dbo].[AA]
After insert
As
--Set XACT_Abort off
Select 1/0
GO
Begin Try
Begin Tran
Select @@TRANCOUNT
exec p6
Commit Tran
End Try
Begin Catch
insert into BB(Col1)Values('')
Select * from AA
--Select XACT_STATE()
Rollback Tran
End Catch
Select Count(*) from AA
GO
When I run this code i am getting this error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I already know what causes this issue. The example is just an example. But I have lots of Business logics inside the trigger that I can't move them out. So one workaround would be to put Set XACT_Abort off at the beginning of trigger. However, by doing that we override the default behaviour of SQL dealing with triggers. My question is if I do that does it expose any issue to the system? Any other solution except for stripping the trigger off logic would be greatly appreciated.
Ta.
What I am trying to accomplish in one shot is update else insert data into 2 tables matching a primary key on one table, and the secondaryID on another. The collection of the initial data will have multiple rows that have the same secondaryID. I want most of the latest data. If it can be added to allow me to do functions on update at the same time, that would be awesome.
USE [TestDB]
GO
--DELETING CREATED FUNCTIONS, TABLES, AND TYPES
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_Proc_1' AND [TYPE] IN (N'P',N'PC'))
BEGIN
DROP PROCEDURE dbo.sp_Proc_1;
END
IF EXISTS (SELECT 1 FROM sys.types WHERE is_user_defined = 1 AND is_table_type = 1 AND name = 'My_Table_Type_1')
BEGIN
DROP TYPE dbo.My_Table_Type_1;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table_Data'))
BEGIN
DROP TABLE Table_Data;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table_1'))
BEGIN
DROP TABLE Table_1;
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table_2'))
BEGIN
DROP TABLE Table_2;
END
/****** Object: Table [dbo].[Table_1] Script Date: 6/28/2015 6:15:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--CREATE SAMPLE TABLE WITH DATA
CREATE TABLE [dbo].[Table_Data](
[id] [int] IDENTITY(1,1) NOT NULL,
[secondaryID] [varchar](50) NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [int] NULL
CONSTRAINT [PK_Table_Data] PRIMARY KEY CLUSTERED
(
[id] 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
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('1234','Mickey','Magic Kingdom',1);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('1234','Goofy','Epcot',1);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('1234','Minnie','Disney',2);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('5678','Toy Story','Universal Studios',4);
INSERT INTO Table_Data (secondaryID,col1,col2,col3) VALUES ('5678','Willie','Sea World',5);
--FIRST TABLE FOR DATA TO BE COPIED INTO BY ID
CREATE TABLE [dbo].[Table_1](
[id] [int] NULL,
[secondaryID] [varchar](50) NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [int] NULL
) ON [PRIMARY]
GO
--SECOND TABLE THAT HAS SECONDARY ID AS "UNIQUE ID"
CREATE TABLE [dbo].Table_2(
[secondaryID] [varchar](50) NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [int] NULL
) ON [PRIMARY]
GO
--CREATE TABLE TYPE (FOR APPLICATION)
CREATE TYPE dbo.My_Table_Type_1 AS TABLE(
[id] [int] NULL,
[secondaryID] [varchar](50) NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [int] NULL
)
GO
--CREATE STORED PROCEDURE FOR MULTI MERGE AND MULTI ROW (UPDATE ELSE INSERT)
CREATE PROCEDURE dbo.sp_Proc_1
@myTBL dbo.My_Table_Type_1 READONLY
AS
BEGIN
MERGE dbo.Table_1 AS Target
USING @myTBL AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
UPDATE SET
Target.secondaryID = Source.secondaryID,
Target.col1 = Source.col1,
Target.col2 = Source.col2,
Target.col3 = Source.col3
WHEN NOT MATCHED THEN
INSERT
(id,secondaryID,col1,col2,col3)
VALUES
(Source.id,Source.secondaryID,Source.col1,Source.col2,Source.col3);
MERGE dbo.Table_2 AS Target
USING @myTBL AS Source
ON Target.secondaryID = Source.secondaryID
WHEN MATCHED THEN
UPDATE SET
Target.col1 = Source.col1,
Target.col2 = Source.col2,
Target.col3 = Target.col3 + Source.col3
WHEN NOT MATCHED THEN
INSERT
(secondaryID,col1,col2,col3)
VALUES
(Source.secondaryID,Source.col1,Source.col2,Source.col3);
END
GO
SET ANSI_PADDING OFF
GO
--GET DATA AND EXECUTE PROC (SIMULATE APPLICATION)
DECLARE @tbl My_Table_Type_1
INSERT INTO @tbl
SELECT * FROM Table_Data;
EXECUTE sp_Proc_1 @myTBL = @tbl
--FIRST SELECT IS CORRECT, should list every value
SELECT * FROM Table_1;
--SECOND SELECT IS ***NOT*** CORRECT
SELECT * FROM Table_2;
--I WANT THIS DATA TO SHOW 2 ITEMS
-- 1234 Minnie Disney 4
-- 5678 Willie Sea World 9
Is it possible to use Rank in a Where Clause. Below is the code that I am intending to use
Select
DebtorID
,Rank () over (partition by DebtorID order by BalanceDate) as RankBalanceDate
,BalanceDate
,Balance
,UnallocatedBalance
,Overdue
From Debtorbalances
Where Rank () Over (Partition by DebtorID order by BalanceDate) = 1
I have problem with my query in sql server 2012
UPDATE STUDENTS SET نام = N'%[احسان]%' WHERE نام = N'%[علی]%';
I used this but result is 0 row affected . I have column نام and other columns with Farsi names .
My Table has 3 columns - OriginalDoc, ChildDoc and Fee. For each Parent, Based on their number of Children or GrandChildren - Parent will be charged.
Each Child Doc is charged as $0.40
From the below example,
DocId : AB12456 has one Child Doc and so Fee is $0.40
Doc Id : CY12345 has 7 Children/GrandChildren Doc and so Fee is $2.80
Docs such as NX23410 and NY23422 are not eligible to be charged. Because their Parent Doc # CY12345 is charged for all the children / grand children.
DECLARE @Sample TABLE ( OriginalDoc VARCHAR(255), ChildDoc VARCHAR(255), Fee MONEY );
INSERT INTO @Sample
VALUES ( 'AB12456', 'NX12450', NULL ),
( 'CY12345', 'NX23410', NULL ),
( 'CY12345', 'NX23421', NULL ),
( 'CY12345', 'NX23432', NULL ),
( 'NX23410', 'NY23411', NULL ),
( 'NX23410', 'NY23422', NULL ),
( 'NY23422', 'NZ23411', NULL ),
( 'NY23422', 'NZ23422',NULL);
How to write a Hierarchy SQL Query without Hard Coding any Doc Id's? We will be passing Input Parameter as DocId.
Ok, so I am using hostgator and my normal function to get the insert id is not working and they assure me that its a code problem. I use these functions at work on MS SQL with no issues at all. So I have to find a work around.
What I would like to know is, is there any issue using two separate queries to get the inserted ID? If another user makes an insert into the same table in between those two queries, will there be issues? Please see my functions below.
This is my normal function to get the inserted id. (Which returns 0 every time on host gator shared server. The insert works properly. But the sqlsrv_get_fiedl returns false every time.)
/**
* Inserts the sql query and returns the ID of the Row Inserted.
*
* @param string $IncomingSql The sql query that you want to execute.
* @return int/string returns the ID of inserted row, or 0 if no result.
*/
function dbInsert($_IncomingSql)
{
$sql=$_IncomingSql.'; SELECT SCOPE_IDENTITY();';
$Result=dbQuery($sql);
sqlsrv_next_result($Result);
sqlsrv_fetch($Result);
$stmt = sqlsrv_get_field($Result, 1);
if($stmt >0)
{
return $stmt;
}
else {
return 0;
}
}
This is the function I am having to use to get the inserted ID.
function dbInsert($_IncomingSql)
{
$sql=$_IncomingSql.'; SELECT SCOPE_IDENTITY();';
$Result=dbQuery($sql);
$stmt = dbStr('SELECT SCOPE_IDENTITY();');
if($stmt >0)
{
return $stmt;
}
else {
return 0;
}
}
The functions that are used in the above functions.
/**
* This function is designed to catch SQL errors and dispese to the appropriate channels.
* It can send error emails or display on screen at the time of error.
* All functions accessing the database need to go through this function in order to catch errors in a standardized way for all pages.
*
* @param string $_IncomingSql The sql query that you want to execute.
* @Param string $_Cursor OPTIONAL PARAMETER - This is the cursor type for scrolling the result set.
* @Return resource/bool
*/
function dbQuery($_IncomingSql)
{
$Result=sqlsrv_query(CONN, $_IncomingSql);
//Catch sql errors on query
if($Result===false) {
if(($errors=sqlsrv_errors())!=null) {
CatchSQLErrors($errors, $_IncomingSql);
}
}
return $Result;
}
/**
*
* Executes the $ImcomingSql query and returns the first cell in the first row
*
* @param string $_IncomingSql The sql query that you want to execute.
* @param string $_DefaultValue The value to return if null.
*/
function dbStr($_IncomingSql, $_DefaultValue=0)
{
$Result=dbQuery($_IncomingSql);
if($Result!=0) {
$Rows=sqlsrv_has_rows($Result);
if($Rows) {
$Row=sqlsrv_fetch_array($Result, SQLSRV_FETCH_NUMERIC);
$Result=$Row[0];
return $Result;
}
}
return $_DefaultValue;
}
I have a function called dbInsert() that I use at work on our own windows servers with MS SQL. It works great to get the last inserted row ID. But when I use it on host gator shared plan, it always returns 0. I literally copy and pasted the functions.
Can anyone see issues with my functions that might effect its cross server compatibility?
We are using MS SQL 2003 at work and 2008 R2 on HostGator.
Does anyone know of limitations on HostGator shared plans? I spoke to them and they say this is a coding issue and they dont help with that. Its really annoying, I have tried for 3 days, my site build is on hold until I get this figured out. Also, no log errors.
I have found a work around doing the select Scope_identity and insert in two completely separate queries, but It scares me that there might be another insert by another user in between those two taking place.
Here are the relevant functions.
Insert:
/**
* Inserts the sql query and returns the ID of the Row Inserted.
*
* @param string $IncomingSql The sql query that you want to execute.
* @return int/string returns the ID of inserted row, or 0 if no result.
*/
function dbInsert($_IncomingSql)
{
$sql=$_IncomingSql.'; SELECT SCOPE_IDENTITY();';
$Result=dbQuery($sql);
sqlsrv_next_result($Result);
sqlsrv_fetch($Result);
$stmt = sqlsrv_get_field($Result, 0);
if($stmt >0)
{
return $stmt;
}
else {
return 0;
}
}
dbQuery:
/**
* This function is designed to catch SQL errors and dispese to the appropriate channels.
* It can send error emails or display on screen at the time of error.
* All functions accessing the database need to go through this function in order to catch errors in a standardized way for all pages.
*
* @param string $_IncomingSql The sql query that you want to execute.
* @Param string $_Cursor OPTIONAL PARAMETER - This is the cursor type for scrolling the result set. More Info: http://ift.tt/1IEfih6
* @Return resource/bool
*/
function dbQuery($_IncomingSql)
{
$Result=sqlsrv_query(CONN, $_IncomingSql);
//Catch sql errors on query
if($Result===false) {
if(($errors=sqlsrv_errors())!=null) {
CatchSQLErrors($errors, $_IncomingSql);
}
}
return $Result;
}
CatchSQLErrors:
function CatchSQLErrors($errors, $_IncomingSql)
{
foreach($errors as $error)
{
//error display
$Path='http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
$Err='<strong>SQL ERROR<br/></strong>'.Format($_IncomingSql).'<br /><span style="font-weight: 600;">Error: </span> '.$error['message'];
if(ON_SCREEN_ERRORS===TRUE) {
err($Err);
}
$Err=Format($_IncomingSql).'<br /><span style="font-weight: 600;">Error: </span> '.$error['message'];
if(SEND_ERROR_EMAILS===TRUE) {
gfErrEmail($Err, $Path, 'SQL Error');
}
}
return 0;
}
Hello everyone I am trying to create and save XML with values from SQL Server database.
My code basically creates an XML string filled with the variables that I get from several SQL queries (I need to fetch data from many tables to complete the XML)
I am using SqlDataReader to fetch values for my variables.
Since my XML will be kind of long and complicated, I decided to put the final XML strings from a few sub-strings according to the queries, basically like:
finalxml string = startxml + headerxml + buyerxml + sellerxml + linesxml + summaryxml
I was all OK till I reached a point, where a SQL query returned more than one row as a result.
Here is my code-behind:
string orderdetails = "select ProductId, Quantity from [Order] inner join OrderItem on [Order].Id=OrderItem.OrderId where OrderId='" + orderID + "'";
SqlCommand com3 = new SqlCommand(orderdetails, dbConn);
dbConn.Open();
using (SqlDataReader orderitem = com3.ExecuteReader())
while (orderitem.Read())
{
string orderxml = "<Order>" + "<OrderItem>" + orderitem.GetInt32(0).ToString() + "</OrderItem>" + "<OrderItem>" + orderitem.GetInt32(1).ToString() + "</OrderItem>" + "</Order>";
Label20.Text = orderxml;
string termid = orderitem.GetInt32(0).ToString();
string termmenny = orderitem.GetInt32(1).ToString();
}
dbConn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string headerxml = "<Order-Header>" +
"<DocumentType> számla </DocumentType>" +
"<OrderNumber>" + rendSz + "</OrderNumber>" +
"<OrderDate>"+ datum +"</OrderDate>" +
"<ExpectedDeliverydate> " + szallido + "</ExpectedDeliverydate>"+
"<PaymentMethod>"+ fizmod +"</PaymentMethod>"+
"<Remarks><![CDATA[" + txtFirstName.Text + "]]></Remarks>"+
"<Note><![CDATA[" + txtLastName + "]]></Note>" +
"<PreviousOrderNumber><![CDATA[]]></PreviousOrderNumber>" +
"</Order-Header>";
string vevoxml = "<Buyer>" +
"<ILN>435</ILN>" +
"<Name>"+ vezeteknev +" "+ keresztnev +"</Name>" +
"<City>" + vevovaros + "</City>" +
"<ZIP>" + irszam + "</ZIP>" +
"<Address>" + vevocim1 + vevocim2 + "</Address>" +
"<E-mail>" + vevoemail + "</E-mail>" +
"<Telefon>" + vevotel + "</Telefon>" +
"<Contact>" + "nincs" + "</Contact>" +
"<DeliveryName>" + szallkeresztnev +" "+ szallvezeteknev + "</DeliveryName>" +
"<DeliveryCity>" + szallcimvaros + "</DeliveryCity>" +
"<DeliveryZIP>" + szallcimirszam + "</DeliveryZIP>" +
"<DeliveryAddress>" + szallvevocim1 + szallvevocim2 + "</DeliveryAddress>" +
"</Buyer>";
string eladoxml = "<Seller>" +
"<ILN />" +
"<Name>E-Szoftver Kft.</Name>" +
"<City>Budapest</City>" +
"<ZIP>1195</ZIP>" +
"<Address>Ady Endre út 97-99. F/04.</Address>" +
"<E-mail>laszlo@marsalsoft.hu</E-mail>" +
"</Seller>";
string linexml = "<Line>" +
"<Line-Item>" +
"<LineNumber>1</LineNumber>" +
"<EAN />" +
"<SupplierItemCode />" +
"<CustomsCode>5829</CustomsCode>" +
"<ItemDescription><![CDATA[E-Számla szoftver frissítési és jogkövetési díj 1 évre]]></ItemDescription>" +
"<ItemNote><![CDATA[]]></ItemNote>" +
"<VATType>27</VATType>" +
"<PackageType>CU</PackageType>" +
"<OrderedQuantity>1</OrderedQuantity>" +
"<UnitOfMeasure>év</UnitOfMeasure>" +
"<OrderedUnitNetPrice>13200</OrderedUnitNetPrice>" +
"</Line-Item>" +
"</Line>";
string strMyXml = "<?xml version=\"1.0\" encoding=\"windows-1250\"?> " +
"<Document-Order>"+
headerxml +
"<Order-Parties>" +
vevoxml +
eladoxml +
"</Order-Parties>"+
"<Order-Lines>" +
linexml +
"</Order-Lines>" +
"<Order-Summary>" +
"<TotalLines>1</TotalLines>" +
"<TotalOrderedAmount>1</TotalOrderedAmount>" +
"<TotalNetPrice>13200</TotalNetPrice>" +
"<TotalVat>3564</TotalVat>" +
"<TotalGross>16764</TotalGross>" +
"</Order-Summary>" +
"</Document-Order>";
XmlDocument xDoc = new XmlDocument();
xDoc.LoadXml(strMyXml);
xDoc.Save(Server.MapPath("//orders//szamla.xml"));
}
Now the lineXML part is my problem, it describes the products, that is included in the order. Every xml describes 1 order, but 1 order can contain 3 products, and in that case I need to create a line for every product with a few elements: number, quantity, net price, gross price.
Is it possible with SqlDataReader and variable added to the string, or do I need to use some other way, like dataset generated from the query results?
I found a lot of info about generating xml from dataset, and generating xml with string, but none of them contained info about this particular situation, generating XML from SQL Server where you need to use multiple queries.
Luckily my code is modular, so if it can be done with dataset only, I don't need to touch the other parts of the xml hopefully. Any help would be appreciated.
I am getting the error message - Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ','. when I run the below query. I can't figure out why the syntax is incorrect. Does anyone have any suggestions?
DECLARE @Vendor AS INT = 41
CREATE TABLE #PivotData
(
[ID] INT,
[Row] VARCHAR(MAX),
[Columns] VARCHAR(MAX),
[AggData] INT
)
INSERT INTO #PivotData
SELECT V.Vendor_Key
,O.Location_Desc
,P.Item_Desc
,IIF.Sales_Qty
FROM PDI.PDI_Warehouse_952_01.dbo.Item_Inventory_Fact IIF
INNER JOIN PDI.PDI_Warehouse_952_01.dbo.Item_Purchases_Fact IPF
ON IIF.Calendar_Key = IPF.Calendar_Key
AND IIF.Organization_Key = IPF.Organization_Key
AND IIF.Product_Key = IPF.Product_Key
INNER JOIN PDI.PDI_Warehouse_952_01.dbo.Organization O
ON IIF.Organization_Key = O.Organization_Key
INNER JOIN PDI.PDI_Warehouse_952_01.dbo.Product P
ON IIF.Product_Key = P.Product_Key
INNER JOIN PDI.PDI_Warehouse_952_01.dbo.Vendor V
ON IPF.Vendor_Key = V.Vendor_Key
WHERE V.Vendor_key = @Vendor
DECLARE @Cols AS NVARCHAR(MAX),
@Query AS NVARCHAR(MAX)
SET @Cols = STUFF ((SELECT DISTINCT ',' + QUOTENAME(P.Columns)
FROM #PivotData P
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Query = 'SELECT Row, ' + @Cols + ' FROM
(
SELECT Row
,Column
,Aggregate
FROM #PivotData
)x
PIVOT
(
SUM(Aggregate)
FOR Row IN (' + @Cols + ')
) p '
EXECUTE(@Query)
DROP TABLE #PivotData
My database has employee id, name, Email, address, and phoneno. I have used a dropdownlist for selection of employee id, for this I wrote this code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class Employeedetails : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=REVATI-PC;Initial Catalog=Test_Database;Integrated Security=True");
SqlCommand cmd;
SqlDataAdapter da;
string query;
protected void Page_Load(object sender, EventArgs e)
{
con.Open();
query = "select Employee_ID from Employee";
cmd = new SqlCommand(query, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DropDownList1.Items.Add(dr[0].ToString());
}
con.Close();
but by clicking on those particular id, I did not get other information. For this I used SelectIndexchanged event and write a select query
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string query = "select Employee_ID from Employee where Employee_ID='" + DropDownList1.SelectedValue.ToString() + "'";
con.Open();
}
But it's not working
When I want to receive 3 values from the database.
16.5
15.5
15.5
With this code:
public static double calculateTotalPrice(int reserveringId)
{
double total=0;
int i = 0;
SqlCommand comm = sqlCrud.returnSqlCommand("select g.prijs from gerechten g inner join besteld b on b.gerechtId=g.gerechtId where b.reserveringId='"+reserveringId+"'");
SqlDataReader dt = comm.ExecuteReader();
if (dt.HasRows)
{
while (dt.Read())
{
total += dt.GetDouble(i);
i++;
}
}
return total;
}
It's telling me:
Additional information: Index was outside the bounds of the array.
What am I doing wrong?
Thanks
What I want to do is take the $res (result of my query) and make an $results array with the keys and values in that row. The query gets the row with the username that is the same as the one they entered in. The username is the primary key of the table.
<?php
/**
* Created by PhpStorm.
* User: TheWolfBadger
* Date: 6/28/15
* Time: 1:12 PM
*/
//MySQL Login
$fig = parse_ini_file('config.ini', true);
$host = $fig['sql']['host'];
$username = $fig['sql']['username'];
$password = $fig['sql']['password'];
$dbname = $fig['sql']['dbname'];
$port = $fig['sql']['port'];
$socket = $fig['sql']['socket'];
$con = new mysqli($host, $username, $password, $dbname, $port, $socket);
// User Functions:
function login($username, $password) {
global $con;
$prepared = $con->prepare("SELECT * FROM credentials WHERE username = '$username';");
if($prepared) {
$query = $prepared->execute();
$res = mysql_result($query, 0); // $res[0] => username $res[2] => password $res[3] => dev $res[4] => admin $res[5] => Email $res[6] => Name $res[7] User_Image
// How to do the above?
if($res[1] == $password) {
$_SESSION['username'] = $username;
$_SESSION['password'] = $password;
if($res[2]) {
$_SESSION['dev'] = true;
}
if($res[3]) {
$_SESSION['admin'] = true;
}
return true;
}
}
return false;
}
function register($username, $password, $email, $user_image) {}
function user_logged_in() {
if(!is_null($_SESSION['username'])) {
return true;
}
return false;
}
function is_admin() {
if(!is_null($_SESSION['admin'])) {
return true;
}
return false;
}
function is_dev() {
if(!is_null($_SESSION['dev'])) {
return true;
}
return false;
}
I need to write a Transact-SQL script in which I am creating 2 tables, Test and Error_log.
Test contains the following columns:
Error_log contains the following columns:
I need to write a stored procedure which will insert values into the Test table (for example 1, 1) and if I were to run it again, it would see that there is a duplicate record and then insert it into the Error_log table instead with an error description (hoping this is built in).
Any help would be greatly appreciated as I am a complete novice to SQL Server.
What do you think is the best way of updating an existing SQL Server (we are using SQL Server 2014, but could update to 2016) database schema (incl its data) with zero downtime of the overall system, i.e. applications using the database?
The business requirements is to have a zero downtime of all the applications and services using the database. We could say we only do backwards and forward compatible database schema changes, e.g. adding columns, but not removing existing columns.
Of course the business would like to have a backup before we do the database change, in case something goes really wrong and we need to rollback.
The system is transactions heavy, meaning potentially thousands of transactions per second.
The applications are .net applications, where most of them run in an IIS execution container at the moment (maybe we switch to some other form like self-hosted service etc.) and exopsing their functionality through Web Services.
What would be your approach?
I have two products, called A and B. Both products share some setting lets say X. Product A runs a timer that perform some action each X second. In product B, the user has the ability to change this parameter X. How can I make product A to know about the changes of the setting made in B? The only thing in common for product A and B are a Microsoft SQL Server database (where the settings will be written to from B as well).
I need some method/architecture that B can notify A when there are changed settings in the database, there are a lot of settings like X also, so should be a general solution.
Thank you!
I'm struggling to understand how the following two queries could be blocking each other.
Running query (could be almost anything though): insert bulk [Import].[WorkTable] ...
I'm trying to run the following SELECT query at the same time:
SELECT *
FROM ( SELECT * FROM @indexPart ip
JOIN sys.indexes i (NOLOCK)
ON i.object_id = ip.ObjectId
and i.name = ip.IndexName) i
CROSS
APPLY sys.dm_db_index_physical_Stats(db_id(), i.object_id,i.index_id,NULL,'LIMITED') ps
WHERE i.is_disabled = 0
The second query is blocked by the first query and shows a LCK_M_IS as wait info. Import information is that the temporary table @indexPart contains one record of an index on a completely different table. My expectation is that the cross apply tries to run the stats on that one index which has nothing to do with the other query running.
Thanks
EDIT (NEW):
After several more tests I think I found the culprit but again can't explain it.
I hope someone can explain this to me ...
i'm trying to do some DMX query on MM using SQL server 2012 management studio my query is
SELECT Flattened PREDICT([Orders Details],2)
FROM [MM]
NATURAL PREDICTION JOIN
(SELECT (select 'Milk' as [item]
union
select 'Bread' as [item] )
AS [Orders Details] ) As T
but i get this error Error (Data mining): Either the user, xxxx-PC\xxxx, does not have permission to access the referenced mining model or structure, MM, or the object does not exist.
I'm trying to return the LastUpdateDate based on a ParentID. This date can come from any of it's child tables. Let's say there are two child tables. Child one has a one to one relationship with the Parent and child two has a many to many relationship. See test tables below. Any help would be appreciated.
Parent Table
ParentID Name LastUpdateDate
1 Parent John 2014-06-26
2 Parent Mark 2004-07-27
3 Parent Bob 2009-04-07
4 Parent Jo 2014-09-26
ChildOne Table (1-1 Relationship)
ChildOneID ParentID Name LastUpdateDate
10 1 FirstChild Tom 2011-03-12
20 2 FirstChild David 2014-08-11
30 3 FirstChild Sally 2009-04-06
40 4 FirstChild Jane 2014-11-26
ChildTwo Table (many - many Relationship)
ChildTwoID ParentID Name LastUpdateDate
100 1 SecondChild Phil 2014-03-12
200 2 SecondChild Smith 2012-08-10
300 3 SecondChild Paul 1999-04-06
400 4 SecondChild Ed 2010-11-26
500 2 SecondChild Donna 2010-08-10
600 4 SecondChild Melissa 2008-10-16
Employees Table:
EmpID EmpName
----- --------------
1 John Torres
2 Irina Williams
Payroll Week Table:
WeekID EmpID WeekStart WeekEnd
------ ----- ---------- ----------
1 1 11-20-2011 11-26-2011
2 2 11-27-2011 12-03-2011
3 1 11-27-2011 12-03-2011
Employee Visits Table:
ID EmpID VisitDate StartTime EndTime Earningcode
-- ----- ---------- --------- ------- -----------
1 1 11-20-2011 10:00 12:00 Sat-Sun1
2 1 11-21-2011 13:30 16:00 Mon-Fri1
3 1 11-22-2011 14:00 15:00 Mon-Fri1
4 1 11-24-2011 10:00 14:00 Mon-Fri1
5 1 11-25-2011 13:30 16:00 Mon-Fri1
6 1 11-26-2011 14:00 15:00 Sat-Sun1
7 2 11-27-2011 09:00 11:00 Sat-Sun1
8 2 11-28-2011 07:00 12:00 Mon-Fri1
9 2 11-29-2011 09:00 11:00 Mon-Fri1
10 2 12-03-2011 07:00 12:00 Sat-Sun1
Expected Results
RecordType EmpID EmpName WeekStart WeekEnd Earning code Hours
---------- ----- -------------- ---------- ---------- ------------ -----
H 1 John Torres 11-20-2011 11-26-2011
D Sat-Sun1 3.00
D Mon-Fri1 10.00
H 2 Irina Williams 11-27-2011 12-03-2011
D Sat-Sun1 7.00
D Mon-Fri1 7.00
I would like some advice on the best way to go about doing this. I have multiple files all with different layouts and I would like to create a procedure to import them into new tables in sql.
I have written a procedure which uses xp_cmdshell to get the list of file names in a folder and the use a cursor to loop through those file names and use a bulk insert to get them into sql but I dont know the best way to create a new table with a new layout each time.
I thought if I could import just the column row into a temp table then I could use that to create a new table to do my bulk insert into. but I couldn't get that to work.
So whats the best way to do this using SQL? I am not that familiar with .net either. I have thought about doing this in SSIS, I know its easy enough to load multiple files which have the same layout in SSIS but can it be doe with variable layouts?
thanks
I am building an application with hibernate 4.3.10. I have setup each and everything but connection between database is not establishing.here is my configuration file.
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://ift.tt/1fnOghG">
<hibernate-configuration>
<session-factory>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.url">jdbc:http://sqlserverlocalhost;databaseName=HibernatePractice</property>
<property name="hibernate.connection.username">NAWAL\SQLEXPRESS</property>
<property name="hibernate.connection.password"></property>
<property name="hibernate.hbm2ddl.auto">create</property>
<property name="hibernate.connection.pool_size">1</property>
<property name="hibernate.current_session_context_class">thread</property>
</session-factory>
</hibernate-configuration>
My hibernateUtil file
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
public class HibernateUtil {
private static SessionFactory sessionFactory;
private static ServiceRegistry serviceRegistry;
private static SessionFactory setSession() {
try {
// Create the SessionFactory from hibernate.cfg.xml
Configuration configuration = new Configuration();
configuration.configure();
StandardServiceRegistryBuilder serviceRegistryBuilder = new StandardServiceRegistryBuilder();
serviceRegistryBuilder.applySettings(configuration.getProperties());
ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();
return sessionFactory = configuration.buildSessionFactory(serviceRegistry);
}
catch (Throwable ex) {
// Make sure you log the exception, as it might be swallowed
System.err.println("Initial SessionFactory creation failed.");
throw new ExceptionInInitializerError(ex);
}
}
public static SessionFactory getSessionFactory() {
sessionFactory = setSession();
return sessionFactory;
}
}
and i am getting this error.
Jun 28, 2015 5:27:29 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
Jun 28, 2015 5:27:29 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.3.10.Final}
Jun 28, 2015 5:27:29 PM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
Jun 28, 2015 5:27:29 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
Jun 28, 2015 5:27:29 PM org.hibernate.cfg.Configuration configure
INFO: HHH000043: Configuring from resource: /hibernate.cfg.xml
Jun 28, 2015 5:27:29 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: HHH000040: Configuration resource: /hibernate.cfg.xml
Jun 28, 2015 5:27:29 PM org.hibernate.cfg.Configuration doConfigure
INFO: HHH000041: Configured SessionFactory: null
Jun 28, 2015 5:27:29 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!)
Jun 28, 2015 5:27:29 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000401: using driver [com.microsoft.sqlserver.jdbc.SQLServerDriver] at URL [jdbc:http://sqlserverlocalhost;databaseName=HibernatePractice]
Jun 28, 2015 5:27:29 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000046: Connection properties: {user=NAWAL\SQLEXPRESS, password=****}
Jun 28, 2015 5:27:29 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000006: Autocommit mode: false
Jun 28, 2015 5:27:29 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
INFO: HHH000115: Hibernate connection pool size: 1 (min=1)
Initial SessionFactory creation failed.
Exception in thread "main" java.lang.ExceptionInInitializerError
at HibernateUtil.setSession(HibernateUtil.java:25)
at HibernateUtil.getSessionFactory(HibernateUtil.java:31)
at Actions.main(Actions.java:8)
Caused by: org.hibernate.exception.SQLGrammarException: Error calling Driver#connect
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:118)
at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:140)
at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:58)
at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:75)
at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:106)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260)
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94)
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845)
at HibernateUtil.setSession(HibernateUtil.java:19)
... 2 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'NAWAL\SQLEXPRESS'. ClientConnectionId:1b0c2212-8381-4e47-a49a-b6a6c886e6c1
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55)
... 15 more
I have added all the files of required folder of hibernate distribution bundle, sqljdbc4.jar as well. Plus i wanna know that do i have to build database and tables in sql server management myself or this application will build itself? I am new to hibernate.
I am trying to create a stored procedure to validate a user(login), everything is working except for when the user enters an email which is NOT registered.You see i have handled that exception wherein if the count of email address in the table is 0,it should give out :
Select 0 as AccountLocked,0 as Authenticated,0 as RetryAttempts,0 as Registered
and if it is not 0,it will execute the next steps.The problem is when the enter an email which is not registered,it gives me back two sets of results which are as below :
Select 0 as AccountLocked,0 as Authenticated,0 as RetryAttempts,0 as Registered
and
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
The complete proc is given for reference.What am i missing here? why is it giviing me the second result too which i dont want?
Alter proc spValidateUser
@EmailAdd nvarchar(30),
@Password nvarchar(20)
as
begin
Set Nocount on;
Declare @UserId nvarchar(10),@LastLogin datetime,@RoleId int,@AccountLocked bit,@RetryCount int,@Count int
Select @Count=Count(EmailAdd) from tblAllUsers
where EmailAdd=@EmailAdd
if(@Count = 0)
begin
Select 0 as AccountLocked,0 as Authenticated,0 as RetryAttempts,0 as Registered
end
else
Select @AccountLocked=IsLocked from tblAllUsers where EmailAdd=@EmailAdd
----if account is already locked------
if(@AccountLocked = 1)
begin
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
end
else
begin
-----check if username and password match-----
Select @UserId = UserId, @LastLogin=LastLogin, @RoleId=RoleId
from tblAllUsers where EmailAdd=@EmailAdd and Password=@Password
----if match found--------
If @UserId is not null
Begin
Update tblAllUsers
SET LastLogin= GETDATE(),RetryAttempts=0 WHERE UserId=@UserId
Select @UserId [UserId],
(Select Role from tblRoles where RoleId=@RoleId) [Roles],0 as AccountLocked,1 as Authenticated,0 as RetryAttempts,1 as Registered
End
Else
------if match not found--------
Begin
Select @RetryCount=ISNULL(RetryAttempts,0) from tblAllUsers where EmailAdd=@EmailAdd
Set @RetryCount=@RetryCount+1
if(@RetryCount<=3)
Begin
----if retry attempts are not completed------
Update tblAllUsers Set RetryAttempts=@RetryCount where EmailAdd=@EmailAdd
Select 0 as AccountLocked,0 as Authenticated,@RetryCount as RetryAttempts,1 as Registered
End
Else
Begin
------if retry attempts are completed--------
Update tblAllUsers Set RetryAttempts=@RetryCount,IsLocked=1,LockedDateTime=GETDATE()
where EmailAdd=@EmailAdd
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
End
End
End
End
Edit: Looks like it is executing the below code too :
Begin
------if retry attempts are completed--------
Update tblAllUsers Set RetryAttempts=@RetryCount,IsLocked=1,LockedDateTime=GETDATE()
where EmailAdd=@EmailAdd
Select 1 as AccountLocked,0 as Authenticated,0 as RetryAttempts,1 as Registered
End
but why should it execute the above when the emails dont match?
I am reading white paper on statistics,but i am not able to get answers to few questions
In Summary
dbcc show_Statistics ('stats_update','PK__stats_up__3213E83FCCAB6EF5') is not getting populated.Below is the total code to reproduce
create table stats_update
(
id int identity(1,1) primary key,
name char(20) default 'a'
)
--check for stats
sp_helpstats 'stats_update'
--insert two values
insert into stats_update
default values
go 2
--check for stats
sp_helpstats 'stats_update','all'
select * from stats_update
--lets create an index now on name
create index nci_test on dbo.stats_update(name)
--check for stats
sp_helpstats 'stats_update','all'
--lets see when we stats last updated
dbcc show_Statistics ('stats_update','PK__stats_up__3213E83FCCAB6EF5')
--now lets insert some rows
insert into stats_update
default values
go 200
--lets check stats again
--check for stats
sp_helpstats 'stats_update','all'
dbcc show_Statistics ('stats_update','PK__stats_up__3213E83FCCAB6EF5')
select * from sys.stats where object_id=object_id('stats_update')
insert into stats_update
default values
go 2000
select * from stats_update
select * from stats_update where id=10
Hello Everyone I'm new in here. I am currently making an asp.net project monitoring module. At this moment I am in the process of editing the project form and adding resources to the selected task in a project.
I'm having a problem in saving the record. Everytime I save the record it says "Column name or number of supplied values does not match table definition."
In my ProjectTasks Table I have RefNo(PK), TaskID(FK), Name and Description
Name - refers to the Task Name
Description - refers to the Task Description
What I want to happen is that my Resource Tables TaskID(FK) will be updated when I clicked the save button. As of now when I add a Resource from a task the TaskID = 0.
protected void btnSave_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO ProjectTasks VALUES (@Name, @Description); " +
"SELECT TOP 1 TaskID FROM ProjectTasks ORDER BY TaskID DESC;";
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Description", txtDescription.Text);
int taskID = (int)cmd.ExecuteScalar();
con.Close();
con.Open();
cmd.CommandText = @"UPDATE Resource_Materials SET TaskID=@TaskID WHERE TaskID=0; " +
"UPDATE Resource_Equipments SET TaskID=@TaskID WHERE TaskID=0; " +
"UPDATE Resource_Vehicles SET TaskID=@TaskID WHERE TaskID=0; " +
"UPDATE Resource_Contractors SET TaskID=@TaskID WHERE TaskID=0;";
cmd.Parameters.AddWithValue("@TaskID", taskID);
cmd.ExecuteNonQuery();
con.Close();
Helper.AddLog("1", "Add", "Assigned Resources to Task");
Response.Redirect("~/Projects/Default.aspx");
}
Sorry about my grammar I'm just a student.
I have a ssis package deployed and created a sql agent job which executes the package.I need to change the SqlStatementSource in one of the sql task in package through job step advanced tab. Can any one help me how to do that? I somewhere read its possible but not able to recall how exactly it can be done?
There are 3 tables:
Table A:
code aname
------- ---------
1 A
2 B
3 C
Table B:
code bname
----------- ----------
1 aaa
1 bbb
2 ccc
2 ddd
Table C:
code cname
----------- ----------
1 xxx
1 yyy
1 zzz
2 www
We need to write a query that would display the following result:
code aname bname cname
----------- ---------- ---------- ----------
1 A aaa xxx
1 A bbb yyy
1 A NULL zzz
2 B ccc www
2 B ddd NULL
3 C NULL NULL
Last week I backed up my SQL Server by using Backup Exec 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.
Does anyone know what causes this? I'm suspecting corruption here (it's just a suspicion as I'm not sure). Please help me. The copies of my databases are very crucial.
Many thanks in advance.
After I insert a value (0.12346789123456789123456789) for example in the table that has a float type column, I query and get back 0.1234567891234568 which contains 17 digits. I have 3 questions
n value during declaration of my column type.17 isn't included in the document, I wish to know where it comes from.What are the use cases where hybrid databases like Aerospike and Crate would out perform compared to SQL DBs? Can someone who have worked with any one (or both) list down the pros/cons of using such DBs?
Will migrating from MSSQL to Aerospike require much of an effort? or will it be even feasible?
I'm trying to hit the stored procedure from C# code but always get the result == -1. I don't know where I went wrong. I have searched a lot but didn't' find any solution. Please have a look into my code snippet and guide me what I'm doing wrong.
Thanks in advance.
C# code:
using (SqlConnection connection = new SqlConnection(getConnectionString()))
using (SqlCommand command = new SqlCommand())
{
Int32 rowsAffected;
command.CommandText = "SP_LOGIN_GETUSERBYNAME";
command.CommandType = CommandType.StoredProcedure;
// command.Parameters.Add(new SqlParameter("@Email", userObj.email));
// command.Parameters.Add("@Email", SqlDbType.VarChar).Value = userObj.email.Trim();
command.Parameters.AddWithValue("@Email", userObj.email.ToString());
command.Connection = connection;
connection.Open();
rowsAffected = command.ExecuteNonQuery();
connection.Close();
return rowsAffected;
}
Connection string:
return "Data Source=MUNEEB-PC;Initial Catalog=HRPayRoll;User ID=sa; Password=sa";
Stored procedure code:
CREATE PROCEDURE SP_LOGIN_GETUSERBYNAME
@Email varchar(50)
AS
SELECT *
FROM [User]
WHERE Email = @Email
GO
while running my asp.net application package on test server I am getting "Win32Exception (0x80004005): The network path was not found" error and then saying: "[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]"
while publishing in publish web settings I checked "Use connection string at runtime " and " update database configure database updates" and in preview also my database is coming. But on deploying it on server I am getting error.
I already tried to change the connection string in web.config file but it didn't work.
please help.
Please help me on this, I have been working to do conditions to get the time within the 24 hours only.
So I have something like this. Can anyone help me or tell me if the conditions is correct.
my @DisplayStartTime = 11:00PM
Basically my @nextStartTime = 11:00PM
OPEN cPhosLineTimeSlot
FETCH NEXT FROM cPhosLineTimeSlot
INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @nextStartTime nvarchar(30)
set @nextStartTime = @DisplayStartTime
--insert into #ActualTimeSlot
--select @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance
WHILE (convert(varchar, convert(time, @nextStartTime), 100) < DATEADD(day, -1, GETDATE()))
BEGIN
SET @DisplayStartTime = @nextStartTime
SELECT @nextStartTime = ltrim(right(convert(nvarchar(100), DATEADD(minute, @Frequency, @nextStartTime)), 8))
INSERT INTO #ActualTimeSlot
SELECT @Parameter, @DisplayStartTime,
@nextStartTime, @CodeEndTime, @Frequency, @Tolerance
SET @intFlag +=1
END
SET @intFlag = 1
FETCH NEXT FROM cPhosLineTimeSlot
INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance
END
CLOSE cPhosLineTimeSlot
DEALLOCATE cPhosLineTimeSlot
This is the sample:
Hi experts can i have an alias of the result of inner join of 2 tables? so that i can call the result of the table in its alias name
SELECT emp_table.Name, dept_table.dept
FROM dept_table
inner join emp_table
on emp_table.dept_id = dept_table.dept_id;
I am using table adapter Query configuration wizard in Visual studio 2013 for getting data from my database. For some queries like this:
SELECT *
FROM ItemsTable
ORDER BY date_of_creation desc, time_of_creation desc
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
it doesn't recognize the @pageNumber as a paremeter and it cannot generate function that has these arguments while it works fine for queries like:
Select Top (@count) * from items_table
Why does in first query tableadapter fail to generate function with mentioned arguments whereas it can generate function fine for second one for example: tableadapter.getDataByCount(?int count)
Am I forced to use stored procedure, if yes since I don't know anything about it how?
When I run select from linked SQL Server 2012 like this:
select A.*
from A, (select TOP 1 * from B) as B
where A.test in (B.col1, B.col2)
I only get 1 row from A though A has many rows. B here is parameters table with only 1 row.
A and B here is synonyms which point to tables on remote server.
I was trying to find an aggregate of all the packages and package items which are late based on the Ship By Date.This is what I have so far and the counts are not right at the last aggregate query.I am expecting a count of 1 for packages(PackageId 123456) and Package Items which are due for shipping today(Assuming today is 06/27/2015)
Below are the sample code am stuck at WHName and WHId represent the warehouse name and Id Greatly appreciate your help Thanks
use test
go
create table #packages
(
PackageId int ,
WHName varchar(100),
WHId int,
ShipByDate date
)
insert into #packages
values
(
123,
'JAX',
1,
'06/25/2015'
),
(
1234,
'OH',
2,
'06/26/2015'
),
(
12345,
'JAX',
1,
'06/24/2015'
),
(
123456,
'DS',
3,
'06/27/2015'
),
(
1234567,
'DS',
3,
'06/27/2015'
)
create table #packageItems
(
PackageItemId int,
PackageId int,
Qty int
)
insert into #PackageItems
values
(
1,
123,
2
),
(
2,
123,
0
),
(
3,
1234,
3
),
(
4,
12345,
2
),
(
5,
123,
3
),
(
6,
123456,
3
),
(
7,
12345,
10
)
create table #PackageSum
(
PackageId int,
ItemsCount int
)
insert into #PackageSum(PackageId,ItemsCount)
select
pki.PackageId,
count(pki.PackageItemId) as Items
from
#packageItems pki
inner join #packages pk
on pki.PackageId = pk.PackageId
where
pki.Qty > 0
group by
pki.PackageId
select * from #Packages
select * from #PackageItems
select * from #PackageSum
declare @Now datetime2(7) = getdate();
select
pk.WHId,
pk.WHName,
SUM(case when datediff(day,pk.ShipByDate,@Now) = 0 then 1 else 0 end) as PackagesDueToday,
SUM(case when datediff(day,pk.ShipByDate,@Now) = 0 then pks.ItemsCount else 0 end) as PackageItemsDueToday,
SUM(case when datediff(day,pk.ShipByDate,@Now) between 1 and 2 then 1 else 0 end) as Packages1To2DaysOld,
SUM(case when datediff(day,pk.ShipByDate,@Now) between 1 and 2 then pks.ItemsCount else 0 end) as PackageItems1To2DaysOld
from
#packages pk
inner join #PackageSum pks
on pk.PackageId = pks.PackageId
group by
pk.WHId,
pk.WHName
drop table #packages
drop table #PackageItems
drop table #PackageSum