Database Discussion Boards

Re: Hash code

Pete O'Hanlon31-May-11 4:10

Pete O'Hanlon

31-May-11 4:10

What version of SQL Server are you using? If it's sufficiently advanced, you could apply this as a CLR Trigger.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Re: Hash code

Vimalsoft(Pty) Ltd31-May-11 4:35

Vimalsoft(Pty) Ltd

31-May-11 4:35

I am SQL 2008

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: Hash code

Pete O'Hanlon31-May-11 4:57

Pete O'Hanlon

31-May-11 4:57

Then it's job done. You can use a SQL CLR trigger and store the hash when the image is inserted or updated.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Re: Hash code

Vimalsoft(Pty) Ltd31-May-11 5:25

Vimalsoft(Pty) Ltd

31-May-11 5:25

this did the Job

 ;with cteSchools as (SELECT DISTINCT S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME]
FROM SCHOOL S

WHERE S.SCHOOLID IN (SELECT SCHOOLID FROM #SCHOOLS))


SELECT S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME],SP.PHOTO AS [PHOTO]
FROM cteSCHOOLS S
CROSS APPLY (Select TOP (1) PHOTO from SchoolPhoto SP
WHERE SP.SCHOOLID = S.SCHOOLID
ORDER BY SCHOOLID DESC) SP

Thanks

Re: Hash code

Shameel31-May-11 5:28

Shameel

31-May-11 5:28

Use the T-SQL function hashbytes like this:

select hashbytes('MD5', 'your value')

BCP CLI Cannot find Invalid object name. [modified]

Groulien30-May-11 23:37

Groulien

30-May-11 23:37

I'm trying to export the results of a stored procedure to a csv, here's what I've got:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" myDb.dbo.spExport out "Data.csv" -T -Slocalhost -t";" -c
It keeps telling me "Invalid object name 'myDb.dbo.spExport'."

Other stored procedures don't work either and none require parameters.
Pointing to a table works so I thought I was pointing to at the SP's the right way.
What am I doing wrong?

-Nevermind, got it working:
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" myDb.dbo.spExport queryout "Data.csv" -T -Slocalhost -t";" -c

modified on Tuesday, May 31, 2011 10:08 AM

Re: BCP CLI Cannot find Invalid object name.

Corporal Agarn31-May-11 4:31

Corporal Agarn

31-May-11 4:31

BCP is tricky at best.

MSSQL Backup database - without BACKUP command

David Hovey30-May-11 4:58

David Hovey

30-May-11 4:58

Hi all, I have a MSSQL database with Godaddy, and I have access using the SQL Server Managements Studio, and of course their Hosting center interface with a web browser.

I need a more robust backup setup. Currently I would have to manually initiate a backup.

-Is there a way to automate a backup using The Sql Server Management Studio? Perhaps from my client PC (COM, DLL)?

-I can't use the BACKUP command statement as the server is attempting to create the backup file on THEIR servers. The only information I'm using the database is table data. If using .NET along with the SQL database, are there any methods (that would be reasonably affective) to SELECT statement for each table in db. Then export this database to csv or other format.

Appreciate your help.

Re: MSSQL Backup database - without BACKUP command

SilimSayo30-May-11 8:41

SilimSayo

30-May-11 8:41

How about creating the back on their servers and then ftping the backup files to your server/computer?

Re: MSSQL Backup database - without BACKUP command [modified]

Shameel31-May-11 0:33

Shameel

31-May-11 0:33

IMO, the only place where you can backup the databases on a server is on the server itself. What you can do is to write a script to execute the BACKUP command and FTP the file from the server to your PC/Laptop.

modified on Tuesday, May 31, 2011 7:11 AM

Re: MSSQL Backup database - without BACKUP command

SilimSayo31-May-11 3:49

SilimSayo

31-May-11 3:49

If I may ask, how is your answer different from mine? Confused | :confused:

Re: MSSQL Backup database - without BACKUP command

Shameel31-May-11 4:22

Shameel

31-May-11 4:22

I did not read your post.

Re: MSSQL Backup database - without BACKUP command

SilimSayo31-May-11 7:24

SilimSayo

31-May-11 7:24

No problem Smile | :)

Stored Procedure

berba29-May-11 23:09

berba

29-May-11 23:09

Hi,

Can anyone provide me with a link for tutorials on stored procedures?

Thanks

Cheers
Nikhil

Re: Stored Procedure

Luc Pattyn29-May-11 23:59

Luc Pattyn

29-May-11 23:59

Everyone can[^].

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.3

Re: Stored Procedure

SilimSayo30-May-11 2:31

SilimSayo

30-May-11 2:31

Depends on what Database you're targetting.

Copy table 1 to table 2 for missing data only

MAW3026-May-11 17:02

MAW30

26-May-11 17:02

I have two tables that are identical, 1 table has old data and the other new data.

I want to copy the old to the new where the records are either blank or null is there an easy way I can do this.

Any help is appreciated.
Thanks in advance,
Michael

Re: Copy table 1 to table 2 for missing data only

Blue_Boy26-May-11 21:14

Blue_Boy

26-May-11 21:14

Which column is unique on both tables?

Syntax is like this:

Insert into table2 (col1,col2,col3)
select col1,col2,col3 from table1

In this select statement you have to include where clause with unique column in table1 and tale2

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.cacttus.com

Re: Copy table 1 to table 2 for missing data only

PIEBALDconsult27-May-11 2:32

PIEBALDconsult

27-May-11 2:32

Are they in the same database? Are they on the same server? Are they the same brand of database?

When I had to copy data from an Ingres database on a Unix system to an SQL Server database on Windows there were times I had to simply throw the records at the database one by one and catch-log-and-ignore any duplicate Exceptions encountered. Simple but effective, and quicker than trying to test each record first.

Re: Copy table 1 to table 2 for missing data only

MAW3027-May-11 6:29

MAW30

27-May-11 6:29

They are in the same DB on the same server.

I copy all the old records to a similar table
Delete the orig table's records
Start with a blank table, this is a starting point to build other info, I do not want many of the records from the old included which I do not know until I start new.
Add new data
Then I want to insert old data where new data is left blank and do not wish to overwrite new data.

The previous response said the following:

Insert into table2 (col1,col2,col3)
select col1,col2,col3 from table1

I am thinking if I used the above then added

Where col1 IS LIKE '' or col1 IS NULL

but I would have to do this for every column, I was hoping there would be a quick easy way

Michael

Re: Copy table 1 to table 2 for missing data only

jschell27-May-11 9:07

jschell

27-May-11 9:07

Might help to know which database vendor you are referring to.

Easy is relative.

You can do an update statement with a 'case' for each column.

Re: Copy table 1 to table 2 for missing data only

dasblinkenlight29-May-11 17:45

dasblinkenlight

29-May-11 17:45

SQL Server supports a rather convenient MERGE[^] command that lets you specify criteria of two records being 'identical', actions to take on finding a match, and actions to take on finding missing records. The documentation at the link supplies several large examples, so you should be able to construct your statement relatively easily.

Problem with POWER

TheComputerMan26-May-11 4:14

TheComputerMan

26-May-11 4:14

I am getting an error message in an SQL statement that I don't understand

This is the statement:

UPDATE [QVSData].[dbo].[tbl_CentCatalogQVS]
SET [Energy] = (POWER(10,(4.8+([Magnitude]*1.5))))/POWER(10,3)
WHERE [AutoID] = 1

This is just a test hence restricted to 1 line to see if it works. It does not, and I am scratching my head as to why not.

This is the error:

Arithmetic overflow error for type int, value = 1995262314968882.700000

There is no int in the table. The affected fields here are:

The Mag field is a decimal field (10,4) with in this instance a value of 7.0000

The energy field is a decimal field (20,1)

Anyone have any ideas as to why this error is appearing?

Actually I make that value 1995262314968.8 so it seems that it is possibly falling over as it calculates the first part before doing the division to get to Kilojoules. Why is it using int?

To make it easier to figure out this error occurs if you run this:

DECLARE @mag decimal(10,4)
DECLARE @energy decimal(20,1)

SET @mag = 7
SET @energy = (POWER(10,(4.8+(@mag*1.5))))/POWER(10,3)

SELECT @energy

The you get:

Msg 232, Level 16, State 3, Line 5
Arithmetic overflow error for type int, value = 1995262314968882.700000.

(1 row(s) affected)

And the value is NULL

Sorry forgot to add: SQL Server 2005 Developer Edition

Re: Problem with POWER

TheComputerMan26-May-11 4:27

TheComputerMan

26-May-11 4:27

Yes I am replying to myself!

This fixed it

DECLARE @mag decimal(10,4)
DECLARE @energy decimal(20,1)
DECLARE @log float

SET @log = 10
SET @mag = 7
SET @energy = (POWER(@log,(4.8+(@mag*1.5))))/POWER(@log,3)

SELECT @energy

Re: Problem with POWER

SilimSayo26-May-11 7:46

SilimSayo

26-May-11 7:46

From your subject line I thought don't like people in positions of power/government/ etc Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.