Asked  7 Months ago    Answers:  5   Viewed   44 times

I'm curious as to whether or not there is a real difference between the money datatype and something like decimal(19,4) (which is what money uses internally, I believe).

I'm aware that money is specific to SQL Server. I want to know if there is a compelling reason to choose one over the other; most SQL Server samples (e.g. the AdventureWorks database) use money and not decimal for things like price information.

Should I just continue to use the money datatype, or is there a benefit to using decimal instead? Money is fewer characters to type, but that's not a valid reason :)

 Answers

87

Never ever should you use money. It is not precise, and it is pure garbage; always use decimal/numeric.

Run this to see what I mean:

DECLARE
    @mon1 MONEY,
    @mon2 MONEY,
    @mon3 MONEY,
    @mon4 MONEY,
    @num1 DECIMAL(19,4),
    @num2 DECIMAL(19,4),
    @num3 DECIMAL(19,4),
    @num4 DECIMAL(19,4)

    SELECT
    @mon1 = 100, @mon2 = 339, @mon3 = 10000,
    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3
    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,
    @num4 AS numericresult

Output: 2949.0000 2949.8525

To some of the people who said that you don't divide money by money:

Here is one of my queries to calculate correlations, and changing that to money gives wrong results.

select t1.index_id,t2.index_id,(avg(t1.monret*t2.monret)
    -(avg(t1.monret) * avg(t2.monret)))
            /((sqrt(avg(square(t1.monret)) - square(avg(t1.monret))))
            *(sqrt(avg(square(t2.monret)) - square(avg(t2.monret))))),
current_timestamp,@MaxDate
            from Table1 t1  join Table1 t2  on t1.Date = traDate
            group by t1.index_id,t2.index_id
Tuesday, June 1, 2021
 
VostanAzatyan
answered 7 Months ago
39

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Tuesday, June 1, 2021
 
max_
answered 7 Months ago
29

Check to see if your user is mapped to the DB you are trying to log into.

Saturday, July 31, 2021
 
iammichael
answered 4 Months ago
91

SQLCMD mode commands are not T-SQL commands; they only work in SQL Server Management Studio (SSMS) / Visual Studio (VS) and SQLCMD.EXE. SQLCMD-mode is inherently how SQLCMD.EXE works and can be manually enabled in SSMS / VS; it is a part of those applications and not something that can be done via a provider.

Those applications interpret the SQLCMD-mode commands and do not pass them through to SQL Server. SQLCMD-mode commands are parsed/executed first (which is how they are able to affect the SQL that is about to be submitted) and then the final version of the SQL is submitted to SQL Server.

Hence, the deployment SQL scripts generated by SQL Server Data Tools (SSDT) / Visual Studio need to be run via one of these three programs.

Since you have a .dacpac file already, Microsoft provides a few ways to publish those that you should check out:

  • SqlPackage.exe and MSDeploy.exe. They are both described on the MSDN page for Project-Oriented Database Development using Command-Line Tools.
  • DacServices.Deploy(). This can be done in C# via the DacServices Class.

You can also create a publish SQL script via DacServices.GenerateDeployScript(), but this won't change the situation as stated above since the publish / deploy SQL script, whether generated from Visual Studio "Publish {project_name}" or GenerateDeployScript(), is the same script. Meaning, it will have the SQLCMD-mode colon-commands such as :setvar and :on error exit as well as the SQLCMD-mode variables, which at the very least will be $(DatabaseName) which is used in the following line:

USE [$(DatabaseName)];

While it is possible to comment out the initial :setvar lines by setting the DacDeployOptions property of CommentOutSetVarDeclarations to true, that will still leave the :on error exit line as well as a line for :setvar __IsSqlCmdEnabled "True" that is used to detect whether or not SQLCMD-mode has been enabled. Just above this particular :setvar line is a comment stating:

/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/

So they really do intend that this script is only run via SQLCMD, whether through DOS -> SQLCMD.EXE or PowerShell -> Invoke-SqlCMD.

Technically, it is possible to generate a string of the deploy script contents (rather than to a stream) and manipulate that string by a) removing any colon-commands, and b) replacing "$(DatabaseName)" with whatever database you intend on deploying to. However, I have not tried this, I am not recommending this, and I am not sure it would work in all situations of what deployment scripts could be generated by SQL Server Data Tools. But it does seem like an option.

Also, minorly related: you don't need SMO to run SQL Scripts. SMO is means of interacting with SQL Server via objects rather than directly through T-SQL commands.

EDIT:
Links where others have tried this and found it did not work:

  • http://www.sqlservercentral.com/Forums/Topic1161673-22-1.aspx
  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d3f64b0-f6ed-44ad-99cc-ce4c8324c09e/running-sqlcmd-commands-using-smodatabaseexecutenonquery?forum=sqlsmoanddmo

Possibilities for getting the generated publish SQL script to work programmaticaly:

  • C# via Process.Start to call SQLCMD.EXE -i filename.sql: http://msdn.microsoft.com/en-us/library/system.diagnostics.process.start(v=vs.110).aspx
  • C# via open source library to handle "GO" statements and SQLCMD-mode colon-commands (currently handles SQL files, but can be easily updated to accept a string instead): https://github.com/rusanu/DbUtilSqlCmd
  • PowerShell via Invoke-SqlCMD: http://www.sqlservercentral.com/Forums/Topic1502697-1351-1.aspx
Tuesday, August 10, 2021
 
Evernoob
answered 4 Months ago
16

To have this question as answered: the problem is that you specify the schema in the table name itself. If you provide "MyDB.dbo.Loader_foo" as the table name, pandas will interprete this full string as the table name, instead of just "Loader_foo".

Solution is to only provide "Loader_foo" as table name. If you need to specify a specific schema to write this table into, you can use the schema kwarg (see docs):

finaloutput.to_sql("Loader_foo", engine, if_exists="append")
finaloutput.to_sql("Loader_foo", engine, if_exists="append", schema="something_else_as_dbo")
Sunday, October 24, 2021
 
Greg Trevellick
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share