New answers tagged sql
0
First make sure the file exists really in that location. Run ls $PGDATA if there is a mistake in the environment variable you will either see the wrong files or get an error because the path does not exist.
Then make sure the owner of the parent directory /home/destination_data_directory and all directories and files under match the user you start pg_ctl ...
3
This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.
mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema ...
2
Solution 1:
Make the event_id nullable.
ALTER TABLE events
MODIFY COLUMN event_id int unsigned NULL ;
This will allow you to insert rows which reference nothing (NULL):
INSERT INTO events
(user_id, event_id, title)
VALUES
(a_valid_user_id, NULL, 'test event 1') ;
And to see events without a "parent" event:
SELECT *
FROM events
WHERE event_id ...
0
Here is your original query
SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = `CallEventID`
LIMIT 1000
In theory, you can do the following
Subquery that has WHERE on the StartTime
Add ORDER BY StartTime
Do the LIMIT inside the subsquery
Specify a LEFT JOIN
Here is ...
1
Your WHERE clause must be after your join, perhaps?
SELECT *
FROM CallDetailRecord
JOIN CallEvent ON EventID = CallEventID
WHERE StartTime >= 1357102799000 AND StartTime <=1357880399000
Note: You may want to prefix the fields with table names or aliases...
0
If you need to run this as schema1, you could just write into your script to temporarily log in as schema2, use the USER_SOURCE solution, generate your script, log in as schema3, then run the script.
0
Why would you want to use the DB to check for files ?
Just write a shell script (if using Linux) or bat script (Windows) to check for the files, and use cron or Windows task scheduler to run that script.
You can also create the INSERT query in the script (on Linux + MySQL that should be easy enough), and every time the files are checked, a new row should ...
0
Here's my stab in the dark at what may be happening.
With a small resultset and a sufficiently simple query plan, the query memory stolen pages (that portion of the query memory grant actually used) is under the grant size, so no spill to tempdb occurs. In this case, the logical reads are what you would expect - the query thread references to user object ...
1
You can send HTML email using below code :
/*********************************************************************
Author : Kin
Date : 5/16/2013
Tested RDBMS: SQL Server 2005 and up .. for dba.stackexchange.com
Purpose : Send HTML Report
**********************************************************************/
declare @tableHTML ...
0
@haki has identified why this error occurs. You can't use an alias (KODE) defined in the SELECT list, in the definition of another column in that list. You have to use the full reference instead (ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).
But the solution provided, does not work I think because of the double nesting. You can reference a column in a inline ...
9
For each single quote you want in a string constant, you need to use two to represent it
Two quotes in the string requires you to use 4 like this:
Update Table Set col = '[189] IS NOT NULL and [189] <> ''''' Where colid= 198
0
you cant use alias from the wraping query in the a sub query
change it this way
SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
(SELECT *
FROM ...
0
If the clients are filtering in almost the same way over and over again you can create an index for those queries.
E.g. the client is filtering on SiteId and StatusId you can create an additional index:
CREATE NONCLUSTERED INDEX IX_Ticket_InsertDateTime_SiteId_StatusId ON Ticket
(InsertDateTime DESC,
SiteId [ASC/DESC],
StatusId [ASC/DESC] )
...
2
As per the code in your comment:
USE msdb
EXEC sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients = 'vince.chan@ufa.com',
@subject = 'T-SQL Query Result',
@body = 'The result from SELECT is appended below.',
@query = '(include script from above)'
The reason this won't work is the same reason why this wouldn't work:
declare ...
0
Since you want to use BPA, this PowerShell script will do the work for you.
Another thing to be aware is that you can use below freely available excellent tools for checking health/best practices for SQL Server.
sp_BLITZ or
SQL Power Doc
1
Since you are trying to avoid SSIS and BCP: If you don't have a deep-seated hatred of MS Access, you might want to take a brief look at it. It has a pretty slick flat-file import wizard. I prefer it over SSIS for small one-time (manual) jobs with flat-files. MSAccess can link directly into SQL Server (or equiv) tables and import directly into the server ...
2
As mentioned in the comments above, your time would be much better spent using SSIS for this task. Here's a recent video post that discusses handling multi-line record data files much like yours.
Since you're okay with spinning your wheels, let's try to find an answer anyway--if you have the data in a single row, you have somewhere to start. My next step ...
1
Your best option (considering your limitations) is probably using OPENROWSET with a FORMAT FILE
Take a look at those links:
Use a Format File to Bulk Import Data
You can use the BCP utility once locally to Create a Format File
I prefer the XML Format Files
0
Most questions and answers assume some sort of "binary tree indexes", but there are other sorts of indexes, perhaps most notably a bitmap index -- which as its name suggests is a bitmap per value with one bit per row to indicate (in)equality with the value. In comparison to tables and binary indexes, bitmap indexes require very little storage and are ...
1
The "Best Way" is probably to do it the former way, calculating the accurate count of messages and friends, etc is going to be easier to manage on the application side. Performance will obviously depend on how many messages and friends, some variables on the database server, and indexes.
A third solution would be to use a caching layer (like memcached) to ...
0
Depends on all the WHERE, JOIN, GROUP BY, ORDER BY (etc.) clauses in your query, and how well the DBMS query optimizer can use each of the indexes to satisfy them. It could use one index or all or anything in between, and different DBMSes will often make different choices.
It is even possible no index will be used even though it could theoretically satisfy ...
1
Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:
UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')
concat_ws() ignores NULL values. With plain ...
0
Use the UNPIVOT clause (11g):
SQL> SELECT d.*, e.table_column, e.table_data
2 FROM departments d
3 LEFT JOIN (SELECT *
4 FROM (SELECT to_char(employee_id) employee_id,
5 first_name, last_name, to_char(salary) salary,
6 e.department_id
7 ...
3
It depends very much on the DBMS being used. Some can never user more than one index per table and query other can do so.
But if there are multiple indexes sharing the same column, I doubt that even the DBMS that can use more than one index would do it, especially if the leading columns are identical.
If there is an index on (foo) and one on (foo, bar) ...
3
This is no single answer
The optimiser will choose an index that best suits the predicates of the query.
This depends on
statistics/selectivity
index key order and includes/covering
number of predicates
You may also have index intersection or key lookups where 2 or more indexes are used
1
If a lot of your queries have conditions similar to what you describe, e.g. range condition on the date column:
WHERE dateColumn >= (CURRENT_DATE() - INTERVAL 1 MONTH)
WHERE dateColumn >= '2012-01-01'
AND dateColumn < '2013-01-01'
it will be useful to define the primary key of the table as (dateColumn, tableAI), where tableAI is an auto ...
0
Maybe consider IBM DB2. It scales quite well for warehousing, and meets your requirements from what I can see.
You can download a free version (the Express-C edition) from IBM here. Some features are turned "off" (such as Database Partitioning Feature, memory/CPU limits....) but it can get you going. When you want to scale it up a notch all it takes is a ...
7
;WITH regCrs AS
(
SELECT regno, crsCode FROM dbo.PAYMENTS
GROUP BY regno, crsCode
),
f AS
(
SELECT f.crsCode, f.instNo, f.fee, regCrs.regno
FROM regCrs INNER JOIN dbo.FEES AS f
ON regCrs.crsCode = f.crsCode
)
SELECT f.regno, f.crsCode, f.instNo, f.fee,
paid = COALESCE(p.payment, 0),
DIFF = f.fee - COALESCE(p.payment, 0)
FROM f
LEFT OUTER ...
4
First, you need to reverse the outer join (either from left join to right join or by reverting the order): FROM Fees LEFT JOIN Payments
Then you'll need the COALESCE() function to convert the NULL produced by the outer join to zeros, for the payment column.
And last, you have to find out how the column RegNo should be populated when it will be NULL. I ...
2
Perhaps you want:
select a.Regno,a.crscode,b.fee,a.payment,b.fee-a.payment
from Fees
Left join Payments
on Payments.CrsCode=Fees.CrsCode and Payments.InstNo=Fees.InstNo
2
First of all, Welcome to stackexchange and thanks for your 1st Post.
To answer your questions :
if this process of reorganize the Full Text Catalog is done online or offline?
The reorganize process is Online, but is slower than Rebuild.
When the catalog reorganize is occurring the users will still be able to query the full text data?
Yes, but there ...
0
The file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WebAnalyticsServiceApplication_ReportingDB##...Aggregation20130519.ndf was probably created recently (looks like it was in the default location) and this path doesn't exist on the log shipping target, so the RESTORE is failing because it doesn't have a valid path to create this ...
0
Are you sure you have only one table? I mean, it seems that you create a table TEST_TBL without schema (taking current/default schema), and you are accessing USERID.TEST_TBL. Probably, the current schema for TEST_TBL is not USERID, and you already have a second table with tha name (USERID.TEST_TBL) that is empty.
For declared temporary tables, the schema is ...
4
By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table.
You should either create the table using the ON COMMIT PRESERVE ROWS option, or ...
1
Looks like this can be done with a single SQL statement, actually, something like this:
select case when n2 = 0 then 0 else n1/n2 from (
select
h.hireId,
count(*) as n1,
sum(case when r.hireResponse in (0,1) then 1 else 0 end) as n2
from
NewHire h, Hire_Response r
where
h.hireId = r.hireId
group by
h.hireId
)
I don't have a SQL ...
2
You need an additional select just after your insert.
declare @t table (Percentage int)
DECLARE @acc INT
SET @acc = 1
DECLARE @max INT
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
BEGIN
IF (@acc in (select HireID from NewHire))
BEGIN try
insert into @t
select
...
0
If you are going the NoSQL way you will have sharding for "free" it is built in in most of the solution at least in Couchbase, Cassandra, and MongoDB.
I am not saying that you won't be able to achieve that into an RDBMS but you may end to implement many layer at the top to manage fast access, for example I start to see caching layer in the discussion with ...
0
You need three more constraints on each of your linkage tables. The first constraint says the the two FKs, taken together, are unique. The other two constraints say that neither of the FKs can be NULL.
If your linkage tables had declared a composite PK consisting of the two FKs, you would have gotten the same effect with just one constraint. The Id on ...
0
Select *
from table
where IntermediaryPK = ParentIntermediaryPK;
Or
Select *
from table
where IntermediaryID = ParentIntermediaryID;
2
You'll have to manually query for the rows and copy then in using either T-SQL or SSIS. There's no way to take the two databases and have SQL Server just merge them into one database.
If you are using identity vales on tables you'll need to be careful of duplicate values and assign new values to the rows that you are inserting. If you have other tables ...
9
Use the better version of the undocumented sp_MSForEachDB to iterate through the databases in a SQL Server Agent job.
Or simply use a WHILE loop yourself on sys.database entries if you have a naming pattern.
7
Usually, the Postgres server process is owned by the accompanying system user (not by root or the installing user), so it only has a limited set of rights, making possible attacks somewhat less dangerous.
Additionally, the Postgres system user is normally used to carry the default privileges to initialize db clusters and access newly created databases ...
1
I think this will be slightly more efficient than the looping method you've chosen (and definitely more efficient than the recursive CTE):
CREATE FUNCTION dbo.FindPatternLocation
(
@string NVARCHAR(MAX),
@term NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN
(
SELECT pos = Number - LEN(@term)
FROM (SELECT Number, Item = ...
0
Please read the user manual section on client authentication.
You must ensure that you're using a user account that actually exists in PostgreSQL; the "localhost server password" is unlikely to be the password for your user account in PostgreSQL unless you set it up that way.
The message fe_sendauth: no password supplied suggests that you did not enter a ...
3
Not in PostgreSQL at this time. PostgreSQL doesn't actually build a single giant SQL statement; instead, it uses the plan tree of the view(s) and inserts that into the top level query's plan tree. To turn that back into SQL would require a deparser, something PostgreSQL doesn't have a the moment.
You can kind-of do-it-yourself by replacing references to ...
1
--Recursive CTE
with cte as
(select 'ali reza dar yek shabe barani ba yek dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...' as name
),
pos as
(select patindex('%ali%',name) pos, name from cte
union all
select pos+patindex('%ali%',substring(name, pos+1, len(name))) pos, name from pos
where patindex('%ali%',substring(name, ...
2
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...'
Declare @a table (pos int)
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex('%ali%',@name)
while @pos > 0 and @oldpos<>@pos
begin
insert into @a Values (@pos)
...
1
Your design is correct. Tables with many to many relationship needs to have an interface table. Interfaces table contains foreign keys to the main table, it must have a primary key. Good luck
Top 50 recent answers are included
