|
I thought of using a view, but at the end I think it will even be better storing the value inside the same invoices table...
I don't need recalculating the value...
It is only calculated once the invoice is made.
Thank you very much for your answer!
|
|
|
|
|
Well, that's a problem with php, not mySQL.
|
|
|
|
|
So I started my quest to learn Angular V6, and I have about a year into it now and doing quite well with it. I've made the move to Angular V7 and will go V8 pretty soon. I choose MongoDB for the database and I really like it, but have yet to do anything advanced with it. Well I just don't know the limitations of MongoDb since it's a NoSQL or document based database.
I'm going to add a store to my project so I can sell things. I've read the right way to do it is to use SQL Server for storing transactions because it's faster. Like run MongoDb to store product information and images, perhaps the cart; and use SQL Server to store the purchase transaction. But then their is FireBase and CosmoDB out there as well. I don't expect to sell much the first year, and I'm considering going Mongo all the way.
Just looking for opinions, help, guidance on this.
I really don't want to go back to SQL Server again.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Quote: Well I just don't know the limitations of MongoDb since it's a NoSQL or document based database.
Yep, that's true, being NoSQL it provides a great amount of flexibility. But most of the flexibility is lost when you take it out of a JavaScript-based environment, such as Node.js. So, consider using it with Node.js web app and then see for yourself.
Quote: I've read the right way to do it is to use SQL Server for storing transactions because it's faster.
Faster in which case? Add a bunch of JOIN statements and heavy on index INSERT INTO queries and you will easily see how MongoDb performs better in most cases, and SQL Server slows down due to housekeeping.
The answer depends entirely on how you want to store the data, do you want to store the transactions as records and then pull out all from the tables one by one? Or do you want to have a single document of everything that a user has done in the system and be returned in a single go? I'll let you answer this.
With SQL Server—or any other relational database—your content is stored as a record, and you have to query the data using several JOIN clauses to prepare a single report. If you do not do this, then you are not following normalization techniques and are wasting money paid for relational features. In NoSQL—especially MongoDb, or other databases or same dialect—you store the data in a fashion that makes it easier to access, yes a bit slower on insert, but querying is better (this statement again of course can be debated upon).
Quote: FireBase and CosmoDB
Oh boy, don't read everything on the internet if you have to develop a product. Pick one and go with it!
Read here how Pinterest used old school MySQL and scaled their hyperactive Pinterest service on the internet; https://medium.com/pinterest-engineering/sharding-pinterest-how-we-scaled-our-mysql-fleet-3f341e96ca6f. There did not break a sweat for SQL Server or MongoDb, or Neo4j or Apache Cassandra (food for thought!  ), all they did was use the infrastructure they have, better optimize it and done.
Quote: I really don't want to go back to SQL Server again.
Okay, how about Apache Cassandra?
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
You got me thinking now.
I'll continue using Mongo and try to come up with a solid compact design.
Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Compress and fix access data in C#, I have to look for examples on google network but it's not running examples, how to compress and fix data in C# ?
|
|
|
|
|
I've just answered it in CG Forum.
Choose something from Google
|
|
|
|
|
Using SQL Server 2016 - I have a table with a TREE column (type is varchar) with data such as the following:
1.0
1.0.1
1.0.2
1.0.2.1
1.0.2.2
1.10.1.35
0) The number of "octets" (values between the periods) is an unknown quantity (it could be anywhere from 2 to 10 levels deep).
2) The number of digits within a given octet will be at least one, but never more than 2.
Desired output: I want all single-digit octets to be 0-padded. So given the sample above, the output would look like this:
01.00
01.00.01
01.00.02
01.00.02.01
01.00.02.02
01.10.01.35
I did it like this, and after spending some time with a couple of DBA's they couldn't improve on it:
;WITH cte AS
(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
'.0.', '.00.'),
'.1.', '.01.'),
'.2.', '.02.'),
'.3.', '.03.'),
'.4.', '.04.'),
'.5.', '.05.'),
'.6.', '.06.'),
'.7.', '.07.'),
'.8.', '.08.'),
'.9.', '.09.') AS TREE
FROM MYTABLE
)
, cte2 AS
(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
'.0.', '.00.'),
'.1.', '.01.'),
'.2.', '.02.'),
'.3.', '.03.'),
'.4.', '.04.'),
'.5.', '.05.'),
'.6.', '.06.'),
'.7.', '.07.'),
'.8.', '.08.'),
'.9.', '.09.') AS TREE
FROM cte
)
SELECT * FROM cte2;
Is there a better way?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Depends what you mean by "better". 
You're using SQL 2016, so STRING_AGG[^] is out - that was added in 2017.
But you can use STRING_SPLIT[^], TRY_PARSE[^], and FORMAT[^]. And there are ways[^] to concatenate row values in 2016 and earlier.
I've assumed a table without a primary key; if your table has one, use that instead of the generated ROW_NUMBER:
WITH cteRN As
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As RN,
tree
FROM
MyTable
)
SELECT
STUFF(
(
SELECT '.' + IsNull(Format(Try_Parse(P.value As int), 'D2'), P.value)
FROM cteRN As T2
CROSS APPLY string_split(T2.tree, '.') As P
WHERE T2.RN = T.RN
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As tree
FROM
cteRN As T
GROUP BY
T.RN
; It's not pretty, but it works:
01.00
01.00.01
01.00.02
01.00.02.01
01.00.02.02
01.10.01.35
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
 That is almost unreadable!
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
That's part of what makes it beautiful.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I did say it wasn't pretty! 
STRING_AGG would probably make it slightly better, but that needs SQL Server 2017.
And if the source table has a primary key already, you can ditch the CTE.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Wanna hear something funny?
Our dev/test boxes are on SQL 2008R2, but our production and pre-production databases are 2016. I don't have access to the prod DBs, so I can't play with the code. :/
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
At least it's not the other way round.
You can play with it here: SQL Fiddle[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That doesn't fix our in-house DB version conflicts.
BTW, I misspoke - dev is 2012, test is 2008r2, and prod/pre-prod are 2016. So it's even worse than I initially indicated.
I complained to the DBAs, and they just shrugged their shoulders...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I'm trying to create a small sample database. Towards the bottom when I', inserting Book3, I suddenly get an FK violation from Books into Categories. Yet the caregory id is there.
Can someone try thnis and tell me wtf is wrong????
USE master
IF EXISTS(SELECT * FROM sys.databases WHERE Name = 'BookCatalog')
DROP DATABASE BookCatalog
Go
CREATE DATABASE BookCatalog
GO
USE [BookCatalog]
GO
CREATE TABLE [dbo].[BookCategories]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] VARCHAR(MAX) NOT NULL,
)
CREATE TABLE [dbo].[Books]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Category] [INT] NULL FOREIGN KEY (Id) REFERENCES BookCategories(Id),
[Title] VARCHAR(MAX) NOT NULL,
[Price] MONEY NULL
)
CREATE TABLE [dbo].[Authors]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] VARCHAR(MAX) NOT NULL,
)
CREATE TABLE [dbo].[BookAuthors]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Book] [INT] NULL FOREIGN KEY (Id) REFERENCES Books(Id),
[Author] [INT] NULL FOREIGN KEY (Id) REFERENCES Authors(Id),
)
GO
USE [BookCatalog]
-- Book Categories
DECLARE @CategoryId1 INT
INSERT INTO BookCategories (Name) VALUES ('C#')
SET @CategoryId1 = SCOPE_IDENTITY()
DECLARE @CategoryId2 INT
INSERT INTO BookCategories (Name) VALUES ('Programming Practices')
SET @CategoryId2 = SCOPE_IDENTITY()
-- Authors
DECLARE @MartinId INT
INSERT INTO Authors (Name) VALUES ('Bob Martin')
SET @MartinId = SCOPE_IDENTITY()
DECLARE @Albahari1Id INT
INSERT INTO Authors (Name) VALUES ('Joseph Albahari')
SET @Albahari1Id = SCOPE_IDENTITY()
DECLARE @Albahari2Id INT
INSERT INTO Authors (Name) VALUES ('Ben Albahari')
SET @Albahari2Id = SCOPE_IDENTITY()
DECLARE @WagnerId INT
INSERT INTO Authors (Name) VALUES ('Bill Wagner')
SET @WagnerId = SCOPE_IDENTITY()
DECLARE @SkeetId INT
INSERT INTO Authors (Name) VALUES ('Jon Skeet')
SET @SkeetId = SCOPE_IDENTITY()
-- Books
DECLARE @BookId1 INT
INSERT INTO Books (Category, Title, Price) VALUES (@CategoryId1, 'Agile Principles, Patterns, and Practices in C#', 64.99)
SET @BookId1 = SCOPE_IDENTITY()
DECLARE @BookId2 INT
INSERT INTO Books (Category, Title, Price) VALUES (@CategoryId2, 'C# 3.0 in a Nutshell', 34.99)
SET @BookId2 = SCOPE_IDENTITY()
DECLARE @BookId3 INT
INSERT INTO Books (Category, Title, Price) VALUES (@CategoryId2, 'C# Core Langaue: Little Black Book', 20.00)
SET @BookId3 = SCOPE_IDENTITY()
DECLARE @BookId4 INT
INSERT INTO Books (Category, Title, Price) VALUES (@CategoryId2, 'C# In Depth', 44.99)
SET @BookId4 = SCOPE_IDENTITY()
--Book-Authors
INSERT INTO BookAuthors (Book, Author) VALUES (@BookId1, @MartinId)
INSERT INTO BookAuthors (Book, Author) VALUES (@BookId2, @Albahari1Id)
INSERT INTO BookAuthors (Book, Author) VALUES (@BookId2, @Albahari2Id)
INSERT INTO BookAuthors (Book, Author) VALUES (@BookId3, @WagnerId)
INSERT INTO BookAuthors (Book, Author) VALUES (@BookId4, @SkeetId)
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Quote:
...
[Category] [INT] NULL FOREIGN KEY (Id) REFERENCES BookCategories(Id),
...
[Book] [INT] NULL FOREIGN KEY (Id) REFERENCES Books(Id),
[Author] [INT] NULL FOREIGN KEY (Id) REFERENCES Authors(Id)
...
The definition of your foreign keys is wrong. You're saying that the ID of the book must also exist in the BookCategories table, and the ID of the book author must exist in both the Books and Authors tables.
Once you fix the FK definitions, the rest of your script will work:
CREATE TABLE [dbo].[BookCategories]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] VARCHAR(MAX) NOT NULL
)
CREATE TABLE [dbo].[Books]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Category] [INT] NULL FOREIGN KEY REFERENCES BookCategories(Id),
[Title] VARCHAR(MAX) NOT NULL,
[Price] MONEY NULL
)
CREATE TABLE [dbo].[Authors]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] VARCHAR(MAX) NOT NULL
)
CREATE TABLE [dbo].[BookAuthors]
(
[Id] [INT] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Book] [INT] NULL FOREIGN KEY REFERENCES Books(Id),
[Author] [INT] NULL FOREIGN KEY REFERENCES Authors(Id)
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That did it!!
Ya know I stared at this for an hour. I'm an idiot.
Thanks!
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
I'm trying to create a small sample database:
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
I think you forgot to post your question or script sample. I don't see it.
|
|
|
|
|
I need to connect to a client't SQL Server remotely. They want to set up a VPN for me to minimize security concerns.
Can I access SQL remotely on their network from my office if they set me up a VPN?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
So long as the VPN's connected, it should be the same as accessing a server on the same network.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I have been trying forever to set up access to SQL Server on my home server.
I again followed all of the instructions here:
https://medium.com/developer-diary/how-to-enable-remote-connections-to-sql-server-dc5b6c812b5
Windows Firewall is off.
When I try to connect remotely I get
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) (Microsoft SQL Server, Error: 53)
This message mentions 'Named Pipes'. Named pipes is enabled, although from what I can see, Named Pipes are for inter-process communication to occur among processes running on the same machine, so I'm not sure what this is part of the error.
At this point I'm out of options. I've tried eveything I can think of. What could possibly be wrong here??
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
What do you mean by "remotely"? Something else on the same LAN, or something outside of your network?
Check which client protocols are enabled on the computer that's trying to connect. If it doesn't have the SQL configuration tools installed, you'll need to run cliconfg.exe from both C:\Windows\System32\ (64-bit) and C:\Windows\SysWow64\ (32-bit).
Also check your connection string to make sure you're using the correct server and instance name. For example, ".\SQLEXPRESS" won't work from a remote computer.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Remote from my office to my home server. I connect using my IP addrss.
Richard Deeming wrote: Check which client protocols are enabled on the computer that's trying to connect. If it doesn't have the SQL configuration tools installed, you'll need to run cliconfg.exe from both C:\Windows\System32\ (64-bit) and C:\Windows\SysWow64\ (32-bit).
Well, that did it!!!
Wow, I've been trying on & off again for MONTHS to get this to work. THANK YOU!!
I've set up SQL Remote access before, and I don't remember ever having use 'cliconfg.exe' before. Is this documented somewhere?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
