Re: How to avoid the alphabetic order of the columns returned when selected from a SQL linked server. - Database Discussion Boards

Re: Inserting bulk data to SQL DB (SQL 2000)

Paddy Boyd21-Dec-07 2:27

Paddy Boyd

21-Dec-07 2:27

Have you considered using something like DTS or SSIS instead. They are set up to bulk load data.

Custom Auto Generated ID

xavier198821-Dec-07 0:58

xavier1988

21-Dec-07 0:58

Hi I wish to create a custom auto generate ID, such as P110, P111 which is generated automatically is there ways to set it in sql server?Thanks alot

Re: Custom Auto Generated ID

pmarfleet21-Dec-07 2:21

pmarfleet

21-Dec-07 2:21

You can set an int column to be an IDENTITY field to generate sequential numbers in that field. If you want your sequence to be alphanumeric, you should create a computed column for that purpose.

Paul Marfleet

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

Databas connection with java script

rajesh4u20-Dec-07 22:48

rajesh4u

20-Dec-07 22:48

Hi
can any one knows how to establish database connection with java script

thanks in advance

Re: Databas connection with java script

Colin Angus Mackay21-Dec-07 0:12

Colin Angus Mackay

21-Dec-07 0:12

rajesh4u wrote:
can any one knows how to establish database connection with java script

You could use "Astoria" - now known as ADO.NET data services: Astoria website[^]

Upcoming FREE developer events:
* Developer Day Scotland

My website | blog

Re: Databas connection with java script

NetBot22-Dec-07 18:26

NetBot

22-Dec-07 18:26

You can do it using Ajax only.

If u r using VS 2005, u can use the Ajax control toolkit for this purpose

How to avoid the alphabetic order of the columns returned when selected from a SQL linked server.

kalam20-Dec-07 22:34

kalam

20-Dec-07 22:34

Hi,

I created a SQL Linked server for excel file. I am using that Linked server to Import the data from Excel files.When selecting the reords from this linked server , it returns the data in alphabetical order.
But I want the same order of columns which is in Excel file.

for selecting the records i am using tis Query:
select * from ExcelImport...Test$

where, ExcelImport is Linked server name and Test$ is the sheet in the excel file.

Please help.

kalam.

Re: How to avoid the alphabetic order of the columns returned when selected from a SQL linked server.

Paddy Boyd21-Dec-07 1:03

Paddy Boyd

21-Dec-07 1:03

Can you not provide the named columns in the select statement:

e.g. Select cola, othercolumnname from excelimport

Sql Server Login

cbkulathunge20-Dec-07 20:05

cbkulathunge

20-Dec-07 20:05

hi,
I need to login to remote sql server trough my Application and need to change the server location

Re: Sql Server Login

Paul Conrad22-Dec-07 7:05

Paul Conrad

22-Dec-07 7:05

Is there a problem you are having? Your post is too vague.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Need Query for Searching Name from database.

mithun narayanan20-Dec-07 19:20

mithun narayanan

20-Dec-07 19:20

i need a query for checkin a name whether it exists or not in database,,
am storing firstname nd lastname in seperate columns.

bt if i enter a first name only in textbox i need to display all names(firstname+lastname) ,starting from the entered first name

PLZ HELP...
Waiting 4 reply...

Re: Need Query for Searching Name from database.

^That's Aragon20-Dec-07 19:31

^That's Aragon

20-Dec-07 19:31

Hi Mithun,

Please try this. Hope it will help you.

if exists(select ID from Tablename where nameFirst like '%Your TextBox Value%')<br />
<br />
select nameFirst + SPACE(1) + nameLast As Name from Tablename where nameFirst like '%Your TextBox Value%'

Keep Smiling Smile | :)

Re: Need Query for Searching Name from database.

mithun narayanan20-Dec-07 20:04

mithun narayanan

20-Dec-07 20:04

Thank u Boss its Workin nw... Big Grin | :-D

bt am passing the name as a parameter

where cand_fname like @cand_fname

its nt wrking

bt if give as cand_fname like 'mith'

this works
so how change that

Re: Need Query for Searching Name from database.

mithun narayanan20-Dec-07 20:50

mithun narayanan

20-Dec-07 20:50

k i got that ive changed to

where cand_fname like '%'+@cand_fname+'%'

thnk u..... Big Grin | :-D

Date

trilokharry20-Dec-07 17:49

trilokharry

20-Dec-07 17:49

how to get system date without time in
SQL Server 2005

thanks

You get the best out of others when you give the best of yourself.

Re: Date

^That's Aragon20-Dec-07 19:24

^That's Aragon

20-Dec-07 19:24

Hi,

Try this

select convert(varchar(50),getdate(),101)

Regards.

Keep Smiling Smile | :)

Re: Date

trilokharry21-Dec-07 0:54

trilokharry

21-Dec-07 0:54

Thank u so much
It's working fine.

You get the best out of others when you give the best of yourself.

Getting tables from a database

MarkB77720-Dec-07 16:47

MarkB777

20-Dec-07 16:47

Maybe i've overlooked it, but can someone tell me how I could get a list of all the tables in a database once i've connected? (C#).

Thankyou.

Mark.

Re: Getting tables from a database

pmarfleet20-Dec-07 21:18

pmarfleet

20-Dec-07 21:18

It depends on what type of database you're using. People at CodeProject aren't psychic, so you'll have to provide that information if you want help.

Paul Marfleet

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

Re: Getting tables from a database

Rajkamal_dfine21-Dec-07 19:47

Rajkamal_dfine

21-Dec-07 19:47

If you using SQL Server
Try this

select object_name(id) from sysObjects where type = 'u'

N.Rajakumar B.E.,
Application Developer,

SQL Server 2008 virtual training available for ITPros and developers during January

brucedkyle20-Dec-07 13:59

brucedkyle

20-Dec-07 13:59

Microsoft® SQL Server™ 2008 provides a comprehensive data platform that is more secure, reliable, manageable and scalable for your mission critical applications. Developers can create new applications that can store and consume any type of data on any device. Users can make informed decisions with relevant insights.

To take advantage of the new features in your products, Microsoft is offering several training opportunities.

A 10-part web seminar series covers topics ranging from high availability to manageability, security, business intelligence, and reporting. January 8 – January 30 at 9:00 AM PST. Learn more and register for the Microsoft SQL Server 2008 training web seminars. Some of the sessions include the hot business intelligence (BI) topics:

What’s new in SQL Server 2008 for Database Administrators January 8
Implementing Change Data Capture (CDC) in SQL Server 2008 January 9
Working with the new DATE data type in SQL Server 2008 January 21
Administering Servers by Using Declarative Management Framework (DMF) Policies January 22
earn How to Build Occasionally Connected Applications January 23
Authoring Reports Using Microsoft SQL Server 2008 Reporting Services January 24
Business Intelligence Overview January 25
Reporting Services January 28
Data Warehousing January 29
Building a cube from scratch January 30

For more information of interest to developers, see the Microsoft US ISV blog[^]

Collation Issue When Using PRINT

Tee12320-Dec-07 7:31

Tee123

20-Dec-07 7:31

Can someone help me with 2 issues that I have? My first issue is when I try to print out that the user account exist with the following line: PRINT 'The login ' + @login_name + ' already exists.'
I receive the following error: Server: Msg 446, Level 16, State 8, Line 45
Cannot resolve collation conflict for concatenation operation.
What can I do to solve this?

My second issue is I'm trying to have this procedure work for both Sql 2000 and Sql 2005. I currently using 2000 for testing but in 2005 to create a user login the line of code: --CREATE USER 'username' FOR LOGIN 'loginname' creates a syntax error. Is there anyway around this?

DECLARE @sqlVersion char(2)
DECLARE @login_name char(12)
DECLARE @loginpassword char(12)
DECLARE @username char(12)
DECLARE @dbase char(8)

SET @login_name = loginname
SET @loginpassword = password
SET @username = username
SET @dbase = password

SELECT @sqlVersion = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS char),1,1)

use database
IF NOT EXISTS(SELECT name FROM sysxlogins WHERE name = @login_name)
IF @sqlVersion = 8
BEGIN
EXEC sp_addlogin @login_name, @loginpassword, @dbase
EXEC sp_grantdbaccess @login_name, @username
EXEC sp_addrolemember 'db_datareader', @username
EXEC sp_addrolemember 'db_datawriter', @username
/* Grant the account access to the rms database */
use database
EXEC sp_grantdbaccess @login_name, @username
/* Grant rights to the STORED PROCEDURES */
BEGIN TRANSACTION

USE database
GRANT EXECUTE ON SP TO username
IF @@ERROR <> 0 GOTO err_handler
PRINT '----User was successfully created ----'
COMMIT TRANSACTION
END
ELSE
PRINT 'The login ' + @login_name + ' already exists.'

IF @sqlVersion = 9
BEGIN
EXEC sp_addlogin @login_name, loginpassword, @dbase
--CREATE USER 'opcenteruser' FOR LOGIN 'opcenteruser'
EXEC sp_addrolemember 'db_datareader', @username
EXEC sp_addrolemember 'db_datawriter', @username

use database
EXEC sp_grantdbaccess login_name, @username
EXEC sp_addrolemember 'db_datareader', @username
BEGIN TRANSACTION
/* Grant rights to the STORED PROCEDURES */
USE database
GRANT PERMISSIONS TO STORED PROCEDURES....
IF @@ERROR <> 0 GOTO err_handler
PRINT '----User was successfully created ----'
COMMIT TRANSACTION
END
RETURN
err_handler:
ROLLBACK TRANSACTION
RAISERROR ('Failed to commit transaction.', 16,1)
RETURN

GO

Terrance C.

Parameters to a SQL View?

keniagm20-Dec-07 7:23

keniagm

20-Dec-07 7:23

Hi everybody,

I'm using MSSQL2005 to build a report system and I need to create some views in order to speed up the execution of queries, but I need these views created so that the WHERE clause contains dynamic values, like parameters or so. If I write this as a simple query, it would be as follows:

SELECT some_field FROM my_table WHERE another_field = @my_parameter

of course, the query I want turned into a view is much more complex, but the point here is the WHERE part and the use of a parameter.
So, I'm wondering if there's a way of using the view somehow that I can specify this parameter.
If anyone knows if this can be done and how to do it, please, let me know.
Thanks in advance

Cheers,

Kenia

Re: Parameters to a SQL View?

pmarfleet20-Dec-07 9:18

pmarfleet

20-Dec-07 9:18

SQL Server doesn't support parameterized views. However you can achieve the same result with a table-valued user-defined function. See this article[^] for more information.

Paul Marfleet

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

SQL Query

sandhya1420-Dec-07 1:04

sandhya14

20-Dec-07 1:04

Hi all

I have table like

Table1

ID Status Flaged

1 NotApproved 1

2 NotApproved 0

3 NotApproved NUll

4 NotApproved NULL

here i need to get the ID in which status=NotApproved and Flaged <>1

so the result should be like this

2 NotApproved 0

3 NotApproved NUll

4 NotApproved NULL

i should get these 3 records....

hw will write the query?...how to pass the parameters?

thanks

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