Database

26-Nov-07 9:29

It's telling you that at least one of your parameters is the wrong type. I would suspect that it's the first one - I'm guessing that you are using an autoincrementing column here. If so, you should really write your query out with an explicit definition as in

INSERT INTO pic_newsletter_email_addr (column2) VALUES ('foo@foo.com');

Deja View - the feeling that you've seen this post before.

Re: What's wrong with this SQL query?

Jordanwb26-Nov-07 9:31

Jordanwb

26-Nov-07 9:31

That was it, thanks.

Re: What's wrong with this SQL query?

Pete O'Hanlon26-Nov-07 10:56

26-Nov-07 10:56

How cool that I could guess this without even seeing the code - not that I've ever hit this problem before.

Deja View - the feeling that you've seen this post before.

Conversion help

solutionsville26-Nov-07 9:12

solutionsville

26-Nov-07 9:12

I want to take an IP address e.g. 111.222.333.444 and convert it to either a float or an unsigned datatype.

Thanks,

Re: Conversion help

Pete O'Hanlon26-Nov-07 9:20

26-Nov-07 9:20

Why? An IP address is really a string of numbers - not a number. Removing the dots won't help because you really don't know where to put them back. I suppose you could always store it as four numbers, but it still doesn't gain you much.

Deja View - the feeling that you've seen this post before.

how to make table whit only one id

Re: Conversion help

solutionsville26-Nov-07 9:25

solutionsville

26-Nov-07 9:25

The reason is for faster search indexing. I found this after I posted;

Create FUNCTION [dbo].[Dot2LongIP]( @IP VarChar(15) )
RETURNS BigInt
AS
BEGIN
DECLARE @ipA BigInt,
@ipB Int,
@ipC Int,
@ipD Int,
@ipI BigInt
SELECT @ipA = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipA) - 1 )

SELECT @ipB = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipB) - 1 )

SELECT @ipC = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipC) - 1 )

SELECT @ipD = @ip
RETURN ( @ipA * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD
END
RETURN @ipI
END

This does speed it up and seems to meet my needs.

Thanks,

E_Gold26-Nov-07 7:30

E_Gold

26-Nov-07 7:30

hi i have table that have id file, and i want that only one value will
apir in arecord.

my table
========
123
333
444
123
444
123

what i need
===========
123
333
444

thank's alot

more information

E_Gold26-Nov-07 7:32

E_Gold

26-Nov-07 7:32

i work in access 2007
name of the table: men
name of the fild : id

Re: how to make table whit only one id

astanton197826-Nov-07 7:36

astanton1978

26-Nov-07 7:36

Are the rest of the values on the row different like this? Are you just trying to get a list of DISTINCT ColumnA values?

ColumnA ColumnB
-----------------
123 ABC
222 ABD
123 ABF
222 SDS

Re: how to make table whit only one id

pmarfleet26-Nov-07 8:09

pmarfleet

26-Nov-07 8:09

Use a SELECT DISTINCT query to fetch unique values from a table.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: how to make table whit only one id

sandhya1419-Dec-07 22:29

sandhya14

19-Dec-07 22:29

wht u want to do?...while retrieving or in the table itself?....

SQL Query from multiple linked tables

jontyler26-Nov-07 7:25

jontyler

26-Nov-07 7:25

Hello,

I have been working for two weeks on a problem to get data out of a database in a meaningful format. The database consists of basic server information along with information on some users, websites, and applications. The database structure is as follows:

Tables: Server Users Applications WebSites
Fields: ServerName ServerName ServerName ServerName
IPAddress UsrName AppName SiteName
Alive
ADsPath
dateAdded
datePwdChanged

I am trying to build a query/report that will give me information in the following format:

ServerName1 IPAddress Alive ADsPath dateAdded datePwdChanged AppName1 UsrName1 SiteName1
AppName2 UsrName2 SiteName2
AppName3 UsrName3 SiteName3
AppName4 SiteName4
AppName5
ServerName2 IPAddress Alive ADsPath dateAdded datePwdChanged AppName1 UsrName1 SiteName1
AppName2 UsrName2 SiteName2
AppName3 UsrName3 SiteName3
AppName4
and so on. (Tried to get the formatting right on here, but not sure it displays completely accurate).

So far, I get either a query showing servernames multiple times with multiple application, user and site entries or I get nothing.

I have googled everything I can think of and have run out of places to look. I have tried googling on what I want it to look like, how I think it should be queried, all to no avail. Obviously, I would be grateful if someone had a magic bullet for this, but at least pointing me in a new and valuable direction would be hugely appreciated.

Cristian Amarie26-Nov-07 7:58

Cristian Amarie

26-Nov-07 7:58

Use a table (fixed or not) to keep this report-like data and populate it using a stored procedure.

Nuclear launch detected

jontyler26-Nov-07 9:03

jontyler

26-Nov-07 9:03

I am assuming that I would need to create a one-to-one column relationship with the server table columns, however, for the apps, users, and websites, would I create a memo/text field and then concatenate all of the entries to show in there?

I am not as familiar with the intricacies of SQL and using stored procedures to get something like this done.

andyharman26-Nov-07 9:01

andyharman

26-Nov-07 9:01

Hi Jon

Your problem can most-easily be solved at a presentation-level, rather than at a SQL level. Under MS-Access, you would define a report that lists just your servers. Then embed 3 sub-reports (for each of your other tables) linking to the server. Most other reporting tools offer similar sub-report facilities.

If you do need to solve it using SQL-Server then your will need to use a stored procedure and a bunch of cursors.

Regards
Andy

SQL Server 2005 Performance Tips

jontyler26-Nov-07 9:05

jontyler

26-Nov-07 9:05

Actually, this makes much more sense. I don't have to do it in SQL. I just need the information in a format that is easily read and makes the most sense for the project.

SQL Server Diagrams

Brendan Vogt26-Nov-07 7:12

Brendan Vogt

26-Nov-07 7:12

Hi,

How do I get the diagrams to print smaller on a page? It's those diagrams that display the tables with their relations to other tables. Currently the tables are printed huge and there is a lot of white space in between.

Are there any better 3rd party tools that can create better diagrams?

Regards,
Brendan

Re: SQL Server Diagrams

Paul Conrad26-Nov-07 16:27

Paul Conrad

26-Nov-07 16:27

.NET Enthusiast wrote:
Currently the tables are printed huge and there is a lot of white space in between.

Have you tried rearranging them closer together?

"I've seen more information on a frickin' sticky note!" - Dave Kreskowiak

Brendan Vogt26-Nov-07 4:22

Brendan Vogt

26-Nov-07 4:22

Hi,

I have searched Google for performance tips but didn't find many because I didn't understand. If any one has a good readable topics please forward it to me.

If I changed int to smallint can this also result in better performance?

Thanks
Brendan

Re: SQL Server 2005 Performance Tips

andyharman26-Nov-07 5:42

andyharman

26-Nov-07 5:42

Hi Brendan

Changing an "int" to a "smallint" is unlikely to make much of a difference.

Putting decent indexes in place will normally yield a quick-win for performance.

Pete O'Hanlon put together these articles[^] about performance optimisation.

Regards
Andy

How can i know which stored procedure is executed when and by which program? [modified]

zeemalik26-Nov-07 2:36

zeemalik

26-Nov-07 2:36

I want to know which stored procedures are executed in the last 24 hour in my database and i wish if i could also know about the program who called the stored procedure?
OR
I can know about a specific stored procedure that when it was executed last time and by which program?

Please help...its urgent?

Regards,

Zeeshan Malik

-- modified at 12:21 Monday 26th November, 2007

To do what can be done is a Intelligent Job and to do what should be done is a Genious Job
done is a Genious Job

Re: How can i know which stored procedure is executed when and by which program?

Pete O'Hanlon26-Nov-07 9:24

26-Nov-07 9:24

You need to look at using SQL Profiler to identify what's going on down at the database level. It won't tell you exactly which program calls the SQL, but it will tell you what SQL is hitting the database and who is doing it? Take a look at this[^] article for details.

Now a request - please don't put it's urgent in a post. They are all urgent to somebody, and this doesn't help anybody.

Deja View - the feeling that you've seen this post before.