Sql server Making table Join Problem?

Using SQL Server 2000 Having Two Tables Table1 Date ID 20090101 001 20090102 001 …. 20090101 002 20090102 002 … 20090101 003 20090102 003 … So on.., Table2 ID Name Date 001 Raja 20090408 001 Raja 20090502 001 Raja 20090503 002 Ravi 20090312 002 Ravi 20090522 003 Saga 20090802 003 Saga 20091022 So on.., Query SELECT Table1.Date, Table1.ID, Table2.Name, Table2.Date FROM table1 INNER JOIN table2 ON table1.id = table2.id ORDER BY table1.id, table1.date Output Date ID Name

Sql server How to Execute SQL Server Stored Procedure in SQL Developer?

I've been given a user account to a SQL Server database that only has privileges to execute a stored procedure. I added the JTDS SQL Server JDBC jar file to SQL Developer and added it as a Third Party JDBC driver. I can successfully log in to the SQL Server database. I was given this syntax for running the procedure: EXEC proc_name 'paramValue1' 'paramValue2' When I run this as either a statement or a script, I get this error: Error starting at line 1 in command: EXEC proc_name 'paramValue1

Sql server T-SQL command to get only rows that can CAST()

I have a DB of raw data that has everything as varchar, one of the columns contains a price, which for the most part would convert into FLOAT easily. However, there are a few rows that have trash in that column with a bunch of characters and would not convert to float. Is there a way to select only that rows that would convert to float (i.e. only the ones taht actually have a number)? Thanks

Sql server Int Variable inside Nvarchar Statement

The following code sends error. @resp2 is INT, it's the result of a sum preiously done. So now I want to update some row in another table using a dinamic statement. SET @SQL = 'update TelepromTableNamesInfo set [Resp] = '+@RESP2+' where nombre = ''' + @TableWBraq + '''' EXEC (@SQL) First thing I've tried is '''+@resp2+''' But I don't want it be ' variable value ' since it's an INT value and there's no need for '' The error makes sence. I can't put some INT value int

Sql server Binary Column is translated to null Java type

I created a domain in JasperRreports Server. I have a table that stores Binary data. When I use it in my domain I get a the following error : java.lang.IllegalArgumentException: getObjectType for javaType: null returned null I exported the schema and found the following: <field id="Id" type="java.lang.Integer" /> <field id="FileData" type="null" /> As you can see, null is used in the type field. I tried changing this to java.io.InputStream which is the type that it maps

Sql server Service Broker -- how to send the table with collected data? XML messages or not?

A technology machine uses SQL Server Express to collect the data like temperature... The database is updated say every several second (i.e. low trafic). The machine and its SQL Server must work independently on anything. Sometimes the technology computer (i.e. SQL Server) is switched-off with the machine. The central SQL Server Standard Edition should collect the data form many machines like the above. What scenario would you recommend? The machine sends each new row of a table when it is c

Sql server Inserting data into one column fails saying values in another column can't be null

I can't understand this misunderstanding by SQLServer. As you can see, I'm trying to insert into column Ordamount, but SQLServer shows me in its error message that it can't insert null into column UserID? Declare @variable1 int =( select sum(Orr.quantity *OI.Iteprice) from Orderrouter Orr inner join OrdItem OI on Orr.OrdItems =OI.ItemId where OrdId = 1) insert into Ord (Ordamount) values (@variable1); Error:

Sql server Taking the Top X results of a complex NHibernate query (QueryOver syntax)

I'm working with an Nhibernate Query where I have to do some complex queryover join aliases to eagerly load the children of my root entity. When loading, I want to filter the root entity results returned by a number of properties, including some which are on the children. I've got this all working fine using joinaliases, but where I'm stumped is filtering the results returned down to the top "X" instances of the root entity when ordered by a property other than the root entities Id. Since I'm

Sql server Calling Stored Procedure w/ Output Parameter within Stored Procedure - Invalid Column Name

I have written a stored procedure that uses an output variable. The stored procedure runs fine, I know that for a fact. I can run the stored procedure using the snippet below DECLARE @return_value int, @coEfficientAlpha float EXEC @return_value = [dbo].[getTestCoEfficientAlphaByDasIdAndKeyStandard] @dasId = 4001, @keyStandardId = 960, @coEfficientAlpha = @coEfficientAlpha OUTPUT SELECT @coEfficientAlpha as N'@coEfficientAlpha' SELECT 'Return Value' = @return_value GO

Sql server Querying 3 tables where I'm looking for non-matches

I have three tables: LitHold, LitHoldDetails and EmailTemplate. The definitions are as follows. CREATE TABLE [dbo].[LitHold]( [LitholdID] [int] IDENTITY(1,1) NOT NULL, [LitHoldStatusID] [tinyint] NOT NULL, [EmailReminderID] [tinyint] NULL, [ApprovedDate] [datetime] NULL, [TerminatedDate] [datetime] NULL, CONSTRAINT [PK_Lithold] PRIMARY KEY CLUSTERED ( [LitholdID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRI

Sql server Can I use sql server log file for change tracking?

Sql Server database have both mdf and ldf files. Mdf being the data and ldf being the change log. I have a need to track every "data" change in the database. Till now Ive used mirror tables with the same name but a dcr (data change record) schema. These are populated by triggers on insert, update, delete. Works fine but it's quite a bit of overhead. Since I'm updating this application at the moment I thought now would be a good time to revisit this almost never used but somehow "required" featu

Sql server How to create an RDL using the report class generated from the RDL schema

I have a project where we are creating a custom report generator for SSRS 2008R2. This project allows the user to select fields from a database and then create and store the RDL on the report server. For some of the initial proof of concept attempts we have been using XMLText writer to generate the XML file. While this works this seems to be very cumbersome and I don't have a lot of confidence in how the schema is being generated as being 100% bulletproof. A second attempt is actually using the

Sql server SQL Server 2014 CTP1 Setup failed

I have uninstalled Microsoft Visual Studio 2012 Ultimate and install VS professional 2013 after, i tried to Setup Sql Server 2014 but a message say "Installation blocked due to detection of version of Visual Studio that install SQL Server 2012 client libraries.." then i have uninstalled all Microsoft programs figured in this link without uninstalling visual studio 2013 . and always i get the same problem any solutions Please

Sql server SSAS Averaging a Dimension Attribute

I am trying to Average a dimension attribute. i have tried the below which i got from a previous question on here. WITH MEMBER [Measures].[Booking Window Value] AS [PNR Details].[Booking Window].CURRENTMEMBER.MEMBER_VALUE MEMBER [Measures].[Avg Booking Window Value] AS AVG([PNR Details].[Booking Window].[Booking Window].MEMBERS,[Measures].[Booking Window Value]) SELECT [Measures].[Avg Booking Window Value] ON COLUMNS FROM [SalesAnalysis] This however duplicates the result when you add

Sql server Can I automatically generate Select queries on Visual Studio similar to what SQL server management studio does?

I had SQL server management studio on my computer and I used to explore my company's database using it. When building queries it was very easy to right click on a table and 'select top 1000' rows. I would take that code then and modify it. This will save me time typing all field names for example. Now I have MS Visual Studio and I can't install the Server Management Express. I can explore the database and I can right click on a table and view the records. However, I am not able to generate the

Sql server Trouble with SQL trigger

Need help with a trigger. If I remove my inner joins from where clause it runs, so I'm guessing this is where the issue would be. If I use this where clause it works "WHERE oeordhdr_sql.A4GLIdentity =@id " Any help would be appreciated. Below is the code... ALTER trigger [dbo].[bdordermakeonhold] on [dbo].[oeordhdr_sql] for insert as declare @edi as char(1) declare @id as numeric(9,0) declare @ordno as char(8) declare @ordtype as char(1) declare @cus_no as char(12) declare @status as char(1)

Sql server How to calculate time difference in T-SQL

I have created a table with columns of datatype time(7) I want to calculate the time difference between them. Table time: id timefrom timeto result -------------------------------------- 1 13:50:00 14:10:00 00:20:00 2 11:10:00 11:00:00 23:50:00 For example: Time From 13:50 Time To 14:10 Result should show 00:20. Is there a function for this? DATEDIFF(hour, UseTimeFrom, UseTimeTo) hourtime, (DATEDIFF(MINUTE, UseTimeFrom , UseTimeTo)) - (((D

Sql server Get a word in a table that contains similar words in SQL Server

I have two type of strings in a column. DECLARE @t table(parameter varchar(100)) INSERT @t values ('It contains eact01' ), ('It contains preact01') I'm trying to get the strings that contain the word 'eact01'. My problem is that using the following SELECT, I get also the variables that contain 'preact01', because it contain 'eact01'. SELECT * FROM @t WHERE parameter LIKE '%eact01%' How could I get only the row containing 'eact01'?

Sql server Move SQL Server DB to cloud and create simple front-end

I have a SQL Server database, and I want to move it into the cloud and create a simple browser-independent UI for it. The details are: The database is about 2GB in size. It uses SQL Server 2008, however I am happy using any later version. I have successfully restored and tested the database in more recent versions of SQL Server. I considered using Amazon RDS or Microsoft SQL Azure for hosting the database. Obviously I have to upload the database into the cloud, I don't mind whether this is by

Sql server Is there a reliable way to determine the number of times an index is being used per stored procedure?

I am currently trying to write some queries for finding out how many times an index is being used for a particular table and what stored procedures are using those indexes. I have tried searching through the Internet and Stack Exchange websites for some helpful information and I was able to put together a helpful query for finding out how many times an index is being used, but my second query for finding out what stored procedures are using a particular index has been returning incorrect counts.

Sql server SQL Server 2008 R2 import from access fails on datetime column

I'm trying to import an Access 2010 DB table (*.accdb) to SQL Server 2008 R2, as a new table not to an existing one. I'm doing it through SQL Server's "Import an Export Data Wizard". I have two datetime columns that I know for a fact that contain some non valid datetime values (don't ask me how some genius managed to enter bad values there). So I thought I can map these columns to nvarchar(max) columns in the wizard and deal with the problem later. But unfortunately after the mapping, I get

Sql server How can I get TOP in a GROUP BY?

I want to get the LAST TargetPosition by Symbol - how can I do this? SELECT TOP(1) [Symbol],[TargetPosition] FROM [FX].[dbo].[Orders] GROUP BY [Symbol] ORDER BY [OrderUTC] Does NOT work Column 'FX.dbo.Orders.TargetPosition' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sql server SELECT data records from MS ACCESS database using MSSQL Server

I already successfully created a link from the other workstation and I can view all the tables from MS ACCESS database. But I can't retrieve the data from the tables using simple SELECT Statement. SELECT * FROM [192.168.1.64].default.dbo.CHECKINOUT; When I execute this query i got this error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'default'. I also found this query: EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distribu

Sql server Change the Schema of a Table to Satisfy a few Contstraints

Given Schema: CREATE TABLE Trains (Platform INTEGER NOT NULL, arrival DATE NOT NULL, departure DATE NOT NULL, TrainName CHAR(255) NOT NULL, PRIMARY KEY (Platform , arrival )); Should make Changes to the Schema to make sure of 2 things. 1. Should not allow Inserts Where arrival date is greater than departure date. 2. There should be no overlaps on a single platform. i.e., If a Train A arrives on Platform 1 on 5th and leaves on 9th No other train should be inser

Sql server Unable to connect to SQL Server 2014 remotely

Unable to connect to SQL Server remotely I have installed SQL Server 2014 Express on Windows 10. I can connect to server locally by entering: computername\SQLEXPRESS or 192.168.0.xxx\SQLEXPRESS When I try to connect to the server using public ipadress 122.xxx.xxx.xxx\SQLSERVER I get an error: Error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider) I also have host name from dlinkddns, tried connecting with host name with same error. Steps which I have

Sql server Avoid referring table two times in the WHERE clause

Following is a simplified version of my database in SQL Server 2005. I need to select employees based on business units. Each employee has home department, parent department and visiting department. Based on the department, business unit can be found out. For an employee, if the HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID. If HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID. Following query w

Sql server Merging results from SQL Query, where most values are the same

Sorry for the not-so-great title. I'm triyng to merge together sets of results to provide a quick view of some data. If I have this query: SELECT tblPriv.ID, tblGroups.Name AS 'Group', tblPriv.User, tblPriv.Role FROM tblPriv INNER JOIN tblGroups on tblPriv.ID = tblGroups.ID which returns these results: ID GROUP USER ROLE --------------------------------- 1 Taxes DAVE Admin 1 Taxes JOHN Admin 1 Taxes BOB PowerUser 2 Catering RON

Sql server SQL left join table with double sum record

I have two tables (dw and click) and using left join to join it if match the date. both tables have date 2016-06-01 (dw table has 1 record and click table has 2 records). I want to sum the column winloss in dw table, but since click table has 2 record with the date 2016-06-01 and caused the winloss double sum. But i just want sum the winloss in dw table. Any suggestion? Please refer to image below.

Sql server How to customize SQL Scripts generated by Visual Studio

So we are attempting to implement DDL triggers on our databases to track DDL changes for our developers, and have that working great. However, we've run into a snag with our Visual Studio 2013/2015 developers. When they edit a table/proc/anything from within Visual Studio, it will disable the ddl trigger, make the change, and re-enable the trigger, which makes the trigger pointless. I have found that I can create a SQL Database project, navigate to Project > Properties > Debug > Advanced, and u

Sql server Inserting an image path to the database

I am developing a system where I will be saving many images (approximately 100+). I researched and I've known that saving many images to the database will make the database's size grow immensely. So I am thinking of doing what many recommend which is to save the path or location of the image. But I'm afraid it is not applicable especially I am doing a thesis. What conflicts will I encounter if it is just the path that is being saved? Will I still be able to retrieve the image when I publish the

Sql server Importing Data in Parallel in SQL Server

I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the following ones: How to load 1 TB data in 30 minutes https://technet.microsoft.com/en-us/library/dd537533(v=sql.100).aspx Importing Data in Parallel with Table Level Locking https://technet.microsoft.com/en-us/library/ms186341(v=sql.105).aspx Controlling Locking Behavior for Bulk Import https://

Sql server SQL Server FileTable move directory with content T-SQL

i am currently looking at trying to move a directory in a FileTable from one location to another. I cannot seem to find any information on how to move a directory that has sub directories and files in it to a different location via T-SQL. i have found examples of how to move files from one location to another and this method will work on a directory as long as its empty but not when it has content in it as it throws a conflict. i assume this is because the path locators need regenerating in an

Sql server How to reverse the OPENJSON() function in SQL server?

The sql-server OPENJSON() function can take a json array and convert it into sql table with key-value pairs, e.g.: DECLARE @json NVARCHAR(MAX); SET @json = '{ "key1": "val1", "key2": "val2", "key3": "val3" }'; SELECT * FROM OPENJSON(@json, '$') Result: key value type -------------------- key1 val1 1 key2 val2 1 key3 val3 1 What is the best general-purpose method for converting this key/value table back into a json array? Why? If we can do this with

Sql server Getting #Error in SSRS when trying to run calculation on null value, despite IfNothing clause

I have a column of data brought in from SQL that is sometimes populated, other times null. In an SSRS report, I have two columns: one for the actual value, one for a calculation on that value. The column for the value is blank when it is null, which is what I want. However, the calculation column returns "#Error" even when I try and catch this with an IfNothing() clause. Below is the expression on the calculated column, with the "Ready to Discharge" field being the column of sql data that is i

Sql server Do not show text when using simple select

I am querying a varchar column and I don't want to see the first characters within the row but I'm not so sure on how to go about that for a select statement. Would anyone know how? I would appreciate it. This is the value in the table: {\rtf1\ansi\ftnbj{\fonttbl{\f0 \fswiss Arial;}}{\colortbl ;\red255\green255\blue255 ;\red0\green0\blue255 ;\red0\green0\blue0 ;\red0\green0\blue255 ;\red0\green128\blue0 ;}{\stylesheet{\f0\fs20\cf0\cb1 Normal;}{\cs1\additive\cf0\cb1 Default Paragraph Font;}}\marg

Sql server Performance tuning on pattern matching query

I have two tables: Table 1: 100 rows Table 2: 10 Millions rows Example: Table 1: tb100 create table tb100 ( name varchar(50) ); insert into tb100 values('Mak John'),('Will Smith'),('Luke W')......100 rows. Table 2: tb10mil create table tb10mil ( name varchar(50) ); insert into tb10mil values('John A Mak'),('K Smith Will'),('James Henry')......10 millions rows. create nonclustered index nci_tb10mil_name on tb10mil(name); Note: I want to match the name between two tables,

Sql server Cannot select from a column that was previously encrypted

I have a column that was previously protected with an Always Encrypted certificate. It was determined that this column didn't need the encryption and we wanted to do some analysis on it so I went back and decrypted the column. However, when I try to select the column in a stored procedure I get the following message: "Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_BIN2" a

Sql server SQL Join 2 Case from a third table

I have three tables: MetaMachine (MM) MetaShift (MS) MachineData (MD) MM Contains all relavent static data about a specific machine, including what worker shift is operating it. MS Contains the different shifts, sub shifts, StartTime(hh:mm) and Duration(h) of said shifts MD Contains sensor readings from the machines, all DateTime Stamped (DTS). Problem I want to know what happened during ghost hours, i.e. when machine was running without any operators. The logic I wish to apply is:

Sql server Passing a table rrecordset to SQL server

Good afternoon experts! I have encountered a very different type of problem than usual. In the past I have pass a single line to the server via 'Pass through Query' and at times when I need to pass more than a single record, I utilise the loop function to send the data to the server multiple times. However if I have over 40 lines of record that loop will take a considerable amount of time to complete. I am just wondering if there is a way to send a table set to the server in 1 move instead of X

Sql server Azure SQL - Create table without schema?

I am new to MS SQL Server, coming from MySQL. I sort of understand MS SQL schemas and their purpose, but I don't see any need for them for small applications with the one DBA. Is it possible to ignore schemas altogether, for example to create and query tables? If so what would be the format to create a table without specifying a schema? This is in Azure, with an Azure SQL DB. UPDATE Thanks to the answer below you don't apparently need to specify a schema when creating a table. Once created

Sql server Un-nest the field in SQL Server 2014

I have a table: CREATE TABLE [dbo].[usecase]( [id] [int] NULL, [desc] [varchar](50) NULL, [comm] [varchar](50) NULL, [catgroup] [varchar](50) NULL ) ON [PRIMARY] GO Example data below: insert into usecase (id, [desc], comm, catgroup) values(1,'desc 1', 'comment 1', '1') insert into usecase (id, [desc], comm, catgroup) values(2,'desc 2', 'comment 2', '1,2') insert into usecase (id, [desc], comm, catgroup) values(3,'desc 3', 'comment 3', '1,2,3') insert into usecase (id, [desc

Sql server AWS Glue - Delete rows from SQL Table

I am creating an AWS Glue job which uses JDBC to connect to SQL Server. Intention of this job is to insert the data into SQL Server after some logic. I need to first delete the existing rows from the target SQL Server table and then insert the data from AWS Glue job into that table. I checked many articles but I could not find the details of deleting/truncating the existing rows in SQL Server. Can you please let me know if there are any options for doing this?

  1    2   3   4   5   6  ... 下一页 最后一页 共 457 页