|
Hi,
How can I insert into a table using a value and a select clause at the same time?
I have the following:
DECLARE @id INT,
@xmlHandle INT,
@Xml AS nvarchar(MAX)
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @Xml
INSERT INTO table
VALUES (@id,
(SELECT p1, p2, p3, p4 FROM OPENXML (@xmlHandle, '/root/table', 1) WITH
(
p1 INT './@p1',
p2 NVARCHAR(50) './@p2',
p3 NVARCHAR(30) './@p3',
p4 NVARCHAR(30) './@p4'
)))
EXEC sp_xml_removedocument @xmlHandle
Cheers
hmmm pie
|
|
|
|
|
The syntax is like:
INSERT INTO table (column1name, column2name, ...)
SELECT @id,
p1,
p2,
p3,
p4
FROM OPENXML (@xmlHandle, '/root/table', 1)
WITH (p1 INT './@p1',
p2 NVARCHAR(50) './@p2',
p3 NVARCHAR(30) './@p3',
p4 NVARCHAR(30) './@p4'
)))
So you don't use the values keyword at all.
|
|
|
|
|
I'm trying to write an SSIS package that will read data from 3 hidden sheets in a workbook.
The idea is that other departments can input data into 3 non-hidden sheets, that have a user friendly layout along with instructions and examples, etc.
And the 3 hidden sheets then take this data and hold it in a basic table format with a single header row.
To simplify importing we want to just read the data from these hidden sheets... but a SSIS's standard Excel connection / Excel Source can't seem to see these hidden sheets.
Is there a way to get SSIS to see these hidden sheets without making them visible in the workbook itself?
Thanks.
|
|
|
|
|
When referring to the worksheet, try using syntax (with correct path):
...\MyExcelWorkbook.xls![Some hidden worksheet]
|
|
|
|
|
Thank you, that looks like it may be halfway to working.... now I'm getting a message about the source being read-only when I try to do anything with my Excel Source object now 
There is no password protection, currently on the workbook or the sheets.
|
|
|
|
|
Red_Wizard_Shot_The_Food wrote: now I'm getting a message about the source being read-only
Red_Wizard_Shot_The_Food wrote: There is no password protection, currently on the workbook or the sheets
Is the file read-only? If it is, could you remove that. Another possibility could be that it resides in a directory where you don't have write privileges or at file level, perhaps you don't write privilege to the file.
|
|
|
|
|
Hi there
I tested Microsoft report for the first time, the report viewer is pretty cool and connecting to db (using dataset) is far easier that Crystal report BUT I have bad design problem.
the first thing in report designer is the first problem, the width of page is terrible! a lot of things in designer is not easy to use and I can't tell the designer how to adjust the table of contents to fill page.
anyone has any idea what is my wrong? do you have same problems?or good sources to look?
thx in advance
|
|
|
|
|
Hi,
I've been stuck for some time trying to set up a database and access it from a program.
I don't know which database to use which is part of my problem. I've tried using sql server but I don't know how to create a connection to it from a program. For example I have a connection string but I don't know what to use as a user id or password. I had an oleDb database set up and it was working fine but I don't know how to write data to it.
Could somebody give me advice on which database to use and how to set it up?
Any help would be appreciated.
Thanks
oh by the way I'm using c#
|
|
|
|
|
I would advise you to get a few good books, but to your questions:
toprogramminguy wrote: I don't know which database to use which is part of my problem
You could use SQL Server Express Edition. Download, install it and start using by creating a db, tables etc.
toprogramminguy wrote: I've tried using sql server but I don't know how to create a connection to it from a program
You use SqlConnection[^]. Define a connection string and open the connection. Good example in the link in examples.
toprogramminguy wrote: I don't know what to use as a user id or password
If you use integrated security, you don't need separate username and password. If you use SQL Server authentication, you use SA as user and the password is defined during setup.
toprogramminguy wrote: I had an oleDb database set up and it was working fine but I don't know how to write data to it
Don't know what this means. OleDb is a way to connect to a database, not a database.
One book you could start with is: Beginning C# 2008 Databases: From Novice to Professional [^]
|
|
|
|
|
I mean an mdb database. I thought that was an ole db database. Thanks anyway 
|
|
|
|
|
toprogramminguy wrote: I mean an mdb database
Okay, I see. Mdb is a SQL Server database file which can be attached to an existing SQL Server instance and then it can be used.
toprogramminguy wrote: Thanks anyway
You're welcome
|
|
|
|
|
Hi..
This is the question I was asked in an interview...
say.. I am executing a stored procedure as under
exec sp_temp("select * from emp")
how will i write in stored procedure to execute this query if i receive this value in one parameter say..@sql as under
create procedure sp_temp
@sql varchar(100)
as
begin
-- what would i write here to execute query in @sql
end
////////////////////////
As I didn't reply to interviewer, I want to know this....
thanks....
Hemant
By:
Hemant Thaker
|
|
|
|
|
You could have written:
create procedure sp_temp
@sql varchar(100)
as
begin
exec (@sql)
end
But then I would have added that it would have been a bad design...
|
|
|
|
|
I am trying to connect to a SQL-2005 Remote server through several VS-2008 applications. Two are web applications and the other is a VB.Net application. In all cases, the connection fails and the error message in the local Windows Event log is #26, suggesting that possibly the SQL server does not allow remote connections.
I know that the remote SQL server is configured correctly because I have gone through all of the steps (Surface Area Configuration, SQL Config, etc). More importantly, the connection through these applications is fine when I disable my local Windows Firewall.
On my Firewall I have created Exceptions for both SQL Server and SQL Browser. I have opened TCP port 1433 and UDP port 1434.
In all of my research, this is the extent of the solutions provided and so I am at a loss. Turning off the Firewall while debugging is a temporary solution and somewhat safe since my network is protected by a separate Firewall device, however, I would like to resolve this issue. I think there are enough developers out there having this same problem so hopefully someone has resolved it beyond the solutions I have tried.
Thanks if you can help!
|
|
|
|
|
Few questions:
- tcp/ip is enabled? (firewall also blocks named pipes)
- what tcp port your sql server is listening?
- do you use dynamic ports?
|
|
|
|
|
TCP/IP is enabled as well as named pipes.
According to the registry, Dynamic ports is enabled which may explain the problem. After messing with this and restarting SQL server (still using Dynamic Ports) I was able to connect. The last Dynamic Port used is 4316. The weird part is that I'm not opening port 4316 on my Firewall and yet the applications can now connect.
It seems as though I should configure the server to listen on a static port (or ports).
|
|
|
|
|
Okay, here's a follow-up. The reason Dynamic IP address might have been enabled on this server is that there is also an instance of SQL-2000 running and that must have been using port 1433. I used the dynamically assigned port # that I found in the registry as the new Static port number for IPALL and restarted the SQL service. I then opened this port in my local firewall.
I can connect to the SQL server through the application. I'm not sure if I have completely solved my problem but I have opened up my understanding of the issue.
Thanks for the reply Mike. You pushed me in the right direction!
|
|
|
|
|
Hampden Tech wrote: there is also an instance of SQL-2000 running and that must have been using port 1433
Dynamic ports is fine. If you have another SQL Server (the older one) in port 1433, you should open 1434 (or some other port) for tcp traffic for this SQL Server (I recall you opened udp). Also make sure that the newer SQL Server is listening on 1434. When changing the network protocols, prefer using SQL Server configuration manager in SQL Server 2005.
Hampden Tech wrote: Thanks for the reply Mike
You're welcome.
|
|
|
|
|
I have an application that uses SQL Server 2005 Express at site A.
I now have users that would like to have access to this application from site B.
At site B I have a server that also has SQL Server 2005 Express.
I would like to explore the use of SQL replication technology to see if that would work for when a user connects to the database, it will pull from its respective local DB. These sites are currently connected to each other through an IPSEC tunnel through our WAN link.
Does SQL Server 2005 support Database replication?
Is it relatively easy to setup?
Can someone point me in the general direction of where I can get some step-by-step directions?
Thank you all very much,
Joey
modified on Friday, January 23, 2009 7:04 PM
|
|
|
|
|
SQL Server supports replication and one-way replication is quite easy to set up. Two-way replication (merge) is much more complex (this is true for all databases).
You can read basics for replication for example here: SQL Server Replication[^]
However since you're using Express edition in all nodes, it won't be possible to setup full replication. Express Edition can only be a subscriber in replication scenarios so you would need a standard version in your setup in order to create a publisher.
For more info about different features in different editions, see: Features Supported by the Editions of SQL Server 2005[^]
|
|
|
|
|
I understand. In order to get full replication I have to have at least one publishing point (which has to come from at a minimum, a standarad version of SQL) and I also have to have one subscriber. Would the DB replicate on a specfic timed interval or when it detects that there has been changes? Thank you,
Joey
|
|
|
|
|
In short: on a specfic timed interval. Replication uses SQL Server Agent jobs which are executed on an interval (configurable).
|
|
|
|
|
Mike, in your opinion, what type of bandwidth would we require for this type of replication to happen smoothly? The sites are linked together through an IPSec tunnel, however we are using a Cable Modem at one end (~3-5MBps down and no more than ~1MBps up) and the other end is 2 bonded T1 connections. I'm assuming that the replication will really only replicate the necessary changes, I'm just a little concerned about it becoming a bandwidth suck.
Thanks,
Joey
|
|
|
|
|
Sounds like you need transactional replication. That replication works so that first a snapshot is taken from the source to get the current situation for the publication. This is delivered to subscriber(s). After that modifications to publication are gathered at the source and they are delivered to subscribers at configurable intervals.
Just a warning. This works fine when the transfer is only from source to target. If it's bi-directional so that the same data can be modified on all nodes, you will easily get into trouble because of update conflicts.
Finally to your question. The bandwith depends at least on these things:
- replication interval, the smaller the interval is, the less modifications need to be transferred
- amount of changes, how much data is changed (inserted, updated and deleted) at the source between two transfers
- characteristics of the changed data, if it is mainly numbers or dates, it requires less bandwith than for example longer varchar data not to mention BLOB data
- reliability of the connection, if the data must be sent several times, it will affect. Also it affects if the data is buffered on distributor because connection isn't available.
So as you see, it's quite impossible to give any accurate bandwith, but I believe that if you investigate those things, you'll get a clear picture on the data amounts.
Also why not build a test replication environment, replicate the actual data and use replication monitor in SQL Server and performance monitor to see what kind of amounts are transferred.
BTW, I noticed that you're new to CodeProject, welcome! Just to inform you that we have a voting system you can use if you want. Each reply can be marked good/bad and if the type of the post isn't reply, you'll see voting options in the lower right corner of the post from 1 (bad) to 5 (good). Usually these are used to indicate if the answers given to a person were helpful or not.
Mika
|
|
|
|
|
hello,
i have to ask for a sql query:
scenerio:
i have two tables CHATROOMS AND MEMBERS
CHATROOMS table contain columns (ROOM_ID(pk) and ROOM_NAME)
MEMBERS table contain columns (MEMBER_ID, ROOM_ID(fk))
now i have a gridview control in which i have to display all ROOM_IDs along with members init.
like:
ROOM_ID | MEMBERS
room1 | 5
room2 | 7
room3 | 6
room5 | 2
i want a single query for it coz i am binding data to gridview using SQLDATASOURCE control in ASP.net(C#)
the query i am trying is not working!
my query is:
select count(*) as total from MEMBERS where ROOM_ID = (select ROOM_ID from CHATROOMS)
the error on this query is logical that the inner query is returning multiple rows on which the outer WHERE is bound
please help me out i am struct in btw my project....
thanks,
tc
regards,
Sana
$@N@
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
