Ciugk

Clash Royale CLAN TAG #URR8PPP Sql Table data formation I have a below table ID Level StoreId 2604553 3 A 2604553 7 C 2604553 7 D 2604553 7 E 2678540 3 A 2678540 7 A 2678540 3 B 2678540 7 B 2678540 3 C I need out put like Below ID A B C D E 2604553 3 0 7 7 7 2678540 3 3 3 0 0 2678540 3 7 3 0 0 2678540 7 3 3 0 0 2678540 7 7 3 0 0 Please help me how to achieve this. By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Clash Royale CLAN TAG #URR8PPP Update a table by using PWDENCRYPT I have an application which use a database and a users table with login and password. The passwords weren't crypted so I searched how to crypt them. I use SQL Server 2017, and I found the PWDENCRYPT function and PWDCOMPARE . PWDENCRYPT PWDCOMPARE So I would like to update my users table to encrypt all the passwords and then edit my application to use pwdcompare. I tested before and there are some behaviors I don't really understand, and I didn't find examples on the internet except for the basic utilisation like PWDENCRYPT('password') or PWDCOMPARE('password',password_hash) . PWDENCRYPT('password') PWDCOMPARE('password',password_hash) In SQL Server when write: select PWDENCRYPT('password') I have the encrypt password, here it is: 0x02001691959A1D475E3DB65AE8F7E7B70E7929B8EF873F213C7B99DEED82D0E6B35289CB172C1998DDEDE058F9015FA2679EED387E718B4E06EB389223AA152C8793D8BA0C...

Clash Royale CLAN TAG #URR8PPP SQL declare datetime - 1001-01-01 I am trying to declare a datetime variable with the value 1001-01-01 00:00:00.000 I have tried the following approaches with no luck declare @d1 datetime = '1001-01-01'; declare @d2 datetime = 10010101; declare @d3 datetime = '1001-01-01 00:00:00'; declare @d4 datetime = cast ('1001-01-01' as datetime) I get the following errors Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type datetime. Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Msg 242, Level 16, State 3, Line 4 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Is it possible to declare a datetime variable which can hold the value 1001-...

Clash Royale CLAN TAG #URR8PPP Connection refused during running app on docker - Spring Boot & mssql server I'm start to play with Docker. I'm trying to run Spring Boot application on Docker which connects with mssql-server-linux on docker. I'm configure and run mssql server. My app has set database port and when I run this app in IntelliJ everything is ok. Also when I build app './mvnw install dockerfile:build' command everything is ok. Also adds entity to database during build. When I try run app 'docker run' command I get this exception: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". What's the problem? My spring boot app prop...

Clash Royale CLAN TAG #URR8PPP data warehouse Initial load from Oracle to SQL Server We are building a DWH and the initial load would be millions of rows. The data will later be updated every 10 minutes using SSIS package which will be like a few thousand rows.Data migration would be from Oracle to SQL Server. Can you suggest an efficient way of extracting data initially. Is using SQL Server Import and Export a good and faster option than SSIS for initial load? Thanks By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Clash Royale CLAN TAG #URR8PPP Speeding up SQL transfer over a network I have two SQL environments, data warehouse which collects data and a datamart which people access for a subset of the data, each with their own SQL 2016 databases. I run a script which pulls out data, transforms it and transfer it from the data warehouse to the datamart using Linked Servers. The entire process takes around 60+ hours to run. I want to avoid at all costs at having the data warehouse data in the datamart. I experimented to see why the whole process was taking so long. I did a backup of the data warehouse, restored it onto the datamart and ran the import script and the entire process took around 3 hours to run. The script itself to 1.5 hours, telling me of the 60+ hours its the linked server transfer of data between the two servers that is the slowest part. I've pretty much ruled out network speed or issues between the two servers; this is all SQL. I'm trying to avoid having to write an applica...

Clash Royale CLAN TAG #URR8PPP Add another character not existing in table when export to text file I create a procedure to export data to text file. It be like: 'bcp "select name,age,grade from test" queryout C:test.txt however i want to add some other character like |. And output file content would be like: Jones|6 C. Which is the most convenient way to do that. Should i create a temporary table? you mean use | as seperator ? use a BCP format file – Squirrel 10 mins ago | BCP @Squirrel that's such a good answer. I never think about that solution. so my command should be 'bcp "select name,age,grade from test" -i test.fmt queryout C:test.txt Is it right? – Nguyen Van Hung 6 mins ago ...

Clash Royale CLAN TAG #URR8PPP Upload CSV file to SQL server What is the best way to upload a large csv data file into SQL server using C# ? The file contains about 30,000 rows and 25 columns. csv why to use .net to upload the file while sql server itself support the import of CSV file?? – Furqan Hameedi Dec 24 '13 at 10:36 SQL Server doesn't have any facility to upload files to - you'll need to use some other means (e.g. a web service running on IIS or something like that). SQL Server can load a file from a disk it can reach - so you'll need find a way to put that file where SQL Server can read it from – marc_s Dec 24 '13 at 10:36 the requireme...

Clash Royale CLAN TAG #URR8PPP Have a value fall off a report after a certain amount of days Edited What I'm trying to do is have customer information fall off of my report for 35 days when someone approves of it. I'm currently using the code CASE WHEN action_code_no = '15' and result_code_no = '93' then datediff(Day,data.date,GETDATE()-35) end <= data.date my desired result is to get data.date back down to 0, the report is set up so that when data.date reaches 35 it populates the report and when the action code = 15 and result code = 93 I want it to subtract 35 from data.date, hope this edit helps Sample data and desired results would really help. It is entirely unclear what those codes have to do wth any approval. – Gordon Linoff 16 mins ago ...

Clash Royale CLAN TAG #URR8PPP Copying some columns from one table to another while also adding user input values I have two tables, Table 1 and Table 2. Table 2 contains the exact same columns as table 1 with five additional columns. Table 1 contains basic employee information, and table 2 contains a history of every time the employee is not at work. Through a VBA built program, the user can mark an employee as absent and then supply a type of absence, an exception can be granted, and then a reason for the exception. Whenever an absence is created, I want to copy the employee info from table 1, insert it into table 2 along with the type of absence, exception, and exception reason. I know how to copy the information from table 1 to table 2, but how can I do that and insert the new values from the user input? INSERT INTO Table 2( FirstName, LastName, AgentName, Location, EmployeeGroup, ContractAgency, Manager, Supervisor, Team, Title, Position, StaffCIMID, FTPT, Bilingual, Five9Email, E...

Clash Royale CLAN TAG #URR8PPP Sql Server: Remove list entry from the string of another list I am sure that title is a bit confusing. Basically I have one table that has a column containing formation like "xx 123 Rg 43" and one that contains information like "Rg".. and if an entry from table 2's column is contained in the string contained in Table 1 then I need just that entry removed.. leaving us with "xx 123 43" Currently I am using: update [Table1] set [Col1] = CASE WHEN (select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0) is not null THEN replace([Col1], (select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0), '') ELSE [Col1] END And this works fine, but fails if the result of select * from [Table2] where charindex(' '+[Col2]+' ', [Col1]) > 0 is more than 1 entry with this error: select * from [Table2] where charindex...

Clash Royale CLAN TAG #URR8PPP MSSQL - GROUP BY Date statement showing wrong data I have a GROUP BY statement which sort my data based on weeks. The problem here is my data are not being counted for the whole week (Sundays are not counted in) Here is an example: CREATE TABLE [dbo].[Products]( [ProductNR] [varchar](14) NULL, [Location] [int] NULL, [Date] [datetime] NULL); INSERT INTO Products (ProductNR, Location, Date) VALUES ('12345678911' ,1, '2018-07-16 00:00:00.000'), -- Monday ('12345678912' ,1, '2018-07-16 00:00:00.000'), -- Monday ('12345678913' ,1, '2018-07-16 00:00:00.000'), -- Monday ('12345678914' ,1, '2018-07-16 00:00:00.000'), -- Monday ('12345678915' ,2, '2018-07-16 00:00:00.000'), -- Monday ('12345678916' ,3, '2018-07-22 00:00:00.000'); -- This is a sunday And here is my Sql query in which it retrives the above data split for on 2 weeks when it supposed to be the count for jus...

Clash Royale CLAN TAG #URR8PPP Produce data extracts using day field for the Friday before the weekend Using TSQL on SQL Server... I need to produce extracts that use a pay day column in a database that only holds the day number, for example 26 to produce extracts for the 26th day of the month with a twist if that pay day falls on a weekend then the data for that extract should be extracted the Friday before the weekend. Has anybody attempted this and able to offer some ways of achieving this through TSQL? Thanks Are you always running this for the current month? Otherwise I cannot see how any logic is expected to guess what month you are running it for. – MandyShaw 14 hours ago 1 Answer 1 I'm assuming you have the month and year available too, in wh...

Clash Royale CLAN TAG #URR8PPP How to insert into a table with just one IDENTITY column? (Came up with this question in the course of trying to answer this other one) Consider the following MS-SQL table, called GroupTable: where GroupID is the primary key and is an Identity column. How do you insert a new row into the table (and hence generate a new ID) without using IDENTITY_INSERT ON? Note that this: INSERT INTO GroupTable() Values () ... won't work. edit: we're talking SQL 2005 or SQL 2008 here. 4 Answers 4 This should work: INSERT INTO GroupTable DEFAULT VALUES I can't get this to work with Visual Studio 2008/SQL Express 2005. Any ideas? Same table layout, one column, primary key, identity(1,1). – Thomas Sandberg Aug 31 '09 at 18:27 ...

Clash Royale CLAN TAG #URR8PPP Why TRUNCATE statements requires ALTER privileges instead DELETE? May someone explains why TRUNCATE instructions requires ALTER privileges? At the same time DELETE instructions requires DELETE privileges. I know that TRUNCATE marks pages as deleted and DELETE marks string as deleted. So it has different transaction log usage and speed of working but this knowledge doesn't allow understand a difference in necessary privileges. I don't think anyone will be able to answer why authoritatively. It's a decision that was probably made decades ago. – Damien_The_Unbeliever 8 mins ago See stackoverflow.com/questions/139630/… – Steve Drake 6 mins ago ...

Clash Royale CLAN TAG #URR8PPP Backup a single table with its data from a database in sql server 2008 I want to get a backup of a single table with its data from a database in SQL Server using a script. How can I do that? SQL Import/Export Wizard. Right click on your database in SMSS/ Choose Item Export – realnumber3012 Oct 31 '13 at 4:18 I want do this with script – EBS Oct 31 '13 at 4:21 please accept MGOwen's Answer – greg121 Nov 9 '15 at 14:16 9 An...

Clash Royale CLAN TAG #URR8PPP How relation between one record on a table and several records on onother table My project is a program like movie collection. Main table is for storing movie data with fields like name,director,release year and ... But each record must have another attribute named genre (genres). Each movie may be categorized by one or several genres (Comedy, Horror, History, Romance,...) Genre table contain two fields.ID:integer and Genre:vchar For each movie, some of genre fields can be selected. How I can do this? thank you you have to use sql join for that, but before this you have to set relationship of primary key table and foreign key table – ershoaib 7 mins ago sql join Possible duplicate of How to implement one-to-one, one-to-many and many-to-many relationships while designing tables? Yo...